MySQL索引失效的10种场景，别再踩坑了

前言

明明加了索引，但查询还是慢？explain一看type是ALL，索引根本没生效！本文总结10种索引失效的场景，帮你避坑。

一、对索引字段用函数

失效写法

SELECT * FROM users WHERE LEFT(name, 3) = '张三';
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';

正确写法

SELECT * FROM users WHERE name LIKE '张三%';
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

原因： 对索引字段用函数，MySQL不知道索引值会变成什么，索引用不上。

二、索引字段用运算

失效写法

SELECT * FROM users WHERE age + 1 = 20;
SELECT * FROM orders WHERE price * 2 > 100;

正确写法

SELECT * FROM users WHERE age = 19;
SELECT * FROM orders WHERE price > 50;

原因： 运算后索引值变了，MySQL没法用索引。

三、最左前缀原则不满足

联合索引

INDEX idx_name_age_city(name, age, city)

失效写法

SELECT * FROM users WHERE age = 20; -- 跳过了name
SELECT * FROM users WHERE city = '北京'; -- 跳过了name和age

正确写法

SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 20;

原则： 联合索引要从最左边的字段开始用，不能跳过。

四、like以%开头

失效写法

SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';

正确写法

SELECT * FROM users WHERE name LIKE '张三%';

原因： 以%开头，MySQL不知道从哪开始找，索引用不上。

五、类型不匹配

失效写法

-- phone是varchar类型
SELECT * FROM users WHERE phone = 13800138000; -- 传了数字

正确写法

SELECT * FROM users WHERE phone = '13800138000'; -- 传字符串

原因： 类型不匹配，MySQL会做隐式转换，索引用不上。

六、OR两边有没索引的字段

失效写法

-- name有索引，age没索引
SELECT * FROM users WHERE name = '张三' OR age = 20;

原因： OR只要有一边没索引，MySQL就干脆全表扫描。

解决

-- age也加索引
ALTER TABLE users ADD INDEX idx_age(age);

七、使用NOT、!=、<>

失效写法

SELECT * FROM users WHERE name != '张三';
SELECT * FROM users WHERE age NOT IN (18, 20);

原因： 不等于操作，MySQL觉得要查大部分数据，不如全表扫描。

解决

-- 改成范围查询
SELECT * FROM users WHERE name > '张三';

八、is null / is not null

这个要看情况，有的版本会失效，有的不会。

建议： 尽量让字段不要为null，设默认值。

九、查询条件用了函数或表达式

失效写法

SELECT * FROM users WHERE ABS(age) = 20;
SELECT * FROM users WHERE age / 2 = 10;

正确写法

SELECT * FROM users WHERE age = 20;

十、MySQL自己判断不走索引

有时候SQL写法没问题，但MySQL优化器觉得走索引还不如全表扫描。

比如： 表数据很少，或者要查的数据占表的大部分。

解决： 用FORCE INDEX强制走索引

SELECT * FROM users FORCE INDEX(idx_name) WHERE name = '张三';

十一、怎么检查索引有没有生效

用explain

EXPLAIN SELECT * FROM users WHERE name = '张三';

看key字段：

• key有值：用了索引
• key是NULL：没用到索引

看type字段：

• type=ALL：全表扫描
• type=ref/range：用了索引

十二、常见坑

坑1：加了索引但SQL写法不对

解决： 按上面10种场景检查。

坑2：索引加太多

索引不是越多越好，写的时候要维护索引，会变慢。

建议： 单表索引不超过5个。

坑3：区分度低的字段不加索引

比如gender（男/女），加索引没用。

原则： 区分度高的字段加索引。

坑4：隐式类型转换

例子： varchar字段传了数字。

解决： 传参时类型要匹配。

十三、索引使用原则

1. 对WHERE条件的字段加索引
2. 对ORDER BY的字段加索引
3. 联合索引遵守最左前缀
4. 不要对索引字段用函数
5. 不要对索引字段做运算
6. like不要以%开头
7. 类型要匹配
8. 区分度高的字段加索引

总结

10种索引失效场景：

1. 对索引字段用函数
2. 索引字段用运算
3. 最左前缀原则不满足
4. like以%开头
5. 类型不匹配
6. OR两边有没索引的字段
7. 使用NOT、!=、<>
8. is null / is not null
9. 查询条件用了表达式
10. MySQL自己判断不走索引

记住：

• 用explain检查索引有没有生效
• key=NULL就是没用到索引
• type=ALL就是全表扫描
• 避开上面10种场景

遇到问题加QQ23979811 协助处理