「我给字段加了索引，查询还是慢成狗」——80%程序员踩过的索引失效骚操作

先说个真事。

三年前，我负责一个接口优化，查询一个用户订单列表，从原来的3.2秒降到了0.08秒。整个团队以为我改了什么高深算法，其实我就做了一件事：把 WHERE created_at >= '2024-01-01' 里的 >= 悄悄改成了 BETWEEN。

你以为是 BETWEEN 比 >= 快？屁。真正的原因是：>= 触发了索引列上的函数调用，导致索引直接失效。

这不是个例。我面试过上百个程序员，能把这个点讲清楚的不超过10%。今天就把索引失效的各种「骚操作」扒个底朝天，看看你中了几条。

一、索引失效的头号杀手：函数和运算

看这段代码：

-- 你的查询
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- 数据库执行计划：全表扫描
-- 原因：索引列被函数包裹，索引失效

-- 优化后
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- 数据库执行计划：索引范围扫描
-- 速度差：10倍起步

原则：索引列绝不能在左边参与任何运算。

啥叫「左边」？就是等号的左边，或者 WHERE 条件的左边。你写了 WHERE id + 1 = 10，那 id 在左边，被运算了，索引就废了。

常见场景：

• WHERE LEFT(name, 4) = '张先' → 前缀匹配，用 LIKE '张先%'
• WHERE id / 2 = 5 → 改成 WHERE id = 10
• WHERE CAST(price AS SIGNED) = 100 → 不要 cast，存的时候就用对类型
• WHERE DATE(created_at) = '2024-06-01' → 改成范围查询

记住一句话：让索引列「裸奔」，别给它穿衣服（函数/运算）。

二、类型转换：最容易被忽略的暗器

这个坑，我愿称之为「表面看起来完全正常但就是慢」的代表。

-- 表结构：user_id 是 varchar(32) 类型
-- 你的查询：
SELECT * FROM orders WHERE user_id = 12345678;

你看这个 SQL，完全正常吧？但是 user_id 是字符串类型，你传了一个整数过去，MySQL 会偷偷做一次隐式类型转换，把字符串转成数字。这个转换发生在每一行上，于是：索引列被函数「暗算」了，全表扫描伺候。

-- 正确写法：
SELECT * FROM orders WHERE user_id = '12345678';  -- 加上引号！

一个小引号的区别，可能是10秒和0.01秒的差距。这玩意儿在线上出问题，你 log 打得再详细也很难发现——因为 SQL 看起来完全没问题。

另一个经典场景：

-- phone 是 varchar，存的是手机号
-- 你写：
WHERE phone = 13800138000  -- 数字，触发类型转换，索引失效
WHERE phone = '13800138000'  -- 字符串，正确使用索引

三、OR 陷阱：说好的一起走，最后各奔东西

SELECT * FROM users WHERE name = '张三分' OR age = 25;

如果 name 有索引，age 没索引，那么这条 SQL 大概率是全表扫描，因为 OR 意味着要遍历每一行来检查 age = 25。

解决方案：

-- 方案1：拆成 UNION
SELECT * FROM users WHERE name = '张三分'
UNION ALL
SELECT * FROM users WHERE age = 25 AND name != '张三分';

-- 方案2：给 age 也加上索引
CREATE INDEX idx_age ON users(age);

关于 OR 还有一个经典误解：以为 IN 和 OR 一样慢。

-- IN 里面是常量，MySQL 会自动优化，走索引
WHERE user_id IN (1, 3, 5, 7, 9)  -- 走索引，高效

-- OR 里面是范围条件或不同列，慢
WHERE name = '张三分' OR age > 25  -- 可能全表扫描

IN 不等于 OR，别把它们混为一谈。

四、LIKE 的隐藏门道：%放前放后，天壤之别

-- 模糊查询，索引完全失效（前置通配符）
WHERE name LIKE '%三分'  -- 暴力扫描

