一、什么是索引

索引（Index），可以看作一個指針，指向表里的數據。當數據庫沒有索引時，查找信息通常是全表掃描；使用了索引，它就會直接引導到數據在表里的準確物理位置。

• 優點：索引的主要目的是提高數據檢索的性能，在數據量比較大時尤其明顯。

• 缺點：

  1. 索引通常與相應的表是分開保存的，索引也會占據物理存儲空間，而且可能比表本身還大。
  2. 索引的維護（索引項的添加和刪除）有時間消耗，會影響數據檢索的速度。

當為字段添加了索引后，索引里記錄了與被索引字段相關聯的位置值。當表里添加新數據時，索引里也會添加新項。如下圖所示： 

 

假設執行

SELECT * FROM table_name WHERE name='SMITH';

• 1

• 1

由于 where 條件里的 name 字段已經設置了索引，所以會首先在索引里搜索'SMITH'，找到以后直接返回被搜索數據在表里的實際位置。

二、創建索引

創建索引使用CREATE INDEX命令或者ALTER TABLE...ADD...命令。數據庫里的表可以創建多種類型的索引。

1. 單字段索引

單字段索引是基于一個字段創建的：

CREATE INDEX index_name ON table_name(column_name);

• 1

• 1

如果某個字段經常在 where 子句作為單獨的查詢條件，那么它的單字段索引是最有效的。

2. 唯一索引

唯一索引不允許被索引字段具有重復值，除此之外，它與普通索引的功能一樣。

CREATE UNIQUE INDEX index_name ON table_name(column_name);

• 1

• 1

唯一索引用于改善性能和保證數據完整性。

3. 組合索引

組合索引是基于兩個或多個字段創建的：

CREATE INDEX index_name ON table_name(column1, column2);

• 1

• 1

如果經常在where子句里聯合使用兩個或多個字段進行查詢，可以考慮創建組合索引。

4. 隱含索引

隱含索引是數據庫服務程序在創建對象時自動創建的。比如，數據庫會為主鍵約束和唯一性約束自動創建索引。

為什么給主鍵約束和唯一性約束自動創建索引？

主鍵約束和唯一性約束都要求字段的每個值都唯一。當用戶向表中添加新記錄時，為了有效地檢查新值在成百上千的記錄里是否唯一，對應的字段必須被索引。

三、刪除索引

MySQL中刪除索引使用ALTER TABLE或者DROP INDEX命令。兩者的功能是一樣的，事實上，DROP INDEX語句在內部被映射到一個ALTER TABLE語句中。

方法一：

ALTER TABLE table_name DROP INDEX index_name;

• 1

• 1

方法二：

DROP INDEX index_name ON table_name;

• 1

• 1

四、何時（不）應該使用索引

考慮使用索引的情況：

1. 一般來說，大多數用于表結合的字段都應該設置索引。比如，外鍵經常用于與父表的結合，適合設置索引。

2. 經常在order by和group by里引用的字段應該考慮設置索引，因為索引會自動進行排序，簡化了實際的排序操作，提高了輸出結果的速度。

3. 具有大量唯一值得字段，或是在where子句里會返回很小部分記錄的字段，都可以考慮設置索引。

索引的初衷是提高數據檢索的性能，但不要認為使用索引就能解決所有的問題，在一些情況下索引可能根本不會改善性能而只是占據磁盤空間。

1. 索引不應該用于小規模的表。

2. 當字段在where子句里作為條件會返回表里的大部分記錄時，該字段不適合設置索引。

3. 包含大量NULL值的字段不應該設置索引。索引對在不同記錄中包含不同數據的字段特別有效，字段中過多的NULL值會嚴重影響索引的運行效率。

4. 經常被更新的字段不應該設置索引。因為對索引的維護會變得很繁重。