MySQL 字符串索引優化方案

字符串建立索引的優化

1. 建立前綴索引

假設建立一個支持郵箱登錄的用戶表,對於郵件字段來說,可以有以下幾種建立索引的方式:

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

    alter table SUser add index index1(email);
    
  2. 對整個字符串的前一部分建立索引 – 前綴索引

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

方式 2 相較於 方式 1 來說,利用前綴索引,佔用的空間更小。但有可能造成性能的損失,讀取數據的次數變多。

假設在 user 表中存在[email protected], [email protected] , [email protected], 三條記錄。

有這樣一條語句 select id,name,email from SUser where email='[email protected]';

使用 index1 索引時,流程如下:

  1. 在 index1 中,找到名字是 [email protected] 的記錄,獲取 ID.
  2. 在主鍵索引上對應 ID的行,判斷 email 是否正確,將記錄加入結果集。
  3. 接着取 index1 索引的下一條記錄,發現不滿足 email 格式,結束循環。

使用 index2 索引:

  1. 在 index2 中,找到名字是 zhangs 的記錄,獲取 ID.
  2. 在主鍵索引上對應 ID的行,這時拿到的是 [email protected] 的行, 發現不符合,丟棄。
  3. 接着在 index2 循環,拿到下一條記錄 ID。
  4. 在主鍵索引上對應 ID的行,這時拿到的是 [email protected] 的行, 發現不符合,丟棄。
  5. 接着在 index2 循環,拿到下一條記錄 ID。
  6. 在主鍵索引上對應 ID的行,這時拿到的是 [email protected] 的行, 發現符合,納入結果集。
  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 字段,性能穩定,但佔用額外的空間,不支持範圍查詢。
Tags: