[TOC]
現(xiàn)在，幾乎所有的系統(tǒng)都支持郵箱登錄可霎，如何在郵箱這樣的字段上建立合理的索引魄鸦，是我們今天要討論的問題。

假設(shè)癣朗，你現(xiàn)在維護(hù)一個(gè)支持郵箱登錄的系統(tǒng)拾因，用戶表是這么定義的：

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb; 

由于要使用郵箱登錄，所以業(yè)務(wù)代碼中一定會(huì)出現(xiàn)類似于這樣的語(yǔ)句：

mysql> select f1, f2 from SUser where email='xxx';

從第 4 和第 5 篇講解索引的文章中旷余，我們可以知道绢记，如果 email 這個(gè)字段上沒有索引，那么這個(gè)語(yǔ)句就只能做全表掃描正卧。

同時(shí)蠢熄，MySQL 是支持前綴索引的，也就是說炉旷，你可以定義字符串的一部分作為索引签孔。默認(rèn)地，如果你創(chuàng)建索引的語(yǔ)句不指定前綴長(zhǎng)度窘行，那么索引就會(huì)包含整個(gè)字符串饥追。

比如，這兩個(gè)在 email 字段上創(chuàng)建索引的語(yǔ)句：

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第一個(gè)語(yǔ)句創(chuàng)建的 index1 索引里面罐盔，包含了每個(gè)記錄的整個(gè)字符串但绕；而第二個(gè)語(yǔ)句創(chuàng)建的 index2 索引里面，對(duì)于每個(gè)記錄都是只取前 6 個(gè)字節(jié)惶看。

那么捏顺，這兩種不同的定義在數(shù)據(jù)結(jié)構(gòu)和存儲(chǔ)上有什么區(qū)別呢？如圖 2 和 3 所示碳竟，就是這兩個(gè)索引的示意圖草丧。

image.png

image.png

從圖中你可以看到，由于 email(6) 這個(gè)索引結(jié)構(gòu)中每個(gè)郵箱字段都只取前 6 個(gè)字節(jié)（即：zhangs）莹桅，所以占用的空間會(huì)更小昌执，這就是使用前綴索引的優(yōu)勢(shì)。

但诈泼，這同時(shí)帶來的損失是懂拾，可能會(huì)增加額外的記錄掃描次數(shù)。

接下來铐达，我們?cè)倏纯聪旅孢@個(gè)語(yǔ)句岖赋，在這兩個(gè)索引定義下分別是怎么執(zhí)行的。

select id,name,email from SUser where email='zhangssxyz@xxx.com';

如果使用的是 index1（即 email 整個(gè)字符串的索引結(jié)構(gòu)）瓮孙，執(zhí)行順序是這樣的：

1. 從 index1 索引樹找到滿足索引值是’zhangssxyz@xxx.com’的這條記錄唐断，取得 ID2 的值选脊；
2. 到主鍵上查到主鍵值是 ID2 的行，判斷 email 的值是正確的脸甘，將這行記錄加入結(jié)果集恳啥；
3. 取 index1 索引樹上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)已經(jīng)不滿足 email='zhangssxyz@xxx.com’的條件了丹诀，循環(huán)結(jié)束钝的。

這個(gè)過程中，只需要回主鍵索引取一次數(shù)據(jù)铆遭，所以系統(tǒng)認(rèn)為只掃描了一行硝桩。

如果使用的是 index2（即 email(6) 索引結(jié)構(gòu)），執(zhí)行順序是這樣的：

1. 從 index2 索引樹找到滿足索引值是’zhangs’的記錄枚荣，找到的第一個(gè)是 ID1碗脊；
2. 到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不是’zhangssxyz@xxx.com’橄妆，這行記錄丟棄望薄；
3. 取 index2 上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)仍然是’zhangs’呼畸，取出 ID2，再到 ID 索引上取整行然后判斷颁虐，這次值對(duì)了蛮原，將這行記錄加入結(jié)果集；
4. 重復(fù)上一步另绩，直到在 idxe2 上取到的值不是’zhangs’時(shí)儒陨，循環(huán)結(jié)束。

在這個(gè)過程中笋籽，要回主鍵索引取 4 次數(shù)據(jù)蹦漠，也就是掃描了 4 行。

通過這個(gè)對(duì)比车海，你很容易就可以發(fā)現(xiàn)笛园，使用前綴索引后，可能會(huì)導(dǎo)致查詢語(yǔ)句讀數(shù)據(jù)的次數(shù)變多侍芝。

