MySQL中IN与NOT IN查询哪个更快？大数据量场景对比

给定两条SQL查询语句：

SELECT * from table_name where status IN (0, 1, 2, 3, 4)
SELECT * from table_name where status NOT IN (5, 6)

已知status字段取值范围为0到6，且已建立索引。当前数据量较小时两条查询速度无差异，请问数据量变大时哪条查询更快？

分析与结论 #

从逻辑上看，两条查询完全等价（因为status仅取值0-6，排除5、6就等于保留0-4），但数据库优化器的执行计划可能因语句写法不同产生差异，最终性能取决于数据分布、索引类型以及数据库优化策略：

• 当符合条件的数据（0-4）占总数据量的比例较高（通常超过30%-40%，不同数据库阈值有差异）：
  数据库优化器会判定全表扫描的成本更低——因为遍历索引后还要回表获取所有字段，开销远大于直接扫描全表。此时两条语句会被优化为相同的全表扫描逻辑，性能几乎无差异。

• 如果使用的是覆盖索引（即SELECT *中的所有字段都包含在status索引中，无需回表）：

  • IN (0,1,2,3,4)会直接定位到索引中这5个值对应的条目，扫描范围精准且更小；
  • NOT IN (5,6)需要扫描整个索引，再排除这两个值的条目，扫描范围覆盖全部索引数据。在数据量很大时，前者的性能会明显优于后者。

不同数据库的优化器处理逻辑有细微差异，但核心都是优先选择扫描范围更小、开销更低的执行路径。

内容的提问来源于stack exchange，提问作者Van Tho