11 | 怎么給字串欄位加索引？

Q：如何在郵箱這樣的欄位上建立合理的索引？

用戶表的定義：

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

由于要使用郵箱登錄，所以業務代碼中一定會出現類似于這樣的陳述句：

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

如果 email 這個欄位上沒有索引，那么這個陳述句就只能做全表掃描，

A：

前綴索引

MySQL 是支持前綴索引的，也就是說，可以定義字串的一部分作為索引，

默認地，如果你創建索引的陳述句不指定前綴長度，那么索引就會包含整個字串，

alter table SUser add index index1(email);

alter table SUser add index index2(email(6));

第一個陳述句創建的 index1 索引里面，包含了每個記錄的整個字串；

而第二個陳述句創建的 index2 索引里面，對于每個記錄都是只取前 6 個位元組，

在資料結構和存盤上的區別：

由于 email(6) 這個索引結構中每個郵箱欄位都只取前 6 個位元組（即：zhangs），所以占用的空間會更小，這就是使用前綴索引的優勢，

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

執行上面的select時：

如果使用的是 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’時，回圈結束，

所以使用前綴索引后，可能會導致查詢陳述句讀資料的次數變多，

使用多長的前綴呢？

在建立索引時關注的是區分度，區分度越高越好，因為區分度越高，意味著重復的鍵值越少，

以通過統計索引上有多少個不同的值來判斷要使用多長的前綴，

首先算出這個列上有多少個不同的值：

select count(distinct email) as L from SUser;

然后，依次選取不同長度的前綴來看這個值：

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;

前綴索引對覆寫索引的影響

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

使用index1，由于通過email可以直接找到id，即覆寫索引，不需要回表，

使用index2（即 email(6) 索引結構）的話，就不得不回到 ID 索引再去判斷 email 欄位的值，

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

前綴索引就用不上覆寫索引對查詢性能的優化

其他方式

Q：遇到前綴的區分度不夠好的情況時，要怎么辦？例如身份證號碼？

希望能既可以占用更小的空間，也能達到相同的查詢效率

A：

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

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

由于身份證號的最后 6 位沒有地址碼這樣的重復邏輯，所以最后這 6 位很可能就提供了足夠的區分度

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

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

然后每次插入新記錄的時候，都同時用 crc32() 這個函式得到校驗碼填到這個新欄位，

crc32 - 計網資料庫中常使用回傳字串的 32 位回圈冗余校驗值，

由于校驗碼可能存在沖突，也就是說兩個不同的身份證號通過 crc32() 函式得到的結果可能是相同的，所以你的查詢陳述句 where 部分要判斷 id_card 的值是否精確相同，

使用倒序存盤和使用 hash 欄位這兩種方法的異同點

相同點是，都不支持范圍查詢，

倒序存盤的欄位上創建的索引是按照倒序字串的方式排序的，已經沒有辦法利用索引方式查出身份證號碼在 [ID_X, ID_Y] 的所有市民，同樣地，hash 欄位的方式也只能支持等值查詢，

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

從占用的額外空間來看，倒序存盤方式在主鍵索引上，不會消耗額外的存盤空間，而 hash 欄位方法需要增加一個欄位，
在 CPU 消耗方面，倒序方式每次寫和讀的時候，都需要額外呼叫一次 reverse 函式，而 hash 欄位的方式需要額外呼叫一次 crc32() 函式，
從查詢效率上看，使用 hash 欄位方式的查詢性能相對更穩定一些，因為 crc32 算出來的值雖然有沖突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近 1，而倒序存盤方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數，

總結

直接創建完整索引，這樣可能比較占用空間；
創建前綴索引，節省空間，但會增加查詢掃描次數，并且不能使用覆寫索引；
倒序存盤，再創建前綴索引，用于繞過字串本身前綴的區分度不夠的問題；
創建 hash 欄位索引，查詢性能穩定，有額外的存盤和計算消耗，跟第三種方式一樣，都不支持范圍掃描，

Q：如果你在維護一個學校的學生資訊資料庫，學生登錄名的統一格式是"學號 @gmail.com"

學號的規則是：十五位的數字，其中前三位是所在城市編號、第四到第六位是學校編號、第七位到第十位是入學年份、最后五位是順序編號，

系統登錄的時候都需要學生輸入登錄名和密碼，驗證正確后才能繼續使用系統，就只考慮登錄驗證這個行為的話，會怎么設計這個登錄名的索引呢？

因為維護的只是一個學校的，因此前面 6 位（其中，前三位是所在城市編號、第四到第六位是學校編號）其實是固定的，郵箱后綴都是 @gamil.com，因此可以只存入學年份加順序編號，它們的長度是 9 位，

而其實在此基礎上，可以用數字型別來存這 9 位數字，比如 201100001，這樣只需要占 4 個位元組，其實這個就是一種 hash，只是它用了最簡單的轉換規則：字串轉數字的規則，而剛好我們設定的這個背景，可以保證這個轉換后結果的唯一性，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/500144.html

標籤：MySQL

上一篇：MySQL實戰45講 10

下一篇：SQL陳述句實戰學習