SQL Server中EXCEPT与NOT IN的性能差异及最佳实践

核心结论预览

EXCEPT 与 NOT IN 均用于获取存在于左表但不存在于右表的记录，但其底层执行策略不同。实测表明：
- 小数据量：两者性能接近，NOT IN 有时略快。
- 大数据量：EXCEPT 通常表现更出色，能更好地利用索引进行排序合并操作。
- 关键变量：性能高度依赖于统计数据、索引结构以及是否存在 NULL 值。

测试环境与准备

为了直观展示性能差异，我们构建了一个包含基础数据的测试场景。请注意，实际生产环境的测试结果会因数据分布、索引状态和硬件配置而异。

第1步：创建测试表

首先，创建两张单列整数表用于模拟左右数据集：

CREATE TABLE tb1(ID int);
CREATE TABLE tb2(ID int); 

第2步：插入测试数据

使用事务循环插入数据。测试中设定 tb1 有 1000 条记录，tb2 有 500 条记录（数据范围重叠）：

BEGIN TRAN;
DECLARE @i INT = 500;
WHILE @i > 0
BEGIN
    INSERT INTO dbo.tb1 VALUES (@i);
    SET @i = @i - 1;
END;
COMMIT;

查询语句对比与分析

在执行性能测试前，务必清除系统缓存以消除干扰，并开启 I/O 和耗时统计：

DBCC FREESYSTEMCACHE ('ALL','default');
SET STATISTICS IO ON;
SET STATISTICS TIME ON; 

-- 方式 A: 使用 EXCEPT
SELECT * FROM tb1 EXCEPT SELECT * FROM tb2;

-- 方式 B: 使用 NOT IN
SELECT * FROM tb1 WHERE id NOT IN (SELECT id FROM tb2); 

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

1. EXCEPT 的执行计划解析

EXCEPT 操作符旨在去除重复值并进行集合比较，其标准执行计划如下：

|--Merge Join(Right Anti Semi Join...)
|   |--Sort(DISTINCT ORDER BY...)
|   |   |--Table Scan(OBJECT:([master1].[dbo].[tb2]))
|   |--Sort(DISTINCT ORDER BY...)
|       |--Table Scan(OBJECT:([master1].[dbo].[tb1]))

• 操作类型：Merge Join (Right Anti Semi Join)。
• 特点：要求输入数据预先排序（Sort）。如果目标列上有索引，SQL Server 可以直接利用现有的物理顺序进行“归并”，避免昂贵的显式排序步骤，从而大幅提升大规模数据下的性能。

2. NOT IN 的执行计划解析

NOT IN 的查询通常会转换为 Anti Semi Join，其执行计划更为复杂：

|--Hash Match(Right Anti Semi Join...)
|   |--Table Scan...
|   |--Nested Loops(Left Anti Semi Join)...

• 操作类型：Hash Match 或 Nested Loops。
• 潜在陷阱：虽然 Hash Match 在处理无序大数据时表现不错，但 NOT IN 对 NULL 值极其敏感。如果子查询结果集中存在任何 NULL，整个表达式将返回空集（因为 `value = NULL` 结果为 Unknown）。此外，当主表数据量大且无合适索引时，Nested Loops 可能导致严重的性能退化。

性能测试数据对比

基于上述测试条件，典型的资源消耗对比如下：

在数据量较大时，EXCEPT 往往能通过有序读取减少随机 I/O。而 NOT IN 由于需要处理潜在的 NULL 检查并在某些情况下触发 Row Count Spool 等复杂操作，开销相对较重。

生产环境优化建议

迷信单一维度的测试结果可能会导致在生产环境中出现性能瓶颈。以下是通用的优化准则：

1. 关注索引与统计信息：
   • 确保参与比较的列（如 ID）上有适当的索引。
   • 定期更新统计信息，让优化器准确估算行数，从而选择最优执行计划（Merge Join 还是 Hash Join）。
2. 谨慎使用 NOT IN：
   • 始终确认子查询列不允许为 NULL。如果存在不确定性，建议使用 NOT EXISTS 或 LEFT JOIN ... IS NULL 替代。
3. 优先评估 EXCEPT/UNION ALL：
   • 对于去重集合运算，EXCEPT 语义明确且常能转化为高效的归并算法。
4. 验证真实场景：
   • 不要在脱机测试机上盲目移植结论。请在具有典型数据分布的生产镜像环境中进行压测。

以上就是 SQL Server 中 EXCEPT 和 NOT IN 性能区别及优化的详细内容，更多关于数据库查询优化的资料请关注本站其它相关文章！