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

前言

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

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

我們知道，MySQL中，數(shù)據(jù)和索引都是在一顆 B+樹 上，我們建立索引的時(shí)候，這棵樹所占用的空間越小，檢索速度就會(huì)越快，而varchar格式的字符串有些會(huì)很長，那么在效率為上的今天，我們?nèi)绾胃雍侠淼慕⒆址乃饕兀?br /> 假如說我們一張表中存在 email 字段，現(xiàn)在要給 email 字段創(chuàng)建索引，email 字段值的格式為：zhangsan@qq.com。

有2種建立索引的方式：

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

索引樹結(jié)構(gòu)為：

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

索引數(shù)據(jù)結(jié)構(gòu)為：

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

當(dāng)使用index1索引時(shí)其執(zhí)行步驟為：

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

2、根據(jù)ID1回表查到該行數(shù)據(jù)確實(shí)為zhangsh123@xxx.com，將結(jié)果加入結(jié)果集；

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

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

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

2、根據(jù)ID1回表查到該行數(shù)據(jù)確實(shí)為zhangsh123@xxx.com，將結(jié)果加入結(jié)果集；

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

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

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

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

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

2、依次選取不同的前綴長度查看不同值的個(gè)數(shù)：

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;

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

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

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

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

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

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

針對身份證號，我們可以使用倒敘存儲(chǔ)，取前綴創(chuàng)建索引或者使用crc32()函數(shù)來獲取一個(gè)hash校驗(yàn)碼（int值）當(dāng)做索引。

倒敘：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函數(shù)，但是回表次數(shù)可能比hash方式多。

在hash方式中，需要新建一個(gè)索引字段并調(diào)用crc32()函數(shù)。（注意：crc32()函數(shù)獲取的結(jié)果不保證能唯一，可能存在重復(fù)的情況，但是這種情況概率較?。?，回表次數(shù)少，幾乎1次就行。

最后

針對字符串索引，一般有以下幾種創(chuàng)建方式：

1、字符串較短，直接全字段索引

2、字符串較長，且前綴區(qū)分度較好，創(chuàng)建前綴索引

3、字符串較長，前綴區(qū)分度不好，倒敘或hash方式創(chuàng)建索引（這種方式范圍查詢就不行了）

4、根據(jù)實(shí)際情況，遇到特殊字符串，特殊對待，如ip。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對腳本之家的支持。