在 MySQL 中，最左前綴匹配指的是在查詢時利用索引的最左邊部分進(jìn)行匹配。當(dāng)你執(zhí)行查詢時，如果查詢條件涉及到組合索引的前幾個列，MySQL 就能夠利用該復(fù)合索引來進(jìn)行匹配。

組合索引即由多個字段組成的聯(lián)合索引，比如 idx_col1_col2_col3 (col1,col2,col3)。

假設(shè)我們創(chuàng)建了一個組合索引 (col1, col2, col3)，如果查詢條件是針對 col1、(col1, col2) 或者 (col1, col2, col3)，那么 MySQL 就能利用該復(fù)合索引進(jìn)行最左前綴匹配。

然而，如果查詢條件只涉及到 col2、只涉及到 col3 或者只涉及到 col2 和 col3，也就是沒有包含 col1，那么通常情況下（不考慮索引跳躍掃描等其他優(yōu)化），就無法利用該索引進(jìn)行最左前綴匹配。

值得注意的是，最左前綴匹配與查詢條件的順序無關(guān)。無論你寫的是 where col1 = "Paidaxing" and col2 = "666" 還是 where col2 = "666" and col1 = "Paidaxing"，對結(jié)果都沒有影響，命中的結(jié)果仍然一樣。

此外，需要大家注意的是，許多人可能會誤以為創(chuàng)建一個組合索引 (col1, col2, col3) 時，數(shù)據(jù)庫會創(chuàng)建三個索引 (col1)、(col1, col2) 和 (col1, col2, col3)，這樣的理解其實(shí)是不正確的。實(shí)際上，數(shù)據(jù)庫只會創(chuàng)建一棵 B+樹，只不過在這顆樹中，首先按照 col1 進(jìn)行排序，然后在 col1 相同時再按照 col2 排序，col2 相同再按照 col3 排序。

另外，如果沒有涉及到聯(lián)合索引，單個字段的索引也需要遵守最左前綴原則。即當(dāng)一個字段的值為"abc"時，當(dāng)我們使用 like 進(jìn)行模糊匹配時，like "ab%" 是可以利用索引的，而 "%bc"則不行，因?yàn)楹笳卟环献钭笄熬Y匹配的原則。

為什么要遵循最左前綴匹配

我們都了解，在 MySQL 的 InnoDB 引擎中，索引是通過 B+樹來實(shí)現(xiàn)的。不論是普通索引還是聯(lián)合索引，都必須構(gòu)建 B+樹的索引結(jié)構(gòu)。

針對普通索引，其存儲結(jié)構(gòu)是在 B+樹的每個非葉子節(jié)點(diǎn)上記錄索引的值，而在 B+樹的葉子節(jié)點(diǎn)上，則記錄了索引的值和聚簇索引（主鍵索引）的值。

如：

圖片

在聯(lián)合索引中，比如聯(lián)合索引 (age, name)，同樣也是構(gòu)建了一棵 B+樹。在這棵 B+樹中，非葉子節(jié)點(diǎn)中記錄的是 name 和 age 兩個字段的值，而在葉子節(jié)點(diǎn)中記錄的是 name、age 兩個字段以及主鍵 id 的值。

圖片

在存儲過程中，如上所述，當(dāng) age 不同時，按照 age 排序；當(dāng) age 相同時，則按照 name 排序。

因此，了解了索引的存儲結(jié)構(gòu)之后，我們就很容易理解最左前綴匹配了：由于索引底層是一棵 B+樹，如果是聯(lián)合索引的話，在構(gòu)造 B+樹時，會先按照左邊的鍵進(jìn)行排序，當(dāng)左邊的鍵相同時，再依次按照右邊的鍵進(jìn)行排序。

因此，在通過索引查詢時，也需要遵守最左前綴匹配的原則，即需要從聯(lián)合索引的最左邊開始進(jìn)行匹配。這就要求查詢語句的 WHERE 條件中包含最左邊的索引值。

MySQL 索引一定遵循最左前綴匹配嗎？

因?yàn)樗饕讓邮且粋€ B+樹，如果是聯(lián)合索引的話，在構(gòu)造 B+樹的過程中，會先按照左邊的鍵進(jìn)行排序。當(dāng)左邊的鍵相同時，再依次按照右邊的鍵排序。

因此，在通過索引進(jìn)行查詢時，也需要遵守最左前綴匹配的原則，即需要從聯(lián)合索引的最左邊開始進(jìn)行匹配。這就要求查詢語句的 WHERE 條件中包含最左邊的索引值。這就是最左前綴匹配的概念。

在 MySQL 之前的版本中，一直都是遵循最左前綴匹配的原則，這句話在以前是正確的，沒有任何問題。但是在 MySQL 8.0 中，情況就有所不同了。因?yàn)樵?8.0.13 中引入了索引跳躍掃描的特性。

