MySQL 索引失效的那些坑：从踩坑到填坑

作为一名程序员，我一直把 索引 当作数据库的“导航系统”。当表里只有几百行数据的时候，MySQL 像个勤快的小弟，翻翻全表也就几毫秒的事。但当数据量上百万甚至上亿时，没有索引就像是在图书馆里找一本书而不看目录，光靠翻页，能急死个人。

索引失效，就是这个“导航系统”突然罢工了。你辛辛苦苦给字段建好了索引，却发现执行计划里压根没用它，反而跑去做全表扫描。结果呢？查询响应时间直线上升，CPU 飙高，磁盘 IO 爆炸，整个系统的性能像被人拽住裤腿一样跑不动。

那么，MySQL 索引到底在哪些场景下会失效呢？ 我结合自己踩过的坑，总结了一些典型的案例。下面就一一展开。

1 对索引列使用函数或计算

索引就像高速公路的入口指示牌，如果你在入口处画个涂鸦（函数或运算），它就认不出来了。

-- 假设 birth_date 字段有索引
SELECT * FROM user WHERE YEAR(birth_date) = 2020; -- 索引失效

这里的 YEAR(birth_date) 让 MySQL 不能直接用索引去匹配范围，而是要对每一行计算年份。正确写法应该是：

SELECT * FROM user WHERE birth_date >= '2020-01-01' AND birth_date < '2021-01-01';

经验总结：能在条件里直接使用字段本身，就别做额外运算。

2 模糊查询前置百分号

很多人喜欢写这样的 SQL：

SELECT * FROM product WHERE name LIKE '%phone';

这一类查询等于告诉数据库：“结尾带 phone 的都给我找出来。” MySQL 无法从索引的左边开始匹配，只能全表扫描。

如果条件写成：

SELECT * FROM product WHERE name LIKE 'iPhone%';

那就能乖乖用上索引了。

我常把这种情况比作 电话号码簿：你要找所有姓“张”的人，很快能定位；但要找名字里带“强”的人，抱歉，只能一页页翻。

3 隐式类型转换

这个坑特别隐蔽，尤其是在 字符串和数字比较 时。

-- 假设 phone 字段是 varchar 类型，建立了索引
SELECT * FROM user WHERE phone = 13800001234;

这里右边是数字，MySQL 会偷偷把 phone 转成 int，再去比对。结果？索引失效。

正确写法应该是：

SELECT * FROM user WHERE phone = '13800001234';

小贴士：写 SQL 时一定要注意字段的真实类型，不要让 MySQL 悄悄帮你“做转换”。

4 复合索引不遵守最左前缀原则

复合索引就像门禁卡，你得从第一个门开始刷，才能一路畅通。

比如有个复合索引 (a, b, c)：

-- 可以走索引
SELECT * FROM test WHERE a = 1;
SELECT * FROM test WHERE a = 1 AND b = 2;

-- 不走索引
SELECT * FROM test WHERE b = 2;

因为 b = 2 单独查时跳过了 a，MySQL 不知道该从哪条路进场，只能选择全表扫描。

5 使用 OR 连接的条件

OR 语句常常让索引失效，尤其是当其中一个条件没法走索引时。

SELECT * FROM order_info WHERE user_id = 100 OR status = 'done';

哪怕 user_id 和 status 各自都有索引，因为 OR 的存在，MySQL 可能直接全表扫一遍。

解决办法：用 UNION ALL 拆开：

SELECT * FROM order_info WHERE user_id = 100
UNION ALL
SELECT * FROM order_info WHERE status = 'done';

这样能充分利用各自的索引。

6 不等于和范围查询

不等于（!=、<>）或者范围条件（>、<、BETWEEN）常常让索引变得“不完全可用”。

比如：

SELECT * FROM user WHERE age != 18;

这里没法精准定位，索引效果大打折扣。范围查询也类似，虽然可能部分利用索引，但后续条件往往被放弃。

7 IS NULL 和 IS NOT NULL

这两个操作符有点特殊：

• IS NULL 在某些版本下还能用上索引
• IS NOT NULL 基本就和索引说拜拜了

比如：

SELECT * FROM user WHERE email IS NOT NULL;

通常走不了索引，因为要扫描所有行确认条件。

8 排序和不同字符集

有时查询本身没问题，但一旦加上排序，索引就失效了。尤其是当 ORDER BY 的字段和索引字段顺序不一致 时。

SELECT * FROM user WHERE age = 20 ORDER BY name;

即使 age 和 name 都建了索引，但这种组合 MySQL 很可能选择全表扫描 + filesort。

另外，不同字符集的字段对比，也容易触发隐式转换，导致索引失效。

9 小表走全表扫描

有时候索引没失效，而是 优化器判断全表扫描更划算。

比如只有几十行的小表，建了索引也未必用。因为 MySQL 觉得直接扫一遍比走索引还快。

这种情况不是 bug，而是优化器的“聪明决定”。

10 强制索引与执行计划

有些时候 MySQL 的优化器并不聪明，选错了索引。比如一个字段有多个索引，优化器选择了代价更大的那个。

这时我们可以手动“指路”：

SELECT * FROM user FORCE INDEX(idx_name) WHERE name = 'Tom';

不过，强制索引是最后的手段，平时最好还是通过合理的建模和 SQL 改写来解决问题。

总结

索引是 MySQL 提升性能的利器，但稍不注意，它就可能失效。总结一下常见的场景：

1. 对索引列做函数或计算
2. 模糊查询前置百分号
3. 隐式类型转换
4. 复合索引不遵守最左前缀
5. OR 条件
6. 不等于和范围查询
7. IS NOT NULL
8. 排序字段与索引不匹配
9. 小表全表扫描
10. 优化器选错索引

在实际开发中，我习惯用 EXPLAIN 来检查执行计划，确认查询是否真正走了索引。

就像调试代码一样，不要只看表面，得掀开盖子看看 MySQL 到底在做什么。

最后一句：别盲目信任索引，它有时候比你想象中还“脆弱”。