MySQL字符串索引更合理的創(chuàng)建規(guī)則討論

更新時間：2019年11月22日 14:59:29 作者：風雨之間

這篇文章主要給大家介紹了關于MySQL字符串索引更合理的創(chuàng)建規(guī)則，文中通過示例代碼介紹的非常詳細，對大家學習或者使用MySQL具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧

前言

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

如何更好的創(chuàng)建字符串索引

我們知道，MySQL中，數據和索引都是在一顆 B+樹上，我們建立索引的時候，這棵樹所占用的空間越小，檢索速度就會越快，而varchar格式的字符串有些會很長，那么在效率為上的今天，我們如何更加合理的建立字符串的索引呢？
假如說我們一張表中存在 email 字段，現在要給 email 字段創(chuàng)建索引，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索引時其執(zhí)行步驟為：

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

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

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

當使用index2索引時其執(zhí)行步驟為：

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

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

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

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

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

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

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的值，無論是否全包含都會根據主鍵值回表查詢判斷。

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

其他字符串索引創(chuàng)建方式

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

針對身份證號，我們可以使用倒敘存儲，取前綴創(chuàng)建索引或者使用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次就行。