MySQL 多列索引 vs 多個單列索引：選擇策略與 Cardinality 核心原理

核心結論先行

對於能夠被利用的查詢，多列聯合索引在所有情況下都優於 Index Merge（多單列索引合併）。
優化器甚至可能放棄 Index Merge 而選擇全表掃描或單一索引。索引越多，寫入越慢，磁碟佔用越大。

為什麼多列索引更強？

MySQL 5.0+ 引入 Index Merge（索引合併）優化，可同時使用多個單列索引，通過 intersection（交集）、union（並集）、sort-union 三種算法合併結果。但：

• 合併過程需在內存中構建位圖/排序，CPU 與內存開銷大。
• 優化器基於成本模型，若估算合併成本高於全表掃描，會直接放棄合併。
• 聯合索引 (a,b) 直接按 a,b 順序存儲，範圍查詢、排序、覆蓋索引一次到位，零合併開銷。

聯合索引列順序的黃金法則：Cardinality（基數）決策

什麼是 Cardinality？

某列不重複值的數量。Cardinality / 總行數 越接近 1，區分度越高。

-- 查看基數
SHOW INDEX FROM tbl_name;
-- 或
SELECT COUNT(DISTINCT col_a), COUNT(DISTINCT col_b) FROM tbl_name;

排序原則

1. 最高區分度列放最左：第一次索引查找過濾掉最多行，後續掃描量最小。
2. 等值查詢列優先於範圍查詢列：WHERE a=1 AND b>10，a 放前面，b 放後面；若 b 在前，範圍查詢會導致後續列索引失效。
3. 覆蓋索引考量：若查詢只需 SELECT a,b FROM t WHERE c=1，將 c 放前，a,b 放後可實現覆蓋索引，避免回表。

實例：表 tb 1700 行，foo 750 個不同值，bar 100 個不同值

• Cardinality(foo)=750，Cardinality(bar)=100
• 聯合索引應為 (foo, bar)：先按 foo 過濾到 ~2 行，再按 bar 精確定位。
• 若建 (bar, foo)：先按 bar 過濾到 ~17 行，再按 foo，掃描量增大 8 倍。

三列以上聯合索引的複雜度

不再單純按基數排序，需綜合考慮：

• 查詢模式分布（哪些列常一起出現在 WHERE、JOIN、ORDER BY）
• 等值 vs 範圍 vs 排序列的位置衝突
• 覆蓋索引機會
• 維護成本（索引大小、寫入放大）

建議：收集生產 EXPLAIN 樣本，用 pt-index-usage 分析索引使用率，按「高頻查詢優先滿足」原則設計 2-3 個聯合索引，而非窮盡排列組合。

優化器可能拒絕使用即使完美的索引

優化器會進一步統計（如直方圖、平均行長、關聯度）判斷索引是否「足夠高效」。若預估回表成本過高，可能仍選全表掃描。可用 FORCE INDEX 強制，但需持續監控執行計劃變化。

官方文檔參考

MySQL 5.7 Multiple-Column Indexes — 含最左前綴原理、Index Merge 條件、優化器決策細節。

實戰檢查清單

1. 用 SHOW INDEX 檢查現有單列索引是否可合併為聯合索引。
2. 用 pt-duplicate-key-checker 發現冗餘/重複索引。
3. 新建聯合索引前，先在從庫/測試庫驗證 EXPLAIN 走向。
4. 上線後觀察 Innodb_rows_read、Handler_read_key 指標變化。

總結

少而精 是索引設計鐵律：
✅ 高區分度列左置 ✅ 等值列前範圍列後 ✅ 覆蓋高頻查詢 ✅ 定期清理低用率索引。
多列索引不是萬能銀彈，但對於 OLTP 核心查詢，它是性能與維護成本的最佳平衡點。