mysql为什么有时索引不生效_mysql索引失效的原因分析

mysql索引失效的五大主因：函数/表达式导致全表扫描；隐式类型转换使索引失效；or条件中任一分支无索引即退化；统计信息过期或数据分布倾斜致优化器误判；即使走索引但type为index或extra含filesort/temporary仍会慢。

WHERE 条件用了函数或表达式导致 INDEX 失效

MySQL 无法对索引字段做函数运算后仍走索引（除非是生成列+函数索引，且 MySQL ≥ 8.0.13）。比如 WHERE YEAR(create_time) = 2023，即使 create_time 有索引，也会全表扫描。

常见踩坑点：

• WHERE UPPER(name) = 'ABC' → 改成 WHERE name = 'abc' 并确保 collation 不区分大小写
• WHERE age + 1 > 25 → 改成 WHERE age > 24
• WHERE date_col LIKE '%2023%' → 左模糊会跳过索引，应改用范围查询或前缀索引

隐式类型转换让 INDEX 彻底失效

当 WHERE 中的字段类型和传入值类型不一致时，MySQL 可能自动转换字段值（而非参数），导致索引无法使用。典型场景是字符串字段存数字、或字段定义为 VARchar 却传入整数。

例如：

SELECT * FROM users WHERE mobile = 13812345678;

如果 mobile 是 VARCHAR(20)，MySQL 会把每一行 mobile 转成数字比较，索引失效。正确写法是加引号：WHERE mobile = '13812345678'。

其他易错情况：

• CHAR 字段与空格敏感比较：WHERE code = 'A ' 可能不命中索引（取决于 pad_char_to_full_length 设置）
• enum 或 SET 字段传字符串 vs 数字：值类型必须严格匹配定义顺序

OR 条件中部分字段无索引

MySQL 对 OR 的索引优化较保守：只要有一个分支字段没索引，整个条件大概率退化为全表扫描。例如：

SELECT * FROM orders WHERE status = 'paid' OR user_id = 1001;

若只有 status 有索引、user_id 没索引，则通常不走任何索引。

解决办法：

• 给所有 OR 分支字段都建索引（复合索引不适用，需单列索引）
• 改写为 union：两个独立查询各自走索引再合并
• 用覆盖索引减少回表成本（但不解决扫描范围问题）

统计信息过期或数据分布极端导致优化器放弃索引

MySQL 优化器基于表的统计信息（如 cardinality）估算成本。如果数据量突增、大批量 INSERT/delete 后未更新统计，或某字段值高度重复（如 is_deleted TINYINT 99% 是 0），优化器可能判断“走索引比全表扫描还慢”，主动跳过。

验证方式：

• 执行 EXPLAIN 看 key 是否为空、rows 是否远大于实际匹配数
• 运行 ANALYZE table table_name 强制刷新统计信息
• 用 FORCE INDEX 临时验证索引是否真能提升性能（但别长期依赖）

真正难排查的是“明明有索引、EXPLAIN 显示走了、但查询还是慢”——这时候得看 type 是 range 还是 index，以及 Extra 里有没有 using filesort 或 Using temporary，这些才是真正拖慢的元凶。