MySQL 索引會失效的情況很多，尤其是在查詢條件、數據類型、排序方式或優化器選擇策略上不合適時。下面我按常見原因分組給你梳理一下，並標明是肯定失效還是可能失效（取決於優化器判斷）。

一、查詢條件寫法導致失效

這些是最常見的坑：

1. 在索引列上使用函數或表達式（肯定失效）

   SELECT * FROM user WHERE YEAR(create_time) = 2024;  -- create_time 上的索引失效
   SELECT * FROM user WHERE id + 1 = 10;               -- 也是一樣

   ✅ 解決：把函數和運算移到等式右邊。

2. 在索引列上使用隱式類型轉換（肯定失效）

   SELECT * FROM user WHERE phone = 1234567890;  -- phone 是 VARCHAR，但用數字比較

   ✅ 解決：保證比較兩邊數據類型一致。

3. 使用 %xxx 模糊匹配（肯定失效）

   SELECT * FROM user WHERE name LIKE '%abc';   -- 前面有 % 導致無法走索引

   ✅ 解決：LIKE 'abc%' 才能用到索引。

4. OR 條件中有未建立索引的列（可能失效）

   SELECT * FROM user WHERE id = 1 OR name = 'Tom';  
   -- 如果 name 沒有索引，id 的索引可能也不用

   ✅ 解決：保證 OR 兩邊的列都建了索引，或拆成 UNION 查詢。

二、範圍查詢和組合索引的限制

1. 組合索引的最左前綴原則被破壞（肯定失效）

   INDEX(a, b, c)
   SELECT * FROM t WHERE b = 1 AND c = 2; -- 不走索引，因為 a 沒有出現在條件中

2. 範圍查詢會截斷索引使用（可能失效）

   INDEX(a, b)
   SELECT * FROM t WHERE a > 5 AND b = 1;
   -- a > 5 用索引，但 b 可能無法繼續用（取決於範圍大小和優化器判斷）

三、數據分佈與優化器策略

1. 查詢返回數據過多時（可能失效）
   如果 MySQL 判斷全表掃描比走索引更快，它會直接放棄索引。
   比如：

   SELECT * FROM user WHERE gender = 'M';  -- 如果性別列只有兩個值且數據量很大

2. 統計信息不準確
   表更新很多但沒有 ANALYZE TABLE，優化器可能做出錯誤決策。

四、排序與分組

1. ORDER BY 與索引順序不一致（可能失效）

   INDEX(a, b)
   SELECT * FROM t ORDER BY b, a; -- 順序不匹配

2. 不同方向排序混用（肯定失效）

   INDEX(a, b)
   SELECT * FROM t ORDER BY a ASC, b DESC; -- 方向不一致無法用索引

五、其他情況

1. IS NULL 與 IS NOT NULL

   • IS NULL 通常可以用到索引
   • IS NOT NULL 可能會失效（取決於數據分佈）
2. NOT IN / <> / !=（大概率失效）
   這些條件很難利用索引，因為它們會排除大量值。
3. 使用臨時表或子查詢結果集沒有索引
   如果子查詢結果是臨時表（沒有索引），外層條件也用不上原表索引。

快速定位索引是否失效的方法
在 SQL 前加 EXPLAIN，看 key 列是否有值，type 是否為 ALL（全表掃描）或 index（全索引掃描）。

EXPLAIN SELECT * FROM user WHERE name LIKE '%abc';