大家在背 MySQL 八股文的時候，是不是經(jīng)郴Ω幔看到這句話饥伊。

聯(lián)合索引的最左匹配原則會一直向右匹配直到遇到范圍查詢(>、<蔫饰、between琅豆、like) 就會停止匹配。

我隨手在網(wǎng)上搜了下篓吁， 基本全部都是這個結(jié)論茫因，似乎這個結(jié)論大家都耳濡目染了，應該大多數(shù)人都覺得這個結(jié)論是正確的吧杖剪。

我在昨晚折騰了幾個實驗冻押，發(fā)現(xiàn)這個結(jié)論并不全對驰贷！去掉 「between 和 like 」這個結(jié)論就沒問題了。

經(jīng)過實驗的證明洛巢，我得出的結(jié)論是這樣的：

聯(lián)合索引的最左匹配原則括袒，在遇到范圍查詢（如 >、<）的時候稿茉，就會停止匹配锹锰，也就是范圍查詢的字段可以用到聯(lián)合索引，但是在范圍查詢字段后面的字段無法用到聯(lián)合索引漓库。但是恃慧，對于 >=、<=米苹、BETWEEN糕伐、like 前綴匹配這四種范圍查詢砰琢，并不會停止匹配蘸嘶。

接下來，我會用幾個實驗例子來說明這個結(jié)論陪汽。

B+Tree 索引

首先训唱，先來認識下 B+Tree 索引。

MySQL 的 InnoDB 存儲引擎會為每一張數(shù)據(jù)庫表創(chuàng)建一個「聚簇索引」來保存表的數(shù)據(jù)挚冤，聚簇索引默認使用的是 B+Tree 索引况增。

為了讓大家理解 B+Tree 索引的存儲和查詢的過程，接下來我通過一個簡單例子训挡，說明一下 B+Tree 索引在存儲數(shù)據(jù)中的具體實現(xiàn)澳骤。

假設有一張商品表，表里有這些數(shù)據(jù)：

這些數(shù)據(jù)澜薄，存儲在 B+Tree 索引時是長什么樣子的为肮？

B+Tree 是一種多叉樹，葉子節(jié)點才存放數(shù)據(jù)肤京，非葉子節(jié)點只存放索引颊艳，而且每個節(jié)點里的數(shù)據(jù)是按主鍵值（id）順序存放的，每一層父節(jié)點的索引值都會出現(xiàn)在下層子節(jié)點的索引值中忘分，因此在葉子節(jié)點中棋枕，包括了所有的索引值信息，并且每一個葉子節(jié)點都指向下一個葉子節(jié)點妒峦，形成一個鏈表重斑，便于范圍查詢。

聚簇索引的 B+Tree 如圖所示：

假設肯骇，執(zhí)行了 select * from t_product where id = 5 查詢語句窥浪，該查詢語句的條件是找到 id（主鍵）為 5 的這條記錄卤恳。因為 B+Tree 是一個有序的數(shù)據(jù)結(jié)構，所以可以通過二分查找算法快速定位到這條記錄寒矿，這也就是我們常說的索引查詢突琳，具體過程如下：

• 從根節(jié)點開始，將 5 與根節(jié)點的索引數(shù)據(jù) (1符相，10拆融，20) 比較，5 在 1 和 10 之間啊终，根據(jù)二分查找算法镜豹，找到第二層的索引數(shù)據(jù) (1，4蓝牲，7)趟脂；
• 在第二層的索引數(shù)據(jù) (1，4例衍，7)中進行查找昔期，因為 5 在 4 和 7 之間，根據(jù)二分查找算法佛玄，找到第三層的索引數(shù)據(jù)（4硼一，5，6）梦抢；
• 在葉子節(jié)點的索引數(shù)據(jù)（4般贼，5，6）中進行查找奥吩，然后我們找到了索引值為 5 的這條記錄哼蛆。

聚簇索引只能用于主鍵字段的快速查詢，如果想實現(xiàn)「非主鍵字段」的快速查詢霞赫，我們就要針對「非主鍵字段」創(chuàng)建索引腮介，這種索引稱作為「二級索引」。二級索引同樣基于 B+Tree 實現(xiàn)的绩脆，不過二級索引的葉子節(jié)點存放的是主鍵值萤厅，不是實際數(shù)據(jù)。

