MySQL 索引失效七大經典場景與排查指南

索引並非萬能，特定查寫模式會導致優化器放棄索引轉為全表掃描。本文總結七大經典失效場景、EXPLAIN 排查方法、避坑最佳實踐。

七大索引失效場景

1. OR 條件未全覆蓋索引

-- 失效：a 有索引，b 無索引
SELECT * FROM t WHERE a = 1 OR b = 2;

-- 生效：a、b 皆有索引 (或複合索引覆蓋)
SELECT * FROM t WHERE a = 1 OR b = 2;
-- 或改寫為 UNION
SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;

原理： 優化器評估 OR 成本時，若任一側無索引，傾向全掃描。

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
WHERE c = 3
WHERE b = 2 AND c = 3

3. LIKE 以萬用字元開頭

-- 失效：無法使用索引
WHERE name LIKE '%abc'
WHERE name LIKE '%abc%'

-- 生效：固定前綴
WHERE name LIKE 'abc%'
WHERE name LIKE 'abc_%'

4. 字串欄位隱式類型轉換 (未加引號)

-- 表結構：name VARCHAR(20)

-- 失效：數字常數導致隱式轉換 CAST(name AS UNSIGNED)
WHERE name = 123

-- 生效：字串常數
WHERE name = '123'

現象： EXPLAIN 顯示 type=ALL，Extra 含 Using where 但無 Using index。

5. 優化器判斷全掃描更快 (數據分布/統計資訊)

-- 場景：查詢返回行數超過表總行數 20-30%
-- 即使有索引，優化器可能選擇全表掃描
WHERE status = 1  -- 假設 90% 行 status=1

對策： 強制索引 FORCE INDEX(idx_status)、調整查詢邏輯、更新統計資訊 ANALYZE TABLE。

6. 索引列參與運算/函數

-- 失效：索引列被函數包裹
WHERE DATE(created_at) = '2024-01-01'
WHERE YEAR(created_at) = 2024
WHERE name + '' = 'abc'

-- 生效：範圍查詢保持索引列乾淨
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

7. 字符集/排序規則不匹配導致隱式轉換

-- 表欄位：utf8mb4_unicode_ci
-- 查詢參數：latin1 或 utf8_general_ci

-- 失效：隱式轉換
WHERE name = 'abc'  -- 連線字符集非 utf8mb4

-- 生效：顯式指定
WHERE name = _utf8mb4'abc' COLLATE utf8mb4_unicode_ci
-- 或確保連線字符集一致
SET NAMES utf8mb4;

索引使用情況監控

SHOW STATUS LIKE 'Handler_read%';

-- 關鍵指標：
-- Handler_read_key       : 越高越好 (索引讀取次數)
-- Handler_read_next      : 索引範圍掃描次數
-- Handler_read_rnd       : 非索引隨機讀取 (排序/臨時表)
-- Handler_read_rnd_next  : 越高越差 (全表掃描次數)

EXPLAIN 關鍵欄位速查

避坑檢查清單

1. 所有 WHERE/JOIN/ORDER BY 欄位是否有索引覆蓋？
2. 複合索引列順序是否符合最左前綴？
3. 字串比較是否加引號？字符集是否一致？
4. 是否對索引列使用函數/運算？
5. LIKE 是否前導萬用字元？
6. 大表是否定期 ANALYZE TABLE 更新統計？
7. 慢查詢日誌是否開啟 (long_query_time=1)？