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 NULL
或 IS NOT NULL
在某些情况下,索引列的 IS NULL
或 IS 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
条件等情况,遵循复合索引的最左前缀原则,并确保数据类型一致,以保证索引的有效性。