MySQL索引失效的5个典型场景及排查方法

索引失效的5个典型场景

了解索引何时“失灵”，有助于在编写SQL时主动规避性能陷阱。以下是导致索引失效的常见情况：

第1步：使用OR连接条件且未全覆盖索引

目的：理解为何OR条件可能导致全表扫描。

当查询条件中包含 OR 时，如果其中部分条件带有索引，而另一部分没有，MySQL通常不会使用该索引，直接进行全表扫描。这是为了降低优化器的复杂度。

解决方法：若希望 OR 条件也能命中索引，必须确保 OR 连接的每一个列都单独建立了索引。或者，将逻辑拆分为多个 SELECT 语句并用 UNION ALL 合并。

第2步：违反联合索引的最左前缀原则

目的：明确多列索引的使用规则。

对于建立的多列索引（例如 (a, b, c)），查询条件必须从最左边的列开始匹配。如果使用索引的第二部分或后续部分（如仅查询 b 或 c），索引将失效，因为MySQL无法跨越已确定的前列去搜索后续列。

第3步：LIKE查询以%开头

目的：识别前缀模糊搜索对索引的影响。

在使用 LIKE 进行模糊查询时，如果通配符 % 出现在字符串的开头（例如 '%abc'），索引将失效。这是因为B+树结构只能高效地查找前缀固定的数据，无法有效利用索引树进行左侧通配的检索。

提示：尽可能使用 'abc%'（后缀通配）这种形式，它仍然可以利用索引。

第4步：字符类型字段未加引号

目的：防止因类型转换导致的隐式索引失效。

如果列的数据类型是字符串（VARCHAR/TEXT），在查询条件中必须使用引号包裹值（如 = 'value'）。如果忽略引号（如 = value，数值型），MySQL会在内存中进行数据类型转换（隐式转换），这会导致引擎放弃使用索引而进行全表扫描。

第5步：优化器估算全表扫描更快

目的：理解执行计划的决策逻辑。

MySQL查询优化器非常智能。如果统计信息显示数据量较小，或者查询返回的数据量占表中总行数的比例很高（通常超过一定阈值，如20%-30%），优化器可能会认为扫描整个表的I/O成本低于通过索引定位再回表查询的成本，从而主动放弃使用索引。

如何监控与诊断索引使用情况

要判断当前查询是否真正用上了索引，可以查看MySQL的运行状态变量。

查看状态参数

在MySQL命令行中执行以下命令，观察索引使用的频率：

show status like 'Handler_read%';

关键指标解读

• handler_read_key：这个值越高越好，表示查询成功通过索引读取行的次数。
• handler_read_next：顺序读取索引下一行的请求数，通常在范围查询时会增加。
• handler_read_rnd_next：这个值越高，说明查询低效。它代表在读过索引后，仍需逐行读取数据的次数，通常意味着发生了全表扫描或文件排序。

总结

以上关于MySQL索引失效情况及监控方法的详细内容。在开发过程中，养成书写规范SQL的习惯，并结合执行计划（EXPLAIN）和状态监控进行分析，能有效保障系统的查询性能。更多关于数据库优化的资料请关注本站其它相关文章！