我這里將前面的商品表中的 product_no （商品編碼）字段設置為二級索引靴迫，那么二級索引的 B+Tree 如下圖惕味，其中非葉子的索引值是 product_no（圖中橙色部分），葉子節(jié)點存儲的數(shù)據(jù)是主鍵值（圖中綠色部分）玉锌。

如果我用 product_no 二級索引查詢商品名挥，如下查詢語句：

select * from product where product_no = '0002';

會先在二級索引的 B+Tree 中快速查找到 product_no 為 0002 的二級索引記錄，然后獲取主鍵值主守，然后利用主鍵值在主鍵索引的 B+Tree 中快速查詢到對應的葉子節(jié)點禀倔，然后獲取完整的記錄榄融。這個過程叫「回表」，也就是說要查兩個 B+Tree 才能查到數(shù)據(jù)救湖。如下圖：

不過愧杯，當查詢的數(shù)據(jù)是能在二級索引的 B+Tree 的葉子節(jié)點里查詢到，這時就不用再查主鍵索引查鞋既，比如下面這條查詢語句：

select id from product where product_no = '0002';

這種在二級索引的 B+Tree 就能查詢到結(jié)果的過程就叫作「覆蓋索引」力九，也就是只需要查一個 B+Tree 就能找到數(shù)據(jù)。

什么是聯(lián)合索引邑闺？

前文我將 product_no 字段設置為了索引跌前，這種二級索引只有一個字段。如果將多個字段組合成一個索引陡舅，那么這種二級索引就被稱為聯(lián)合索引抵乓。

比如，將商品表中的 product_no 和 name 字段組合成聯(lián)合索引`(product_no, name)``靶衍，創(chuàng)建聯(lián)合索引的方式如下：

CREATE INDEX index_product_no_name ON product(product_no, name);

聯(lián)合索引 ``(product_no, name)` 的 B+Tree 示意圖如下：

可以看到灾炭，聯(lián)合索引的非葉子節(jié)點用兩個字段的值作為 B+Tree 的索引值。

聯(lián)合索引的 B+Tree 是先按 product_no 進行排序摊灭，然后再 product_no 相同的情況再按 name 字段排序咆贬。記住這句話，很重要帚呼！

最左匹配原則

使用聯(lián)合索引時，存在最左匹配原則皱蹦，也就是按照最左優(yōu)先的方式進行索引的匹配煤杀。

在使用聯(lián)合索引進行查詢的時候，如果不遵循「最左匹配原則」沪哺，聯(lián)合索引會失效沈自，這樣就無法利用到索引快速查詢的特性了。

比如辜妓，如果創(chuàng)建了一個 (a, b, c) 聯(lián)合索引枯途，如果查詢條件是以下這幾種，就可以利用聯(lián)合索引：

• where a=1籍滴；
• where a=1 and b=2 and c=3酪夷；
• where a=1 and b=2；

需要注意的是孽惰，因為有查詢優(yōu)化器晚岭，所以 a 字段在 where 子句的順序并不重要。但是勋功，如果查詢條件是以下這幾種坦报，因為不符合最左匹配原則库说，所以就無法匹配上聯(lián)合索引，聯(lián)合索引就會失效:

• where b=2片择；
• where c=3潜的；
• where b=2 and c=3；

上面這些查詢條件之所以會失效字管，是因為(a, b, c) 聯(lián)合索引夏块，是先按 a 排序，在 a 相同的情況再按 b 排序纤掸，在 b 相同的情況再按 c 排序脐供。所以，b 和 c 是全局無序借跪，局部相對有序的政己，這樣在沒有遵循最左匹配原則的情況下，是無法利用到索引的掏愁。

我這里舉聯(lián)合索引（a歇由，b）的例子，該聯(lián)合索引的 B+ Tree 如下：

可以看到果港，a 是全局有序的（1, 2, 2, 3, 4, 5, 6, 7 ,8）沦泌，而 b 是全局是無序的（12，7辛掠，8谢谦，2，3萝衩，8回挽，10，5猩谊，2）千劈。因此，直接執(zhí)行 where b = 2 這種查詢條件沒有辦法利用聯(lián)合索引的牌捷，利用索引的前提是索引里的 key 是有序的墙牌。