補(bǔ)充知識

索引跳躍掃描

MySQL 8.0.13 版本引入了索引跳躍掃描（Index Skip Scan）優(yōu)化，對于 range 查詢提供了支持。即使不符合組合索引最左前綴原則的條件下，SQL 依然能夠使用組合索引，從而減少不必要的掃描。

讓我們通過一個例子來解釋一下。首先，我們有下面這樣一張表（參考了 MySQL 官網(wǎng)的例子，但經(jīng)過了一些改動和優(yōu)化）：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL);
CREATE INDEX idx_t on t1(f1,f2);
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;

通過以下 SQL 語句，先創(chuàng)建一張名為 t1 的表，并將字段 f1 和 f2 設(shè)置為聯(lián)合索引。然后向其中插入一些記錄。

接著，分別在 MySQL 5.7.9 和 MySQL 8.0.30 上執(zhí)行EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 = 40;。

圖片

可以看到，主要有以下幾個區(qū)別：

MySQL 5.7 中，type = index，rows=160，extra=Using where;Using index

MySQL 8.0 中，type = range，rows=16，extra=Using where;Using index for skip scan

type 字段表示掃描方式，其中 range 表示范圍掃描，而 index 表示索引樹掃描。通常情況下，范圍掃描要比索引樹掃描快得多。

通過 rows 字段也能夠觀察到這一點(diǎn)，使用索引樹掃描的方式共掃描了 160 行，而范圍掃描方式只掃描了 16 行。

然后，關(guān)鍵在于為什么 MySQL 8.0 中的掃描方式更快呢？這主要是因?yàn)椴捎昧?Using index for skip scan"的技術(shù)。

換句話說，盡管我們的 SQL 沒有遵循最左前綴原則，僅僅使用了 f2 作為查詢條件，但經(jīng)過 MySQL 8.0 的優(yōu)化，仍然通過索引跳躍掃描的方式利用了索引。

優(yōu)化原理

那么他是怎么優(yōu)化的呢？在 MySQL 8.0.13 及以后的版本中，執(zhí)行SELECT f1, f2 FROM t1 WHERE f2 = 40;的過程如下：

1. 獲取 f1 字段的第一個唯一值，即 f1=1。
2. 構(gòu)造條件f1=1 and f2=40，進(jìn)行范圍查詢。
3. 獲取 f1 字段的第二個唯一值，即 f1=2。
4. 構(gòu)造條件f1=2 and f2=40，進(jìn)行范圍查詢。
5. 重復(fù)上述步驟，直到掃描完 f1 字段的所有唯一值。
6. 最后將結(jié)果合并并返回。

換句話說，最終執(zhí)行的 SQL 語句類似于下面的形式：

SELECT f1, f2 FROM t1 WHERE f1 =1 and f2 = 40
UNION
SELECT f1, f2 FROM t1 WHERE f1 =2 and f2 = 40;

即，MySQL 的優(yōu)化器幫我們把聯(lián)合索引中的 f1 字段作為查詢條件進(jìn)行查詢了。

在了解了索引跳躍掃描的執(zhí)行過程后，一些聰明的讀者可能會意識到，這種查詢優(yōu)化更適用于具有較少取值范圍和低區(qū)分度的字段（比如性別），而當(dāng)字段的區(qū)分度特別高時（比如出生年月日），這種查詢可能會變得更慢。

因此，是否使用索引跳躍掃描，實(shí)際上取決于 MySQL 優(yōu)化器經(jīng)過成本預(yù)估后做出的決定。

通常情況下，這種優(yōu)化技術(shù)適用于聯(lián)合索引中第一個字段的區(qū)分度較低的情況。但需要注意的是，并非絕對如此。盡管一般情況下我們不太會將區(qū)分度較低的字段放在聯(lián)合索引的左邊，但 MySQL 提供了這樣的優(yōu)化方案，這說明確實(shí)存在這樣的需求。

然而，我們不應(yīng)該過度依賴這種優(yōu)化。在建立索引時，仍然應(yīng)優(yōu)先考慮將區(qū)分度高且頻繁查詢的字段放置在聯(lián)合索引的左邊。

此外，在 MySQL 官網(wǎng)中還提到了索引跳躍掃描的其他一些限制條件：

• 表 T 必須至少有一個聯(lián)合索引，但對于聯(lián)合索引(A,B,C,D)，A 和 D 可以為空，但 B 和 C 必須非空。
• 查詢只能依賴于單張表，不能進(jìn)行多表連接。
• 查詢中不能使用 GROUP BY 或 DISTINCT 語句。
• 查詢的字段必須是索引中的列。