MySQL索引失效的10个常见原因及排查优化指南

常见索引失效场景解析

索引失效通常意味着MySQL优化器放弃了B+树查找，转而进行效率较低的全表扫描或文件排序。以下是日常开发中最容易踩坑的几种情况：

第1步：避免隐式的类型转换

目的：防止因数据类型不匹配导致的自动转换，确保索引能被命中。
现象：当索引列类型为字符串（如varchar），而查询条件传入数字时，MySQL会进行隐式转换。
代码示例：

-- 假设 num 字段为 varchar 类型
select * from test where num=13911111111; # 失效：字符串与数字比较触发转换

正确做法是在SQL中对字符串类型的数值加上引号，保持类型一致：'13911111111'。

第2步：谨慎使用 OR 连接多条件

目的：避免因为非索引列拖累整体查询性能。
原理：如果 OR 前后的列并非都建立了索引，MySQL可能会放弃索引直接全表扫描。即使都有索引，优化器也可能认为成本过高而不走索引。
建议：若必须使用 OR，请确保所有参与条件的列都有索引；或者将语句拆分为多次查询后在应用层合并结果。

第3步：LIKE 查询注意前缀通配符

目的：保留前缀匹配的索引能力。
规则：LIKE 'abc%' 可以使用索引（前缀模糊匹配），但 LIKE '%abc' 或 LIKE '%abc%' 会导致索引完全失效，因为无法确定起始位置。

第4步：遵守联合索引的最左匹配原则

目的：最大化利用复合索引覆盖范围。
原理：对于联合索引 (a, b, c)，它相当于创建了 (a)、(a, b) 和 (a, b, c) 三个索引。如果查询条件跳过了第一列 a（例如只查 b 和 c），则整个联合索引失效。除非使用覆盖索引且数据量极小，否则应避免跳过最左列。

第5步 & 第6步：不在索引列上进行函数或算术运算

目的：保持索引列原始值可访问性。
现象：以下两种写法都会导致索引失效：

-- 对列应用内置函数（失效）
select * from user where DATE_ADD(login_time, INTERVAL 1 DAY) = '2022-11-08';

-- 对列进行加减乘除运算（失效）
select * from user where age - 1 = 39;

优化：应将计算移至等号右侧常数端。例如：login_time = DATE_ADD('2022-11-08', ...) 或 age = 39 + 1。

第7步：慎用不等于与范围过大查询

目的：让优化器评估最优路径。
说明：使用 !=、<> 或 NOT IN 时，如果符合条件的数据占比很高，优化器可能认为全表扫描比回表更快，从而主动放弃索引。这属于优化器的正常判断逻辑，开发者需结合具体数据分布理解。

第8步：关注 IS NULL / IS NOT NULL 的选择性

目的：排除低选择性列的索引干扰。
说明：在某些旧版本或特定配置下，大量 IS NULL 查询可能导致优化器选择忽略索引。此外，若查询结果返回行数过多，优化器同样会判定“回表成本高”而放弃索引。

第9步：JOIN 关联字段编码必须一致

目的：防止连接时的隐式转换。
场景：两张表进行左连接（Left Join）时，如果关联字段的字符集（Charset）或校对规则（Collation）不一致（如一个为 utf8mb4，另一个为 utf8），MySQL会在运行时进行转换，导致索引失效。

第10步：维护索引的健康度

目的：保证底层数据结构的高效。
操作：虽然索引有自我维护机制，但在频繁的增删改操作下，索引页可能出现碎片化。此时可通过重建索引（如 OPTIMIZE TABLE 或删除重建索引）来恢复其高效状态。

如何验证索引是否生效

判断SQL是否走了索引，最直接的方法是使用 EXPLAIN 关键字查看执行计划。

# 在查询语句前添加 explain 关键字
mysql> explain select * from sampleInfo where agents = "XXX中心有限公司";

查看重点：

• key 列：显示实际使用的索引名称。如果为 NULL，表示未使用索引。
• type 列：连接类型。ALL 代表全表扫描（最差），range 代表范围检索，ref 或 eq_ref 则表示较好。

通过持续分析执行计划并调整SQL结构或索引定义，可以显著优化MySQL的查询响应速度。

总结

以上就是MySQL索引失效的10个常见原因以及SQL查询不走索引的原因的详细解析内容，更多关于MySQL性能优化及EXPLAIN用法的技术资料请关注本站其它相关文章！