通過 EXPLAIN 觀察 type=ALL、key=NULL 確認失效。以下五大場景覆蓋 90% 線上案例。
-- 失效：b 無索引
SELECT * FROM t WHERE a = 1 OR b = 2;
-- 生效：a、b 均有索引 (或複合索引覆蓋)
SELECT * FROM t WHERE a = 1 OR b = 2; -- 索引合併 Index Merge
原理：MySQL 無法在單次索引查找中處理跨列 OR，除非每個 OR 分支都能獨立走索引並觸發 Index Merge (union)。
最佳實踐：改用 UNION ALL (結果無重複) 或 IN 重寫。
-- 索引 idx(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 a = 1 AND c = 3 -- 中間斷層，僅用到 a
鐵律：WHERE/ORDER BY/GROUP BY 必須從索引最左列連續匹配，中間不可跳過。
-- 生效
WHERE name LIKE 'Zhang%'
WHERE name LIKE 'Zh%ng%'
-- 失效
WHERE name LIKE '%Zhang'
WHERE name LIKE '%Zhang%'
原因：B+Tree 索引按字典序存儲，前綴固定才能範圍定位。
替代方案：全文索引 (FULLTEXT)、Elasticsearch、反轉存儲 (REVERSE(name) 索引)。
-- 表結構: phone VARCHAR(11)
-- 失效：數字字面量導致隱式 CAST(phone AS UNSIGNED)
SELECT * FROM t WHERE phone = 13800138000;
-- 生效：字符串字面量
SELECT * FROM t WHERE phone = '13800138000';
原理：MySQL 將字符串列與數字比較時，會將列值轉為數字，**導致索引不可用**。
排查：EXPLAIN 顯示 Using where 無 Using index，CAST() 隱式出現在優化器跟蹤中。
-- 數據分布：status=1 佔 95%
SELECT * FROM t WHERE status = 1; -- 優化器選擇全表掃描
觸發條件：預估走索引需讀取 > 30% 表數據 (隨機 I/O 成本 > 循環讀取)。
干預手段：
FORCE INDEX (idx_status) 強制索引。LIMIT 讓優化器重新評估。ANALYZE TABLE t;。SHOW GLOBAL STATUS LIKE 'Handler_read%';
| 指標 | 含義 | 健康標準 |
|---|---|---|
Handler_read_key | 索引讀取次數 | 越高越好 (主力查詢走索引) |
Handler_read_rnd_next | 全表掃描/隨機讀下一行 | 越低越好 (大量出現 = 失效/無索引) |
Handler_read_first | 讀第一行 (全掃描開始) | 低 |
Handler_read_last | 讀最後一行 (DESC 掃描) | 低 |
Handler_read_next | 索引順序讀下一行 (範圍掃描) | 正常 |
Handler_read_prev | 索引逆序讀 | 正常 |
Handler_read_rnd | 隨機讀 (排序/臨時表) | 低 |
EXPLAIN 顯示 type=ALL。optimizer_switch 影響。FORCE INDEX / ANALYZE TABLE → 再次 EXPLAIN 確認 type=ref/range/eq_ref。索引失效多源於「寫法不規範」與「統計信息偏差」。將「最左前綴、字符串加引號、OR 改 UNION、LIKE 不以 % 開頭、定期 ANALYZE」刻入團隊 SQL 規範，配合自動化 EXPLAIN 審核 (CI/CD 管道)，可從源頭消除 95% 索引失效風險。