但是研铆，對(duì)于這個(gè)查詢語(yǔ)句來說，如果你定義的 index2 不是 email(6) 而是 email(7）州叠，也就是說取 email 字段的前 7 個(gè)字節(jié)來構(gòu)建索引的話棵红，即滿足前綴’zhangss’的記錄只有一個(gè)，也能夠直接查到 ID2咧栗，只掃描一行就結(jié)束了逆甜。

也就是說使用前綴索引虱肄，定義好長(zhǎng)度，就可以做到既節(jié)省空間交煞，又不用額外增加太多的查詢成本咏窿。

于是，你就有個(gè)問題：當(dāng)要給字符串創(chuàng)建前綴索引時(shí)错敢，有什么方法能夠確定我應(yīng)該使用多長(zhǎng)的前綴呢翰灾？

實(shí)際上，我們?cè)诮⑺饕龝r(shí)關(guān)注的是區(qū)分度稚茅，區(qū)分度越高越好纸淮。因?yàn)閰^(qū)分度越高，意味著重復(fù)的鍵值越少亚享。因此咽块，我們可以通過統(tǒng)計(jì)索引上有多少個(gè)不同的值來判斷要使用多長(zhǎng)的前綴。

首先欺税，你可以使用下面這個(gè)語(yǔ)句侈沪，算出這個(gè)列上有多少個(gè)不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次選取不同長(zhǎng)度的前綴來看這個(gè)值晚凿，比如我們要看一下 4~7 個(gè)字節(jié)的前綴索引亭罪，可以用這個(gè)語(yǔ)句：

mysql> 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;

當(dāng)然，使用前綴索引很可能會(huì)損失區(qū)分度歼秽，所以你需要預(yù)先設(shè)定一個(gè)可以接受的損失比例应役，比如 5%。然后燥筷，在返回的 L4~L7 中箩祥，找出不小于 L * 95% 的值，假設(shè)這里 L6肆氓、L7 都滿足袍祖，你就可以選擇前綴長(zhǎng)度為 6。

前綴索引對(duì)覆蓋索引的影響

前面我們說了使用前綴索引可能會(huì)增加掃描行數(shù)谢揪，這會(huì)影響到性能蕉陋。其實(shí)，前綴索引的影響不止如此键耕，我們?cè)倏匆幌铝硗庖粋€(gè)場(chǎng)景寺滚。

你先來看看這個(gè) SQL 語(yǔ)句：

select id,email from SUser where email='zhangssxyz@xxx.com';

與前面例子中的 SQL 語(yǔ)句

select id,name,email from SUser where email='zhangssxyz@xxx.com';

相比，這個(gè)語(yǔ)句只要求返回 id 和 email 字段屈雄。

所以村视，如果使用 index1（即 email 整個(gè)字符串的索引結(jié)構(gòu)）的話，可以利用覆蓋索引酒奶，從 index1 查到結(jié)果后直接就返回了蚁孔，不需要回到 ID 索引再去查一次奶赔。而如果使用 index2（即 email(6) 索引結(jié)構(gòu)）的話，就不得不回到 ID 索引再去判斷 email 字段的值杠氢。

即使你將 index2 的定義修改為 email(18) 的前綴索引站刑，這時(shí)候雖然 index2 已經(jīng)包含了所有的信息，但 InnoDB 還是要回到 id 索引再查一下鼻百，因?yàn)橄到y(tǒng)并不確定前綴索引的定義是否截?cái)嗔送暾畔ⅰ?/p>

也就是說绞旅，使用前綴索引就用不上覆蓋索引對(duì)查詢性能的優(yōu)化了，這也是你在選擇是否使用前綴索引時(shí)需要考慮的一個(gè)因素温艇。

其他方式

對(duì)于類似于郵箱這樣的字段來說因悲，使用前綴索引的效果可能還不錯(cuò)。但是勺爱，遇到前綴的區(qū)分度不夠好的情況時(shí)晃琳，我們要怎么辦呢？

比如琐鲁，我們國(guó)家的身份證號(hào)卫旱，一共 18 位，其中前 6 位是地址碼围段，所以同一個(gè)縣的人的身份證號(hào)前 6 位一般會(huì)是相同的顾翼。

假設(shè)你維護(hù)的數(shù)據(jù)庫(kù)是一個(gè)市的公民信息系統(tǒng)，這時(shí)候如果對(duì)身份證號(hào)做長(zhǎng)度為 6 的前綴索引的話奈泪，這個(gè)索引的區(qū)分度就非常低了暴构。

