MySQL 索引失效七大場景與優化對策

MySQL 索引不生效的常見原因與應對策略

建立索引不等於用上索引。以下七大場景會導致優化器放棄索引，掃描全表，嚴重拖慢查詢性能。

1. 低基數列（重複值過多）

如性別（M/F）、狀態（0/1）、布爾標誌。優化器判斷掃描成本低於索引回表，直接全表掃描。
對策：不建索引，或組合索引中置於低選擇性列之後。

2. WHERE 條件使用不等號（!= / <>）

WHERE status != 1 無法使用索引。
對策：改寫為 IN 或 UNION，或接受全表掃描（若結果集大）。

3. WHERE 條件對列使用函數/表達式

WHERE DAY(create_time) = 1、WHERE UPPER(name) = 'ABC' 導致索引失效。
對策：改寫為範圍查詢 WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'；或建立函數索引（MySQL 8.0+ 支持）。

4. JOIN 關聯列數據類型不一致

主鍵 INT 與外鍵 VARCHAR 關聯，隱式轉換導致索引失效。
對策：統一關聯列類型、長度、字符集、校驗規則。

5. LIKE/REGEXP 以通配符開頭

LIKE '%abc%'、REGEXP '.*abc' 無法利用索引前綴匹配。
對策：全文索引、倒排索引、或業務層面避免前置模糊查詢。

6. ORDER BY 條件非索引列或包含表達式

ORDER BY RAND()、ORDER BY col1 + col2 不用索引。多表 JOIN 時即使有索引，ORDER BY 往往也需 filesort。
對策：覆蓋索引包含排序列；小結果集在應用層排序。

7. 隱式類型轉換

WHERE varchar_col = 123 觸發列隱式轉換為數字，索引失效。
對策：WHERE varchar_col = '123'，應用層嚴格類型綁定。

快速排查清單

1. EXPLAIN 觀察 type 是否為 ALL（全表掃描）。
2. key 列為 NULL 表示未用索引。
3. 檢查 WHERE、JOIN、ORDER BY 是否觸發上述場景。
4. 優化 SQL 或調整索引結構（覆蓋索引、組合索引順序）。