MySQL 字串索引優化方案

以終為始發表於2020-07-01
MySql字串索引優化

字串建立索引的優化

1. 建立字首索引

假設建立一個支援郵箱登入的使用者表，對於郵件欄位來說，可以有以下幾種建立索引的方式：

  1. 直接對整個字串建立索引

    alter table SUser add index index1(email);

  2. 對整個字串的前一部分建立索引 - 字首索引

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

方式 2 相較於 方式 1 來說，利用字首索引，佔用的空間更小。但有可能造成效能的損失，讀取資料的次數變多。

假設在 user 表中存在zhangsp1234@gmail.com, zhangs1pzxyz@qq.com , zhangssxyz@xxx.com, 三條記錄。

有這樣一條語句 select id,name,email from SUser where email='zhangssxyz@xxx.com';

使用 index1 索引時，流程如下：

  1. 在 index1 中，找到名字是 zhangssxyz@xxx.com 的記錄，獲取 ID.
  2. 在主鍵索引上對應 ID的行，判斷 email 是否正確，將記錄加入結果集。
  3. 接著取 index1 索引的下一條記錄，發現不滿足 email 格式，結束迴圈。

使用 index2 索引：

  1. 在 index2 中，找到名字是 zhangs 的記錄，獲取 ID.
  2. 在主鍵索引上對應 ID的行，這時拿到的是 zhangss1234@gmail.com 的行， 發現不符合，丟棄。
  3. 接著在 index2 迴圈，拿到下一條記錄 ID。
  4. 在主鍵索引上對應 ID的行，這時拿到的是 zhangsspzxyz@qq.com 的行， 發現不符合，丟棄。
  5. 接著在 index2 迴圈，拿到下一條記錄 ID。
  6. 在主鍵索引上對應 ID的行，這時拿到的是 zhangssxyz@xxx.com 的行， 發現符合，納入結果集。
  7. 接著在 index2 迴圈，發現記錄格式不符合，結束迴圈。

看這個過程，很容易發現，字首索引會增加查詢語句讀取資料的次數。

但如果將字首索引的 email(6) 改成 email(7)，就會減少查詢的次數，對應在主鍵索引上只搜尋一次。這就說明，如果能合適的設定字首索引的長度，就能在空間和效率上取得平衡。

如何找到合適的字首索引長度

在建立索引時，應該去關注區分度，區分度越高，則說明重複的鍵值越少。

可以通過執行查詢來統計列上有多少不同的值。

mysql> select 
  count(distinct email）as L,
  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%, 用 L 的數量 * 區分度比例（1-5%=95%），然後看在 L4 到 L7 中哪個滿足。

字首索引的影響

在之前覆蓋索引的文章中，如果查詢的列的資訊被包含在二級索引上，那麼就可以避免回表的過程，進而減少查詢次數，提供效率。但如果在建立索引時，使用了字首索引，那麼無論滿不滿足覆蓋索引的規則，都會回表。因為系統不能確定字首索引是否擷取了完成資訊，進而必須做一次判斷。

也就是說，字首索引除了會增加查詢語句的次數，還會禁止使用覆蓋索引。

2. 倒序儲存

對於郵箱這類的字串來說，由於前幾位有較大的區分度，所以用字首索引還不錯。但如果是區分度不好的情況，比如身份證，前 6 位都是地址碼，很多人都會一樣。這時如果想要使用字首索引，就需要至少 12 位以上，對應查詢效率和空間都不是很合適。

一個比較好的辦法是將字串倒序儲存，將區分度高的字元開頭。

例如：

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

3. 使用 hash 欄位

在網路傳輸時，CRC - 迴圈冗餘校驗被用於檢驗檔案。對應在 MySQL 裡也有這個函式，crc32().

該函式的返回範圍是 0-4294967296 也就是 4 位元組，相對於其他字串來說，屬於較短的長度。

在建立表時，可再建立一個整數字段，來儲存這類字串，如身份證的校驗碼（crc32()的返回值）, 併為該欄位建立索引。

如：

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

在插入記錄時，將 crc32() 的結果插入到記錄中。

但由於 crc32() 只有 32 位的特性，容易發生 hash 碰撞，就是說可能兩個字串經過計算後得到相同的驗證碼。這時就存在衝突，所以還需要判斷下查詢的值是否一致。

如：

mysql> select field_list from t where \
id_card_crc=crc32('input_id_card_string') and \
id_card='input_id_card_string'

總結

我們知道，MySQL 中使用的是 B+ 樹來儲存索引的，這自然就是有序的，所以字首查詢就支援範圍查詢。

而 Hash 欄位和倒序查詢兩種方式就不行了，倒序查詢是按照倒序字串儲存的，而 hash 欄位和字串本身也沒有關係，這就意味著這兩種方式是不支援範圍查詢的。

在佔用空間上來說，倒序儲存佔用的是和普通索引的一樣的空間。而 hash 欄位，需要增加一個欄位來存在 hash 校驗碼。

在 CPU 消耗，倒序時，每次讀和寫都需要呼叫 reverse 函式。hash 方式需要額外呼叫 crc32() 函式。兩個函式實現來看，reverse 函式 CPU 消耗會少些。

在查詢效率上，hash 欄位查詢效能更好穩定些。雖然可能存在衝突的情況，但概率很小。而倒序儲存還是用字首索引的方式，會額外增加掃描行數。

總結一下，一般提高查詢字串的效率有如下方式：

  • 直接建立完成索引，但佔用空間較大。
  • 建立字首索引，節省空間，但會增加掃描次數，不能利用覆蓋索引。
  • 倒序儲存，再建立字首索引，節省空間，增加掃描次數，不能利用覆蓋索引。
  • hash 欄位，效能穩定，但佔用額外的空間，不支援範圍查詢。

相關文章