字串中索引位置是什麼意思（sql索引怎麼新增索引）

2023-06-25 09:41:58

現在，幾乎所有的系統都支援郵箱登入，如何在郵箱這樣的欄位上建立合理的索引，是我們今天要討論的問題。

假設，你現在維護一個支援郵箱登入的系統，使用者表是這麼定義的：

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

由於要使用郵箱登入，所以業務程式碼中一定會出現類似於這樣的語句：

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

如果email這個欄位上沒有索引，那麼這個語句就只能做全表掃描。

同時，MySQL是支援字首索引的，也就是說，你可以定義字串的一部分作為索引。預設地，如果你建立索引的語句不指定字首長度，那麼索引就會包含整個字串。

比如，這兩個在email欄位上建立索引的語句：

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

第一個語句建立的index1索引裡面，包含了每個記錄的整個字串；而第二個語句建立的index2索引裡面，對於每個記錄都是隻取前6個位元組。

那麼，這兩種不同的定義在資料結構和儲存上有什麼區別呢？如圖2和3所示，就是這兩個索引的示意圖。

圖1 email 索引結構

圖2 email(6) 索引結構

從圖中你可以看到，由於email(6)這個索引結構中每個郵箱欄位都只取前6個位元組（即：zhangs），所以佔用的空間會更小，這就是使用字首索引的優勢。

但，這同時帶來的損失是，可能會增加額外的記錄掃描次數。

接下來，我們再看看下面這個語句，在這兩個索引定義下分別是怎麼執行的。

select id,name,email from SUser where email='[email protected]';

如果使用的是index1（即email整個字串的索引結構），執行順序是這樣的：

從index1索引樹找到滿足索引值是’[email protected]’的這條記錄，取得ID2的值；
到主鍵上查到主鍵值是ID2的行，判斷email的值是正確的，將這行記錄加入結果集；
取index1索引樹上剛剛查到的位置的下一條記錄，發現已經不滿足email='[email protected]’的條件了，迴圈結束。

這個過程中，只需要回主鍵索引取一次資料，所以系統認為只掃描了一行。

如果使用的是index2（即email(6)索引結構），執行順序是這樣的：

從index2索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是ID1；
到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’[email protected]’，這行記錄丟棄；
取index2上剛剛查到的位置的下一條記錄，發現仍然是’zhangs’，取出ID2，再到ID索引上取整行然後判斷，這次值對了，將這行記錄加入結果集；
重複上一步，直到在idxe2上取到的值不是’zhangs’時，迴圈結束。

在這個過程中，要回主鍵索引取4次資料，也就是掃描了4行。

通過這個對比，你很容易就可以發現，使用字首索引後，可能會導致查詢語句讀資料的次數變多。