只有在 a 相同的情況才，b 才是有序的暗甥，比如 a 等于 2 的時候喜滨，b 的值為（7，8）淋袖，這時就是有序的鸿市，這個有序狀態(tài)是局部的，因此，執(zhí)行 where a = 2 and b = 7 這種查詢條件時焰情， a 和 b 字段能用到聯(lián)合索引的陌凳，也就是聯(lián)合索引生效了。

聯(lián)合索引范圍查詢

聯(lián)合索引有一些特殊情況内舟，并不是查詢過程使用了聯(lián)合索引查詢合敦，就代表聯(lián)合索引中的所有字段都用到了聯(lián)合索引進行索引查詢，也就是可能存在部分字段用到聯(lián)合索引的 B+Tree验游，部分字段沒有用到聯(lián)合索引的 B+Tree 的情況充岛。

這種特殊情況就發(fā)生在范圍查詢。也就是文章開頭的那句話：聯(lián)合索引的最左匹配原則會一直向右匹配直到遇到「范圍查詢」就會停止匹配耕蝉。也就是范圍查詢的字段可以用到聯(lián)合索引崔梗，但是范圍查詢字段的后面的字段無法用到聯(lián)合索引。

范圍查詢有很多種垒在，那到底是哪些范圍查詢會導致聯(lián)合索引的最左匹配原則會停止匹配呢蒜魄？

接下來，舉例幾個范圍查詢的例子场躯，下面的實驗案例是基于 MySQL 8.0 做的谈为。

例子一

Q1: select * from t_table where a > 1 and b = 2，聯(lián)合索引（a, b）哪一個字段用到了聯(lián)合索引的 B+Tree踢关？

由于聯(lián)合索引（二級索引）是先按照 a 字段的值排序的伞鲫，所以符合 a > 1 條件的二級索引記錄肯定是相鄰的，于是在進行索引掃描的時候签舞，可以定位到符合 a > 1 條件的第一條記錄秕脓，然后沿著記錄所在的鏈表向后掃描，直到某條記錄不符合 a > 1 條件位置瘪菌。所以 a 字段可以在聯(lián)合索引的 B+Tree 中進行索引查詢撒会。

但是在符合 a > 1 條件的二級索引記錄的范圍里，b 字段的值是無序的师妙。

比如，下圖的聯(lián)合索引的 B+ Tree 里：

下面這三條記錄的 a 字段的值都符合 a > 1 查詢條件屹培，而 b 字段的值是無序的：

• a 字段值為 5 的記錄默穴，該記錄的 b 字段值為 8；
• a 字段值為 6 的記錄褪秀，該記錄的 b 字段值為 10蓄诽；
• a 字段值為 7 的記錄，該記錄的 b 字段值為 5媒吗；

因此仑氛，我們不能根據(jù)查詢條件 b = 2 來進一步減少需要掃描的記錄數(shù)量（b 字段無法利用聯(lián)合索引進行索引查詢的意思）。

所以在執(zhí)行 Q1 這條查詢語句的時候，對應的掃描區(qū)間是 (2, + ∞)锯岖，形成該掃描區(qū)間的邊界條件是 a > 1介袜，與 b = 2 無關。

因此出吹，Q1 這條查詢語句只有 a 字段用到了聯(lián)合索引進行索引查詢遇伞，而 b 字段并沒有使用到聯(lián)合索引。

我們也可以在執(zhí)行計劃中的 key_len 知道這一點捶牢，在使用聯(lián)合索引進行查詢的時候鸠珠，通過 key_len 我們可以知道優(yōu)化器具體使用了多少個字段的查詢條件來形成掃描區(qū)間的邊界條件。

舉例個例子 秋麸，a 和 b 都是 int 類型且不為 NULL 的字段渐排，那么 Q1 這條查詢語句執(zhí)行計劃如下：

可以看到 key_len 為 4 字節(jié)（如果字段允許為 NULL，就在字段類型占用的字節(jié)數(shù)上加 1灸蟆，也就是 5 字節(jié)）驯耻，說明只有 a 字段用到了聯(lián)合索引進行索引查詢，而且可以看到次乓，即使 b 字段沒用到聯(lián)合索引吓歇，key 為 idx_a_b，說明 Q1 查詢語句使用了 idx_a_b 聯(lián)合索引票腰。

