如何在mysql中分析索引失效原因

索引失效是MySQL性能问题的常见原因。要分析索引是否生效，关键在于理解查询执行计划，并结合SQL语句结构、表设计和索引类型进行综合判断。以下是具体的分析方法和常见失效场景。

使用EXPLAIN分析执行计划

在SQL语句前加上EXPLAIN，可以查看MySQL如何执行查询：

重点关注字段：

• type：访问类型，从const、ref、range到index、ALL，越靠后效率越低。ALL表示全表扫描，通常意味着索引未被使用。
• key：实际使用的索引。如果为NULL，说明没有使用索引。
• possible_keys：可能使用的索引。如果这里列出但key为空，说明索引未被选中。
• rows：扫描行数。数值越大，查询效率越低。
• Extra：额外信息，如“Using where; Using filesort”或“Using temporary”，往往暗示性能问题。

常见索引失效场景及解决方法

以下情况会导致索引无法使用，需特别注意：

纳米搜索

纳米搜索：360推出的新一代AI搜索引擎

30

查看详情

• 对索引列进行表达式或函数操作
  例如：WHERE YEAR(create_time) = 2023，即使create_time有索引，也会失效。应改为：WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。
• 隐式类型转换
  字符串字段用数字查询时，如WHERE user_id = 123（user_id为VARCHAR），MySQL会转换类型导致索引失效。应保持类型一致：WHERE user_id = '123'。
• 使用不等于或NOT IN
  !=、<>、NOT IN等操作通常不走索引，尽量避免或改写为可索引形式。
• 左模糊匹配
  LIKE '%abc'无法使用B+树索引。只有右模糊LIKE 'abc%'才有效。
• 联合索引未遵循最左前缀原则
  比如索引为(a,b,c)，查询条件只用b或c，索引不会生效。必须从最左边开始连续使用。
• OR连接条件导致索引失效
  当OR前后字段未都建有索引，或索引未覆盖所有条件时，可能导致全表扫描。可用UNION替代或确保各分支都能走索引。

检查索引设计与数据分布

有时索引存在但未被使用，可能是优化器认为全表扫描更快：

• 数据量小：表只有几十行，MySQL可能直接全表扫描。
• 选择性差：索引列重复值过多（如性别），优化器判断索引收益低。
• 统计信息过期：运行ANALYZE TABLE table_name;更新统计信息，帮助优化器做更优决策。

利用工具辅助诊断

除了EXPLAIN，还可以：

• 开启慢查询日志，定位执行时间长的SQL。
• 使用Performance Schema或sys schema中的视图，如sys.statements_with_full_table_scans找出未使用索引的语句。
• 通过FORCE INDEX强制使用某个索引，验证是否提升性能，进而反推原计划为何不选索引。

基本上就这些。关键是养成写SQL时思考索引的习惯，配合EXPLAIN验证，逐步积累对执行计划的理解。