系列文章
六、五分鐘，讓你明白MySQL是怎麼選擇索引《死磕MySQL系列 六》
相信大多數小夥伴跟咔咔一樣，給字串新增索引從未設定過長度，今天就來聊聊如何正確的給字串加索引。
一、如何建立索引
大多數系統都會存在使用者表，並且系統初始設計使用了手機號碼登入的。
這是產品提出了一個需求，讓系統也可以支援郵箱登入。
肯定知道的是若不給郵箱欄位新增索引執行查詢是會全表掃描。
此時你心裡竊喜這還不簡單，給郵箱欄位加個索引完事唄！但要做到複雜的需求做好，簡單的需求要最好，減輕一切對系統的壓力。
此時的你拿起鍵盤就執行了alter table table_name add index idx_field (field)
有部分小夥伴不喜歡命令列建立索引，喜歡使用phpmyadmin工具來操作MySQL，那麼在建立索引時有沒有發現後邊可以設定大小呢？
通過上邊給大家展示的圖片知道字串建立索引是可以定義長度的，那麼兩者有什麼區別。
使用命令列alter table table_name add index idx_field (field)直接建立的索引預設是包含整個字串。
若這樣執行就指定了索引字首長度alter table table_name add index idx_field (field(6))
一圖解千愁，看一下建立的兩個索引結構是什麼樣的。
索引一結構圖
索引二結構圖
從圖中可以看到，指定了索引長度為6那麼就只取郵箱欄位的前6個欄位，相對索引包含整個字串來說每個節點儲存的資料會更多。
索引那篇文章也給大家說了建立索引在合適的範圍內越小越好。
萬物皆兩面，有壞就有好，第六期文章誤選索引的因素之一就是掃描行數。
索引長度減少帶來的影響就是索引基數變大，從而增加額外的掃描記錄數（執行explain的row欄位）。
此時要執行select id,name,email from mac_user where email='1397393964@qq.com';
給整個字串新增索引執行流程
1、從email索引樹找到滿足1397393964@qq.com的記錄，得到主鍵ID為1
2、根據ID為1到主鍵索引樹找到這條記錄並判斷email是否正確，將這行記錄假如結果集。
3、重複第一步，直到不滿足查詢條件，迴圈結束。
指定索引長度執行流程
1、從email索引樹找到滿足139739的記錄，得到主鍵ID為1
2、根據ID為1到主鍵索引樹找到這條記錄並判斷email不正確，丟棄這行記錄。
3、在email索引樹找剛剛查詢的下一條記錄，發現還是139739，去除ID2，再到ID的索引樹進行判斷，當值對後加入結果集。
4、再繼續重複上一步，直到不滿足查詢條件，迴圈結束。
結論
在模擬執行流程過程中很容易就發現，使用字首索引會導致讀取資料的次數增加，那是不是就代表使用字首索引會增加查詢代價呢？
肯定不是的，試想此時定義的長度是6那麼設定為7或者8呢！是不是會好很多，圖中的案例為了方便設定了三個一樣的資料，但實際情況基本不會出現這樣的情況。
建立索引關注的是區分度，只有區分度越高，重複值就越少，查詢效率就越高。
所以使用字首索引，只要定義好長度，就可以坐到既節省空間，又不用額外增加太多的查詢代價。
二、建立索引如何確定使用多長的字首
MySQL中關鍵詞distinct可以返回本列不同的結果集。
例如查詢email列有多少個不同的值select count(distinct email) as num from mac_user。
如何計算列不同字首有多少行
結合MySQL自帶的函式left來實現，例如select count(distinct left (email,4)) as num4 from mac_user，擷取email的前四個字串計算有多少行。
再用這個值去除總數得到的就是比例，根據業務情況來判斷多少比例可以。
三、使用字首索引的影響
使用字首索引會增加掃描行數，同時也會使覆蓋索引失效。
為什麼會影響覆蓋索引？
若執行語句為select id,email from mac_user where email = '1397393964@qq.com'。
使用整個字串索引結構查詢可以使用覆蓋索引，從email索引獲取到結果就直接返回了，不用再進行回表。
若使用字首索引在email索引獲取到結果後還需要回到id索引在查一下判斷查詢的email的值是否正確。
哪怕是設定了大於了email的長度也會回表再進行判斷，因為MySQL並不知道定義的字首是否擷取了完整資訊。
結論
使用字首索引會增加掃描行數，同樣也使用不到覆蓋索引。這個因素是你選擇是否使用字首索引要考慮的一個因素。
如果你不知道使用字首索引還是全字串索引，本地進行測試選一個合適的方案上到生產環境即可。
四、如何把不可以變為可以使用
假設身份認證系統儲存的是身份證號，應該都知道身份證號前6位是地址碼，同縣的身份證號前6位一般是一樣的。
這樣使用字首索引的話區分度會十分低，不但沒有起到加速查詢的作用，反而會造成索引區分度不大影響查詢效能。
若把索引長度越長則每個節點存放的索引值就越少，查詢效率也會變的低效。
如果解決這種場景
第一種方案
儲存資料時將資料倒敘儲存，查詢時在正序處理一下即可
第二種方案
在表中新增一個欄位，儲存資料的hash值，給hash新增字首索引。
區別
使用這兩種方案共同點都不支援範圍查詢，都只能等值查詢。
從佔用空間來看：倒敘方式不會增加額外的儲存空間，hash會增加一個欄位。兩者在空間不相上下
從CPU消耗來看：倒敘需要使用函式reverse，hash需要使用crc32 ，reverse消耗會小
從查詢效率來看：hash查詢更穩定，crc32計算的值雖有衝突但概率非常小，基本每次查詢的平均掃描行數接近1。而倒敘使用的字首索引方式，還會增加掃描行數。
五、總結
直接給字串建立佔用空間。
建立字首索引，節省空間，會增加掃描行數，無法使用覆蓋索引。
倒敘儲存，建立字首索引解決區分度不大的問題。
使用hash方式，查詢穩定，不支援範圍查詢。
“堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助，我是咔咔，下期見。
”