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 整個字元串的索引結構），執行順序是這樣的：

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

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

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

1. 從 index2 索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是 ID1；
2. 到主鍵上查到主鍵值是 ID1 的行，判斷出 email 的值不是’[email protected]’，這行記錄丟棄；
3. 取 index2 上剛剛查到的位置的下一條記錄，發現仍然是’zhangs’，取出 ID2，再到 ID 索引上取整行然後判斷，這次值對了，將這行記錄加入結果集；
4. 重覆上一步，直到在 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 欄位的方式也只能支持等值查詢。

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

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

總結

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

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

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

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

A:

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

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