明明建了索引，SQL 为什么还是这么慢？聊聊索引失效的那些坑
在日常开发中，我们都知道“给字段加索引”是提升查询性能的银弹。但很多时候，明明我们已经在 EXPLAIN 里看到了索引，查询速度却依旧慢如蜗牛；或者明明建了索引，数据库偏偏选择全表扫描。
最近在优化公司几个核心接口的慢查询时，我重新梳理了一遍 SQL 索引失效的场景。今天就把这些压箱底的“坑”总结出来，希望能帮大家在写 SQL 时少走弯路。
1. 别在索引列上“搞动作”（计算与函数）
这是最容易犯的错误。如果你在 WHERE 子句中对索引字段进行了函数操作或数学运算，MySQL 优化器就会放弃走索引。
- 错误写法：
SELECT * FROM orders WHERE YEAR(create_time) = 2023; - 正确写法：
SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time <= '2023-12-31';
原因：索引 B+ 树存储的是原始值，经过函数计算后的值在索引树中是无序的，数据库只能全表扫描。
2. 类型转换的“隐形杀手”
如果你的字段是字符串类型（VARCHAR），但在查询时传了数字，MySQL 会偷偷进行隐式类型转换。
- 案例：字段
user_code是 VARCHAR。 - 失效写法：
SELECT * FROM users WHERE user_code = 123456;（不加引号） - 生效写法：
SELECT * FROM users WHERE user_code = '123456';
博主提醒：别小看这两个引号，一旦发生转换，索引直接作废。
3. 模糊查询的“左侧规则”
LIKE 查询我们常用，但一定要记住：百分号不要放开头。
- 失效：
LIKE '%keyword'（无法利用索引） - 生效：
LIKE 'keyword%'（可以利用索引前缀）
如果业务真的需要全模糊查询，建议考虑搜索引擎（如 Elasticsearch），或者利用覆盖索引来优化。
4. 复合索引的“最左前缀”原则
如果你建了一个复合索引 idx_a_b_c (a, b, c)，那么查询条件的顺序至关重要。
- 生效：
WHERE a = 1 AND b = 2 - 失效：
WHERE b = 2 AND c = 3（跳过了首列 a，索引直接断层）
想象一下，复合索引就像一本字典，你必须先按姓（a）找，再按名（b）找。直接查名，索引就没法用了。
5. OR 连接带来的麻烦
在 WHERE 中使用 OR 时，如果 OR 连接的条件中有一个字段没有索引，那么即便其他字段有索引，整个查询也不会走索引。
- 场景：
id有索引，temp_age没有索引。 - 失效：
SELECT * FROM table WHERE id = 1 OR temp_age = 18;
优化建议：尽量保证 OR 连接的所有字段都有索引，或者使用 UNION ALL 代替 OR。
6. 范围查询后的索引失效
在复合索引中，如果中间某个字段使用了范围查询（如 >, <, BETWEEN），那么该字段之后的索引字段将失效。
- 场景：索引为
(age, score, name) - 写法：
WHERE age > 20 AND score = 100（此时score无法利用索引的有序性）
7. 负向查询（NOT IN, !=）
虽然现代 MySQL 优化器越来越聪明，但在使用 NOT IN、!= 或 <> 时，往往会导致全表扫描。
博主建议：尽量将“否定”逻辑转化为“肯定”逻辑。例如 status != 0 是否可以改为 status IN (1, 2)？
8. 数据库优化器的“迷之选择”
有时候你明明按规矩写了 SQL，索引也没失效，但 EXPLAIN 显示还是全表扫描。这可能是因为 MySQL 觉得全表扫描比走索引更快。
这种情况通常发生在：
- 表中数据量太小。
- 索引列的选择性太低（例如性别字段，只有男/女，索引收益极低）。
- 回表成本过高。
写在最后
索引不是万能药，理解索引的底层逻辑（B+ Tree）比死记硬背这些规则更重要。在写完复杂的 SQL 后，习惯性地用 EXPLAIN 跑一下，观察 type 和 key 字段，能帮你规避 90% 的性能瓶颈。
大家在生产环境还遇到过哪些诡异的索引失效场景？欢迎在评论区留言交流。
