MySQL 索引失效五大經典場景深度解析與優化對策

MySQL 索引失效五大經典場景深度解析與優化對策

索引並非萬能，錯誤的寫法會導致優化器放棄索引轉而全表掃描。本文總結五大典型失效場景、原理解析、EXPLAIN 驗證方法與優化重寫方案。

場景一：OR 條件未全覆蓋索引

-- 失效：email 無索引
SELECT * FROM user WHERE phone = '138...' OR email = 'test@xx.com';

-- 優化：為 email 加索引，或改寫為 UNION ALL
SELECT * FROM user WHERE phone = '138...'
UNION ALL
SELECT * FROM user WHERE email = 'test@xx.com' AND phone != '138...';

原則：OR 兩側列必須都有索引才會使用 index merge，否則全表掃描。

場景二：複合索引最左前綴原則違背

-- 索引 idx_name_email(name, email)
-- 生效：使用最左列
SELECT * FROM user WHERE name = '張三';

-- 失效：跳過最左列
SELECT * FROM user WHERE email = 'test@xx.com';

複合索引 (a,b,c) 只有查詢條件包含 a、ab、abc 才能用到索引。

場景三：LIKE 以通配符開頭

-- 失效
SELECT * FROM user WHERE name LIKE '%三';

-- 生效
SELECT * FROM user WHERE name LIKE '張%';

解決：全文索引、倒序索引 (存儲 reverse(name))、Elasticsearch、Trigram 索引 (pg_trgm)。

場景四：字符類型隱式轉換

-- phone 是 VARCHAR(11)
-- 失效：數字常數導致隱式 CAST
SELECT * FROM user WHERE phone = 13800138000;

-- 生效：字符串常數
SELECT * FROM user WHERE phone = '13800138000';

MySQL 將字符串列與數字比較時，會將列值逐行轉為數字，導致索引不可用。永遠用引號包裹字符串常數。

場景五：優化器判定全表掃描更優

• 數據分布極度不均，索引選擇性低 (如性別、狀態枚舉)。
• 需回表行數超過表總行數 20%~30% (版本相關)，優化器傾向全表掃描。
• 統計信息過期，ANALYZE TABLE 更新統計信息。

驗證工具：EXPLAIN 關鍵列

索引使用狀態監控

SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_key      | 高=好  |  -- 索引讀取次數
| Handler_read_rnd_next | 低=好  |  -- 全表掃描次數
+-----------------------+-------+