現(xiàn)在主流網(wǎng)站都支持手機(jī)號(hào)登錄，如何在手機(jī)號(hào)這樣的字符串字段建立合適的索引呢？
假設(shè)，你現(xiàn)在維護(hù)一個(gè)支持郵箱登錄的系統(tǒng)，用戶表是這么定義的：
create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;
要使用郵箱登錄，會(huì)有語(yǔ)句：
select f1, f2 from SUser where email='xxx';
若email字段無(wú)索引，該語(yǔ)句只能全表掃描。
MySQL支持前綴索引，可定義字符串的一部分作為索引。
若創(chuàng)建索引的語(yǔ)句不指定前綴長(zhǎng)度，那么索引默認(rèn)包含整個(gè)字符串。
比如，這倆在email字段創(chuàng)建索引的語(yǔ)句：
alter table SUser add index index1(email);
alter table SUser add index index2(email(6));
- 第一個(gè)語(yǔ)句創(chuàng)建的index1索引，包含每個(gè)記錄的整個(gè)字符串
- 第二個(gè)語(yǔ)句創(chuàng)建的index2索引，對(duì)每個(gè)記錄都只取前6個(gè)字節(jié)
可見，email(6)索引結(jié)構(gòu)中每個(gè)郵箱字段都只取前6字節(jié)（zhangs），占用空間更小，這就是前綴索引優(yōu)勢(shì)。
這同時(shí)帶來?yè)p失：可能會(huì)增加額外的記錄掃描次數(shù)。
看看下面這語(yǔ)句，在這倆索引定義分別怎么執(zhí)行。select id,name,email from SUser where email='[email protected]'; 如果使用index1，執(zhí)行順序如下：
- 從index1索引樹找到滿足索引值 ‘[email protected]’的記錄，取得ID2的值
- 到主鍵上查到主鍵值是ID2的行，判斷email值是正確的，將改行記錄加入結(jié)果集
- 取index1索引樹上剛剛查到位置的下條記錄，發(fā)現(xiàn)已不滿足email=’[email protected]’條件，結(jié)束循環(huán)
該過程，只需回主鍵索引取一次數(shù)據(jù)，所以系統(tǒng)認(rèn)為只掃描一行。
如果使用是index2，執(zhí)行順序如下： - 從index2索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個(gè)是ID1
- 到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’[email protected]’，該行記錄丟棄
- 取index2上剛剛查到的位置的下條記錄，仍是’zhangs’，取出ID2，再到ID索引取整行判斷，這次值對(duì)，將該行記錄加入結(jié)果集
- 重復(fù)上一步，直到在idxe2上取值不是’zhangs’，結(jié)束循環(huán)結(jié)束
該過程，要回主鍵索引取4次數(shù)據(jù)，即掃描4行。
對(duì)比發(fā)現(xiàn)，使用前綴索引，可能導(dǎo)致查詢語(yǔ)句讀數(shù)據(jù)的次數(shù)變多。
但對(duì)該查詢語(yǔ)句，如果定義index2不是email(6)而是email(7），即取email字段前7字節(jié)構(gòu)建索引，即滿足前綴’zhangss’記錄只有一個(gè)，也能直接查到ID2，只掃描一行結(jié)束。
即使用前綴索引，定義好長(zhǎng)度，就可做到既節(jié)省空間，又不用增加額外太多的查詢成本。
要給字符串創(chuàng)建前綴索引1 確定前綴長(zhǎng)度 在建立索引時(shí)我們關(guān)注的是區(qū)分度，區(qū)分度越高越好。區(qū)分度越高，重復(fù)的鍵值越少。因此可通過統(tǒng)計(jì)索引上有多少不同值判斷要使用多長(zhǎng)前綴。
可使用如下語(yǔ)句，計(jì)算該列上有多少不同值select count(distinct email) as L from SUser; 依次選取不同長(zhǎng)度前綴來測(cè)該值，比如看4~7個(gè)字節(jié)前綴索引：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 SUser; 使用前綴索引可能會(huì)損失區(qū)分度，所以需要預(yù)先設(shè)定一個(gè)可接受損失比例，比如5%。
然后，在返回的L4~L7中，找出不小于 L * 95%的值，假設(shè)L6、L7都滿足時(shí)，即可選擇前綴長(zhǎng)度最短為6。2 前綴索引對(duì)覆蓋索引的影響 看如下SQL：select id,email from SUser where email='[email protected]'; 與前例SQL語(yǔ)句：select id,name,email from SUser where email='[email protected]'; 相比，該語(yǔ)句只要求返回id和email。
若使用index1，可利用覆蓋索引，從index1查到結(jié)果后直接返回，不需回到ID索引再查一次。
而若使用index2（email(6)），得回ID索引再判斷email字段值。
即使將index2定義改為email(18)，雖然index2已包含所有信息，但I(xiàn)nnoDB還是要回id索引再查，因?yàn)橄到y(tǒng)并不確定前綴索引的定義是否截?cái)嗔送暾畔ⅰ?br />即前綴索引根本用不上覆蓋索引對(duì)查詢的優(yōu)化，這也是選擇是否使用前綴索引時(shí)需要考慮的因素。3 其他方案 對(duì)類似郵箱這樣字段，前綴索引可能還行。但遇到前綴區(qū)分度不好的，怎么辦？
比如身份證號(hào)18位，前6位是地址碼，所以同縣人身份證號(hào)前6位一般相同。
假設(shè)維護(hù)數(shù)據(jù)庫(kù)是個(gè)市公民信息系統(tǒng)，若對(duì)身份證號(hào)做長(zhǎng)度6前綴索引，區(qū)分度非常低。
可能需創(chuàng)建長(zhǎng)度12以上前綴索引，才能夠滿足區(qū)分度要求。
但索引選取越長(zhǎng)，占磁盤空間越大，相同數(shù)據(jù)頁(yè)能放下索引值越少，查詢效率就越低。
- 若能確定業(yè)務(wù)需求只有按身份證進(jìn)行等值查詢的需求，還有沒有別的處理方法，既可占用更小空間，也能達(dá)到相同查詢效率？
Yes！
第一種方式使用3.1 倒序存儲(chǔ) 如果存儲(chǔ)身份證號(hào)時(shí)把它倒過來存，每次查詢這么寫：select field_list from t where id_card = reverse('input_id_card_string'); 由于身份證號(hào)最后6位沒有地址碼這樣重復(fù)邏輯，所以最后6位可能提供足夠的區(qū)分度。
實(shí)踐中也別忘記使用count(distinct)驗(yàn)證區(qū)分度哦！
第二種方式是使用3.2 hash字段 可在表再創(chuàng)建整數(shù)字段，保存身份證的校驗(yàn)碼，同時(shí)在該字段創(chuàng)建索引。alter table t add id_card_crc int unsigned, add index(id_card_crc); 每次插新記錄時(shí)，同時(shí)用crc32()函數(shù)得到校驗(yàn)碼填到該新字段。
由于校驗(yàn)碼可能存在沖突，即兩不同身份證號(hào)crc32()所得結(jié)果可能相同（哈希沖突），所以查詢語(yǔ)句where部分要判斷id_card值是否精確相同。select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string' 這索引長(zhǎng)度變4字節(jié)，比原來小很多。3.3 倒序存儲(chǔ)和使用hash字段異同點(diǎn) 相同點(diǎn) 都不支持范圍查詢。 - 倒序存儲(chǔ)的字段上創(chuàng)建的索引
按倒序字符串的方式排序，已無(wú)法利用索引查出身份證號(hào)碼在[ID_X, ID_Y]的所有市民 - hash字段也只支持等值查詢區(qū)別 占用的額外空間
- 倒序存儲(chǔ)在主鍵索引上，不會(huì)消耗額外存儲(chǔ)空間
當(dāng)然，倒序存儲(chǔ)使用4字節(jié)前綴長(zhǎng)度應(yīng)該不夠，若再長(zhǎng)點(diǎn)，這消耗和hash字段也差不多了 - hash字段需要加個(gè)字段CPU消耗
- 倒序方式每次讀寫時(shí)，都需額外調(diào)用次reverse函數(shù)
- hash字段需額外調(diào)用一次crc32()函數(shù)
若只從這倆函數(shù)計(jì)算復(fù)雜度看，reverse函數(shù)額外消耗CPU資源較少。查詢效率 - hash字段查詢性能較穩(wěn)定
因?yàn)閏rc32值雖然會(huì)沖突，但概率很小，可認(rèn)為每次查詢的平均掃描行數(shù)接近1 - 倒序存儲(chǔ)
還是前綴索引，即還是會(huì)增加掃描行數(shù)總結(jié) 字符串字段創(chuàng)建索引的場(chǎng)景，可使用的方式如下：
- 直接創(chuàng)建完整索引，這樣可能比較占用空間
- 創(chuàng)建前綴索引，節(jié)省空間，但增加查詢掃描次數(shù)，且不能使用覆蓋索引
- 倒序存儲(chǔ)，再創(chuàng)建前綴索引，用于繞過字符串本身前綴的區(qū)分度不足缺陷
- 創(chuàng)建hash字段索引，查詢性能穩(wěn)定，有額外存儲(chǔ)和計(jì)算消耗，跟第三種方式一樣不支持范圍掃描
實(shí)際應(yīng)用中，根據(jù)業(yè)務(wù)字段的特點(diǎn)選擇使用哪種方式。思考題 維護(hù)學(xué)生信息數(shù)據(jù)庫(kù)，學(xué)生登錄名的統(tǒng)一格式是”學(xué)號(hào)@gmail.com”
學(xué)號(hào)的則是：十五位的數(shù)字，其中前三位是所在城市編號(hào)、第四到第六位是學(xué)校編號(hào)、第七位到第十位是入學(xué)年份、最后五位是順序編號(hào)。
系統(tǒng)登錄時(shí)輸入登錄名和密碼，驗(yàn)證正確后才能繼續(xù)使用系統(tǒng)。
只考慮登錄驗(yàn)證，怎么設(shè)計(jì)這個(gè)登錄名的索引呢？
上期我留給你的問題是，給一個(gè)學(xué)號(hào)字段創(chuàng)建索引，有哪些方法。
由于學(xué)號(hào)規(guī)則，無(wú)論正向反向前綴索引，重復(fù)度都較高。
因?yàn)榫S護(hù)的只是一個(gè)學(xué)校的，因此前面6位（其中，前三位是所在城市編號(hào)、第四到第六位是學(xué)校編號(hào)）固定，郵箱后綴都是@gamil.com，因此可只存入學(xué)年份加順序編號(hào)，長(zhǎng)度9位。
在此基礎(chǔ)，可用數(shù)字型存這9位數(shù)字。比如201100001，只需占4字節(jié)。其實(shí)這就是種hash，只是用最簡(jiǎn)單轉(zhuǎn)換規(guī)則：字符串轉(zhuǎn)數(shù)字，而剛好我們?cè)O(shè)定背景，可保證轉(zhuǎn)換結(jié)果唯一。
當(dāng)然了，一個(gè)學(xué)校的總?cè)藬?shù)這種數(shù)據(jù)量，50年才100萬(wàn)學(xué)生，這表肯定是小表。為了業(yè)務(wù)簡(jiǎn)單，直接存原來的字符串。“優(yōu)化成本和收益”的思想。
- 參考
《MySQL 實(shí)戰(zhàn) 45 講》
