需求

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

前綴索引

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

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

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

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

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

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

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

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

倒序+前綴索引

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

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

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

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

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

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

總結(jié)

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

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

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

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

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

最新評(píng)論