前言

明明加了索引，但查询还是慢？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 协助处理

前言

做数据库开发的同学都知道，索引是提升查询性能最有效的手段。很多时候一张几百万行的大表，加个合适的索引查询速度就能从几秒变成几毫秒。

但是很多同学不知道的是，就算你加了索引，如果SQL语句写得不对，索引也可能会失效，结果还是全表扫描，查询慢得要命。这篇文章就把MySQL索引失效的常见场景都整理出来，帮你避免踩坑。

常见索引失效场景

1. 对索引列使用函数或运算

这是最常见的索引失效场景。如果你在索引列上用了函数或者做了运算，MySQL就没法用到这个索引了。

失效的写法：

-- 对索引列使用函数
SELECT * FROM users WHERE DATE(create_time) = '2026-10-05';

-- 对索引列做运算
SELECT * FROM orders WHERE amount + 100 > 500;

正确的写法：

-- 改成范围查询
SELECT * FROM users 
WHERE create_time >= '2026-10-05 00:00:00' 
AND create_time < '2026-10-06 00:00:00';

-- 把运算移到右边
SELECT * FROM orders WHERE amount > 400;

2. 不满足最左前缀原则

如果你建的是联合索引（比如 index(a, b, c)），查询的时候必须从最左边的列开始用，否则索引就会失效。

联合索引：index(name, age, city)

失效的写法：

-- 没有用到最左列name
SELECT * FROM users WHERE age = 25;

-- 跳过了中间的列
SELECT * FROM users WHERE name = '张三' AND city = '北京';

正确的写法：

-- 从最左列开始
SELECT * FROM users WHERE name = '张三';

-- 按顺序用
SELECT * FROM users WHERE name = '张三' AND age = 25;

3. 使用like以%开头

如果你的查询条件是 like '%xxx' 或者 like '%xxx%'，那索引就会失效，因为MySQL不知道从哪里开始找。

失效的写法：

-- %在前面
SELECT * FROM users WHERE name LIKE '%张%';

-- %在前后都有
SELECT * FROM users WHERE name LIKE '%张';

正确的写法：

-- %在后面，这样可以用到索引
SELECT * FROM users WHERE name LIKE '张%';

如果一定要用前后模糊匹配的话，可以考虑全文索引或者ES搜索引擎。

4. 索引列使用 != 或 <>

如果查询条件用了 != 或者 <>，MySQL优化器会觉得大部分数据都要查，还不如直接全表扫描，所以索引就失效了。

失效的写法：

SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE status <> 1;

正确的写法：

-- 改成in或者or
SELECT * FROM users WHERE status IN (0, 2, 3);

5. 使用or连接条件

如果or两边的列不是都有索引，那整个查询就会全表扫描。

失效的写法：

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

正确的写法：

-- 用union all代替or
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25;

或者给age也加上索引，这样两边都能用索引。

6. 隐式类型转换

这是最坑的一个场景。如果你的索引列是字符串类型，但是查询的时候传了数字，MySQL会自动做类型转换，结果索引就失效了。

失效的写法：

-- 索引列phone是varchar类型，但是传了数字
SELECT * FROM users WHERE phone = 13800138000;

正确的写法：

-- 加引号，传字符串
SELECT * FROM users WHERE phone = '13800138000';

7. 使用not in、not exists

not in 和 not exists 也很容易导致索引失效。因为MySQL优化器对这类反查询的优化做得不太好。

失效的写法：

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);

正确的写法：

-- 改成left join
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

8. 优化器认为全表扫描更快

还有一种情况是，虽然你写的SQL完全符合索引使用规则，但是MySQL优化器自己判断说"这张表数据太少了，全表扫描比走索引还快"，那它就会选择全表扫描。

这种情况是正常的，不用纠结。一般小表全表扫描确实更快。

如何检查索引是否生效

那怎么知道自己写的SQL有没有用到索引呢？很简单，用EXPLAIN关键字看执行计划就行。

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

重点看这几个字段：

• type：如果是 ALL 就是全表扫描，说明索引失效了；如果是 ref、eq_ref、const 就说明用到索引了
• key：这里显示实际用到的索引名，如果是 NULL 说明没用到索引
• rows：扫描的行数，越大说明效率越低

常见坑

坑1：加了索引但是没生效，以为索引没用

很多同学加了索引之后发现查询还是慢，就觉得索引没用。其实大概率是你的SQL写法有问题，导致索引失效了。一定要先用EXPLAIN看一下执行计划，确认索引有没有被用到。

坑2：联合索引建了不用最左列

很多同学建了联合索引，但是查询的时候没用到最左边的列，结果索引白建了。一定要记住最左前缀原则。

坑3：隐式类型转换导致索引失效

这个最坑，因为写SQL的时候完全看不出来。比如手机号、身份证号这些字符串类型的字段，查询的时候一定要加引号，不然索引就失效了。

总结

MySQL索引失效的常见场景总结一下：

1. 对索引列使用函数或运算
2. 不满足最左前缀原则
3. like以%开头
4. 使用 != 或 <>
5. 使用or连接条件
6. 隐式类型转换
7. 使用not in、not exists
8. 表太小，优化器选择全表扫描

写SQL的时候一定要注意避开这些坑，写完之后用EXPLAIN检查一下索引有没有生效。这样你的数据库查询性能才能真正提上去。

遇到问题加QQ23979811 协助处理