-- 前缀匹配，索引生效
WHERE name LIKE '张三分%'  -- 索引范围扫描

这个大家都知道。但还有一个更隐蔽的坑——当你的字段是字符串类型且排序规则（collation）不同时，LIKE 也会失效。

-- utf8mb4_general_ci（大写不敏感）+ LIKE '%xxx%'
-- 可能无法使用索引的排序扫描

如果你的业务真的需要同时支持前置通配符搜索（比如搜索「三分张」能找到「张三分」），别硬撑着在数据库上搞，加个 Elasticsearch 或者提前构建反向索引，对双方都好。

五、最左前缀原则：这个没吃透，别说你懂联合索引

-- 你建了联合索引：INDEX idx_a_b_c (a, b, c)

-- 能命中索引的查询：
WHERE a = 1              -- ✅ 命中 a
WHERE a = 1 AND b = 2    -- ✅ 命中 a, b
WHERE a = 1 AND b = 2 AND c = 3  -- ✅ 命中 a, b, c

-- 失效的查询：
WHERE b = 2              -- ❌ 跳过了 a，索引失效
WHERE c = 3              -- ❌ 跳过了 a, b，索引失效
WHERE b = 2 AND c = 3    -- ❌ 跳过了 a，索引失效

联合索引的结构，就跟电梯一样——你必须从一楼（最左列）开始坐，不能跳着楼层按。

我见过太多人建了 (a, b, c) 联合索引，然后只查 WHERE b = ?，还抱怨「我明明建了索引怎么还是慢」。

另一个高频错误：

-- 你建了 INDEX(a, b)
-- 查询：WHERE a = 1 AND b > 10 ORDER BY b
-- 问题：b 上有范围查询，c 的排序无法使用索引
-- 结果：Using index condition; Using filesort（慢）

范围查询（>、<、BETWEEN）之后的列，在 ORDER BY 和 GROUP BY 中无法利用索引排序。这是很多人忽视的细节。

六、数据分布不均匀：你的索引可能只是「看起来在」

-- 假设 status 字段有索引
-- 但 99% 的数据 status = 'active'，只有 1% 是 'deleted'
SELECT * FROM orders WHERE status = 'deleted';  -- 全表扫描！

MySQL 优化器很聪明，它会发现 status = 'deleted' 的数据只有1%，走索引反而要回表多次，不如直接全表扫描。你的索引在物理上存在，但优化器选择了不用它。

这种情况的解决方案：

• 用 FORCE INDEX 强制走索引（不推荐，容易在其他数据量下翻车）
• 重新设计索引，考虑复合索引的列顺序
• 如果是极端分布，考虑分区表或分库分表

怎么知道索引有没有被使用？用 EXPLAIN 看结果：

EXPLAIN SELECT * FROM orders WHERE status = 'deleted';
-- 看 key 字段：显示你用的索引
-- 看 rows 字段：扫描了多少行
-- 看 Extra 字段：出现 Using filesort 或 Using temporary 就是有问题

七、一个小技巧：如何快速诊断索引问题

我日常用这个顺序排查：

1. EXPLAIN 看执行计划，重点关注 type、key、rows、Extra
2. 检查查询条件里索引列有没有被函数/运算包裹
3. 检查类型是否匹配（字符串列传数字常量？）
4. 检查是否跳过了最左前缀
5. 检查 OR 是否涉及无索引列
6. 检查数据分布，是否走了错误的执行计划

把这套流程走完，95% 的索引失效问题都能定位到。别再迷信「我建了索引就会快」——索引是工具，不是魔法，用错了一样慢，而且慢得你根本想不到。

写在最后

写这篇文章的时候，我又回去翻了那个3.2秒的接口的 EXPLAIN 结果。怎么说呢——那是我职业生涯里最想删掉的代码之一。

优化这事，有时候真的不是技术有多难，而是你有没有踩过那个坑。索引失效这个话题，满大街都是教程，但大多数人看完还是会在同一个地方摔倒。

希望你是那10%。