前言
當查詢條件涉及多個單欄索引欄位時，MySQL 5.0+ 引入 Index Merge (索引合併) 優化，可同時使用多個索引再合併結果。但何時優化器會選擇 Index Merge？何時複合索引更優？本文深度剖析三種合併演算法、觸發條件、成本模型、實戰基準測試與最佳實踐。
一、Index Merge 三種演算法
| 演算法 | 適用邏輯 | Extra 輸出 | 運作方式 |
|---|
| Intersection (交集) | WHERE idx_a = 1 AND idx_b = 2 | Using intersect(idx_a,idx_b) | 分別掃描兩索引取得主鍵 ID 集合，求交集後回表 |
| Union (聯集) | WHERE idx_a = 1 OR idx_b = 2 | Using union(idx_a,idx_b) | 分別掃描取得 ID 集合，求聯集（去重）後回表 |
| Sort-Union (排序聯集) | WHERE idx_a < 10 OR idx_b > 100 (範圍條件) | Using sort_union(idx_a,idx_b) | 範圍掃描產生大量 ID，需先排序再合併去重，成本最高 |
二、觸發條件與限制
- 單表查詢，不涉及 JOIN。
- WHERE 條件為多個單欄索引欄位的 AND/OR 組合。
- 無合適複合索引 覆蓋查詢條件（若有複合索引，優化器通常傾向複合索引）。
- InnoDB 限制：Index Merge 僅支援 Intersection，不支援 Union/Sort-Union (5.7+ 文檔說明)。MyISAM 支援三種。
- 成本閾值：優化器估算合併成本 < 全表掃描成本 才啟用。
- 關閉開關：
SET optimizer_switch='index_merge=off'; 或 index_merge_intersection=off 等細粒度控制。
三、複合索引 vs Index Merge 成本模型對比
| 維度 | 複合索引 (a,b) | Index Merge (idx_a, idx_b) |
|---|
| 索引數量 | 1 個 B+Tree | 2 個獨立 B+Tree |
| 寫入開銷 | 1 次索引維護 | 2 次索引維護 (INSERT/UPDATE/DELETE 更重) |
| 空間佔用 | 單樹，鍵值包含 a+b | 雙樹，總空間通常更大 |
查詢 WHERE a=1 AND b=2 | 單次範圍掃描，極快 | Intersection：兩次點查 + 合併 + 回表，較慢 |
查詢 WHERE a=1 OR b=2 | 無法使用 (需改寫 UNION ALL) | Union：兩次點查 + 合併去重 + 回表，可行 |
查詢 WHERE a=1 | 最左前綴命中，快 | 單索引 idx_a，同等 |
查詢 WHERE b=2 | 無法使用 (非最左) | 單索引 idx_b，Index Merge 勝出 |
排序 ORDER BY a,b | 天然有序，避免 filesort | 無序，需 filesort |
| 覆蓋索引潛力 | 易構建 (a,b,c) 覆蓋 | 需三個單欄索引合併，不現實 |
四、實戰基準測試 (模擬 100 萬行，a/b 基數各 10 萬)
CREATE TABLE t_merge (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
a INT NOT NULL,
b INT NOT NULL,
c VARCHAR(100),
INDEX idx_a (a),
INDEX idx_b (b),
INDEX idx_ab (a,b)
) ENGINE=InnoDB;
-- 插入 100 萬測試資料...
測試查詢與 EXPLAIN 結果
| SQL | 最佳索引 | EXPLAIN type/Extra | 耗時 (ms) |
|---|
WHERE a=123 AND b=456 | idx_ab | ref / Using index | 0.8 |
WHERE a=123 AND b=456 | Index Merge | index_merge / Using intersect(idx_a,idx_b) | 3.2 |
WHERE a=123 OR b=456 | 改寫 UNION ALL | ref / Using index (兩次) | 1.5 |
WHERE a=123 OR b=456 | Index Merge (MyISAM) | index_merge / Using union(idx_a,idx_b) | 4.0 |
WHERE b=456 | idx_b | ref / Using index | 0.9 |
WHERE b=456 | idx_ab | index / Using where; Using index | 120 (全索引掃描) |
五、最佳實踐決策樹
- 高頻 AND 查詢 → 建立複合索引 (最左前綴覆蓋核心條件)。
- 高頻 OR 查詢 → 改寫為 UNION ALL (各分支命中單欄索引) +
optimizer_switch='index_merge=off' 穩定計劃。
- 混合查詢模式：
WHERE a=1、WHERE b=2、WHERE a=1 AND b=2 共存 → 複合索引 (a,b) + 單獨索引 (b) (或 (b,a) 視基數)。
- 低基數欄位 (性別、狀態) → 勿建單欄索引，Index Merge 交集極大、成本高，優化器常選全表掃描。
- InnoDB 環境 → Index Merge 僅 Intersection，複合索引幾乎總是優於 Index Merge。
- 大表加索引 → 使用
pt-online-schema-change 或 8.0 瞬間 DDL，避免鎖表。
六、進階：強制/禁止 Index Merge
-- Session 級關閉
SET SESSION optimizer_switch='index_merge=off';
-- Query 級強制使用特定索引 (優先級高於 Index Merge)
SELECT * FROM t_merge FORCE INDEX (idx_ab) WHERE a=1 AND b=2;
-- Query 級忽略特定索引 (誘導 Index Merge)
SELECT * FROM t_merge IGNORE INDEX (idx_ab) WHERE a=1 AND b=2;
七、總結
Index Merge 是「無合適複合索引時的補救方案」，而非首選策略。複合索引在寫入開銷、空間、查詢速度、排序、覆蓋潛力上全面優於 Index Merge (尤其 InnoDB 僅支援 Intersection)。實戰中：核心查詢上複合索引，邊緣單欄查詢補單索引，OR 查詢改寫 UNION ALL，定期審視 optimizer_switch 與 EXPLAIN 確保計劃穩定。
