索引失效的主要原因有：1.没有正确创建索引；2.查询语句写法不当；3.索引区分度低；4.隐式类型转换；5.联合索引未使用第一列；6.数据量太小。排查方法是通过explain查看执行计划，关注key、type和extra字段。优化建议包括检查sql写法、合理设计索引、增加覆盖索引、定期分析表统计信息以及避免隐式转换。
索引失效在MySQL中是比较常见的问题，尤其是在查询性能变慢时。主要原因包括索引设计不合理、SQL语句写法不规范，或者是数据本身的特性导致优化器放弃使用索引。排查起来也不复杂，但需要有条理地分析。
索引失效的常见原因
- 没有正确创建索引：比如查询条件中的字段压根没加索引，或者多列索引没有遵循最左前缀原则。
-
查询语句写法不当：例如用了
OR连接条件、LIKE以通配符开头（如LIKE '%abc'）、对字段进行函数操作等。 - 索引区分度低：如果某个字段重复值很多（比如性别字段），即使加了索引，优化器也可能认为走全表扫描更快。
- 隐式类型转换：比如字段是字符串类型，而传入的是数字，MySQL会做自动转换，导致索引无法使用。
-
联合索引未使用第一列：比如一个联合索引是
(a, b, c)，但如果查询只用到了b或c，那么这个索引可能就不会被使用。 - 数据量太小：当表的数据量非常少的时候，MySQL可能会直接选择全表扫描，因为走索引反而更费事。
如何排查索引是否生效？
最常用也最有效的方式就是通过 EXPLAIN 来查看 SQL 的执行计划。重点关注以下字段：
-
key：实际使用的索引，如果是
NULL说明没用上索引 -
type：访问类型，越靠前越好（比如
ref、range），如果是ALL那就是全表扫描了 -
Extra：有没有出现
Using filesort、Using temporary之类的提示，这些也是性能问题
举个例子：
EXPLAIN SELECT * FROM user WHERE name LIKE '%张三';
你会发现key是NULL，type是ALL，说明这个查询没有走索引。
常见优化建议
-
检查SQL写法：避免在查询字段上使用函数，尽量不用
OR，LIKE要以固定前缀开头 - 合理设计索引：尤其是联合索引，注意顺序和最左匹配原则
- 适当增加覆盖索引：把查询字段都包含进索引里，减少回表操作
-
定期分析表统计信息：使用
ANALYZE TABLE来更新索引的统计信息，帮助优化器做出更好判断 - 避免不必要的隐式转换：确保传参类型和字段类型一致，比如字符串就加引号
基本上就这些情况和排查方法。虽然看起来有点多，但只要每次查SQL的时候养成用EXPLAIN的习惯，就能很快发现问题所在。
