MySQL中索引失效的常见状况
MySQL里对索引进行优化是提升查询性能的重要方式之一，不过有时候因为使用不当会让索引不起作用。接下来我们一同探究哪些情况下索引会失效。
1、联合索引未遵循最左前缀规则
-
失效的例子：联合索引
(a,b,c)SELECT * FROM table WHERE b=1 AND c=2; -- ❌ 索引失效 -
正确的写法：
WHERE a = ? -- ✅ WHERE a = ? AND b = ? -- ✅ WHERE a = ? AND b = ? AND c = ? -- ✅ -- 注：MySQL对于=条件的列，优化器会按索引顺序重新整合WHERE条件，比如： WHERE b = ? AND a = ? AND c = ? -- ✅ 同样会走索引
2、在索引列上运用函数或进行运算
-
失效的例子：
SELECT * FROM orders WHERE YEAR(create_time) = 2025; -- ❌ 索引失效 -
正确的写法：
-- 换成范围查询 SELECT * FROM orders WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31'; -- ✅
3、隐式的类型转换
-
失效的例子：字段类型和查询值的类型不一致
-- user_id 是 VARCHAR 类型 SELECT * FROM users WHERE user_id = 1001; -- ❌ 索引失效（数字转字符串，MySQL得把列值转成数字再比较，没法走索引） -
正确的写法：
SELECT * FROM users WHERE user_id = '1001'; -- ✅ 保证类型一致
4、LIKE 查询时左边加了通配符 %
- 失效的例子：
SELECT * FROM users WHERE name LIKE '%王'; -- ❌ 索引失效
- 正确的写法：
SELECT * FROM users WHERE name LIKE '王%'; -- ✅ 能够使用索引
5、OR连接非索引列
- 失效的例子：
-- age 有索引，address 无索引
SELECT * FROM users WHERE age > 25 OR address = '北京'; -- ❌ 索引失效
- 正确的写法：
**-- 拆分成 UNION SELECT * FROM users WHERE age > 25 UNION SELECT * FROM users WHERE address = '北京'; -- ✅
6、使用 IS NULL / IS NOT NULL
SELECT * FROM users WHERE name IS NULL; -- ✅ 通常能用索引
SELECT * FROM users WHERE name IS NOT NULL; -- ❌ 索引不一定会用，一般不能用
7、NOT IN / NOT EXISTS
- 失效的例子：
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist); -- ❌ 索引失效
- 正确的写法：
-- 改用 LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL; -- ✅
有时候会碰到明明使用方法正确，但看执行计划却没走索引的情况，这有可能是数据量比较少时，MySQL自带的优化器觉得全表扫描更快。而且索引失效的情况，我只是列举了几种常见的。像重复索引、索引统计信息过期、范围查询中断联合索引等等，也都会造成索引失效。我们能够依据具体情况来进行分析，对于执行计划的解读，大家可以参考另一篇博文 MySQL EXPLAIN 关键字详解。
资本的低迷时期终究会过去。-- 烟沙九洲
◀
◀
◀
文章整理自互联网，只做测试使用。发布者：Lomu，转转请注明出处：https://www.it1024doc.com/12915.html
