前言“这条 SQL 明明在索引列上查怎么还是全表扫描”如果你把WHERE status 1改成WHERE status ! 1很可能就会遇到这个现象同一个列、同一个索引等值查询走得好好的一换成!或NOT IN、索引就失效了。《阿里巴巴 Java 开发手册》里有一条相关的**【推荐】**规约【推荐】SQL 性能优化的目标至少要达到 range 级别要求是 ref 级别如果可以是 consts 最好。说明1consts单表中最多只有一个匹配行主键或者唯一索引在优化阶段即可读取到数据。2ref指的是使用普通的索引normal index。3range对索引进行范围检索。反例explain结果typeindex索引物理文件全扫描速度非常慢。!和NOT IN之所以危险正是因为它们很容易让查询掉到range级别以下退化成全表扫描。这篇文章从 B 树的结构讲清楚为什么不等于这类否定条件天生就和索引不对付。环境说明本文基于 MySQL 8.0存储引擎 InnoDB。继续复用前几篇的orders表100 万行。一、先看现象一个!让索引失效orders表上有联合索引idx_user_status(user_id, status)。先看等值查询EXPLAINSELECT*FROMordersWHEREuser_id88888;--------------------------------------------------------- | id | table | type | key | key_len | rows | Extra | --------------------------------------------------------- | 1 | orders | ref | idx_user_status | 8 | 10 | NULL | ---------------------------------------------------------type ref走索引只扫 10 行。很理想。现在把换成!EXPLAINSELECT*FROMordersWHEREuser_id!88888;-------------------------------------------------------------------------------------- | id | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------- | 1 | orders | ALL | idx_user_status| NULL | NULL | NULL | 1000000 | 99.99 | Using where | --------------------------------------------------------------------------------------type ALL、key NULL——索引没用上直接全表扫 100 万行。possible_keys里明明有idx_user_status说明这个索引可用但优化器最终选择了不用它。NOT IN也是一样EXPLAINSELECT*FROMordersWHEREuser_idNOTIN(88888,99999);-- 同样 type ALL全表扫描为什么会这样答案在索引的底层结构里。二、底层索引为什么怕否定条件2.1 B 树的本质是有序InnoDB 的索引是 B 树它最核心的特性是叶子节点上的数据是按索引列的值从小到大排好序的。正因为有序索引才能高效地做两件事等值查找 88888像查字典一样直接二分定位到那个值O(log n)。范围查找 88888、BETWEEN、 88888定位到范围的起点然后顺着有序的叶子链表往后连续读读到终点为止。关键词是连续。索引能加速靠的就是把要找的数据圈定在一段连续的区间里一次定位、顺序扫描。2.2!圈出来的不是一段区间而是两段 挖空现在看user_id ! 88888要的是什么除了 88888 以外的所有值。在有序的 B 树上这意味着要的是88888左边的一整段 88888加上右边的一整段 88888中间挖掉一个点。这就麻烦了它不是一段连续区间而是两段中间还断开更要命的是这两段加起来几乎是整张表——排除掉一个值剩下的还是绝大多数数据优化器一算走索引的话要扫描几乎全部索引项还得每条回表取完整数据因为是SELECT *这么大的量回表的代价比直接全表顺序扫还高。于是它干脆放弃索引选择全表扫描。这就是!/NOT IN/让索引失效的真相不是不能用而是它们圈定的数据范围太大、太碎优化器算下来用索引反而更慢主动放弃了。2.3 对比和为什么就没事 88888圈定的是一个点命中极少走索引稳赚。 88888圈定的是一段连续区间如果这段不算太大走索引扫这一段仍然比全表扫划算。! 88888圈定的是几乎全表走索引毫无优势反而多了回表开销。看出规律了吗索引怕的不是否定本身而是要的数据范围太大。!恰好几乎总是圈中绝大部分数据所以几乎总是失效。三、这是优化器的选择不是语法禁止有一点要澄清!让索引失效是优化器基于成本的主动选择不是 MySQL 语法上禁止!用索引。证据是如果否定条件排除掉的是大部分数据即最终只剩一小部分优化器又会愿意走索引了。举个例子假设某个status值占了全表 99% 的数据那status ! 那个值只剩 1%这时候走索引扫这 1% 就划算了优化器可能就会用索引。也就是说最终结果集占全表的比例才是优化器决策的关键结果集占比小 → 走索引划算 → 用索引结果集占比大!通常如此→ 走索引还不如全表扫 → 放弃索引所以严格讲不是!一定不走索引而是!通常命中太多行导致优化器算下来不划算。理解这一层比死记!让索引失效更有用。四、那该怎么办4.1 能改成范围/等值就改如果!在业务上可以等价改写成一段明确的范围就改。比如状态不是已完成3如果状态只有 0/1/2/3可以写成IN-- 不推荐WHEREstatus!3-- 如果能明确列举改成 IN正向枚举WHEREstatusIN(0,1,2)IN是正向的、离散的等值集合每个值都能走索引定位比!友好得多。4.2 接受它但别让它扫大表有些!无法避免。那就要保证它不是在大表上裸跑——通过其他更有选择性的条件先把范围缩小。比如-- user_id 先用索引把范围缩到几十行再在这几十行里过滤 status ! 3WHEREuser_id88888ANDstatus!3这条 SQL 里user_id 88888先走索引定位到约 10 行status ! 3只是在这极小的结果集里做过滤完全没问题。让高选择性的等值条件走索引把!降级为过滤而非检索。4.3 用 EXPLAIN 确认别猜最实在的办法写完 SQL 用EXPLAIN看一眼type。对照手册那条规约type ALL→ 全表扫描最差要优化type index→ 全索引扫描也慢type range→ 及格线范围扫描type ref→ 良好普通索引等值type const→ 最优主键/唯一索引只要没掉到range以下就基本达标。五、常见误区与面试高频问答Q所有!都一定不走索引吗不是。这是优化器基于结果集占比的成本选择。当!排除后剩下的数据很少时优化器仍可能走索引。只是大多数场景下!命中绝大部分行所以通常失效。别绝对化。QNOT IN和!是一回事吗原理一样都是否定条件圈定的都是排除某些值后的剩余大部分数据所以都容易失效。另外NOT IN遇到子查询、遇到 NULL 时还有额外的坑NULL 会导致整个结果异常能用NOT EXISTS或正向IN时优先考虑。QIS NOT NULL也会失效吗同理取决于非 NULL 的行占多大比例。如果绝大多数行都非 NULLIS NOT NULL命中几乎全表也会倾向全表扫描。Q为什么possible_keys有索引key却是 NULL这正是索引可用但优化器不用的典型信号。possible_keys表示这个索引理论上能用于这个查询key NULL表示优化器算完成本后决定不用它——通常就是因为走索引的代价大量回表比全表扫还高。总结!/NOT IN/让索引失效根子在 B 树的有序结构索引靠有序加速擅长圈定一段连续区间等值是一个点范围是一段。!圈定的是排除一个值后的几乎全表——不是连续区间且数据量巨大。优化器算下来走索引大量回表还不如直接全表顺序扫于是主动放弃索引type掉到ALL。本质是结果集占比决定的成本选择不是语法禁止。占比小时!也能走索引。应对能正向枚举就用IN避免不了就用高选择性的等值条件先缩小范围让!只做过滤最后用EXPLAIN确认type不低于range。一句话记忆索引怕的不是否定是范围太大。!几乎总是命中绝大部分数据所以几乎总是失效——把它降级成过滤条件别让它当检索条件。
