MySQL字符串索引更合理的創建規則討論

2020-01-18 20:39:14

字體：大中小

來源：轉載

供稿：網友

前言

針對使用MySQL的索引，我們之前介紹過索引的最左前綴規則，索引覆蓋，唯一索引和普通索引的使用以及優化器選擇索引等概念，今天我們討論下如何更合理的給字符串創建索引。

如何更好的創建字符串索引

我們知道，MySQL中，數據和索引都是在一顆 B+樹上，我們建立索引的時候，這棵樹所占用的空間越小，檢索速度就會越快，而varchar格式的字符串有些會很長，那么在效率為上的今天，我們如何更加合理的建立字符串的索引呢？
假如說我們一張表中存在 email 字段，現在要給 email 字段創建索引，email 字段值的格式為：zhangsan@qq.com。

有2種建立索引的方式：

1、直接給 email 字段建立索引：alter table t add index index1(email);

索引樹結構為：

2、建立 email 的前綴索引：alter table t add index index2(email(6));

索引數據結構為：

此時我們的查詢語句為：select id,name,email from t where email='zhangsh123@xxx.com';

當使用index1索引時其執行步驟為：

1、從index1索引樹查找索引值為zhangsh123@xxx.com的主鍵值ID1；

2、根據ID1回表查到該行數據確實為zhangsh123@xxx.com，將結果加入結果集；

3、繼續查找index1索引樹下一個索引值是否滿足zhangsh123@xxx.com，不滿足則結束查詢。

當使用index2索引時其執行步驟為：

1、從index2索引樹查找索引值為zhangs的主鍵值ID1；

2、根據ID1回表查到該行數據確實為zhangsh123@xxx.com，將結果加入結果集；

3、繼續查找index2索引樹下一個索引值是否滿足zhangs，滿足則繼續回表查詢該行數據是否為zhangsh123@xxx.com，不是則跳過繼續查找；

4、持續查找index2索引樹，直到索引值不是zhangs為止。

從以上分析中我們可以看出，全字段索引相比前綴索引來說，減少了回表的次數，但是如果我們將前綴從6個增加到7個8個的話，前綴索引回表的次數就會減少，也就是說，只要定義好前綴的長度，我們就能既節省空間又保證效率。

那么問題來了，我們怎么衡量使用前綴索引的長度呢？

1、使用 select count(distinct email) as L from t; 查詢字段不同值的個數；

2、依次選取不同的前綴長度查看不同值的個數：

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 t;

然后根據實際可接受的損失比例，選取適合的最短的前綴長度。

前綴的長度問題我們解決了，但是一個問題是，如果使用前綴索引，那我們索引覆蓋的特性就用不到了。
用全字段索引時，當我們查詢select id,email from t where email='zhangsh123@xxx.com';時，不用回表直接就能查到id和email字段。

但是用前綴索引時，MySQL并不清楚前綴是否會整個覆蓋email的值，無論是否全包含都會根據主鍵值回表查詢判斷。

所以說，使用前綴索引雖然能節省空間保證效率但是卻不能用到覆蓋索引的特性，是否使用就在于具體考慮了。

其他字符串索引創建方式

實際情況實際考慮，并不是所有的字符串都能使用前綴截取的方式創建索引，如身份證號或者ip這些字符串使用前綴索引就不合理了，身份證號一般同一個地區的人前幾位都是一模一樣的，使用前綴索引就不合理了，而ip值我們一般在實際中將其轉化為數字去存儲。

針對身份證號，我們可以使用倒敘存儲，取前綴創建索引或者使用crc32()函數來獲取一個hash校驗碼（int值）當做索引。

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

crc32：select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

這兩種方式相對來說效率都差不多，都不支持范圍查找，支持等值查找。

在倒敘方式中，需要使用reverse函數，但是回表次數可能比hash方式多。

在hash方式中，需要新建一個索引字段并調用crc32()函數。（注意：crc32()函數獲取的結果不保證能唯一，可能存在重復的情況，但是這種情況概率較?。乇泶螖瞪?，幾乎1次就行。