系列文章
六、五分鐘，讓你明白MySQL是怎么選擇索引《死磕MySQL系列 六》
相信大多數(shù)小伙伴跟咔咔一樣，給字符串添加索引從未設(shè)置過長度，今天就來聊聊如何正確的給字符串加索引。
一、如何建立索引
大多數(shù)系統(tǒng)都會存在用戶表，并且系統(tǒng)初始設(shè)計使用了手機號碼登錄的。
這是產(chǎn)品提出了一個需求，讓系統(tǒng)也可以支持郵箱登錄。
肯定知道的是若不給郵箱字段添加索引執(zhí)行查詢是會全表掃描。
此時你心里竊喜這還不簡單，給郵箱字段加個索引完事唄！但要做到復(fù)雜的需求做好，簡單的需求要最好，減輕一切對系統(tǒng)的壓力。
此時的你拿起鍵盤就執(zhí)行了alter table table_name add index idx_field (field)
有部分小伙伴不喜歡命令行創(chuàng)建索引，喜歡使用phpmyadmin工具來操作MySQL，那么在建立索引時有沒有發(fā)現(xiàn)后邊可以設(shè)置大小呢？
通過上邊給大家展示的圖片知道字符串建立索引是可以定義長度的，那么兩者有什么區(qū)別。
使用命令行alter table table_name add index idx_field (field)直接創(chuàng)建的索引默認(rèn)是包含整個字符串。
若這樣執(zhí)行就指定了索引前綴長度alter table table_name add index idx_field (field(6))
一圖解千愁，看一下建立的兩個索引結(jié)構(gòu)是什么樣的。
索引一結(jié)構(gòu)圖
<figcaption style="margin-top: 5px; text-align: center; color: #888; display: block; font-size: 12px;">索引一結(jié)構(gòu)圖</figcaption>
索引二結(jié)構(gòu)圖
<figcaption style="margin-top: 5px; text-align: center; color: #888; display: block; font-size: 12px;">索引二結(jié)構(gòu)圖</figcaption>
從圖中可以看到，指定了索引長度為6那么就只取郵箱字段的前6個字段，相對索引包含整個字符串來說每個節(jié)點存儲的數(shù)據(jù)會更多。
索引那篇文章也給大家說了建立索引在合適的范圍內(nèi)越小越好。
萬物皆兩面，有壞就有好，第六期文章誤選索引的因素之一就是掃描行數(shù)。
索引長度減少帶來的影響就是索引基數(shù)變大，從而增加額外的掃描記錄數(shù)（執(zhí)行explain的row字段）。
此時要執(zhí)行select id,name,email from mac_user where email='1397393964@qq.com';
給整個字符串添加索引執(zhí)行流程
1、從email索引樹找到滿足1397393964@qq.com的記錄，得到主鍵ID為1
2、根據(jù)ID為1到主鍵索引樹找到這條記錄并判斷email是否正確，將這行記錄假如結(jié)果集。
3、重復(fù)第一步，直到不滿足查詢條件，循環(huán)結(jié)束。
指定索引長度執(zhí)行流程
1、從email索引樹找到滿足139739的記錄，得到主鍵ID為1
2、根據(jù)ID為1到主鍵索引樹找到這條記錄并判斷email不正確，丟棄這行記錄。
3、在email索引樹找剛剛查詢的下一條記錄，發(fā)現(xiàn)還是139739，去除ID2，再到ID的索引樹進行判斷，當(dāng)值對后加入結(jié)果集。
4、再繼續(xù)重復(fù)上一步，直到不滿足查詢條件，循環(huán)結(jié)束。
結(jié)論
在模擬執(zhí)行流程過程中很容易就發(fā)現(xiàn)，使用前綴索引會導(dǎo)致讀取數(shù)據(jù)的次數(shù)增加，那是不是就代表使用前綴索引會增加查詢代價呢？
肯定不是的，試想此時定義的長度是6那么設(shè)置為7或者8呢！是不是會好很多，圖中的案例為了方便設(shè)置了三個一樣的數(shù)據(jù)，但實際情況基本不會出現(xiàn)這樣的情況。
建立索引關(guān)注的是區(qū)分度，只有區(qū)分度越高，重復(fù)值就越少，查詢效率就越高。
所以使用前綴索引，只要定義好長度，就可以坐到既節(jié)省空間，又不用額外增加太多的查詢代價。
二、創(chuàng)建索引如何確定使用多長的前綴
MySQL中關(guān)鍵詞distinct可以返回本列不同的結(jié)果集。
例如查詢email列有多少個不同的值select count(distinct email) as num from mac_user。
如何計算列不同前綴有多少行
結(jié)合MySQL自帶的函數(shù)left來實現(xiàn)，例如select count(distinct left (email,4)) as num4 from mac_user，截取email的前四個字符串計算有多少行。
再用這個值去除總數(shù)得到的就是比例，根據(jù)業(yè)務(wù)情況來判斷多少比例可以。
三、使用前綴索引的影響
使用前綴索引會增加掃描行數(shù)，同時也會使覆蓋索引失效。
為什么會影響覆蓋索引？
若執(zhí)行語句為select id,email from mac_user where email = '1397393964@qq.com'。
使用整個字符串索引結(jié)構(gòu)查詢可以使用覆蓋索引，從email索引獲取到結(jié)果就直接返回了，不用再進行回表。
若使用前綴索引在email索引獲取到結(jié)果后還需要回到id索引在查一下判斷查詢的email的值是否正確。
哪怕是設(shè)置了大于了email的長度也會回表再進行判斷，因為MySQL并不知道定義的前綴是否截取了完整信息。
結(jié)論
使用前綴索引會增加掃描行數(shù)，同樣也使用不到覆蓋索引。這個因素是你選擇是否使用前綴索引要考慮的一個因素。
如果你不知道使用前綴索引還是全字符串索引，本地進行測試選一個合適的方案上到生產(chǎn)環(huán)境即可。
四、如何把不可以變?yōu)榭梢允褂?/h2>
假設(shè)身份認(rèn)證系統(tǒng)存儲的是身份證號，應(yīng)該都知道身份證號前6位是地址碼，同縣的身份證號前6位一般是一樣的。
這樣使用前綴索引的話區(qū)分度會十分低，不但沒有起到加速查詢的作用，反而會造成索引區(qū)分度不大影響查詢性能。
若把索引長度越長則每個節(jié)點存放的索引值就越少，查詢效率也會變的低效。
如果解決這種場景
第一種方案
存儲數(shù)據(jù)時將數(shù)據(jù)倒敘存儲，查詢時在正序處理一下即可
第二種方案
在表中新增一個字段，存儲數(shù)據(jù)的hash值，給hash添加前綴索引。
區(qū)別
使用這兩種方案共同點都不支持范圍查詢，都只能等值查詢。
從占用空間來看：倒敘方式不會增加額外的存儲空間，hash會增加一個字段。兩者在空間不相上下
從CPU消耗來看：倒敘需要使用函數(shù)reverse，hash需要使用crc32 ，reverse消耗會小
從查詢效率來看：hash查詢更穩(wěn)定，crc32計算的值雖有沖突但概率非常小，基本每次查詢的平均掃描行數(shù)接近1。而倒敘使用的前綴索引方式，還會增加掃描行數(shù)。
五、總結(jié)
直接給字符串創(chuàng)建占用空間。
創(chuàng)建前綴索引，節(jié)省空間，會增加掃描行數(shù)，無法使用覆蓋索引。
倒敘存儲，創(chuàng)建前綴索引解決區(qū)分度不大的問題。
使用hash方式，查詢穩(wěn)定，不支持范圍查詢。
“
堅持學(xué)習(xí)、堅持寫作、堅持分享是咔咔從業(yè)以來所秉持的信念。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來一點幫助，我是咔咔，下期見。
”
