MySQL Index Merge 優化實戰：從 570ms 到 0ms 的索引合併陷阱與破解

Index Merge 優化實戰：當「索引合併」成為性能殺手

某手機微博 4040 端口長期高延遲，pt-query-digest 定位到一條 COUNT(*) 慢查詢。EXPLAIN 顯示 type=index_merge、Using intersect(idx_weiboId,idx_type)，理應走索引，實際卻掃描百萬行、耗時 570ms、CPU 飆升 96%。本文完整復盤從現象觀測、執行計劃分析、數據分佈推理、索引重構到負載驗證的全過程，揭示 Index Merge Intersection 在低選擇性欄位上的致命缺陷 及破解之道。

一、現場還原：慢查詢畫像

-- 慢查詢
SELECT COUNT(*) AS total 
FROM user_rec_07 
WHERE type = 5 AND weiboId = '1934676487';

-- 表結構（TokuDB 引擎）
CREATE TABLE `user_rec_45` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `softId` INT(11) NOT NULL DEFAULT '0',
  `weiboId` BIGINT(20) NOT NULL DEFAULT '0',
  `type` TINYINT(4) NOT NULL DEFAULT '0',
  `content` VARCHAR(512) NOT NULL DEFAULT '',
  `ctime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_softId_weiboId` (`softId`,`weiboId`),
  KEY `idx_weiboId` (`weiboId`),
  KEY `idx_type` (`type`)
) ENGINE=TokuDB DEFAULT CHARSET=utf8 ROW_FORMAT=TOKUDB_LZMA;

-- EXPLAIN 顯示 index_merge
EXPLAIN SELECT COUNT(*) ... WHERE type=5 AND weiboId=...;
+----+-------------+--------------+-------------+-------------------+-------------------+---------+------+------+---------------------------------------------------+
| id | select_type | table        | type        | possible_keys     | key               | key_len | ref  | rows | Extra                                             |
+----+-------------+--------------+-------------+-------------------+-------------------+---------+------+------+---------------------------------------------------+
|  1 | SIMPLE      | user_rec_07  | index_merge | idx_weiboId,idx_type| idx_weiboId,idx_type| 8,1     | NULL | 1    | Using intersect(idx_weiboId,idx_type); Using where; Using index |
+----+-------------+--------------+-------------+-------------------+-------------------+---------+------+------+---------------------------------------------------+

二、數據分佈推理：為什麼 Index Merge 失效？

1. 單列選擇性對比

-- 單獨 weiboId：極高選擇性
EXPLAIN SELECT COUNT(*) FROM user_rec_07 WHERE weiboId=1934676487;
-- rows=18, type=ref, 0.00 sec

-- 單獨 type：極低選擇性
EXPLAIN SELECT COUNT(*) FROM user_rec_07 WHERE type=5;
-- rows=114,834, type=ref, 0.19 sec

2. 實際執行時間對比

3. Index Merge Intersection 執行邏輯

MySQL 官方文檔定義：

index_merge：對多個獨立索引分別進行範圍掃描，將各自結果集的 Row ID 取交集，再回表/覆蓋索引過濾。

本案中：

1. 掃描 idx_weiboId → 得到 18 個 Row ID
2. 掃描 idx_type → 得到 114,834 個 Row ID
3. 在內存中對兩個 Row ID 集合做 Intersection（交集）運算
4. 交集結果極小（1 行），但 掃描 11.5 萬 Row ID、內存排序/哈希交集、CPU 密集計算 導致耗時 570ms、CPU 96%

三、優化方案：聯合索引替代 Index Merge

1. 創建最優聯合索引

-- 刪除單列索引，建立 (weiboId, type) 聯合索引
ALTER TABLE user_rec_45 
  DROP INDEX idx_weiboId, 
  ADD INDEX idx_weiboId_type (weiboId, type);

設計原則：高選擇性列（weiboId）前置，低選擇性列（type）後置，B+Tree 直接定位到極小範圍，無需合併計算。

2. 優化前後對比

四、替代方案：為什麼不直接 DROP idx_type？

若業務絕無單獨 WHERE type=5 查詢，DROP INDEX idx_type 同樣能讓優化器退回 idx_weiboId 单列索引，效果相同且節省索引空間。但聯合索引 (weiboId, type) 同時服務：

• WHERE weiboId=... AND type=...（最優）
• WHERE weiboId=...（最左前綴匹配，同樣優）
• WHERE type=...（無法使用，但業務無此查詢）

故聯合索引為「無損增強」，優於單純刪除。

五、終極殺招：關閉 Index Merge Intersection

若無法 DDL（如第三方系統表），可會話/全局關閉：

-- 會話級（立即生效，斷開失效）
SET SESSION optimizer_switch='index_merge_intersection=off';

-- 全局級（持久化需寫入 my.cnf）
SET GLOBAL optimizer_switch='index_merge_intersection=off';

關閉後優化器將不再考慮 Intersection 策略，強制走單列索引或全表掃描，本案中會自動選擇 idx_weiboId，同樣達到 0ms。

六、核心經驗總結

1. Index Merge ≠ 銀彈：當參與合併的索引選擇性懸殊（一高一低）時，Intersection 會掃描大量 Row ID 做集合運算，反而比全表掃描更慢、CPU 更高。
2. 優先構建最優聯合索引：(高選擇性列, 低選擇性列) 覆蓋 WHERE 所有等值條件，徹底規避 Index Merge。
3. 警惕 EXPLAIN rows=1 的假象：index_merge 顯示 rows=1 是交集後預估，實際掃描量可能達十萬級。
4. 觀測 CPU 與磁盤 IO：index_merge 典型特徵是 高 CPU（用戶態）、高磁盤讀、低網絡，區別於鎖等待（高 IO wait）或網絡瓶頸。
5. optimizer_switch 兜底：無法 DDL 時，關閉 index_merge_intersection 為低成本緩解方案。

七、延伸閱讀

• MySQL 官方文檔：Index Merge Optimization
• optimizer_switch 完整參數列表：SHOW VARIABLES LIKE 'optimizer_switch';
• TokuDB/PerconaFT 特有壓縮對 Index Merge 的放大效應分析