MySQL 單列索引 vs 組合索引：原理、效能對比與「最左前綴」法則實戰

前言：索引是查詢加速器，但用錯反成負擔

建立索引不等於建對索引。本文以一張 1 萬行測試表為例，通過單列索引與組合索引在同一 SQL 下的執行計劃對比，深度解析為什麼組合索引能「無掃描直達唯一行」，並揭示 最左前綴原則 的本質與實戰誤區。

一、測試表結構與資料分布

CREATE TABLE myIndex (
  i_testID INT NOT NULL AUTO_INCREMENT,
  vc_Name VARCHAR(50) NOT NULL,
  vc_City VARCHAR(50) NOT NULL,
  i_Age INT NOT NULL,
  i_SchoolID INT NOT NULL,
  PRIMARY KEY (i_testID)
);

表中 1 萬行，vc_Name='erquan' 僅 5 行，城市、年齡、學校組合各異。目標 SQL：

SELECT i_testID FROM myIndex
WHERE vc_Name='erquan' AND vc_City='鄭州' AND i_Age=25;

二、方案 A：三個單列索引（各自為戰）

ALTER TABLE myIndex ADD INDEX idx_name (vc_Name);
ALTER TABLE myIndex ADD INDEX idx_city (vc_City);
ALTER TABLE myIndex ADD INDEX idx_age (i_Age);

執行流程：

1. 優化器選擇「看起來最有效」的單列索引（通常是區分度最高的 idx_name）。
2. 通過 idx_name 定位到 5 行 erquan 記錄（中間結果集）。
3. 回表逐行過濾 vc_City='鄭州' 和 i_Age=25。

缺點：仍需回表、仍需在 Server 層過濾、無法利用其他兩個索引（MySQL 單表單次查詢通常只用一個索引，Index Merge 例外但效能一般）。

三、方案 B：一個組合索引（一鍵直達）

ALTER TABLE myIndex ADD INDEX idx_name_city_age (vc_Name(10), vc_City, i_Age);

為什麼 vc_Name(10)？ 前綴索引：名字實際長度多在 10 字內，截斷存儲可縮小索引體積、提升記憶體命中率、加速 INSERT/UPDATE。

執行流程：

1. B+ 樹直接按 (vc_Name, vc_City, i_Age) 三元組定位到唯一滿足條件的葉子節點。
2. 零回表、零 Server 層過濾，若只查 i_testID（主鍵），甚至可「覆蓋索引」直接返回。

四、組合索引的「隱形福利」：最左前綴原則

建立 (a, b, c) 組合索引，等價於同時擁有：

• (a, b, c)
• (a, b)
• (a)

可用的查詢模式：

WHERE a = ? AND b = ? AND c = ?   -- 用到 (a,b,c)
WHERE a = ? AND b = ?             -- 用到 (a,b)
WHERE a = ?                       -- 用到 (a)

不可用的查詢模式（不走該組合索引）：

WHERE b = ? AND c = ?   -- 缺少最左列 a
WHERE c = ?             -- 缺少最左列 a
WHERE b = ?             -- 缺少最左列 a

本質：B+ 樹索引鍵按 (a, b, c) 字典序排序，查詢必須從最左列開始連續匹配，才能利用索引有序性範圍掃描。

五、實戰決策表：何時用單列、何時用組合？

六、避坑提醒

• 索引非越多越好：每個索引增加 INSERT/UPDATE 開銷，單表建議 ≤ 6 個。
• 字段順序即索引順序：組合索引列順序 = 區分度由高到低 = 查詢 WHERE 順序 = ORDER BY 順序，三合一最佳。
• 前綴索引長度：SELECT COUNT(DISTINCT LEFT(col, n))/COUNT(*) FROM tbl 計算區分度，選擇接近 1.0 的最小 n。
• 覆蓋索引：SELECT a, b FROM tbl WHERE a=? AND b=? 若索引含 (a,b) 且不需回表，EXPLAIN 顯示 Using index，效能極致。

結語

單列索引像「獨木橋」，組合索引像「高速公路」。掌握最左前綴、善用前綴索引、追求覆蓋索引，才能在寫入開銷與查詢速度間找到平衡點。下次 EXPLAIN 看到 Using where 而非 Using index，問自己：能否調整組合索引列順序，讓它變成覆蓋索引？