按照我們前面說的方法，可能你需要?jiǎng)?chuàng)建長(zhǎng)度為 12 以上的前綴索引段磨，才能夠滿足區(qū)分度要求。

但是耗绿，索引選取的越長(zhǎng)苹支，占用的磁盤空間就越大，相同的數(shù)據(jù)頁(yè)能放下的索引值就越少误阻，搜索的效率也就會(huì)越低债蜜。

那么，如果我們能夠確定業(yè)務(wù)需求里面只有按照身份證進(jìn)行等值查詢的需求究反，還有沒有別的處理方法呢寻定？這種方法，既可以占用更小的空間精耐，也能達(dá)到相同的查詢效率狼速。

答案是，有的卦停。

第一種方式是使用倒序存儲(chǔ)向胡。如果你存儲(chǔ)身份證號(hào)的時(shí)候把它倒過來存恼蓬，每次查詢的時(shí)候，你可以這么寫：

mysql> select field_list from t where id_card = reverse('input_id_card_string');

由于身份證號(hào)的最后 6 位沒有地址碼這樣的重復(fù)邏輯僵芹，所以最后這 6 位很可能就提供了足夠的區(qū)分度处硬。當(dāng)然了，實(shí)踐中你不要忘記使用 count(distinct) 方法去做個(gè)驗(yàn)證拇派。

第二種方式是使用 hash 字段荷辕。你可以在表上再創(chuàng)建一個(gè)整數(shù)字段，來保存身份證的校驗(yàn)碼件豌，同時(shí)在這個(gè)字段上創(chuàng)建索引疮方。

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新記錄的時(shí)候，都同時(shí)用 crc32() 這個(gè)函數(shù)得到校驗(yàn)碼填到這個(gè)新字段苟径。由于校驗(yàn)碼可能存在沖突案站，也就是說兩個(gè)不同的身份證號(hào)通過 crc32() 函數(shù)得到的結(jié)果可能是相同的，所以你的查詢語(yǔ)句 where 部分要判斷 id_card 的值是否精確相同棘街。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

這樣蟆盐，索引的長(zhǎng)度變成了 4 個(gè)字節(jié)，比原來小了很多遭殉。

接下來石挂，我們?cè)僖黄鹂纯词褂玫剐虼鎯?chǔ)和使用 hash 字段這兩種方法的異同點(diǎn)。

首先险污，它們的相同點(diǎn)是痹愚，都不支持范圍查詢。倒序存儲(chǔ)的字段上創(chuàng)建的索引是按照倒序字符串的方式排序的蛔糯，已經(jīng)沒有辦法利用索引方式查出身份證號(hào)碼在[ID_X, ID_Y]的所有市民了拯腮。同樣地，hash 字段的方式也只能支持等值查詢蚁飒。

它們的區(qū)別动壤，主要體現(xiàn)在以下三個(gè)方面：

1. 從占用的額外空間來看，倒序存儲(chǔ)方式在主鍵索引上淮逻，不會(huì)消耗額外的存儲(chǔ)空間琼懊，而 hash 字段方法需要增加一個(gè)字段。當(dāng)然爬早，倒序存儲(chǔ)方式使用 4 個(gè)字節(jié)的前綴長(zhǎng)度應(yīng)該是不夠的哼丈，如果再長(zhǎng)一點(diǎn)，這個(gè)消耗跟額外這個(gè) hash 字段也差不多抵消了筛严。
2. 在 CPU 消耗方面醉旦，倒序方式每次寫和讀的時(shí)候，都需要額外調(diào)用一次 reverse 函數(shù)，而 hash 字段的方式需要額外調(diào)用一次 crc32() 函數(shù)髓抑。如果只從這兩個(gè)函數(shù)的計(jì)算復(fù)雜度來看的話咙崎，reverse 函數(shù)額外消耗的 CPU 資源會(huì)更小些。
3. 從查詢效率上看吨拍，使用 hash 字段方式的查詢性能相對(duì)更穩(wěn)定一些褪猛。因?yàn)?crc32 算出來的值雖然有沖突的概率，但是概率非常小羹饰，可以認(rèn)為每次查詢的平均掃描行數(shù)接近 1伊滋。而倒序存儲(chǔ)方式畢竟還是用的前綴索引的方式，也就是說還是會(huì)增加掃描行數(shù)队秩。

小結(jié)

在今天這篇文章中笑旺，我跟你聊了聊字符串字段創(chuàng)建索引的場(chǎng)景。我們來回顧一下馍资，你可以使用的方式有：

