需求

在日常需求中，用戶使用手機(jī)號或者郵箱登錄某一個(gè)系統(tǒng)，是一個(gè)很常見的操作，那如何在類似手機(jī)號或者郵箱這樣的字段上建立一個(gè)合理的索引呢？

前綴索引

前綴索引，就是以一個(gè)字段值的一部分作為索引。我們在MySQL中創(chuàng)建索引時(shí)，如果不指定索引字段的長度，那么就會(huì)以整個(gè)字符串來建立索引。

語句1：
alter table test add index idx(email);
語句2：
alter table test add index idx(email(8));

對于語句1，創(chuàng)建的索引中，會(huì)包含每條記錄中的整個(gè)email字符串值。

對于語句2，創(chuàng)建的索引中，保存的是每條記錄中email字段的前8個(gè)字節(jié)。

使用前綴索引的優(yōu)勢很明顯，那就是索引占用的空間會(huì)更小，整個(gè)索引樹會(huì)更緊湊，樹的高度相對更低。

但是相應(yīng)的，索引的區(qū)分度會(huì)變低，可能導(dǎo)致索引掃描行數(shù)增加。

在我們創(chuàng)建索引時(shí)，索引的區(qū)分度是一個(gè)很重要的指標(biāo)。區(qū)分度越高，重復(fù)的值就越少，掃描的效率就越高。

在使用前綴索引時(shí)，合理規(guī)劃使用的前綴長度，不僅可以節(jié)省空間，還可以不用額外增加掃描的行數(shù)。具體使用多少的長度，建議根據(jù)我們的實(shí)際業(yè)務(wù)場景來判斷、測試。

倒序+前綴索引

倒序+前綴索引有一個(gè)經(jīng)典的使用場景，就是對身份證號做索引。

假設(shè)我們現(xiàn)在要維護(hù)一個(gè)市縣所有人的身份信息，其中按身份證號查詢是一個(gè)高頻場景。

身份證號碼一共15位或者18位，一般來說，同一個(gè)市縣的人身份證號前6位一般是相同的，如果直接對身份證號做全索引，那么會(huì)比較浪費(fèi)空間，導(dǎo)致性能下降。直接前綴索引的話，前6位的區(qū)分度又很低（甚至可以說沒有，因?yàn)榇蠹仪?位基本都一樣）。

這時(shí)，使用倒序+前綴索引的好處就體現(xiàn)出來了。

我們先將身份證倒序存儲(chǔ)，或者冗余一個(gè)倒序的身份證號字段，然后取前6位做前綴索引。

身份證后6位的區(qū)分度已經(jīng)基本上夠我們使用了，如果你還覺得查詢速度不夠，那取前8位也是沒問題的。

總結(jié)

我們之所以盡量避免對字符串加全值索引，是因?yàn)樽址侄蔚拈L度不好預(yù)估，可能會(huì)變得很大。在一個(gè)值很大的字段上做索引，會(huì)額外的占用更多的空間，數(shù)據(jù)頁上可存儲(chǔ)的索引值會(huì)變少，導(dǎo)致MySQL樹的高度變高，這樣查詢數(shù)據(jù)時(shí)，IO增加，性能下降。

但是，凡事都有例外，有時(shí)我們也不能為了優(yōu)化而優(yōu)化。

比如我們現(xiàn)在要維護(hù)一個(gè)高校的師生信息，按整個(gè)學(xué)校2萬人，每年新增1萬新學(xué)生來計(jì)算，10年時(shí)間也不過12萬數(shù)據(jù)而已。即使在一個(gè)身份證號字段上加上全字段索引又能多占用多少空間呢？能省出多少性能呢？

有時(shí)，從業(yè)務(wù)量預(yù)估優(yōu)化和收益比，也是一個(gè)很好的習(xí)慣。

到此這篇關(guān)于MySQL給字符串加一個(gè)高效索引的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL字符串高效索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！