MySQL 索引失效 5 大經典場景與規避指南

MySQL 索引失效 5 大經典場景與規避指南

索引建好了卻不走，是調優中最令人抓狂的問題。本文總結 5 大高發失效場景、EXPLAIN 驗證方法、監控指標、以及針對性改寫/加索引策略，助你快速定位並修復。

場景 1：OR 條件列索引不全

-- 失效
EXPLAIN SELECT * FROM t WHERE a=1 OR b=2;  -- type=ALL
-- a 有索引，b 無索引 → 優化器放棄索引全掃

-- 修復：b 也加索引，或改寫 UNION ALL
SELECT * FROM t WHERE a=1
UNION ALL
SELECT * FROM t WHERE b=2 AND a!=1;

場景 2：聯合索引非最左前綴

INDEX idx_a_b_c (a, b, c);

-- 生效
WHERE a=1
WHERE a=1 AND b=2
WHERE a=1 AND b=2 AND c=3

-- 失效
WHERE b=2          -- 缺最左列 a
WHERE c=3          -- 缺 a,b
WHERE a=1 AND c=3  -- 中間斷層 (b 缺失)，僅用到 a

場景 3：LIKE 以通配符開頭

-- 失效
WHERE name LIKE '%張三%'
WHERE name LIKE '%三'

-- 生效
WHERE name LIKE '張三%'
WHERE name LIKE '張%三%'  -- 前綴固定可走索引

場景 4：字符列隱式類型轉換

-- 表結構: phone VARCHAR(11)
-- 失效: 數值字面量導致列隱式 CAST
EXPLAIN SELECT * FROM user WHERE phone = 13800138000;  -- type=ALL

-- 生效: 字符串字面量
EXPLAIN SELECT * FROM user WHERE phone = '13800138000'; -- type=ref

場景 5：優化器判斷全掃更優 (回表代價過大)

-- 表 1000 萬行，索引選擇性 30% (需回表 300 萬行)
EXPLAIN SELECT * FROM t WHERE status=1;  -- 可能 type=ALL

-- 修復方向
-- 1. 覆蓋索引: INDEX(status, col1, col2...) 包含 SELECT 列
-- 2. 只查主鍵: SELECT id FROM t WHERE status=1; 再回表分批
-- 3. 強制索引 (慎用): SELECT * FROM t FORCE INDEX(idx_status) WHERE status=1;
-- 4. 調整 optimizer_switch: SET optimizer_switch='index_condition_pushdown=off';

快速驗證手段

1. EXPLAIN 觀察 type、key、rows、Extra。
2. SHOW STATUS LIKE 'Handler_read%'; 對比 Handler_read_key (索引讀) vs Handler_read_rnd_next (全掃)。
3. EXPLAIN ANALYZE (8.0+) 看實際執行時間、循環次數。

監控告警建議

• Handler_read_rnd_next / Questions > 0.1 觸發慢查詢/索引缺失告警。
• 定期跑 sys.schema_unused_indexes 清理無用索釋放寫入能。
• 新上線 SQL 必須經過預發 EXPLAIN 審核，CI/CD 管道集成 pt-query-digest 或 sqllint。

索引失效本質是「優化器基於成本模型的理性選擇」。理解成本模型 (IO、CPU、網絡、回表代價) 比死記規則更有效。遇到失效先問：是條件寫法問題、還是索引設計缺陷、還是統計信息過期、還是優化器 Bug？ 分層排查，對症下藥。