通過 Q1 查詢語句我們可以知道城看，a 字段使用了 > 進行范圍查詢，聯(lián)合索引的最左匹配原則在遇到 a 字段的范圍查詢（ >）后就停止匹配了杏慰，因此 b 字段并沒有使用到聯(lián)合索引测柠。

例子二

Q2: select * from t_table where a >= 1 and b = 2，聯(lián)合索引（a, b）哪一個字段用到了聯(lián)合索引的 B+Tree缘滥？

Q2 和 Q1 的查詢語句很像轰胁，唯一的區(qū)別就是 a 字段的查詢條件「大于等于」。

由于聯(lián)合索引（二級索引）是先按照 a 字段的值排序的朝扼，所以符合 >= 1 條件的二級索引記錄肯定是相鄰赃阀，于是在進行索引掃描的時候，可以定位到符合 >= 1 條件的第一條記錄擎颖，然后沿著記錄所在的鏈表向后掃描榛斯，直到某條記錄不符合 a>= 1 條件位置。所以 a 字段可以在聯(lián)合索引的 B+Tree 中進行索引查詢搂捧。

雖然在符合 a>= 1 條件的二級索引記錄的范圍里驮俗，b 字段的值是「無序」的，但是對于符合 a = 1 的二級索引記錄的范圍里允跑，b 字段的值是「有序」的（因為對于聯(lián)合索引王凑，是先按照 a 字段的值排序搪柑，然后在 a 字段的值相同的情況下，再按照 b 字段的值進行排序）索烹。

于是工碾，在確定需要掃描的二級索引的范圍時，當二級索引記錄的 a 字段值為 1 時术荤，可以通過 b = 2 條件減少需要掃描的二級索引記錄范圍（b 字段可以利用聯(lián)合索引進行索引查詢的意思）倚喂。也就是說，從符合 a = 1 and b = 2 條件的第一條記錄開始掃描瓣戚，而不需要從第一個 a 字段值為 1 的記錄開始掃描端圈。

所以，Q2 這條查詢語句 a 和 b 字段都用到了聯(lián)合索引進行索引查詢子库。

我們也可以在執(zhí)行計劃中的 key_len 知道這一點舱权。執(zhí)行計劃如下：

可以看到 key_len 為 8 字節(jié)，說明優(yōu)化器使用了 2 個字段的查詢條件來形成掃描區(qū)間的邊界條件仑嗅，也就是 a 和 b 字段都用到了聯(lián)合索引進行索引查詢宴倍。

通過 Q2 查詢語句我們可以知道，雖然 a 字段使用了 >= 進行范圍查詢仓技，但是聯(lián)合索引的最左匹配原則并沒有在遇到 a 字段的范圍查詢（ >=）后就停止匹配了鸵贬，b 字段還是可以用到了聯(lián)合索引的。

例子三

Q3: SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2脖捻，聯(lián)合索引（a, b）哪一個字段用到了聯(lián)合索引的 B+Tree阔逼？

Q3 查詢條件中 a BETWEEN 2 AND 8 的意思是查詢 a 字段的值在 2 和 8 之間的記錄。

不同的數(shù)據(jù)庫對 BETWEEN ... AND 處理方式是有差異的地沮。在 MySQL 中嗜浮，BETWEEN 包含了 value1 和 value2 邊界值，類似于 >= and =<摩疑。而有的數(shù)據(jù)庫則不包含 value1 和 value2 邊界值（類似于 > and <）危融。

這里我們只討論 MySQL。由于 MySQL 的 BETWEEN 包含 value1 和 value2 邊界值雷袋，所以類似于 Q2 查詢語句吉殃，因此 Q3 這條查詢語句 a 和 b 字段都用到了聯(lián)合索引進行索引查詢。

我們也可以在執(zhí)行計劃中的 key_len 知道這一點楷怒。執(zhí)行計劃如下：

可以看到 key_len 為 8 字節(jié)寨腔，說明優(yōu)化器使用了 2 個字段的查詢條件來形成掃描區(qū)間的邊界條件，也就是 a 和 b 字段都用到了聯(lián)合索引進行索引查詢率寡。

通過 Q3 查詢語句我們可以知道，雖然 a 字段使用了 BETWEEN 進行范圍查詢倚搬，但是聯(lián)合索引的最左匹配原則并沒有在遇到 a 字段的范圍查詢（ BETWEEN）后就停止匹配了冶共，b 字段還是可以用到了聯(lián)合索引的。

