MySQL 索引不當導致 CPU 飆升 336%：index_merge 到覆蓋索引的優化實戰

MySQL 索引不當導致 CPU 飆升 336%：index_merge 到覆蓋索引的優化實戰

收到負載告警，load average 280+，CPU 336%，IO/內存正常。經驗判斷：高 CPU 低 IO 多為索引問題。定位高頻 SQL，發現優化器選擇 index_merge 且掃描行數尚可但執行計劃不優。將兩個單列索引合併為聯合索引，CPU 瞬降至 12% 以下。本文完整復盤優化過程。

現場定位

-- 高頻 SQL
SELECT count(1) FROM a, b 
WHERE a.id = b.video_id 
  AND b.state = 1 
  AND b.column_id = '81';

-- 原執行計劃
EXPLAIN ...\G
*************************** 1. row ***************************
table: b
type: index_merge
possible_keys: columnid_videoid,column_id,state,video_time_stamp,idx_videoid
key: column_id,state
key_len: 4,4
rows: 100
Extra: Using intersect(column_id,state); Using where
*************************** 2. row ***************************
table: a
type: eq_ref
key: PRIMARY
rows: 1
Extra: Using where; Using index

表 b 使用 index_merge 合併 column_id 和 state 兩個單列索引，state 基數極低（Cardinality=5），索引效果極差。

索引現狀

SHOW INDEX FROM b\G
Key_name: column_id  (Cardinality: 8374)
Key_name: state      (Cardinality: 5)  -- 只有 5 個不同值，幾乎無區分度

優化動作：合併為聯合索引

ALTER TABLE b DROP INDEX column_id, DROP INDEX state;
ALTER TABLE b ADD INDEX idx_columnid_state (column_id, state);

優化後執行計劃

EXPLAIN ...\G
*************************** 1. row ***************************
table: b
type: ref
possible_keys: columnid_videoid,idx_videoid,idx_columnid_state
key: idx_columnid_state
key_len: 8
ref: const
rows: 199
Extra: Using where

• type 從 index_merge 變為 ref，單一索引查找。
• ref 從 NULL 變為 const，常數等值查找。
• rows 從 100 降為 199（實際掃描更少）。
• 執行時間 0.07s → 0.00s，CPU 336% → <12%。

核心原理

1. index_merge 代價高：需掃描多個索引、合併結果集、回表，CPU 密集型操作。
2. 低基數索引拖累：state 只有 5 個值，索引幾乎等同全表掃描，參與 merge 純屬負擔。
3. 聯合索引 (覆蓋索引) 效率高：(column_id, state) 直接定位符合條件的行，且 state 在索引中可直接過濾，無需回表。

經驗總結

CPU 持續高、IO/內存正常 → 十有八九是索引問題。

• 優先檢查 index_merge、Using temporary、Using filesort、全表掃描的高頻 SQL。
• 低基數列（性別、狀態、類型）單獨建索引通常無效，應放入聯合索引後部或不建。
• 聯合索引列順序遵循「等值優先、範圍次之、排序最後」原則。
• 定期 pt-index-usage 或 sys.schema_unused_indexes 清理無用索引。

變更前後對比表

1. 上一篇文章： MySQL Cardinality 異常導致索引失效：一條 ANALYZE TABLE 救活業務
2. 下一篇文章： Discuz! 熱帖翻頁性能優化：從 filesort 到覆蓋索引再到延遲關聯

MySQL 綜合教學

SQL 優化實戰：IN 子查詢改 JOIN，耗時從 4 秒降至 0.011 秒

慢 SQL（4 秒） SELECT (op.cash_pay

MySQL 命令行匯入匯出實戰：Source、重導向、字元集、建庫與驗證

匯出 (mysqldump) 實戰 full.sql.gz

CSS Counter 深度實戰：偽元素計數器實現靈活有序列表、嵌套編號與動態樣式

CSS Counter（計數器）配合 ::before/::

SchemaSpy 實戰：MySQL 資料庫結構視覺化與關聯分析

工具簡介 SchemaSpy（Java 5+）分析資料庫中繼