MySQL 索引失效常见的 9 种情况

一则或许对你有用的小广告

欢迎加入小哈的星球 ,你将获得:专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸:仿小红书(微服务架构)》 正在持续爆肝中,基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...点击查看项目介绍
  • 《从零手撸:前后端分离博客项目(全栈开发)》 2 期已完结,演示链接: http://116.62.199.48/

截止目前, 星球 内专栏累计输出 72w+ 字,讲解图 3103+ 张,还在持续爆肝中.. 后续还会上新更多项目,目标是将 Java 领域典型的项目都整一波,如秒杀系统, 在线商城, IM 即时通讯,权限管理,Spring Cloud Alibaba 微服务等等,已有 2400+ 小伙伴加入学习 ,欢迎点击围观

在 MySQL 中,索引能有效加速查询,提高数据检索的效率。然而,有时即使表中的某列创建了索引,查询也可能不会使用索引,而是选择全表扫描。这种现象被称为索引失效。本节将介绍导致索引失效的常见原因及解决方法,以便在实际应用中尽量避免这些情况,确保索引正常工作。

1. 什么是索引失效?

索引失效是指在查询时,MySQL 优化器没有选择使用索引,而是直接进行了全表扫描。索引失效通常会导致查询速度变慢,影响系统性能。

2. 导致索引失效的常见原因

2.1 查询条件中使用函数或计算

当查询条件使用了函数或表达式时,索引无法直接匹配,导致失效。例如:

SELECT * FROM t_employee WHERE LEFT(name, 3) = 'Tom';

解决方法:避免在索引列上使用函数或计算,将条件改为原始字段查询,例如:

SELECT * FROM t_employee WHERE name LIKE 'Tom%';

2.2 使用不等于(<>!=)操作符

使用 <>!= 查询时,索引会失效,因为数据库无法通过范围查找快速定位数据。

SELECT * FROM t_employee WHERE salary != 5000;

解决方法:尽量避免使用不等于运算符,或者使用其他逻辑条件替代,若非必要时可不使用索引。

2.3 使用 IS NULLIS NOT NULL

在某些情况下,索引列的 IS NULLIS NOT NULL 条件也会导致索引失效,因为 MySQL 需要检查每一行的 NULL 状态。

SELECT * FROM t_employee WHERE position IS NOT NULL;

解决方法:使用默认值替代 NULL,或尽量避免在查询中检查 NULL

2.4 使用 OR 条件

OR 条件通常会导致索引失效,除非 OR 的每个条件列上都有索引。

SELECT * FROM t_employee WHERE position = 'Manager' OR salary > 7000;

解决方法:可以将 OR 改为 UNION 或者使用多个查询条件,例如:

SELECT * FROM t_employee WHERE position = 'Manager'
UNION
SELECT * FROM t_employee WHERE salary > 7000;

2.5 隐式类型转换

如果查询条件的类型与索引列的类型不一致,MySQL 会进行隐式转换,从而导致索引失效。例如:

SELECT * FROM t_employee WHERE employee_id = '123'; -- employee_id 是 INT 类型

解决方法:确保查询条件的类型与索引列的数据类型一致,避免隐式类型转换。

2.6 使用 LIKE 时通配符在开头

LIKE 查询使用通配符 _ 开头时,MySQL 会进行全表扫描,导致索引失效。例如:

SELECT * FROM t_employee WHERE name LIKE '%Smith';

解决方法:尽量将通配符放在结尾,例如:

SELECT * FROM t_employee WHERE name LIKE 'Smith%';

2.7 使用范围条件后再用索引列排序

如果在查询中先使用了范围条件(如 BETWEEN<>),再对索引列排序,可能会导致索引无法被完全利用。

SELECT * FROM t_employee WHERE salary > 5000 ORDER BY position;

解决方法:根据查询场景,适当调整排序或索引列顺序,或将排序条件改为非范围条件。

2.8 使用联合索引时未遵循最左前缀原则

复合索引中的列是有顺序的,如果查询中没有包含最左侧的列,索引将不会生效。这称为最左前缀原则

-- 假设有联合索引 (name, position)
SELECT * FROM t_employee WHERE position = 'Manager';

解决方法:确保查询中包含复合索引的最左列,例如:

SELECT * FROM t_employee WHERE name = 'John' AND position = 'Manager';

2.9 数据选择性过低

当索引列的数据选择性过低(如只有几个不同的值),MySQL 优化器可能会认为全表扫描比索引查找更有效率,从而选择跳过索引。

SELECT * FROM t_employee WHERE gender = 'M'; -- 假设 gender 列只有 "M" 和 "F" 两个值

解决方法:对于选择性低的列,可以考虑不创建索引,或改用其他高选择性的列来过滤数据。

3. 小结

索引是 MySQL 查询优化的关键,但不合理的查询方式或条件可能会导致索引失效。掌握导致索引失效的常见原因并进行优化,可以提升数据库性能。在实际应用中,应注意避免在查询条件中使用函数、不等于运算符、OR 条件等情况,遵循复合索引的最左前缀原则,并确保数据类型一致,以保证索引的有效性。