MySQL 聯合索引深度解析：最左前綴、排序優化與引擎差異

聯合索引核心原理：B+ 樹與最左前綴

聯合索引（複合索引）將多個列按順序組合成一個 B+ 樹索引，結構類似電話簿「先按姓氏排序，同姓氏再按名字排序」。MySQL 優化器遵循最左前綴原則：索引 (a,b,c) 可支持 a、a,b、a,b,c 三種查找組合，但無法直接用於 b、c、b,c 的查詢。

列順序決定索引效用

CREATE TABLE test (a INT, b INT, c INT, KEY idx_abc (a,b,c));

建立聯合索引時，區分度高（Cardinality 大）、常用於 WHERE/JOIN/ORDER BY 的列應優先靠左。若查詢頻繁單獨檢索 b 或 c，需額外建立單列索引或調整列序。

聯合索引對 ORDER BY 的排序優化

聯合索引天然有序，可消耗 Filesort。但需嚴格匹配索引列的順序與方向：

CREATE INDEX idx_ex ON t (col1 ASC, col2 DESC, col3 ASC);

• ✅ ORDER BY col1 ASC, col2 DESC, col3 ASC（完全一致）
• ✅ ORDER BY col1 DESC, col2 ASC, col3 DESC（完全相反）
• ❌ ORDER BY col1 ASC, col2 ASC, col3 ASC（方向不匹配，觸發 Filesort）

若 WHERE 條件固定了前導列為常量，後續列仍可利用索引排序。

查詢優化器與索引選擇策略

優化器根據統計信息估算掃描行數，選擇成本最低的索引。關鍵規則：

1. 最左前綴匹配：WHERE col1=1 AND col2=2 用到 (col1,col2,col3)；WHERE col2=2 則無效
2. 範圍列截斷：WHERE col1=1 AND col2>2 AND col3=3 中 col3 無法用到索引
3. LIKE 前綴匹配：name LIKE '張%' 走索引，%張 或 %張% 全表掃描

索引失效常見陷阱與解法

引擎差異：InnoDB vs MyISAM 覆蓋索引策略

場景：SELECT id FROM articles WHERE category_id=5 ORDER BY created DESC LIMIT 10

• InnoDB：非主鍵索引葉子節點存主鍵值，建立 (category_id, created) 即可實現 Covering Index，無需回表
• MyISAM：非主鍵索引葉子節點存數據指針，需建立 (category_id, created, id) 才能覆蓋

額外優化細節

• Cardinality 閾值：唯一值佔比 < 20% 時索引效用極低，反拖慢寫入
• 字符集選擇：純英文/數字字段用 latin1 或 ascii，索引體積縮減 50%+
• 索引列數控制：單表索引建議 ≤ 5 個，複合索引列數 ≤ 3 個，避免內存膨脹