但是，對於這個查詢語句來說，如果你定義的index2不是email(6)而是email(7），也就是說取email欄位的前7個位元組來構建索引的話，即滿足字首’zhangss’的記錄只有一個，也能夠直接查到ID2，只掃描一行就結束了。

也就是說使用字首索引，定義好長度，就可以做到既節省空間，又不用額外增加太多的查詢成本。

於是，你就有個問題：當要給字串建立字首索引時，有什麼方法能夠確定我應該使用多長的字首呢？

實際上，我們在建立索引時關注的是區分度，區分度越高越好。因為區分度越高，意味著重複的鍵值越少。因此，我們可以通過統計索引上有多少個不同的值來判斷要使用多長的字首。

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

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

然後，依次選取不同長度的字首來看這個值，比如我們要看一下4~7個位元組的字首索引，可以用這個語句：

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;

當然，使用字首索引很可能會損失區分度，所以你需要預先設定一個可以接受的損失比例，比如5%。然後，在返回的L4~L7中，找出不小於 L * 95%的值，假設這裡L6、L7都滿足，你就可以選擇字首長度為6。

字首索引對覆蓋索引的影響

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

你先來看看這個SQL語句：

select id,email from SUser where email='[email protected]';

與前面例子中的SQL語句

select id,name,email from SUser where email='[email protected]';

相比，這個語句只要求返回id和email欄位。

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

即使你將index2的定義修改為email(18)的字首索引，這時候雖然index2已經包含了所有的資訊，但InnoDB還是要回到id索引再查一下，因為系統並不確定字首索引的定義是否截斷了完整資訊。

也就是說，使用字首索引就用不上覆蓋索引對查詢效能的優化了，這也是你在選擇是否使用字首索引時需要考慮的一個因素。

其他方式

對於類似於郵箱這樣的欄位來說，使用字首索引的效果可能還不錯。但是，遇到字首的區分度不夠好的情況時，我們要怎麼辦呢？

比如，我們國家的身份證號，一共18位，其中前6位是地址碼，所以同一個縣的人的身份證號前6位一般會是相同的。

假設你維護的資料庫是一個市的公民資訊系統，這時候如果對身份證號做長度為6的字首索引的話，這個索引的區分度就非常低了。

按照我們前面說的方法，可能你需要建立長度為12以上的字首索引，才能夠滿足區分度要求。

但是，索引選取的越長，佔用的磁碟空間就越大，相同的資料頁能放下的索引值就越少，搜尋的效率也就會越低。

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

答案是，有的。

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

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

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

第二種方式是使用hash欄位。你可以在表上再建立一個整數字段，來儲存身份證的校驗碼，同時在這個欄位上建立索引。

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

然後每次插入新記錄的時候，都同時用crc32()這個函式得到校驗碼填到這個新欄位。由於校驗碼可能存在衝突，也就是說兩個不同的身份證號通過crc32()函式得到的結果可能是相同的，所以你的查詢語句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'

這樣，索引的長度變成了4個位元組，比原來小了很多。

接下來，我們再一起看看使用倒序儲存和使用hash欄位這兩種方法的異同點。

首先，它們的相同點是，都不支援範圍查詢。倒序儲存的欄位上建立的索引是按照倒序字串的方式排序的，已經沒有辦法利用索引方式查出身份證號碼在[ID_X, ID_Y]的所有市民了。同樣地，hash欄位的方式也只能支援等值查詢。

它們的區別，主要體現在以下三個方面：

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

小結

在今天這篇文章中，我跟你聊了聊字串欄位建立索引的場景。我們來回顧一下，你可以使用的方式有：

直接建立完整索引，這樣可能比較佔用空間；
建立字首索引，節省空間，但會增加查詢掃描次數，並且不能使用覆蓋索引；
倒序儲存，再建立字首索引，用於繞過字串本身字首的區分度不夠的問題；
建立hash欄位索引，查詢效能穩定，有額外的儲存和計算消耗，跟第三種方式一樣，都不支援範圍掃描。

在實際應用中，你要根據業務欄位的特點選擇使用哪種方式。

上期問題時間

上篇文章中的第一個例子，評論區有幾位同學說沒有復現，大家要檢查一下隔離級別是不是RR（Repeatable Read，可重複讀），建立的表t是不是InnoDB引擎。

復現截圖

在上一篇文章最後，我給你留的問題是，為什麼經過這個操作序列，explain的結果就不對了？這裡，我來為你分析一下原因。

delete 語句刪掉了所有的資料，然後再通過call idata()插入了10萬行資料，看上去是覆蓋了原來的10萬行。

但是，session A開啟了事務並沒有提交，所以之前插入的10萬行資料是不能刪除的。這樣，之前的資料每一行資料都有兩個版本，舊版本是delete之前的資料，新版本是標記為deleted的資料。

這樣，索引a上的資料其實就有兩份。

然後你會說，不對啊，主鍵上的資料也不能刪，那沒有使用force index的語句，使用explain命令看到的掃描行數為什麼還是100000左右？（潛臺詞，如果這個也翻倍，也許優化器還會認為選欄位a作為索引更合適）

是的，不過這個是主鍵，主鍵是直接按照表的行數來估計的。而表的行數，優化器直接用的是show table status的值。

這個值的計算方法，我會在後面有文章為你詳細講解。