共計(jì) 2185 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。
自動(dòng)寫(xiě)代碼機(jī)器人，免費(fèi)開(kāi)通
MySQL 中字符串索引的創(chuàng)建規(guī)則有哪些？很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面丸趣 TV 小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。
如何更好的創(chuàng)建字符串索引
我們知道，MySQL 中，數(shù)據(jù)和索引都是在一顆 B+ 樹(shù) 上，我們建立索引的時(shí)候，這棵樹(shù)所占用的空間越小，檢索速度就會(huì)越快，而 varchar 格式的字符串有些會(huì)很長(zhǎng)，那么在效率為上的今天，我們?nèi)绾胃雍侠淼慕⒆址乃饕兀?br /> 假如說(shuō)我們一張表中存在 email 字段，現(xiàn)在要給 email 字段創(chuàng)建索引，email 字段值的格式為：zhangsan@qq.com。
有 2 種建立索引的方式：
1、直接給 email 字段建立索引：alter table t add index index1(email);
索引樹(shù)結(jié)構(gòu)為：
2、建立 email 的前綴索引：alter table t add index index2(email(6));
索引數(shù)據(jù)結(jié)構(gòu)為：
此時(shí)我們的查詢(xún)語(yǔ)句為：select id,name,email from t where email= zhangsh223@xxx.com
當(dāng)使用 index1 索引時(shí)其執(zhí)行步驟為：
1、從 index1 索引樹(shù)查找索引值為 zhangsh223@xxx.com 的主鍵值 ID1；
2、根據(jù) ID1 回表查到該行數(shù)據(jù)確實(shí)為 zhangsh223@xxx.com，將結(jié)果加入結(jié)果集；
3、繼續(xù)查找 index1 索引樹(shù)下一個(gè)索引值是否滿(mǎn)足 zhangsh223@xxx.com，不滿(mǎn)足則結(jié)束查詢(xún)。
當(dāng)使用 index2 索引時(shí)其執(zhí)行步驟為：
1、從 index2 索引樹(shù)查找索引值為 zhangs 的主鍵值 ID1；
2、根據(jù) ID1 回表查到該行數(shù)據(jù)確實(shí)為 zhangsh223@xxx.com，將結(jié)果加入結(jié)果集；
3、繼續(xù)查找 index2 索引樹(shù)下一個(gè)索引值是否滿(mǎn)足 zhangs，滿(mǎn)足則繼續(xù)回表查詢(xún)?cè)撔袛?shù)據(jù)是否為 zhangsh223@xxx.com，不是則跳過(guò)繼續(xù)查找；
4、持續(xù)查找 index2 索引樹(shù)，直到索引值不是 zhangs 為止。
從以上分析中我們可以看出，全字段索引相比前綴索引來(lái)說(shuō)，減少了回表的次數(shù)，但是如果我們將前綴從 6 個(gè)增加到 7 個(gè) 8 個(gè)的話(huà)，前綴索引回表的次數(shù)就會(huì)減少，也就是說(shuō)，只要定義好前綴的長(zhǎng)度，我們就能既節(jié)省空間又保證效率。
那么問(wèn)題來(lái)了，我們?cè)趺春饬渴褂们熬Y索引的長(zhǎng)度呢？
1、使用 select count(distinct email) as L from t; 查詢(xún)字段不同值的個(gè)數(shù)；
2、依次選取不同的前綴長(zhǎng)度查看不同值的個(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í)際可接受的損失比例，選取適合的最短的前綴長(zhǎng)度。
前綴的長(zhǎng)度問(wèn)題我們解決了，但是一個(gè)問(wèn)題是，如果使用前綴索引，那我們索引覆蓋的特性就用不到了。
用全字段索引時(shí)，當(dāng)我們查詢(xún) select id,email from t where email= zhangsh223@xxx.com 時(shí)，不用回表直接就能查到 id 和 email 字段。
但是用前綴索引時(shí)，MySQL 并不清楚前綴是否會(huì)整個(gè)覆蓋 email 的值，無(wú)論是否全包含都會(huì)根據(jù)主鍵值回表查詢(xún)判斷。
所以說(shuō)，使用前綴索引雖然能節(jié)省空間保證效率但是卻不能用到覆蓋索引的特性，是否使用就在于具體考慮了。
其他字符串索引創(chuàng)建方式
實(shí)際情況實(shí)際考慮，并不是所有的字符串都能使用前綴截取的方式創(chuàng)建索引，如身份證號(hào)或者 ip 這些字符串使用前綴索引就不合理了，身份證號(hào)一般同一個(gè)地區(qū)的人前幾位都是一模一樣的，使用前綴索引就不合理了，而 ip 值我們一般在實(shí)際中將其轉(zhuǎn)化為數(shù)字去存儲(chǔ)。
針對(duì)身份證號(hào)，我們可以使用倒敘存儲(chǔ)，取前綴創(chuàng)建索引或者使用 crc32() 函數(shù)來(lái)獲取一個(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
這兩種方式相對(duì)來(lái)說(shuō)效率都差不多，都不支持范圍查找，支持等值查找。
在倒敘方式中，需要使用 reverse 函數(shù)，但是回表次數(shù)可能比 hash 方式多。
在 hash 方式中，需要新建一個(gè)索引字段并調(diào)用 crc32() 函數(shù)。（注意：crc32() 函數(shù)獲取的結(jié)果不保證能唯一，可能存在重復(fù)的情況，但是這種情況概率較?。?，回表次數(shù)少，幾乎 1 次就行。
最后
針對(duì)字符串索引，一般有以下幾種創(chuàng)建方式：
1、字符串較短，直接全字段索引
2、字符串較長(zhǎng)，且前綴區(qū)分度較好，創(chuàng)建前綴索引
3、字符串較長(zhǎng)，前綴區(qū)分度不好，倒敘或 hash 方式創(chuàng)建索引（這種方式范圍查詢(xún)就不行了）
4、根據(jù)實(shí)際情況，遇到特殊字符串，特殊對(duì)待，如 ip。
看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注丸趣 TV 行業(yè)資訊頻道，感謝您對(duì)丸趣 TV 的支持。
向 AI 問(wèn)一下細(xì)節(jié)
