基于子查询删除记录的查询有更好的写法吗?

5

我有以下查询:

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
 )

MailingListTable是从多个列表动态构建的。然后我运行上面的查询以删除任何在全局删除列表中的列表项。对于小数据集来说还不错,但对于大数据集来说可能需要5到8分钟(基于我的一些测试)。我想知道是否有更好的方法来编写这个查询。我不认为可以在删除语句中使用连接,所以我选择了子查询。我还尝试使用“EXISTS”,但速度慢得多。由于我正在使用SQL Server 2008,是否最好使用“common-table expressions”?

你可以使用select distinct dbo.ListItems.Md5Hash,此外你可以确保MailingListTable的Md5Hash列上有一个键。 - dcaswell
你是否要删除大量记录?如果是这样,也许你应该将要保留的记录选择到一个新表中。否则为什么要进行删除呢?只需在表上创建一个返回所需记录的FilteredMailingListTableView并使用它即可。加速某个操作的最佳方法是不去执行它。 - dcaswell
从阅读问题来看:这是一个动态构建的临时表。 - dcaswell
@user814064,这不是临时表,我只是说它是动态生成的。该表实际上是创建出来的。除非您说的是其他内容。 - DDiVita
@user814064 你觉得这个表每小时都会被构建吗? - Aaron Bertrand
显示剩余4条评论
1个回答

8

我猜这需要很长时间,因为(a)你正在删除数百万行数据,(b)你把你的日志当做旋转门。仅仅使用EXISTS代替IN或将子查询改为CTE或使用JOIN并不能让执行时间从5-8分钟变为5秒钟。试试吧,我敢打赌也不会更好:

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分钟并且阻塞。这应该大大缓解这个问题(如果确实如此,为什么还要关心它需要多长时间?)。 我在这里写了更多关于这种技术的内容

1
我想补充一下这个非常好的答案,你也可以检查一下是否有一个删除触发器正在进行逐行处理而不是基于集合的处理。我已经修复了其中的一些问题。 - HLGEM
1
@HLGEM 是的,这是一个我没有考虑到的很好的观点。即使是基于集合的触发器也可能会产生连锁效应。 - Aaron Bertrand

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接