志在峰巔的攀登者，不會(huì)陶醉在沿途的某個(gè)腳印之中，在碼農(nóng)的世界里，優(yōu)美的應(yīng)用體驗(yàn)，來源于程序員對(duì)細(xì)節(jié)的處理以及自我要求的境界，年輕人也是忙忙碌碌的碼農(nóng)中一員，每天、每周，都會(huì)留下一些腳印，就是這些創(chuàng)作的內(nèi)容，有一種執(zhí)著，就是不知為什么，如果你迷茫，不妨來瞅瞅碼農(nóng)的軌跡。

我們這里有一張用戶表，建表語句如下

這個(gè)表中有三個(gè)索引 id 的主鍵索引、id_card 的身份索引、手機(jī)號(hào)的索引。

1 字符串 前綴索引

我們?cè)趯?shí)際業(yè)務(wù)開發(fā)中，會(huì)有手機(jī)號(hào)密碼登錄的功能，所以會(huì)常有查詢語句如下：

select * from t_user where u_phone='xxx' and u_password='xxx';

所以我們?cè)?u_phone 上添加了索引，如果不添加索引，這個(gè)查詢就會(huì)走全表掃描，當(dāng)用戶量足夠大時(shí)，這個(gè)查詢會(huì)足夠的慢。

在這里我們?yōu)?u_phone 添加的索引，默認(rèn)是11位長(zhǎng)度的（因?yàn)槭謾C(jī)號(hào)一般是11位），MySQL 是支持前綴索引的，所以可以考慮定義字符串的一部分作為索引，如這里我們把 u_phone 原來 11位的長(zhǎng)度索引修改為 4 位長(zhǎng)度的索引：

#先刪除索引 ALTER TABLE t_user DROP INDEX u_phone; #再修改 alter table t_user add index u_phone2(u_phone(4));

相比于之前的索引，后者 u_phone2 索引結(jié)構(gòu)中每個(gè)電話號(hào)碼字段都只取前 64 個(gè)字節(jié)，占用的空間會(huì)更小，查詢速度會(huì)更快，但這可能會(huì)增加額外的記錄掃描次數(shù)。

2 普通索引與前綴索引 查詢過程分析

如執(zhí)行以下查詢語句

select * from t_user where u_phone='13309090909'

如果我們添加的是普通索引如下：

alter table t_user add index u_phone('u_phone');

那么在執(zhí)行查詢過程：

1. 從 u_phone 索引樹找到滿足索引值是’13309090909’的這條記錄對(duì)應(yīng)的ID

2. 然后回表到主鍵索引上 獲取對(duì)應(yīng)ID的這一條數(shù)據(jù)

3. 然后在 u_phone 索引樹取剛剛查到的位置的下一條記錄，判斷不浪花兄弟條件，循環(huán)結(jié)束。

這個(gè)過程中，只需要回主鍵索引取一次數(shù)據(jù)，所以系統(tǒng)認(rèn)為只掃描了一行。

如果我們使用的是前綴索引，如下

alter table t_user add index u_phone2(u_phone(4));

那么在執(zhí)行查詢過程：

1. 從 u_phone2 索引樹找到滿足條件的第一個(gè)記錄ID;

2. 然后回表到主鍵上查詢獲取這一行數(shù)據(jù)，然后判斷出 這條數(shù)據(jù)中的電話號(hào)碼是 ‘13309090909’，滿足條件，保留數(shù)據(jù)，否則丟棄這條查詢

3. 然后再去 u_phone2 索引樹找到滿足條件 ‘1330’ 的ID，然后回表到ID 索引上取整行然后判斷，這次值對(duì)了，將這行記錄加入結(jié)果集;

4. 重復(fù)上一步，直到在 u_phone2 上取到的值不是’1330’時(shí)，循環(huán)結(jié)束。

在這個(gè)過程中，要回主鍵索引取 4 次數(shù)據(jù)，也就是掃描了 4 行。

當(dāng)我們?cè)偃_phone 字段的前5個(gè)字節(jié)或者是前6個(gè)字節(jié)來構(gòu)建索引，查詢次數(shù)又可能不一樣，也就是使用前綴索引，定義好長(zhǎng)度，就可以做到既節(jié)省空間，又不用額外增加太多的查詢 成本。

關(guān)鍵就是定義前綴索引的長(zhǎng)度的權(quán)衡問題

3 前綴索引的長(zhǎng)度的權(quán)衡

我們可以先查詢這個(gè)表中不同手機(jī)號(hào)碼的用戶數(shù)量

select count(distinct mobile) as L from tb_user;

比如我這個(gè)測(cè)試表中的查詢結(jié)果如下：

然后，依次選取不同長(zhǎng)度的前綴來看這個(gè)值對(duì)比一下不同數(shù)據(jù)的數(shù)量:

SELECT count(DISTINCT LEFT(mobile , 4)) AS L4 , count(DISTINCT LEFT(mobile , 5)) AS L5 , count(DISTINCT LEFT(mobile , 6)) AS L6 , count(DISTINCT LEFT(mobile , 7)) AS L7 FROM tb_user

對(duì)比一下，在這里我們可以使用 前綴7個(gè)字節(jié)，這樣區(qū)別掃描次數(shù)最少，如我們這個(gè)表中有 190 個(gè)不同的電話號(hào)碼用戶數(shù)據(jù)，如果使用電話號(hào)碼的全字段創(chuàng)建索引，有190個(gè)不同的值，在查詢數(shù)據(jù)里，掃描電話號(hào)碼索引表查詢到數(shù)據(jù)，掃描一次。

如果選用前7個(gè)字節(jié)構(gòu)建索引數(shù)據(jù)結(jié)構(gòu)，重復(fù)的索引值有 190-124 = 56個(gè)，也就是說當(dāng)查詢到前綴正好在這56個(gè)重復(fù)索引中，需要回表查詢56次進(jìn)行判斷。

如果選用前6個(gè)字節(jié)構(gòu)建索引數(shù)據(jù)結(jié)構(gòu)，重復(fù)的索引值有 190-78 = 112個(gè)，也就是說當(dāng)查詢到前綴正好在這112個(gè)重復(fù)索引中，需要回表查詢112次進(jìn)行判斷。

所以需要選擇 前綴7個(gè)字節(jié)。

完畢

不局限于思維，不局限語言限制，才是編程的最高境界。