MySQL 優化實戰：深入解析 B-Tree 索引與聯合索引最左前綴原則

前言：從一道面試題切入

在 MySQL 性能調優中，索引設計是核心環節。今天我們通過一道經典的 SQL 分析題，深入剖析聯合索引的最左前綴原則與 B-Tree 索引的底層匹配邏輯。

場景設定

假設某表存在聯合索引 idx_c1234(c1, c2, c3, c4)，請判斷以下查詢能否用到該索引：

• A: WHERE c1=x AND c2=x AND c4>x AND c3=x
• B: WHERE c1=x AND c2=x AND c4=x ORDER BY c3
• C: WHERE c1=x AND c4=x GROUP BY c3, c2
• D: WHERE c1=? AND c5=? ORDER BY c2, c3
• E: WHERE c1=? AND c2=? AND c5=? ORDER BY c2, c3

實驗環境搭建

CREATE TABLE t (
  c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10),
  c4 VARCHAR(10), c5 VARCHAR(10)
);
INSERT INTO t VALUES ('a1','a2','a3','a4','a5'), ('b1','b2','b3','b4','b5');
ALTER TABLE t ADD INDEX idx_c1234(c1, c2, c3, c4);

核心原則：最左前綴匹配

聯合索引 (c1, c2, c3, c4) 的 B-Tree 結構按順序排列。查詢優化器會從索引最左列開始匹配，遇到範圍查詢（>, <, >=, <=, LIKE '%...'）即停止匹配後續列。

案例逐一分析

A 條件：c1=x AND c2=x AND c4>x AND c3=x

結果：僅用到 c1、c2、c4（c3 無法使用）

雖然 SQL 寫法中 c3 在 c4 之後，但優化器會自動優化條件順序。實際執行計劃中，c1、c2 為等值查詢，c4 為範圍查詢，索引匹配到 c4 即終止，c3 無法利用索引。

B 條件：c1=x AND c2=x AND c4=x ORDER BY c3

結果：用到 c1、c2、c4；需 filesort

WHERE 條件中 c1、c2、c4 均為等值查詢，可全部命中索引。但 ORDER BY c3 因 c3 未出現在 WHERE 條件中，且索引順序為 c1,c2,c3,c4，中間跳過 c4 直接排序 c3 會導致索引順序不連續，故需額外排序。

C 條件：c1=x AND c4=x GROUP BY c3, c2

結果：用到 c1、c4；使用臨時表與 filesort

GROUP BY 通常先建立臨時表再排序。索引僅能用於 c1、c4 的等值過濾，分組字段 c3、c2 無法利用索引順序。

D 條件：c1=? AND c5=? ORDER BY c2, c3

結果：僅用到 c1；需 filesort

c5 不在索引中，c2、c3 雖在索引中但因 c5 打斷最左前綴，無法用於排序。

E 條件：c1=? AND c2=? AND c5=? ORDER BY c2, c3

結果：用到 c1、c2；無需 filesort

c1、c2 為等值查詢，符合最左前綴。ORDER BY c2, c3 剛好與索引順序一致（c2 為常量，c3 有序），可直接利用索引順序避免排序。

總結與最佳實踐

1. 等值查詢優先：WHERE 條件中等值比較（=, IN）的列放在聯合索引前列。
2. 範圍查詢終止匹配：遇到 >, <, >=, <=, LIKE '%...' 即停止後續列索引匹配。
3. ORDER BY/GROUP BY 順序一致性：排序/分組字段順序需與索引順序一致，且前導列需為常量（等值查詢）。
4. 覆蓋索引優化：若 SELECT 列均包含在索引中，可避免回表查詢，大幅提升性能。

核心口訣：最左前綴，等值優先；範圍之後，索引失效；排序分組，順序要對；覆蓋索引，免去回表。