兩種：
- 全字段索引
- 字首索引
舉例：
mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;
可以對 email 字段建立全字段索引，或者字首索引。
mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));
差別
1、全字段索引占用空間大，字首索引占用空間小；
2、全字段索引查詢效率高，字首索引則會增加額外的記錄掃描次數。
執行過程
1、全字段索引
① 從 index1 索引樹中找到索引值是
[email protected]
的記錄，然後得到主鍵值；
② 根據主鍵值擷取到該行的完整資料（回表），再判斷 email 是否滿足條件，将這行記錄加入結果集；
③ 沿着索引樹繼續查找下一條滿足條件的資料，若不滿足，循環結束；
2、字首索引
① 從 index2 索引樹上查找索引值是
zhangs
的記錄，找到一條後，得到主鍵值；
② 根據主鍵值擷取到該行的完整資料（回表），再判斷 email 是否滿足條件，将這行記錄加入結果集；
③ 沿着索引樹繼續查找下一條滿足條件的資料，發現仍然滿足條件，重複上面的操作；
④ 重複上一步，直到在 index2 上取到的值不滿足條件，循環結束。
很明顯，使用字首索引，導緻查詢次數增多。
如何減少字首索引查詢次數？
區分度，區分度越高，字首重複的可能性越小，進而，查詢次數就越少。
通過如下語句，可以查詢到不通字首長度，分别有多少個不同的值：
mysql> select
count(distinct email) as L
count(distinct left(email,4)）as L4,
count(distinct left(email,5)）as L5,
count(distinct left(email,6)）as L6,
count(distinct left(email,7)）as L7,
from SUser;
自己可以預先設定一個可以接受的重複比例，比如大于 L * 95%。
字首索引對覆寫索引的影響
使用字首索引将無法利用覆寫索引的優化。
查詢時，系統并不确定字首索引的定義是否截斷了完整資訊。
字首索引的優化
1、倒序存儲
适合字段值前面部分重複度高，後半部分重複度低，這時可以倒序存儲資料。
查詢時可以這麼寫：
2、做 hash
新增一個字段，專門存儲字段的 hash 值：
每次插入資料時，都要調用
crc32()
這個函數得到校驗碼填到這個新字段。
這個字段有可能重複，需要聯合判斷 id_card 的值是否精确相同。
二者的差別
1、都不支援範圍查詢
2、hash 字段需要額外的空間
3、CPU 消耗：倒序插入時需要額外調用 reverse 函數，hash 需要調用 crc32() 函數。reverse 函數消耗的 CPU 更小一些；
4、hash 字段方式的查詢效率更高，因為計算出來的 hash 值重複的可能性較小，掃描次數接近于 1
總結
1、直接建立完整索引，這樣可能比較占用空間；
2、建立字首索引，節省空間，但會增加查詢掃描次數，并且不能使用覆寫索引；
3、倒序存儲，再建立字首索引，用于繞過字元串本身字首的區分度不夠的問題；
4、建立 hash 字段索引，查詢性能穩定，有額外的存儲和計算消耗，跟第三種方式一樣，都不支援範圍掃描。
如何選擇？
當資料量大時，一個學校每年預估 2 萬新生，50 年才 100 萬記錄，能節省多少空間，直接全字段索引。省去了開發轉換及局限性風險，碰到超大量迫不得已再用後兩種辦法。從業務量預估優化和收益，這不失為一個不錯的想法。
關注本公衆号，背景回複「2018」即可擷取傳智播客 2018 最新 Python 和 Java 教程。
公衆号提供CSDN資源免費下載下傳服務！