關(guān)于MySQL，全網(wǎng)都在說(shuō)一個(gè)錯(cuò)誤的結(jié)論

大家好，我是小林。

大家在背 MySQL 八股文的時(shí)候，是不是經(jīng)?？吹竭@句話。

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

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

我在昨晚折騰了幾個(gè)實(shí)驗(yàn)，發(fā)現(xiàn)這個(gè)結(jié)論并不全對(duì)！去掉 「between 和 like 」這個(gè)結(jié)論就沒(méi)問(wèn)題了。

經(jīng)過(guò)實(shí)驗(yàn)的證明，我得出的結(jié)論是這樣的：

接下來(lái)，我會(huì)用幾個(gè)實(shí)驗(yàn)例子來(lái)說(shuō)明這個(gè)結(jié)論。

B+Tree 索引

首先，先來(lái)認(rèn)識(shí)下 B+Tree 索引。

MySQL 的 InnoDB 存儲(chǔ)引擎會(huì)為每一張數(shù)據(jù)庫(kù)表創(chuàng)建一個(gè)「聚簇索引」來(lái)保存表的數(shù)據(jù)，聚簇索引默認(rèn)使用的是 B+Tree 索引。

為了讓大家理解 B+Tree 索引的存儲(chǔ)和查詢的過(guò)程，接下來(lái)我通過(guò)一個(gè)簡(jiǎn)單例子，說(shuō)明一下 B+Tree 索引在存儲(chǔ)數(shù)據(jù)中的具體實(shí)現(xiàn)。

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

這些數(shù)據(jù)，存儲(chǔ)在 B+Tree 索引時(shí)是長(zhǎng)什么樣子的？

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

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

假設(shè)，執(zhí)行了  select * from t_product where id = 5 查詢語(yǔ)句，該查詢語(yǔ)句的條件是找到 id（主鍵）為 5 的這條記錄。因?yàn)?B+Tree 是一個(gè)有序的數(shù)據(jù)結(jié)構(gòu)，所以可以通過(guò)二分查找算法快速定位到這條記錄，這也就是我們常說(shuō)的索引查詢，具體過(guò)程如下：

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

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

如果我用 product_no 二級(jí)索引查詢商品，如下查詢語(yǔ)句：

select * from product where product_no = '0002';

會(huì)先在二級(jí)索引的 B+Tree 中快速查找到 product_no 為 0002 的二級(jí)索引記錄，然后獲取主鍵值，然后利用主鍵值在主鍵索引的 B+Tree 中快速查詢到對(duì)應(yīng)的葉子節(jié)點(diǎn)，然后獲取完整的記錄。這個(gè)過(guò)程叫「回表」，也就是說(shuō)要查兩個(gè) B+Tree 才能查到數(shù)據(jù)。如下圖：

不過(guò)，當(dāng)查詢的數(shù)據(jù)是能在二級(jí)索引的 B+Tree 的葉子節(jié)點(diǎn)里查詢到，這時(shí)就不用再查主鍵索引查，比如下面這條查詢語(yǔ)句：

select id from product where product_no = '0002';

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

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

前文我將 product_no 字段設(shè)置為了索引，這種二級(jí)索引只有一個(gè)字段。如果將多個(gè)字段組合成一個(gè)索引，那么這種二級(jí)索引就被稱為聯(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é)點(diǎn)用兩個(gè)字段的值作為 B+Tree 的索引值。

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

最左匹配原則

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

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

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

需要注意的是，因?yàn)橛胁樵儍?yōu)化器，所以 a 字段在 where 子句的順序并不重要。但是，如果查詢條件是以下這幾種，因?yàn)椴环献钭笃ヅ湓瓌t，所以就無(wú)法匹配上聯(lián)合索引，聯(lián)合索引就會(huì)失效:

上面這些查詢條件之所以會(huì)失效，是因?yàn)?a, b, c) 聯(lián)合索引，是先按 a 排序，在 a 相同的情況再按 b 排序，在 b 相同的情況再按 c 排序。所以，b 和 c 是全局無(wú)序，局部相對(duì)有序的，這樣在沒(méi)有遵循最左匹配原則的情況下，是無(wú)法利用到索引的。

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

可以看到，a 是全局有序的（1, 2, 2, 3, 4, 5, 6, 7 ,8），而 b 是全局是無(wú)序的（12，7，8，2，3，8，10，5，2）。因此，直接執(zhí)行 where b = 2 這種查詢條件沒(méi)有辦法利用聯(lián)合索引的，利用索引的前提是索引里的 key 是有序的。

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

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

聯(lián)合索引有一些特殊情況，并不是查詢過(guò)程使用了聯(lián)合索引查詢，就代表聯(lián)合索引中的所有字段都用到了聯(lián)合索引進(jìn)行索引查詢，也就是可能存在部分字段用到聯(lián)合索引的 B+Tree，部分字段沒(méi)有用到聯(lián)合索引的 B+Tree 的情況。

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

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

