需求

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

前綴索引

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

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

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

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

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

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

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

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

倒序+前綴索引

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

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

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

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

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

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

總結(jié)

我們之所以盡量避免對字符串加全值索引，是因為字符串字段的長度不好預(yù)估，可能會變得很大。在一個值很大的字段上做索引，會額外的占用更多的空間，數(shù)據(jù)頁上可存儲的索引值會變少，導(dǎo)致MySQL樹的高度變高，這樣查詢數(shù)據(jù)時，IO增加，性能下降。

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

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

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

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

最新評論