MySQL 索引優化實戰：存儲分類、使用條件、失效場景與監控指標深度解析

MySQL 索引優化實戰百科：存儲架構、優化器決策邏輯、失效陷阱避坑與性能監控

索引是 SQL 優化的核心槓桿。本文系統整理 MyISAM/InnoDB 索引存儲差異、B+Tree/HASH 選擇、優化器使用條件、6 大經典失效場景、覆蓋索引最佳實踐、Handler 監控指標體驐。

一、索引存儲架構對比

前綴索引：CREATE INDEX idx_name ON tbl(col(4)) 截取前 4 字符，大幅縮小體積，適用高基數長列。

二、優化器使用索引的鐵律

1. 條件中必須出現索引鍵 (WHERE/JOIN/ORDER BY/GROUP BY)
2. 最左前綴原則：複合索引 (a,b,c) 僅 a、a,b、a,b,c 可用
3. 索引列不可做函數/運算/隱式轉換

三、索引生效的 5 大典型場景

1. 複合索引最左列出現在 WHERE
2. LIKE 'prefix%' 右側通配符
3. 全文索引 MATCH...AGAINST
4. IS NULL 條件
5. 覆蓋索引：Extra: Using index 避免回表

四、索引存在但失效的 6 大陷阱

五、覆蓋索引：避免回表的殺手鐧

-- 查詢 SELECT a,b FROM t WHERE a=1 AND b=2
-- 建立 INDEX(a,b,c) 即為覆蓋索引，Extra 顯示 Using index
-- 二級索引葉子已包含 a,b,c + 主鍵，無需回聚簇索引

六、Handler 監控指標：量化索引效果

SHOW STATUS LIKE 'Handler_read_%';

黃金比率：Handler_read_key / (Handler_read_key + Handler_read_rnd_next) > 90%

七、實戰優化清單

1. 單表索引 ≤ 5
2. 複合索引列序：高區分度 → 低，高頻 WHERE → 低
3. 覆蓋索引優化核心查詢
4. 消除冗余索引 (INDEX(a) + INDEX(a,b) → 保留後者)
5. 定期 ANALYZE TABLE 更新統計信息
6. 大表 DDL 用 pt-online-schema-change 無鎖變更
7. 監控 Handler_read_rnd_next 暴增告警