[[439434]]

聯(lián)合索引的最左前綴原則屬于面試高頻題，想必大部分同學(xué)都知道一些，但是，那些不符合最左前綴的部分，會(huì)怎么樣呢(索引下推)

索引下推不算高頻題，知道的同學(xué)應(yīng)該不是很多(不過并不代表有啥難度哈，挺簡單的)，學(xué)起來裝波杯

老規(guī)矩，背誦版在文末。點(diǎn)擊閱讀原文可以直達(dá)我收錄整理的各大廠面試真題

引子

看下面這張用戶表，包含主鍵 id、身份證號(hào) id_card、姓名 name、年齡 age和性別 sex，并且在 id_card 上建立了輔助索引(普通索引/非聚集索引)

如果現(xiàn)在有一個(gè)高頻請求，要根據(jù)市民的身份證號(hào)查詢他的姓名：

select name from user where id_card = xxx; 

眾所周知，這會(huì)導(dǎo)致回表查詢，通過 id_card 這棵輔助索引樹只能找到主鍵 id，然后需要再回到主鍵索引(聚集索引)樹上根據(jù)主鍵 id 查找相應(yīng)的 name。

所以，這個(gè)時(shí)候，我們可以建立一個(gè) (id_card, name) 的聯(lián)合索引來進(jìn)行優(yōu)化，對于這條語句來說，也就是覆蓋索引，在這個(gè)高頻請求上用到覆蓋索引，不再需要回表查整行記錄，大幅減少了語句的執(zhí)行時(shí)間。

不過，索引字段的維護(hù)總是有代價(jià)的，如果為每一種查詢都設(shè)計(jì)一個(gè)聯(lián)合索引，索引是不是太多了?反過來說，單獨(dú)為一個(gè)不頻繁的請求創(chuàng)建一個(gè)聯(lián)合索引是不是有點(diǎn)浪費(fèi)了。因此在建立冗余索引來支持覆蓋索引時(shí)就需要我們?nèi)プ龀鲆恍?quán)衡考慮了。

具體來說，我們應(yīng)該怎么做呢?

最左前綴原則

B+ 樹這種索引結(jié)構(gòu)，可以利用聯(lián)合索引的 “最左前綴” 來定位記錄。

何為聯(lián)合索引的最左前綴原則?

從本質(zhì)上來說，聯(lián)合索引也是一棵 B+ 樹，不同的是聯(lián)合索引的鍵值的數(shù)量不是 1，而是大于等于 2。我們來看下兩個(gè)整型列組成的聯(lián)合索引，假定兩個(gè)鍵值的名稱分別為 a、b。

從圖中可以看到多個(gè)鍵值的 B+ 樹情況，鍵值都是排序的。通過葉子節(jié)點(diǎn)可以邏輯上順序讀取所有數(shù)據(jù)，就上面圖中所示，即為 (1，1)、(1、2)、(2、1)、(2、4)、(3、1)、(3、2)，數(shù)據(jù)是按照 (a, b) 的順序進(jìn)行存放。

?? 這里 “**鍵值都是排好序” 的這種說法可能會(huì)讓大伙很疑惑，**似乎只有 a 列是排序的，b 列并沒有排序啊。

注意!這里的排序，意思是確定了第一個(gè)鍵，對于第一個(gè)鍵相同的記錄來說，查詢的結(jié)果是對第二個(gè)鍵進(jìn)行了排序。

這也是**使用聯(lián)合索引的第二個(gè)好處，即已經(jīng)對第二個(gè)鍵值進(jìn)行了排序處理，可以避免多一次排序操作。**舉個(gè)例子：有些應(yīng)用程序都需要查詢某個(gè)用戶的購物情況，并按照時(shí)間進(jìn)行排序，取出最近 n 次的購買記錄，這時(shí)使用聯(lián)合索引就可以避免多一次排序操作，因?yàn)樗饕旧碓谌~子節(jié)點(diǎn)已經(jīng)排序了。

更進(jìn)一步說，假設(shè)有聯(lián)合索引(a，b，c)，下列語句可以直接通過聯(lián)合索引得到結(jié)果：

• SELECT ... FROM TABLE WHERE a=xxx ORDER BY b
• SELECT ... FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c

但是對于下面的語句，聯(lián)合索引不能直接得到結(jié)果，其還需要執(zhí)行一次排序操作，因?yàn)樗饕?(a，c) 并未排序：

• SELECT ... FROM TABLE WHERE a=xxx ORDER BY c

說了這么多，好像和最左前綴啥關(guān)系也沒有啊

考慮下，對于下面這條語句，能否用到聯(lián)合索引(a, b)?

select * from table where a = XXX and b= XXX; 

這個(gè)當(dāng)然沒問題。

那對于 a 列的單獨(dú)查詢，能否用到聯(lián)合索引(a, b)?

select * from table where a = XXX; 

當(dāng)然也可以，我們不是說了，a 列是已經(jīng)排好序的。

但是對于 b 列的單獨(dú)查詢則不能使用聯(lián)合索引(a, b)!

select * from table where b = XXX; 

因?yàn)榘讶~子節(jié)點(diǎn)中的 b 值單獨(dú)拎出來看它不是有序的：1、2、1、4、1、2，因此對于 b 的查詢是使用不到 (a，b) 這個(gè)聯(lián)合索引的。

同樣的道理，對于(a, b, c)聯(lián)合索引來說，查詢 (a, b) 可以用到這個(gè)聯(lián)合索引，但是查詢 (b, c) 就沒辦法使用這個(gè)聯(lián)合索引，因?yàn)?b 和 c 列的有序性都是依托于 a 列的存在的。

