現(xiàn)在主流網站都支持手機號登錄，如何在手機號這樣的字符串字段建立合適的索引呢？
假設，你現(xiàn)在維護一個支持郵箱登錄的系統(tǒng)，用戶表是這么定義的：
create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;
要使用郵箱登錄，會有語句：
select f1, f2 from SUser where email='xxx';
若email字段無索引，該語句只能全表掃描。
MySQL支持前綴索引，可定義字符串的一部分作為索引。
若創(chuàng)建索引的語句不指定前綴長度，那么索引默認包含整個字符串。
比如，這倆在email字段創(chuàng)建索引的語句：
alter table SUser add index index1(email);
alter table SUser add index index2(email(6));
- 第一個語句創(chuàng)建的index1索引，包含每個記錄的整個字符串
- 第二個語句創(chuàng)建的index2索引，對每個記錄都只取前6個字節(jié)
可見，email(6)索引結構中每個郵箱字段都只取前6字節(jié)（zhangs），占用空間更小，這就是前綴索引優(yōu)勢。
這同時帶來損失：可能會增加額外的記錄掃描次數(shù)。
看看下面這語句，在這倆索引定義分別怎么執(zhí)行。select id,name,email from SUser where email='zhangssxyz@xxx.com'; 如果使用index1，執(zhí)行順序如下：
- 從index1索引樹找到滿足索引值 ‘zhangssxyz@xxx.com’的記錄，取得ID2的值
- 到主鍵上查到主鍵值是ID2的行，判斷email值是正確的，將改行記錄加入結果集
- 取index1索引樹上剛剛查到位置的下條記錄，發(fā)現(xiàn)已不滿足email=’zhangssxyz@xxx.com’條件，結束循環(huán)
該過程，只需回主鍵索引取一次數(shù)據(jù)，所以系統(tǒng)認為只掃描一行。
如果使用是index2，執(zhí)行順序如下： - 從index2索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是ID1
- 到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’zhangssxyz@xxx.com’，該行記錄丟棄
- 取index2上剛剛查到的位置的下條記錄，仍是’zhangs’，取出ID2，再到ID索引取整行判斷，這次值對，將該行記錄加入結果集
- 重復上一步，直到在idxe2上取值不是’zhangs’，結束循環(huán)結束
該過程，要回主鍵索引取4次數(shù)據(jù)，即掃描4行。
對比發(fā)現(xiàn)，使用前綴索引，可能導致查詢語句讀數(shù)據(jù)的次數(shù)變多。
但對該查詢語句，如果定義index2不是email(6)而是email(7），即取email字段前7字節(jié)構建索引，即滿足前綴’zhangss’記錄只有一個，也能直接查到ID2，只掃描一行結束。
即使用前綴索引，定義好長度，就可做到既節(jié)省空間，又不用增加額外太多的查詢成本。
要給字符串創(chuàng)建前綴索引1 確定前綴長度 在建立索引時我們關注的是區(qū)分度，區(qū)分度越高越好。區(qū)分度越高，重復的鍵值越少。因此可通過統(tǒng)計索引上有多少不同值判斷要使用多長前綴。
可使用如下語句，計算該列上有多少不同值select count(distinct email) as L from SUser; 依次選取不同長度前綴來測該值，比如看4~7個字節(jié)前綴索引：select 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; 使用前綴索引可能會損失區(qū)分度，所以需要預先設定一個可接受損失比例，比如5%。
然后，在返回的L4~L7中，找出不小于 L * 95%的值，假設L6、L7都滿足時，即可選擇前綴長度最短為6。2 前綴索引對覆蓋索引的影響 看如下SQL：select id,email from SUser where email='zhangssxyz@xxx.com'; 與前例SQL語句：select id,name,email from SUser where email='zhangssxyz@xxx.com'; 相比，該語句只要求返回id和email。
若使用index1，可利用覆蓋索引，從index1查到結果后直接返回，不需回到ID索引再查一次。
而若使用index2（email(6)），得回ID索引再判斷email字段值。
即使將index2定義改為email(18)，雖然index2已包含所有信息，但InnoDB還是要回id索引再查，因為系統(tǒng)并不確定前綴索引的定義是否截斷了完整信息。
即前綴索引根本用不上覆蓋索引對查詢的優(yōu)化，這也是選擇是否使用前綴索引時需要考慮的因素。3 其他方案 對類似郵箱這樣字段，前綴索引可能還行。但遇到前綴區(qū)分度不好的，怎么辦？
比如身份證號18位，前6位是地址碼，所以同縣人身份證號前6位一般相同。
假設維護數(shù)據(jù)庫是個市公民信息系統(tǒng)，若對身份證號做長度6前綴索引，區(qū)分度非常低。
可能需創(chuàng)建長度12以上前綴索引，才能夠滿足區(qū)分度要求。
但索引選取越長，占磁盤空間越大，相同數(shù)據(jù)頁能放下索引值越少，查詢效率就越低。
- 若能確定業(yè)務需求只有按身份證進行等值查詢的需求，還有沒有別的處理方法，既可占用更小空間，也能達到相同查詢效率？
Yes！
第一種方式使用3.1 倒序存儲 如果存儲身份證號時把它倒過來存，每次查詢這么寫：select field_list from t where id_card = reverse('input_id_card_string'); 由于身份證號最后6位沒有地址碼這樣重復邏輯，所以最后6位可能提供足夠的區(qū)分度。
實踐中也別忘記使用count(distinct)驗證區(qū)分度哦！
第二種方式是使用3.2 hash字段 可在表再創(chuàng)建整數(shù)字段，保存身份證的校驗碼，同時在該字段創(chuàng)建索引。alter table t add id_card_crc int unsigned, add index(id_card_crc); 每次插新記錄時，同時用crc32()函數(shù)得到校驗碼填到該新字段。
由于校驗碼可能存在沖突，即兩不同身份證號crc32()所得結果可能相同（哈希沖突），所以查詢語句where部分要判斷id_card值是否精確相同。select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string' 這索引長度變4字節(jié)，比原來小很多。3.3 倒序存儲和使用hash字段異同點 相同點 都不支持范圍查詢。 - 倒序存儲的字段上創(chuàng)建的索引
按倒序字符串的方式排序，已無法利用索引查出身份證號碼在[ID_X, ID_Y]的所有市民 - hash字段也只支持等值查詢區(qū)別 占用的額外空間
- 倒序存儲在主鍵索引上，不會消耗額外存儲空間
當然，倒序存儲使用4字節(jié)前綴長度應該不夠，若再長點，這消耗和hash字段也差不多了 - hash字段需要加個字段CPU消耗
- 倒序方式每次讀寫時，都需額外調用次reverse函數(shù)
- hash字段需額外調用一次crc32()函數(shù)
若只從這倆函數(shù)計算復雜度看，reverse函數(shù)額外消耗CPU資源較少。查詢效率 - hash字段查詢性能較穩(wěn)定
因為crc32值雖然會沖突，但概率很小，可認為每次查詢的平均掃描行數(shù)接近1 - 倒序存儲
還是前綴索引，即還是會增加掃描行數(shù)總結 字符串字段創(chuàng)建索引的場景，可使用的方式如下：
- 直接創(chuàng)建完整索引，這樣可能比較占用空間
- 創(chuàng)建前綴索引，節(jié)省空間，但增加查詢掃描次數(shù)，且不能使用覆蓋索引
- 倒序存儲，再創(chuàng)建前綴索引，用于繞過字符串本身前綴的區(qū)分度不足缺陷
- 創(chuàng)建hash字段索引，查詢性能穩(wěn)定，有額外存儲和計算消耗，跟第三種方式一樣不支持范圍掃描
實際應用中，根據(jù)業(yè)務字段的特點選擇使用哪種方式。思考題 維護學生信息數(shù)據(jù)庫，學生登錄名的統(tǒng)一格式是”學號@gmail.com”
學號的則是：十五位的數(shù)字，其中前三位是所在城市編號、第四到第六位是學校編號、第七位到第十位是入學年份、最后五位是順序編號。
系統(tǒng)登錄時輸入登錄名和密碼，驗證正確后才能繼續(xù)使用系統(tǒng)。
只考慮登錄驗證，怎么設計這個登錄名的索引呢？
上期我留給你的問題是，給一個學號字段創(chuàng)建索引，有哪些方法。
由于學號規(guī)則，無論正向反向前綴索引，重復度都較高。
因為維護的只是一個學校的，因此前面6位（其中，前三位是所在城市編號、第四到第六位是學校編號）固定，郵箱后綴都是@gamil.com，因此可只存入學年份加順序編號，長度9位。
在此基礎，可用數(shù)字型存這9位數(shù)字。比如201100001，只需占4字節(jié)。其實這就是種hash，只是用最簡單轉換規(guī)則：字符串轉數(shù)字，而剛好我們設定背景，可保證轉換結果唯一。
當然了，一個學校的總人數(shù)這種數(shù)據(jù)量，50年才100萬學生，這表肯定是小表。為了業(yè)務簡單，直接存原來的字符串。“優(yōu)化成本和收益”的思想。
- 參考
《MySQL 實戰(zhàn) 45 講》
