有没有更好的方法来编写基于子查询删除记录的查询

Question

我有这个查询：

DELETE from MailingListTable where Md5Hash in (
   SELECT
      dbo.ListItems.Md5Hash
   FROM dbo.Lists
   INNER JOIN dbo.ListItems ON dbo.Lists.Id = dbo.ListItems.ListId
   where dbo.Lists.IsGlobal = 1
 )

邮件列表表是从多个列表动态构建的。然后，我运行上述查询以删除全局删除列表中的任何列表项。

这在小集合上并不可怕，但较大的集合可能需要几乎 5 到 8 分钟（基于我所做的一些测试）。我很好奇是否有更好的方法来写这个。我不相信我可以将连接与删除语句一起使用。所以这就是我选择子查询的原因。

我也尝试使用EXISTS，但这要慢得多。使用common-table expressions会更好吗，因为我使用的是SQL Server 2008？

Answer 1

我认为这需要很长时间，因为 （a） 您正在删除数百万行，并且 （b） 您将日志视为旋转门。这不会神奇地从 5-8 分钟缩短到 5 秒，因为您使用 EXISTS 而不是 IN，或者将子查询更改为 CTE 或使用 JOIN。继续尝试一下，我敢打赌它不会更好：

DELETE ml 
  FROM dbo.MailingListTable AS ml
  INNER JOIN dbo.ListItems AS li
  ON ml.Md4Hash = li.Md5Hash
  INNER JOIN dbo.Lists AS l
  ON l.Id = li.ListId 
  WHERE l.IsGlobal = 1;

问题几乎可以肯定是执行DELETE所涉及的I/O，而不是用于识别要删除的行的方法。我打赌使用完全相同的数据并且不更改索引结构等的SELECT，无论隔离级别如何都不需要 5-8 分钟。

那么，如何解决呢？

首先，确保您的日志已调整为处理该大小的事务。

• 预先调整日志的大小，使其在此类操作期间不必增长，也许可以将您见过的最大大小翻倍。确切的理想大小不是Stack Overflow上的人能够告诉您的。

• 确保自动增长未设置为愚蠢的默认值，如 10% 或 1MB。自动增长应该是一个回退，但是，当你需要它时，它应该只发生一次，而不是多次来涵盖任何特定的活动。因此，请确保它是固定大小（使大小 + 持续时间可预测）并且大小合理（以便它只发生一次）。什么是合理的？不知道 - 太多的"这取决于"。

• 永久禁用任何收缩日志的作业。周旋根据具体情况进行失控日志，而不是"预防"通过反复收缩日志文件来日志增长。

接下来，请考虑更改查询以将这些删除批处理到块中。您可以根据有多少行导致交易持续时间来使用 TOP (?) 参数（即使我们确实有更多信息，也没有神奇的公式）。

CREATE TABLE #x
(
  Md5Hash SOME_DATA_TYPE_I_DO_NOT_KNOW PRIMARY KEY
);
INSERT #x SELECT DISTINCT li.Md5Hash
  FROM dbo.ListItems AS li
  INNER JOIN dbo.Lists AS l
  ON l.Id = li.ListId 
  WHERE l.IsGlobal = 1;
DECLARE @p TABLE(p INT SOME_DATA_TYPE_I_DO_NOT_KNOW PRIMARY KEY);
SELECT @rc = 1;
WHILE @rc > 0
BEGIN
  DELETE @p;
  DELETE TOP (?)  
    OUTPUT deleted.Md5Hash INTO @p
    FROM #x;
  SET @rc = @@ROWCOUNT;
  BEGIN TRANSACTION;    
    DELETE ml FROM dbo.MailingListTable AS ml
    WHERE EXISTS (SELECT 1 FROM @p WHERE Md5Hash = ml.Md5Hash);
  COMMIT TRANSACTION;
  -- to minimize log impact you may want to CHECKPOINT
  -- or backup the log here, every loop or every N loops
END

这可能会延长操作所需的总时间（特别是如果您在每个循环上备份或检查点，或使用 WAITFOR 或两者添加人为延迟），但应该允许其他事务在块之间潜入，等待较短的事务而不是整个过程。此外，由于您对日志的单个影响较小，因此它实际上最终可能会更快地完成。但我必须假设问题不在于它需要 5-8 分钟，而可能是它需要 5-8 分钟和块。这应该大大缓解这种情况（如果是这样，你为什么关心需要多长时间？

我在这里写了很多关于这种技术的文章。