有没有过这种经历？有那么几条简单的查询总是拖慢了整个系统，看监控发现 MySQL 在疯狂做全表扫描，动不动就直接把CPU 和 IO 给拉满了？明明查询的字段都建了索引，可是查询速度依然慢的像蜗牛一样。是不是满脸问号？明明数据也就几百上千万，数据也不没有那么复杂，完全算不上海量数据，索引也见了，查询怎么还会这么慢呢？大概率你是掉进了索引失效的坑里了!
今天就来盘盘MySQL到底有哪些索引失效的坑。
一、索引失效的9个坑
这个部分很重要，废话不多说，下面将是大家最容易踩的坑的地方，建议收藏。
1. 跳着用联合索引
这是非常容易遇到的坑，在上一篇提到过了，这里再强调一遍：联合索引必须从最左列开始连续使用，跳过中间的列，后面的索引就失效了。比如索引(name, age, score)，查WHERE name='张三' AND score=90，那score的索引就用不上，只能用到name的部分。
2. 在索引列上做函数或计算
这是最常见的坑之一。比如你写WHERE YEAR(create_time)=2024 ，MySQL 会对每一行的create_time都调用 YEAR 函数，这就破坏了 B + 树的有序性，索引直接失效。正确的写法是把条件改成范围查询：WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'`，这样就能正常走索引了。
3. 隐式类型转换
在MySQL中这是最容易忽略的坑，通常能正常查询，不会有任何异常，但是很有可能就会造成索引失效。比如你的order_no是 VARCHAR 类型，结果你写WHERE order_no=1001，MySQL 会偷偷把order_no转换成数字，也就是WHERE CAST(order_no AS SIGNED)=1001，这跟上面的函数操作是一个道理，直接导致索引失效。所以查询字符串类型的字段，一定要加引号！
4. OR 两侧的条件只有部分有索引
这个也是同学们极易忽视的坑之一。OR 两侧的条件要都有索引，否则就会导致索引失效。如果你的查询是WHERE name='张三' OR score=90，只有name有索引，score没有，那整个查询会直接走全表扫描。因为 OR 的逻辑是只要满足一个条件就行，MySQL 没法用索引快速定位所有符合条件的行，干脆就全表扫了。这种情况可以拆成两个查询用 UNION 连接：SELECT * FROM user WHERE name='张三' UNION SELECT * FROM user WHERE score=90。
5. 范围查询导致索引中断
这个坑可能很多同学都不知道。联合索引里如果有范围查询比如>、<、BETWEEN，那范围查询后面的列就没法用索引了。比如索引(name, age, score)，查WHERE name='张三' AND age>18 AND score=90，score的索引就用不上。解决办法是把精确匹配的字段放到范围查询前面，或者调整索引顺序为(name, score, age)。
6. LIKE 以 % 开头
这个非常典型的坑可能依然有部分同学不知道。WHERE name LIKE '%张三'这种写法，MySQL 根本没法用索引的前缀匹配，只能全表扫描。如果业务上必须模糊查询，要么改成LIKE '张三%'，要么就用全文索引，或者考虑用 ES 这种专门的搜索引擎。
7. IS NOT NULL 要谨慎
这个坑对于对于经验欠缺的同学来说比较容易遇到。如果索引列允许 NULL 值，IS NULL可能还能用索引，但IS NOT NULL基本都会导致全表扫描。所以建表的时候尽量给字段设默认值，避免 NULL 值出现，比如性别字段默认设为 ' 男'，而不是允许 NULL。
8. 索引选择性太低
这个也是个经验坑，如果不了解其原理就会觉得像个“薛定谔的坑”，有时可能出现，有时不能出现，但实际是根据索引字段数据的区分度来决定的。当索引选择性太低时，优化器会选择不走索引。比如性别字段，只有男和女两个值，就算建了索引，优化器一算，用索引还要回表，还不如直接全表扫来得快，所以会主动放弃使用索引。这种字段就别建单独索引了，要是必须用，就把它放到联合索引的后面。
9. 优化器觉得 “全表扫更快”
这也是一个经验坑，如果经验不足且如果不了解优化器原理就可能遇到这种。当你的查询结果超过表数据的 30% 左右时，优化器会觉得用索引还要回表，不如直接全表扫来得高效，这时候就算你建了索引也没用。这种情况要么优化查询条件，减少返回的数据量，要么用FORCE INDEX强制指定索引，不过一般不建议这么做，优化器的判断大多时候是对的。
二、避坑索引失效的核心逻辑
其实说来说去，避坑索引失效的核心就是理解索引的底层原理和实现，避坑索引失效的核心逻辑就是索引优化的核心逻辑。索引优化的核心逻辑就是让 MySQL 的优化器能高效地通过索引定位到数据，尽量减少磁盘 IO 和回表次数，避免出现有索引无法直接使用的情况。避开这些坑要记住这几点：
- 先搞懂 B + 树的原理，所有规则都是从这个来的；
- 建索引要精准，优先给高价值字段建，联合索引要注意顺序；
- 写查询的时候，别破坏索引的有序性，别让优化器 “犯难”。
下次再遇到慢查询，先别急着加索引，用
EXPLAIN看看执行计划，是不是索引失效了。
如果内容对您有所帮助，麻烦给个点赞、收藏、分享 ，更多技术干货持续更新，关注我获取
