索引失效主因包括函数操作、否定查询、OR连接无索引字段、前导模糊匹配、类型转换、联合索引违背最左原则及大数据量回表；通过EXPLAIN分析执行计划，检查索引设计与SQL写法，避免隐式转换，可有效排查并优化。
MySQL索引失效会显著影响查询性能，导致原本应走索引的查询变成全表扫描。了解索引失效的原因并掌握排查方法，是优化数据库查询的关键。
一、MySQL索引失效的常见原因
1. 查询条件中使用了函数或表达式
对索引列进行函数操作或计算会导致索引无法使用。
例如：-
SELECT * FROM user WHERE YEAR(create_time) = 2023;—— 对日期字段使用YEAR()函数，即使create_time有索引也会失效。 -
SELECT * FROM user WHERE age + 1 = 20;—— 对索引列做加法运算。
2. 使用了不等于（!= 或 ）、NOT IN、NOT EXISTS等否定操作
这类操作通常无法有效利用B+树索引结构，导致全表扫描。
例如：SELECT * FROM user WHERE status != 1;SELECT * FROM user WHERE id NOT IN (1,2,3);
3. 使用OR连接多个条件，且部分字段无索引
当OR前后有一个字段没有索引时，可能导致整个查询放弃使用索引。
例如：-
SELECT * FROM user WHERE name = '张三' OR phone = '138...';—— 若phone无索引，name的索引也可能不生效。
4. 模糊查询以%开头
B+树索引从左往右匹配，前导通配符破坏了最左匹配原则。
例如：-
SELECT * FROM user WHERE name LIKE '%三';—— 索引失效。 -
SELECT * FROM user WHERE name LIKE '张%';—— 可用索引。
5. 类型转换或隐式转换
当查询字段与条件值类型不一致时，MySQL会自动做类型转换，导致索引失效。
例如：-
SELECT * FROM user WHERE user_id = '123';—— user_id是INT类型，传入字符串会触发隐式转换。
6. 联合索引未遵循最左前缀原则
联合索引(a,b,c)，只有按a、a+b、a+b+c的顺序才能命中索引。跳过a直接查b或c，索引无效。
例如：-
WHERE b = 2 AND c = 3;—— 无法使用(a,b,c)联合索引。
7. 查询返回数据量过大，优化器选择全表扫描
即使有索引，如果MySQL预估通过索引扫描的行数接近全表，优化器可能认为全表扫描更高效。
例如：
- 表中有10万条数据，查询条件匹配9万条，此时走索引成本更高。
8. 使用SELECT *
若索引不是覆盖索引，查询*需要回表，当数据量大时优化器可能放弃索引。
二、MySQL索引失效的排查方法
1. 使用EXPLAIN分析执行计划
在SQL语句前加上EXPLAIN，查看是否使用了索引。
重点关注字段：- type：ALL表示全表扫描，index表示索引扫描，ref/eq_ref表示使用了索引，性能较好。
- key：实际使用的索引名称，为NULL表示未使用索引。
- rows：扫描的行数，越大说明效率越低。
- Extra：出现Using where; Using filesort或Using temporary说明存在性能问题。
2. 检查索引设计是否合理
- 确认查询条件中的字段是否包含在索引中。
- 联合索引是否遵循最左前缀原则。
- 是否可以创建覆盖索引减少回表。
3. 查看表结构和索引信息
使用以下命令查看索引情况：
SHOW INDEX FROM table_name;DESCRIBE table_name;4. 避免隐式类型转换
- 确保查询条件的数据类型与字段定义一致。
- 尤其是INT字段不要传字符串，VARCHAR不要漏掉引号。
5. 优化SQL写法
- 避免在索引列上做计算或函数处理。
- 尽量用IN代替OR（前提是字段都有索引）。
- 模糊查询尽量避免前导%。
6. 使用FORCE INDEX强制走索引（谨慎使用）
可用于验证索引是否有效：
SELECT * FROM user FORCE INDEX(idx_name) WHERE name = '张三';但生产环境不建议长期使用，干扰优化器判断。
基本上就这些。索引失效多数源于SQL写法不当或设计不合理。通过EXPLAIN分析执行计划，结合业务逻辑调整索引和查询方式，能有效提升查询性能。
