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

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

mysql>createtableSUser(IDbigintunsignedprimarykey,emailvarchar(64),...)engine=innodb;

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

mysql>selectf1,f2fromSUserwhereemail='xxx';

如果email這個(gè)字段上沒(méi)有索引，那么這個(gè)語(yǔ)句就只能做全表掃描。

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

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

mysql>altertableSUseraddindexindex1(email);或mysql>altertableSUseraddindexindex2(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è)索引的示意圖。

圖1 email 索引結(jié)構(gòu)

圖2 email(6) 索引結(jié)構(gòu)

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

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

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

selectid,name,emailfromSUserwhereemail='zhangssxyz@';

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

從index1索引樹(shù)找到滿(mǎn)足索引值是’zhangssxyz@’的這條記錄，取得ID2的值；到主鍵上查到主鍵值是ID2的行，判斷email的值是正確的，將這行記錄加入結(jié)果集；取index1索引樹(shù)上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)已經(jīng)不滿(mǎn)足email='zhangssxyz@’的條件了，循環(huán)結(jié)束。

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

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

從index2索引樹(shù)找到滿(mǎn)足索引值是’zhangs’的記錄，找到的第一個(gè)是ID1；到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’zhangssxyz@’，這行記錄丟棄；取index2上剛剛查到的位置的下一條記錄，發(fā)現(xiàn)仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判斷，這次值對(duì)了，將這行記錄加入結(jié)果集；重復(fù)上一步，直到在idxe2上取到的值不是’zhangs’時(shí)，循環(huán)結(jié)束。

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

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

但是，對(duì)于這個(gè)查詢(xún)語(yǔ)句來(lái)說(shuō)，如果你定義的index2不是email(6)而是email(7），也就是說(shuō)取email字段的前7個(gè)字節(jié)來(lái)構(gòu)建索引的話(huà)，即滿(mǎn)足前綴’zhangss’的記錄只有一個(gè)，也能夠直接查到ID2，只掃描一行就結(jié)束了。

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

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

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

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

mysql>selectcount(distinctemail)asLfromSUser;

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

mysql>selectcount(distinctleft(email,4)）asL4,count(distinctleft(email,5)）asL5,count(distinctleft(email,6)）asL6,count(distinctleft(email,7)）asL7,fromSUser;

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

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

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

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

selectid,emailfromSUserwhereemail='zhangssxyz@';

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

selectid,name,emailfromSUserwhereemail='zhangssxyz@';

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

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

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

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

其他方式

對(duì)于類(lèi)似于郵箱這樣的字段來(lái)說(shuō)，使用前綴索引的效果可能還不錯(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的前綴索引的話(huà)，這個(gè)索引的區(qū)分度就非常低了。

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

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

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

答案是，有的。

第一種方式是使用倒序存儲(chǔ)。如果你存儲(chǔ)身份證號(hào)的時(shí)候把它倒過(guò)來(lái)存，每次查詢(xún)的時(shí)候，你可以這么寫(xiě)：

mysql>selectfield_listfromtwhereid_card=reverse('input_id_card_string');

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

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

mysql>altertabletaddid_card_crcintunsigned,addindex(id_card_crc);

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

mysql>selectfield_listfromtwhereid_card_crc=crc32('input_id_card_string')andid_card='input_id_card_string'

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

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

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

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

從占用的額外空間來(lái)看，倒序存儲(chǔ)方式在主鍵索引上，不會(huì)消耗額外的存儲(chǔ)空間，而hash字段方法需要增加一個(gè)字段。當(dāng)然，倒序存儲(chǔ)方式使用4個(gè)字節(jié)的前綴長(zhǎng)度應(yīng)該是不夠的，如果再長(zhǎng)一點(diǎn)，這個(gè)消耗跟額外這個(gè)hash字段也差不多抵消了。在CPU消耗方面，倒序方式每次寫(xiě)和讀的時(shí)候，都需要額外調(diào)用一次reverse函數(shù)，而hash字段的方式需要額外調(diào)用一次crc32()函數(shù)。如果只從這兩個(gè)函數(shù)的計(jì)算復(fù)雜度來(lái)看的話(huà)，reverse函數(shù)額外消耗的CPU資源會(huì)更小些。從查詢(xún)效率上看，使用hash字段方式的查詢(xún)性能相對(duì)更穩(wěn)定一些。因?yàn)閏rc32算出來(lái)的值雖然有沖突的概率，但是概率非常小，可以認(rèn)為每次查詢(xún)的平均掃描行數(shù)接近1。而倒序存儲(chǔ)方式畢竟還是用的前綴索引的方式，也就是說(shuō)還是會(huì)增加掃描行數(shù)。小結(jié)

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

直接創(chuàng)建完整索引，這樣可能比較占用空間；創(chuàng)建前綴索引，節(jié)省空間，但會(huì)增加查詢(xún)掃描次數(shù)，并且不能使用覆蓋索引；倒序存儲(chǔ)，再創(chuàng)建前綴索引，用于繞過(guò)字符串本身前綴的區(qū)分度不夠的問(wèn)題；創(chuàng)建hash字段索引，查詢(xún)性能穩(wěn)定，有額外的存儲(chǔ)和計(jì)算消耗，跟第三種方式一樣，都不支持范圍掃描。

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

上期問(wèn)題時(shí)間

上篇文章中的第一個(gè)例子，評(píng)論區(qū)有幾位同學(xué)說(shuō)沒(méi)有復(fù)現(xiàn)，大家要檢查一下隔離級(jí)別是不是RR（Repeatable Read，可重復(fù)讀），創(chuàng)建的表t是不是InnoDB引擎。

復(fù)現(xiàn)截圖

在上一篇文章最后，我給你留的問(wèn)題是，為什么經(jīng)過(guò)這個(gè)操作序列，explain的結(jié)果就不對(duì)了？這里，我來(lái)為你分析一下原因。

delete 語(yǔ)句刪掉了所有的數(shù)據(jù)，然后再通過(guò)call idata()插入了10萬(wàn)行數(shù)據(jù)，看上去是覆蓋了原來(lái)的10萬(wàn)行。

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

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

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

是的，不過(guò)這個(gè)是主鍵，主鍵是直接按照表的行數(shù)來(lái)估計(jì)的。而表的行數(shù)，優(yōu)化器直接用的是show table status的值。

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