接下來(lái)，舉例幾個(gè)范圍查詢的例子，下面的實(shí)驗(yàn)案例是基于 MySQL 8.0 做的。

例子一

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

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

但是在符合 a > 1 條件的二級(jí)索引記錄的范圍里，b 字段的值是無(wú)序的。

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

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

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

所以在執(zhí)行 Q1 這條查詢語(yǔ)句的時(shí)候，對(duì)應(yīng)的掃描區(qū)間是 (2, + ∞)，形成該掃描區(qū)間的邊界條件是 a > 1，與 b = 2 無(wú)關(guān)。

因此，Q1 這條查詢語(yǔ)句只有 a 字段用到了聯(lián)合索引進(jìn)行索引查詢，而 b 字段并沒(méi)有使用到聯(lián)合索引。

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

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

可以看到 key_len 為 4 字節(jié)（如果字段允許為 NULL，就在字段類型占用的字節(jié)數(shù)上加 1，也就是 5 字節(jié)），說(shuō)明只有 a 字段用到了聯(lián)合索引進(jìn)行索引查詢，而且可以看到，即使 b 字段沒(méi)用到聯(lián)合索引，key 為 idx_a_b，說(shuō)明 Q1 查詢語(yǔ)句使用了 idx_a_b 聯(lián)合索引。

通過(guò) Q1 查詢語(yǔ)句我們可以知道，a 字段使用了 > 進(jìn)行范圍查詢，聯(lián)合索引的最左匹配原則在遇到 a 字段的范圍查詢（ >）后就停止匹配了，因此 b 字段并沒(méi)有使用到聯(lián)合索引。

例子二

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

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

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

雖然在符合 a>= 1 條件的二級(jí)索引記錄的范圍里，b 字段的值是「無(wú)序」的，但是對(duì)于符合 a = 1 的二級(jí)索引記錄的范圍里，b 字段的值是「有序」的（因?yàn)閷?duì)于聯(lián)合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情況下，再按照 b 字段的值進(jìn)行排序）。

于是，在確定需要掃描的二級(jí)索引的范圍時(shí)，當(dāng)二級(jí)索引記錄的 a 字段值為 1 時(shí)，可以通過(guò) b = 2 條件減少需要掃描的二級(jí)索引記錄范圍（b 字段可以利用聯(lián)合索引進(jìn)行索引查詢的意思）。也就是說(shuō)，從符合 a = 1 and b = 2 條件的第一條記錄開始掃描，而不需要從第一個(gè) a 字段值為 1 的記錄開始掃描。

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

我們也可以在執(zhí)行計(jì)劃中的 key_len 知道這一點(diǎn)。執(zhí)行計(jì)劃如下：

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

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

例子三

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

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

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

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

我們也可以在執(zhí)行計(jì)劃中的 key_len 知道這一點(diǎn)。執(zhí)行計(jì)劃如下：

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

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

例子四

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

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

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

雖然在符合前綴為 ‘j’ 的 name 字段的二級(jí)索引記錄的范圍里，age 字段的值是「無(wú)序」的，但是對(duì)于符合 name = j 的二級(jí)索引記錄的范圍里，age字段的值是「有序」的（因?yàn)閷?duì)于聯(lián)合索引，是先按照 name 字段的值排序，然后在 name 字段的值相同的情況下，再按照 age 字段的值進(jìn)行排序）。

于是，在確定需要掃描的二級(jí)索引的范圍時(shí)，當(dāng)二級(jí)索引記錄的 name 字段值為 ‘j’ 時(shí)，可以通過(guò) age = 22 條件減少需要掃描的二級(jí)索引記錄范圍（age 字段可以利用聯(lián)合索引進(jìn)行索引查詢的意思）。也就是說(shuō)，從符合 name = 'j' and age = 22 條件的第一條記錄時(shí)開始掃描，而不需要從第一個(gè) name 為 j 的記錄開始掃描 。如下圖的右邊：

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

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

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

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

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

小結(jié)

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

經(jīng)過(guò)實(shí)驗(yàn)的證明，我得出的結(jié)論是這樣的：

聯(lián)合索引的最左匹配原則，在遇到范圍查詢（如 >、<）的時(shí)候，就會(huì)停止匹配，也就是范圍查詢的字段可以用到聯(lián)合索引，但是在范圍查詢字段后面的字段無(wú)法用到聯(lián)合索引。注意，對(duì)于 >=、<=、BETWEEN、like 前綴匹配的范圍查詢，并不會(huì)停止匹配。

好了，講完了，怎么樣，是不是又被我裝到了