大表批量删除：比NOT EXISTS更高效的SQL Delete方案？

针对你2亿条记录的Customers表、要删除200万无发票客户的场景，直接替换NOT EXISTS未必能带来明显性能提升，但换一种思路优化关联逻辑，能显著加快删除速度。以下是具体分析和方案：

先抓基础优化：索引是前提 #

不管用什么写法，必须确保CustomerInvoices.CustomerId上有非聚集索引。如果没有索引，每次NOT EXISTS都会全表扫描CustomerInvoices，这才是性能瓶颈的核心，和NOT EXISTS本身无关。

替换NOT EXISTS的可选方案及效果 #

1. LEFT JOIN + IS NULL替代 #

写法如下：

WHILE (1=1)
BEGIN
  DELETE TOP(10000) c
  FROM Customers c
  LEFT JOIN CustomerInvoices ci ON ci.CustomerId = c.CustomerId
  WHERE ci.CustomerId IS NULL
  IF (@@ROWCOUNT = 0)
  BREAK
END

在SQL Server中，优化器通常会把NOT EXISTS和LEFT JOIN + IS NULL生成完全一致的执行计划，性能几乎没差别。除非统计信息严重过时，否则这种替换不会有明显变化。

2. 预存需保留的客户ID到临时表（推荐） #

既然要删除的是“无发票的客户”，反过来就是保留“有发票的客户”。可以先把有发票的CustomerId提取到临时表并加索引，后续删除只和这个小临时表关联：

-- 第一步：生成需保留的客户ID临时表
SELECT DISTINCT CustomerId INTO #KeepCustomers FROM CustomerInvoices
CREATE CLUSTERED INDEX IX_KeepCustomers_CustomerId ON #KeepCustomers(CustomerId)

-- 第二步：批量删除不在保留列表的客户
WHILE (1=1)
BEGIN
  DELETE TOP(10000) c
  FROM Customers c
  WHERE NOT EXISTS (SELECT 1 FROM #KeepCustomers k WHERE k.CustomerId = c.CustomerId)
  IF (@@ROWCOUNT = 0)
  BREAK
END

DROP TABLE #KeepCustomers

这种方法的优势是：只扫描一次CustomerInvoices生成临时表，后续每次删除的查询只需要和小表关联，避免了原语句每次循环都扫描大表CustomerInvoices的开销，对于2亿条的Customers表，性能提升会非常明显。

3. EXCEPT筛选待删除记录 #

用EXCEPT获取无发票的客户ID，再批量删除：

WHILE (1=1)
BEGIN
  DELETE TOP(10000) c
  FROM Customers c
  WHERE c.CustomerId IN (
    SELECT CustomerId FROM Customers
    EXCEPT
    SELECT CustomerId FROM CustomerInvoices
  )
  IF (@@ROWCOUNT = 0)
  BREAK
END

这种写法的效率取决于Customers.CustomerId是否为主键：如果是主键，EXCEPT的去重和对比效率不错；如果不是，可能会产生大量重复值，反而不如临时表方案。

额外性能优化建议 #

• 调整批次大小：10000条是合理值，但如果事务日志空间充足，可适当调大到30000-50000条，减少循环次数。
• 临时关闭自动统计更新：批量操作期间频繁的统计更新会拖慢速度，可执行ALTER DATABASE [你的数据库名] SET AUTO_UPDATE_STATISTICS OFF，操作完成后再重新开启。
• 低峰期执行：避免和业务高峰期的查询冲突，减少锁等待和资源竞争。

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