mysql 索引( mysql index )

工程師WWW發表於2014-10-10
MySql索引Index

以下是查閱《mysql_administrators_bible》的摘錄和總結

 

 

索引 mysql index

 

 

        索引是一種資料結構，可以是B-tree, R-tree, 或者 hash 結構。其中R-tree 常用於查詢比較接近的資料；B-trees適合用於查詢某範圍內的資料，可以很快的從當前資料找到下條資料；hash結構則適用於隨機訪問的場合，查詢每條資料的時間幾乎相同。顯然，若要查詢某個時間段的資料，用B-tree結構要比hash結構快好多。
  
       優化查詢的有效方法是為經常查詢的欄位建立索引，如無索引查詢資料時，會遍歷整張表(多麼恐怖啊);若有了索引查詢會容易很多。當進行 UPDATE, DELETE, 以及 INSERT 操作時，mysql會自動更新索引資訊。

1.建立和刪除索引( Creating and dropping indexes )

建立：

[xhtml] view plaincopy
  1. mysql> USE sakila;  
  2. Database changed  
  3. mysql> CREATE INDEX idx_actor_first_name ON actor (first_name);  
  4. 或者  
  5. mysql> ALTER TABLE actor ADD INDEX idx_actor_first_name (first_name);  

PS:當用 create index 建立索引時,必須指定索引的名字，否則mysql會報錯；
  用 ALTER TABLE 建立索引時，可以不指定索引名字，若不指定mysql會自動生成索引名字
  
建立索引時，若不想用儲存引擎的預設索引型別，可以指定索引的型別:

[css] view plaincopy
  1. mysql> ALTER TABLE temp_index  
  2.  ADD INDEX (first_name),  
  3.  ADD INDEX lname (last_name) USING BTREE  

刪除：

[css] view plaincopy
  1. DROP INDEX indexname ON tblname  
  2. mysql> DROP INDEX idx_actor_fname ON actor;  
  3. mysql> ALTER TABLE actor DROP INDEX idx_actor_fname;  

2.索引型別


BTREE    適合連續讀取資料
RTREE    適合根據一條資料找附近的資料
HASH      適合隨機讀取資料
FULLTEXT    
SPATIAL

 

 

檢視某個表中存在的索引型別

[css] view plaincopy
  1. mysql> SELECT INDEX_NAME,INDEX_TYPE  
  2. -> FROM INFORMATION_SCHEMA.STATISTICS  
  3. -> WHERE TABLE_NAME=’temp_index’;  
  4. +------------+------------+  
  5. | INDEX_NAME | INDEX_TYPE |  
  6. +------------+------------+  
  7. | first_name | HASH       |  
  8. | lname      | BTREE      |  
  9. +------------+------------+  

3.索引冗餘


如果兩個或者多個索引包含了相同的索引資訊，那麼就存在索引冗餘。

不同型別的索引有不同的索引冗餘判斷:
(1) SPATIAL 索引
SPATIAL 只能是一個簡單索引，不能說複合索引；存在冗餘的情況是同一個欄位有兩個索引。
(2) FULLTEXT 索引
若一個FULLTEXT索引是另一個索引的子集(不考慮欄位順序),則存在冗餘。
例如: 表中包含如下兩個索引 
■ (field1)
■ (field1, field2)
(3) HASH 索引
若一個索引在不考慮欄位順序的情況下，索引欄位相同，則存在冗餘。
例如：表中包含如下兩個索引 
■ (field1, field2, field3)
■ (field1, field3, field2)
(4) BTREE 索引
若一個索引是另一個索引的子集(考慮欄位順序),則存在冗餘。
例如：表中包含如下兩個索引 
■ (field2)
■ (field2, field1)

PS：若兩個或多個索引有相同的索引欄位和欄位順序，但是有不同的索引型別，這樣的索引是不冗餘的。因為每種索引型別有自己的索引值。

相關文章