1. 直接創(chuàng)建完整索引筒主，這樣可能比較占用空間；
2. 創(chuàng)建前綴索引鸟蟹，節(jié)省空間乌妙，但會(huì)增加查詢掃描次數(shù)，并且不能使用覆蓋索引建钥；
3. 倒序存儲(chǔ)藤韵，再創(chuàng)建前綴索引，用于繞過字符串本身前綴的區(qū)分度不夠的問題熊经；
4. 創(chuàng)建 hash 字段索引泽艘，查詢性能穩(wěn)定，有額外的存儲(chǔ)和計(jì)算消耗镐依，跟第三種方式一樣匹涮，都不支持范圍掃描。

在實(shí)際應(yīng)用中槐壳，你要根據(jù)業(yè)務(wù)字段的特點(diǎn)選擇使用哪種方式焕盟。

好了，又到了最后的問題時(shí)間宏粤。

如果你在維護(hù)一個(gè)學(xué)校的學(xué)生信息數(shù)據(jù)庫(kù)，學(xué)生登錄名的統(tǒng)一格式是”學(xué)號(hào) @gmail.com", 而學(xué)號(hào)的規(guī)則是：十五位的數(shù)字灼卢，其中前三位是所在城市編號(hào)绍哎、第四到第六位是學(xué)校編號(hào)、第七位到第十位是入學(xué)年份鞋真、最后五位是順序編號(hào)崇堰。

系統(tǒng)登錄的時(shí)候都需要學(xué)生輸入登錄名和密碼，驗(yàn)證正確后才能繼續(xù)使用系統(tǒng)。就只考慮登錄驗(yàn)證這個(gè)行為的話海诲，你會(huì)怎么設(shè)計(jì)這個(gè)登錄名的索引呢繁莹？

在上一篇文章最后，我給你留的問題是特幔，為什么經(jīng)過這個(gè)操作序列咨演，explain 的結(jié)果就不對(duì)了？這里蚯斯，我來為你分析一下原因薄风。

delete 語(yǔ)句刪掉了所有的數(shù)據(jù)，然后再通過 call idata() 插入了 10 萬行數(shù)據(jù)拍嵌，看上去是覆蓋了原來的 10 萬行遭赂。

但是，session A 開啟了事務(wù)并沒有提交横辆，所以之前插入的 10 萬行數(shù)據(jù)是不能刪除的撇他。這樣，之前的數(shù)據(jù)每一行數(shù)據(jù)都有兩個(gè)版本狈蚤，舊版本是 delete 之前的數(shù)據(jù)困肩，新版本是標(biāo)記為 deleted 的數(shù)據(jù)。

這樣炫惩，索引 a 上的數(shù)據(jù)其實(shí)就有兩份僻弹。

然后你會(huì)說，不對(duì)啊他嚷，主鍵上的數(shù)據(jù)也不能刪蹋绽，那沒有使用 force index 的語(yǔ)句，使用 explain 命令看到的掃描行數(shù)為什么還是 100000 左右筋蓖？（潛臺(tái)詞卸耘，如果這個(gè)也翻倍，也許優(yōu)化器還會(huì)認(rèn)為選字段 a 作為索引更合適）

是的粘咖，不過這個(gè)是主鍵蚣抗，主鍵是直接按照表的行數(shù)來估計(jì)的。而表的行數(shù)瓮下，優(yōu)化器直接用的是 show table status 的值翰铡。

這個(gè)值的計(jì)算方法，我會(huì)在后面有文章為你詳細(xì)講解讽坏。

image.png

FAQ

刪的時(shí)候锭魔，由于有未提交事務(wù)開啟的一致性視圖read-view，所以導(dǎo)致了存在兩個(gè)數(shù)據(jù)版本的數(shù)據(jù)路呜，貌似優(yōu)化器在"看"二級(jí)索引的時(shí)候迷捧，"看到"了多個(gè)歷史版本的數(shù)據(jù)织咧，錯(cuò)誤以為有很多數(shù)據(jù)

而主鍵索引數(shù)量由于確認(rèn)機(jī)制不同，數(shù)量沒有變漠秋，綜合考慮笙蒙，優(yōu)化器選擇了主鍵索引

首先排除全部索引，占空間庆锦，其次排除前綴索引捅位，區(qū)分度不高，再排除倒序索引肥荔，區(qū)分度還沒前綴索引高绿渣。
最后hash索引適合，而且只是登錄檢驗(yàn)燕耿，不需要范圍查詢中符。