This，就是聯(lián)合索引的最左前綴原則，只要查詢的是聯(lián)合索引的最左 N 個(gè)字段，就可以利用該聯(lián)合索引來加速查詢。

基于上面對最左前綴索引的說明以及用戶表的例子，我們來討論一個(gè)問題：在建立聯(lián)合索引的時(shí)候，如何安排索引內(nèi)的字段順序?

有兩點(diǎn)原則。

首先，第一原則，如果通過調(diào)整順序，可以少維護(hù)一個(gè)索引，那么這個(gè)字段順序往往就是需要優(yōu)先考慮采用的

很好理解，當(dāng)已經(jīng)有了 (a,b) 這個(gè)聯(lián)合索引后，一般就不需要單獨(dú)在 a 上建立索引了。

那么，再思考一個(gè)問題：如果既有聯(lián)合查詢 (a,b)，又有基于 a、b 各自的查詢呢?

顯然，如果查詢條件里面只有 b 的語句，是無法使用 (a,b) 這個(gè)聯(lián)合索引的，這時(shí)候你不得不維護(hù)另外一個(gè) b 列的索引，也就是說你需要同時(shí)維護(hù) (a,b)、(b) 這兩個(gè)索引。

舉個(gè)用戶表例子，有這樣三個(gè)高頻查詢需求：

• 根據(jù) name 查詢 id：select id from user where name = xxx;
• 根據(jù) age 查詢 id：select id from user where age= xxx;
• 根據(jù) name 和 age 查詢 id：select id from user where name = xxx and age = xxx;

這個(gè)時(shí)候，我們有兩種索引建立的選擇：

• 聯(lián)合索引 (age, name) + 單字段索引 (name)
• 聯(lián)合索引 (name, age) + 單字段索引 (age)

怎么選?

這種場景下，我們要考慮的原則就是空間。

顯然，name 字段是要比 age 字段大的，所以，第二種選擇占用的空間要小于第一種選擇，推薦大伙兒使用第二種選擇：聯(lián)合索引 (name, age) + 單字段索引 (age)

索引下推

最左前綴可以用于在索引中定位記錄，那么，那些不符合最左前綴的部分，會(huì)怎么樣呢?

以用戶表的聯(lián)合索引(name, age)為例，假設(shè)現(xiàn)在有一個(gè)需求，找出所有姓 “張” 并且 20 歲的男性：

select * from tuser where name like '張%' and age = 20 and sex = male 

《高性能 MySQL》 書中提到：對于聯(lián)合索引，如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無法使用索引進(jìn)行快速定位。

所以對于這條語句來說，其實(shí)并不能完全踩中 (name, age) 這個(gè)聯(lián)合索引，他只能踩到 name。

具體來說，這個(gè)語句在搜索(name，age)的聯(lián)合索引樹的時(shí)候，并不會(huì)去看 age 的值，只是按順序把 “name 第一個(gè)字是張” 的記錄一條條取出來，然后開始回表，到主鍵索引上找出數(shù)據(jù)行，再一個(gè)一個(gè)判斷其他條件是否滿足。從下圖可以看出來，需要回表 3 次。

這是 MySQL 5.6 之前的做法，簡單總結(jié)，當(dāng)進(jìn)行索引查詢時(shí)，首先根據(jù)索引來查找記錄，然后再根據(jù) where 條件來過濾記錄。

而 MySQL 5.6 開始，數(shù)據(jù)庫在取出索引的同時(shí)，會(huì)根據(jù) where 條件直接過濾掉不滿足條件的記錄，減少回表次數(shù)。這就是 索引下推 (Index Condition Pushdown,ICP) ，一種根據(jù)索引進(jìn)行查詢的優(yōu)化方式。

從圖中可以看出來，InnoDB 在 (name,age) 索引內(nèi)部就判斷了 age 是否等于 20，對于不等于 20 的記錄，直接判斷并跳過，所以只需要對 ID1 這條記錄進(jìn)行回表判斷就可以了。

面試官：講一下聯(lián)合索引的最左前綴原則，為什么得最左匹配，不按照這個(gè)來為什么失效?

小牛肉：最左前綴原則就是只要查詢的是聯(lián)合索引的最左 N 個(gè)字段，就可以利用該聯(lián)合索引來加速查詢。

不按照最左匹配來為什么失效，其原因就在于聯(lián)合索引的 B+ 樹中的鍵值是排好序的。不過，這里指的排好序，其實(shí)是相對的，舉個(gè)例子，有 (a, b, c) 聯(lián)合索引，a 首先是排序好的，而 b 列是在 a 列排序的基礎(chǔ)上做的排序，同樣的 c 是在 a,b 有序的基礎(chǔ)上做的排序。所以說，如果有 where a = xxx order by b = xxx 這種請求的話，是可以直接在這顆聯(lián)合索引樹上查出來的，不用對 b 列進(jìn)行額外的排序;而如果是 where a = xxx order by c = xxx 這種請求的話，還需要額外對 c 列進(jìn)行一次排序才行。

另外，如果有對 a,b,c 的聯(lián)合條件查詢的話，并且 a 是模糊匹配或者說是范圍查詢的話，其實(shí)并不能完全踩中聯(lián)合索引(a,b,c)，a 列右邊的所有列都無法使用索引進(jìn)行快速定位了。所以這個(gè)時(shí)候就需要進(jìn)行回表判斷。也就是說數(shù)據(jù)庫會(huì)首先根據(jù)索引來查找記錄，然后再根據(jù) where 條件來過濾記錄。

不過在 MySQL 5.6 中支持了索引下推 ICP，數(shù)據(jù)庫在取出索引的同時(shí)，會(huì)根據(jù) where 條件直接過濾掉不滿足條件的記錄，減少回表次數(shù)。