例子四

Q4: SELECT * FROM t_user WHERE name like 'j%' and age = 22，聯(lián)合索引（name, age）哪一個字段用到了聯(lián)合索引的 B+Tree捅僵？

由于聯(lián)合索引（二級索引）是先按照 name 字段的值排序的家卖，所以前綴為 ‘j’ 的 name 字段的二級索引記錄都是相鄰的， 于是在進行索引掃描的時候庙楚，可以定位到符合前綴為 ‘j’ 的 name 字段的第一條記錄上荡，然后沿著記錄所在的鏈表向后掃描，直到某條記錄的 name 前綴不為 ‘j’ 為止馒闷。

所以 a 字段可以在聯(lián)合索引的 B+Tree 中進行索引查詢酪捡，形成的掃描區(qū)間是['j','k')。注意纳账， j 是閉區(qū)間逛薇。如下圖：

雖然在符合前綴為 ‘j’ 的 name 字段的二級索引記錄的范圍里，age 字段的值是「無序」的疏虫，但是對于符合 name = j 的二級索引記錄的范圍里永罚，age字段的值是「有序」的（因為對于聯(lián)合索引，是先按照 name 字段的值排序卧秘，然后在 name 字段的值相同的情況下呢袱，再按照 age 字段的值進行排序）。

于是翅敌，在確定需要掃描的二級索引的范圍時羞福，當二級索引記錄的 name 字段值為 ‘j’ 時，可以通過 age = 22 條件減少需要掃描的二級索引記錄范圍（age 字段可以利用聯(lián)合索引進行索引查詢的意思）哼御。也就是說坯临，從符合 name = 'j' and age = 22 條件的第一條記錄時開始掃描，而不需要從第一個 name 為 j 的記錄開始掃描 恋昼。如下圖的右邊：

所以看靠，Q4 這條查詢語句 a 和 b 字段都用到了聯(lián)合索引進行索引查詢。

我們也可以在執(zhí)行計劃中的 key_len 知道這一點液肌。本次例子中：

• name 字段的類型是 varchar(30) 且不為 NULL挟炬，數(shù)據(jù)庫表使用了 utf8mb4 字符集，一個字符集為 utf8mb4 的字符是 4 個字節(jié)嗦哆，因此 name 字段的實際數(shù)據(jù)最多占用的存儲空間長度是 120 字節(jié)（30 x 4）谤祖，然后因為 name 是變長類型的字段，需要再加 2老速，也就是 name 的 key_len 為 122粥喜。
• age 字段的類型是 int 且不為 NULL，key_len 為 4橘券。

Q4 查詢語句的執(zhí)行計劃如下：

可以看到 key_len 為 126 字節(jié)额湘，name 的 key_len 為 122卿吐，age 的 key_len 為 4，說明優(yōu)化器使用了 2 個字段的查詢條件來形成掃描區(qū)間的邊界條件锋华，也就是 name 和 age 字段都用到了聯(lián)合索引進行索引查詢嗡官。

通過 Q4 查詢語句我們可以知道，雖然 name 字段使用了 like 前綴匹配進行范圍查詢毯焕，但是聯(lián)合索引的最左匹配原則并沒有在遇到 name 字段的范圍查詢（ like 'j%'）后就停止匹配了衍腥，age 字段還是可以用到了聯(lián)合索引的。

小結(jié)

網(wǎng)上傳來穿去這句話：「聯(lián)合索引的最左匹配原則會一直向右匹配直到遇到范圍查詢(>纳猫、<婆咸、between、like) 就會停止匹配」并不是對的续担。

經(jīng)過實驗的證明擅耽，我得出的結(jié)論是這樣的：

聯(lián)合索引的最左匹配原則佛析，在遇到范圍查詢（如 >度秘、<）的時候，就會停止匹配规哲，也就是范圍查詢的字段可以用到聯(lián)合索引询兴，但是在范圍查詢字段后面的字段無法用到聯(lián)合索引乃沙。注意，對于 >=诗舰、<=警儒、BETWEEN、like 前綴匹配的范圍查詢眶根，并不會停止匹配蜀铲。

好了，講完了属百，怎么樣记劝，是不是又被我裝到了