什麼是最左前綴匹配？為什麼要遵守？

2024.05.27

在MySQL 中，最左前綴匹配指的是查詢時利用索引的最左邊部分來匹配。當你執行查詢時，如果查詢條件涉及到組合索引的前幾個列，MySQL 就能夠利用該複合索引來進行比對。

組合索引即由多個欄位組成的聯合索引，例如idx_col1_col2_col3 (col1,col2,col3)。

假設我們建立了一個組合索引(col1, col2, col3)，如果查詢條件是針對col1、(col1, col2) 或(col1, col2, col3)，那麼MySQL 就能利用該複合索引進行最左前綴匹配。

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

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

此外，需要大家注意的是，許多人可能會誤以為創建一個組合索引(col1, col2, col3) 時，資料庫會建立三個索引(col1)、(col1, col2) 和(col1, col2, col3) ，這樣的理解其實是不正確的。實際上，資料庫只會建立一棵B+樹，只不過在這顆樹中，先依照col1 排序，然後在col1 相同時再依照col2 排序，col2 相同再依照col3 排序。

另外，如果沒有涉及到聯合索引，單一欄位的索引也需要遵守最左前綴原則。即當一個字段的值為"abc"時，當我們使用like 進行模糊匹配時，like "ab%" 是可以利用索引的，而"%bc"則不行，因為後者不符合最左前綴匹配的原則。

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

我們都了解，在MySQL 的InnoDB 引擎中，索引是透過B+樹來實現的。不論是普通索引或聯合索引，都必須建構B+樹的索引結構。

針對普通索引，其儲存結構是在B+樹的每個非葉子節點上記錄索引的值，而在B+樹的葉子節點上，則記錄了索引的值和聚集索引（主鍵索引）的值。

如：

圖片

在聯合索引中，例如聯合索引(age, name)，同樣也是建構了一棵B+樹。在這棵B+樹中，非葉子節點中記錄的是name 和age 兩個欄位的值，而在葉子節點中記錄的是name、age 兩個欄位以及主鍵id 的值。

圖片

在預存程序中，如上所述，當age 不同時，依照age 排序；當age 相同時，則依照name 排序。

因此，了解了索引的儲存結構之後，我們就很容易理解最左前綴匹配了：由於索引底層是一棵B+樹，如果是聯合索引的話，在構造B+樹時，會先按照左邊的鍵進行排序，當左邊的鍵相同時，再依序依照右邊的鍵進行排序。

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

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

因為索引底層是一個B+樹，如果是聯合索引的話，在構造B+樹的過程中，會先依照左邊的鍵排序。當左邊的鍵相同時，再依序依照右邊的鍵排序。

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

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

補充知識

索引跳躍掃描

MySQL 8.0.13 版本引入了索引跳躍掃描（Index Skip Scan）最佳化，對於range 查詢提供了支援。即使在不符合組合索引最左前綴原則的條件下，SQL 仍能使用組合索引，從而減少不必要的掃描。

讓我們透過一個例子來解釋一下。首先，我們有下面這樣一張表（參考了MySQL 官網的例子，但經過了一些改變和優化）：

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;1.
2.
3.
4.
5.
6.
7.
8.
9.

透過下列SQL 語句，先建立一張名為t1 的表，並將欄位f1 和f2 設定為聯合索引。然後向其中插入一些記錄。

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

圖片

可以看到，主要有以下幾個差異：

MySQL 5.7 中，type = index，rows=160，extra=Using where;使用索引

MySQL 8.0 中，type = range，rows=16，extra=Using where;使用索引進行跳躍掃描

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

透過rows 欄位也能夠觀察到這一點，使用索引樹掃描的方式共掃描了160 行，而範圍掃描方式只掃描了16 行。

然後，關鍵在於為什麼MySQL 8.0 中的掃描方式更快呢？這主要是因為採用了"Using index for skip scan"的技術。

換句話說，儘管我們的SQL 沒有遵循最左前綴原則，僅僅使用了f2 作為查詢條件，但經過MySQL 8.0 的最佳化，仍然透過索引跳躍掃描的方式利用了索引。

最佳化原理

那他是怎麼優化的呢？在MySQL 8.0.13 及以後的版本中，執行SELECT f1, f2 FROM t1 WHERE f2 = 40;的過程如下：

取得f1 欄位的第一個唯一值，即f1=1。
構造條件f1=1 and f2=40，進行範圍查詢。
取得f1 欄位的第二個唯一值，即f1=2。
構造條件f1=2 and f2=40，進行範圍查詢。
重複上述步驟，直到掃描完f1 欄位的所有唯一值。
最後將結果合併並回傳。

換句話說，最終執行的SQL 語句類似於下面的形式：

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

即，MySQL 的最佳化器幫我們把聯合索引中的f1 欄位當作查詢條件來查詢了。

限制條件

在了解了索引跳躍掃描的執行過程後，一些聰明的讀者可能會意識到，這種查詢優化更適用於具有較少取值範圍和低區分度的字段（例如性別），而當字段的區分度特別高時（例如出生年月日），這種查詢可能會變得更慢。

因此，是否使用索引跳躍掃描，實際上取決於MySQL 優化器經過成本預估後所做的決定。

通常情況下，這種最佳化技術適用於聯合索引中第一個欄位的區分度較低的情況。但要注意的是，並非絕對如此。儘管一般情況下我們不太會將區分度較低的欄位放在聯合索引的左邊，但MySQL 提供了這樣的最佳化方案，這說明確實存在這樣的需求。

然而，我們不應該過度依賴這種優化。在建立索引時，仍應優先考慮將區分度高且頻繁查詢的欄位放置在聯合索引的左邊。

此外，在MySQL 官網中也提到了索引跳躍掃描的其他一些限制條件：

表T 必須至少有一個聯合索引，但對於聯合索引(A,B,C,D)，A 和D 可以為空，但B 和C 必須非空。
查詢只能依賴單張表，不能進行多表連線。
查詢中不能使用GROUP BY 或DISTINCT 語句。
查詢的欄位必須是索引中的欄位。

新聞