1. 前綴索引與全部索引概念

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

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

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

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

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

我們知道，如果email這個字段上沒有索引，那么這個語句就只能做全表掃描。

MySQL支持全部索引與前綴索引。MySQL是支持前綴索引的，也就是說，你可以定義字符串的一部分作為索引。默認地，如果你創(chuàng)建索引的語句不指定前綴長度，那么索引就會包含整個字符串。

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

-- 全部索引，索引字段為email整個字符串
alter table SUser add index index1(email);

-- 前綴索引，6表示索引字段為email的前6位
alter table SUser add index index2(email(6));

第一個語句創(chuàng)建的index1索引里面，包含了每個記錄的整個字符串；而第二個語句創(chuàng)建的index2索引里面，對于每個記錄都是只取前6個字節(jié)。

2. 前綴索引與全部索引數(shù)據(jù)結(jié)構(gòu)

這兩種不同的定義在數(shù)據(jù)結(jié)構(gòu)和存儲上有什么區(qū)別呢？如圖2和3所示，就是這兩個索引的示意圖。
mysql全部索引數(shù)據(jù)結(jié)構(gòu)

mysql前綴索引數(shù)據(jù)結(jié)構(gòu)

從圖中你可以看到，由于email(6)這個索引結(jié)構(gòu)中每個郵箱字段都只取前6個字節(jié)（即：zhangs），所以占用的空間會更小，這就是使用前綴索引的優(yōu)勢。但，這同時帶來的損失是，可能會增加額外的記錄掃描次數(shù)。

3. 前綴索引與全部索引引執(zhí)行流程

接下來，我們再看看下面這個語句，在這兩個索引定義下分別是怎么執(zhí)行的。
如 index1（即email整個字符串的索引結(jié)構(gòu)），執(zhí)行順序是這樣的：

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

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

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

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

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

通過這個對比，你很容易就可以發(fā)現(xiàn)，使用前綴索引后，可能會導致查詢語句讀數(shù)據(jù)的次數(shù)變多。

4. 前綴索引長度如何取舍

對于這個查詢語句來說，如果你定義的index2不是email(6)而是email(7），也就是說取email字段的前7個字節(jié)來構(gòu)建索引的話，即滿足前綴’zhangss’的記錄只有一個，也能夠直接查到ID2，只掃描一行就結(jié)束了。
也就是說使用前綴索引，定義好長度，就可以做到既節(jié)省空間，又不用額外增加太多的查 使詢成本。
于是，你就有個問題：當要給字符串創(chuàng)建前綴索引時，有什么方法能夠確定我應該使用多長的前綴呢？實際上，我們在建立索引時關注的是區(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。

5. 前綴索引對覆蓋索引的影響

前面我們說了使用前綴索引可能會增加掃描行數(shù)，這會影響到性能。其實，前綴索引的影響不止如此，我們再看一下另外一個場景。

你先來看看這個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是索引，name不是索引的情況下，先說結(jié)論，當email是全部索引的時候第一個sql會走覆蓋索引，第二個不會走覆蓋索引，當email為前綴索引的時候哪怕前綴為全部字符串長度依然無法走覆蓋索引。

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

即使你將index2的定義修改為email(18)的前綴索引，這時候雖然index2已經(jīng)包含了所有的信息，但InnoDB還是要回到id索引再查一下，因為系統(tǒng)并不確定前綴索引的定義是否截斷了完整信息。

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

6. 其他解決方案

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

比如，我們國家的身份證號，一共18位，其中前6位是地址碼，所以同一個縣的人的身份證號前6位一般會是相同的。假設你維護的數(shù)據(jù)庫是一個市的公民信息系統(tǒng)，這時候如果對身份證號做長度為6的前綴索引的話，這個索引的區(qū)分度就非常低了。按照我們前面說的方法，可能你需要創(chuàng)建長度為12以上的前綴索引，才能夠滿足區(qū)分度要求。但是，索引選取的越長，占用的磁盤空間就越大，相同的數(shù)據(jù)頁能放下的索引值就越少，搜索的效率也就會越低。

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

第一種方式是使用倒序存儲。 第 如果你存儲身份證號的時候把它倒過來存，每次查詢的時候，你可以這么寫：

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

由于身份證號的最后6位沒有地址碼這樣的重復邏輯，所以最后這6位很可能就提供了足夠的區(qū)分度。當然了，實踐中你不要忘記使用count(distinct)方法去做個驗證。

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

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

然后每次插入新記錄的時候，都同時用crc32()這個函數(shù)得到校驗碼填到這個新字段。由于校驗碼可能存在沖突，也就是說兩個不同的身份證號通過crc32()函數(shù)得到的結(jié)果可能是相同的，所以你的查詢語句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é)，比原來小了很多。

接下來，我們再一起看看使用倒序存儲和使用 使 hash h 字段這兩種方法的異同點。 字首先，它們的相同點是，都不支持范圍查詢。倒序存儲的字段上創(chuàng)建的索引是按照倒序字符串的方式排序的，已經(jīng)沒有辦法利用索引方式查出身份證號碼在[ID_X, ID_Y]的所有市民了。同樣地，hash字段的方式也只能支持等值查詢。

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

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

7. 梳理總結(jié)

總體來說的話全部索引會占用一部分空間，但是可以走覆蓋索引，區(qū)分度比較高，減少回表次數(shù)，前綴索引雖然減少了部分空間，但是需要平衡區(qū)分度，而且需要時刻關注表中索引字段的變化，因為隨著數(shù)據(jù)的變化區(qū)分度也會變化，可能原來前綴6個字段區(qū)分度就能達到95%以上，但是隨著數(shù)據(jù)增加前6個字段只能達到60%的區(qū)分度，另外一點就是覆蓋索引無法使用，雖然倒敘或者hash能解決空間問題，但是又會產(chǎn)生新的問題，比如需要函數(shù)，增加字段等，是查詢效率降低，折中選擇全部索引會更加穩(wěn)妥，效率更高，當然對于特定的字符串比如自己公司email，前6位足夠區(qū)分，而且后續(xù)不會有改動，這時前綴索引既能減少空間占用，又能達到與全部索引一樣的效果，使用前綴會更好。具體使用場景一定是結(jié)合具體的業(yè)務場景選擇。

到此這篇關于MySQL字符串前綴索引使用的文章就介紹到這了,更多相關MySQL字符串前綴索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論