为什么MySQL不推荐在索引字段上使用IS NOT NULL？一文分析扫描成本差异

在数据库开发的面试题和公司的SQL规范里，经常能看到一条：禁止在索引字段上使用IS NOT NULL判断，或者说NOT NULL判断会导致索引失效。这句话其实只说对了一半。IS NOT NULL并不一定让索引完全失效，很多情况下优化器还是会选择索引，但它带来的扫描成本往往远超开发者的预期，这才是真正需要警惕的地方。要理解这个问题，得先从NULL值在B+树索引里是怎么存放的说起。

NULL值在B+树索引中的存放位置

MySQL的InnoDB引擎使用B+树组织索引，很多人以为NULL值不进索引，这是最常见的误解。实际上InnoDB会把NULL值统一存放在索引的最左侧。也就是说，如果你有一个字段status，表里有一万行status为NULL的记录，它们会集中占据索引最左边的一段连续区间。

这个设计带来了一个直接后果：IS NULL条件对索引非常友好。因为所有NULL值挤在一起，优化器只需要定位到索引最左侧，顺序读取这一段就能拿到全部结果，本质上是一次范围扫描，扫描量等于NULL值的实际数量。

而IS NOT NULL正好相反，它要读取的是NULL区间之后的所有记录。假设一张表有100万行数据，其中99万行都不为NULL，那么一次IS NOT NULL的索引扫描就要遍历这99万条索引项。这时候优化器就要算账了：走索引再回表，和直接全表扫描，哪个更便宜？答案往往是不走索引，或者走了索引也没占到便宜。

优化器如何估算IS NOT NULL的扫描成本

MySQL优化器选择执行计划的核心逻辑是成本估算，主要看预计扫描的行数rows和回表次数。我们可以通过EXPLAIN直观地观察这一点。先建一张测试表：

CREATE TABLE t_order (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL,
  status TINYINT NULL,
  remark VARCHAR(200) NULL,
  KEY idx_status (status)
) ENGINE=InnoDB;

-- 插入100万行，其中99%的status为1，1%为NULL
EXPLAIN SELECT * FROM t_order WHERE status IS NOT NULL;
EXPLAIN SELECT * FROM t_order WHERE status IS NULL;

执行后你会发现，IS NULL这条SQL的执行计划中type为ref，rows大约是1万，非常精准；而IS NOT NULL这条SQL，rows直接显示98万以上，优化器很可能判定走索引的代价不低于全表扫描，于是type变成了ALL。这就是扫描成本差异的直观体现。

关键点在于：IS NOT NULL并不是让索引失效，而是它的语义决定了它要扫描的数据范围太大。当非NULL值占比超过某个临界点（通常认为在20%到30%左右），走二级索引加回表的成本就会超过聚集索引的顺序全扫，优化器自然放弃索引。反过来，如果一张表里90%的值都是NULL，IS NOT NULL筛选出的只有10%，此时它反而能高效走索引。所以这个问题的本质是选择性，而不是NULL判断本身。

IS NOT NULL的几种替代写法与优化方案

既然问题出在过滤条件的选择性上，优化思路也应该围绕缩小扫描范围展开。第一种方案是改写条件。如果业务上NULL和空字符串等价，可以把字段设为NOT NULL并给默认值，查询改写成status != 1或者status <> 1，配合具体的值条件，让优化器更容易估算范围。需要注意!=同样可能触发大范围扫描，必须结合实际数据分布评估。

第二种方案是利用覆盖索引避免回表。IS NOT NULL扫描量大的一个重要放大因素是回表：二级索引上每命中一行都要回到聚集索引取完整记录。如果查询的字段全部包含在索引里，就能直接从索引返回结果，成本大幅下降。例如：

-- 原查询需要回表
SELECT order_no, status FROM t_order WHERE status IS NOT NULL;

-- 建立覆盖索引后无需回表
ALTER TABLE t_order ADD INDEX idx_status_order (status, order_no);
SELECT order_no, status FROM t_order WHERE status IS NOT NULL;

第三种方案是从索引设计和数据建模层面入手。能明确业务含义的字段，尽量在建表时就声明NOT NULL并设置默认值，从根源上减少NULL的存在。NULL值除了影响执行计划，还会带来统计信息偏差、聚合函数忽略NULL等一系列隐患。此外，如果确实需要频繁按是否为NULL查询，可以考虑把判断条件冗余成一个小字段，配合组合索引使用，让优化器的估算更可控。

总结

IS NOT NULL不推荐用在索引字段上，根本原因不是索引失效，而是它通常对应着索引上的超大范围扫描，选择性差，优化器经过成本比较后大概率选择全表扫描。当NULL值占比很高时，IS NOT NULL反而能高效走索引，这说明脱离数据分布谈索引失效都是不严谨的。实际开发中，优先保证字段有合理的NOT NULL约束，查询时尽量缩小过滤范围，必要时借助覆盖索引和EXPLAIN验证执行计划，才能写出稳定高效的SQL。

MySQL索引IS NOT NULL扫描成本修改时间：2026-09-11 05:24:25