sql - 从 SQL Server 中删除大量行 - 以高效且非锁定的方式

Question

我正在编写一个程序来从 n 天前的几个表中删除所有行。

一个简单的查询很容易编写

DELETE FROM [myTable] 
WHERE [Created] < GETDATE()-30

一个问题是日期字段上没有索引 - 我可以添加一个，但我正在通过执行以下操作来解决它:

SELECT @var = MAX([ID]) FROM myTable WHERE Created < GETDATE()-30; 
DELETE FROM myTable WHERE ID < @var

这看起来是一种可以接受的方法吗？

问题是表很大，每次运行这个查询可能会删除数十万行。

在(稍慢的)测试服务器上运行它需要一个小时左右，并从其他试图读/写它的进程中杀死该表。

我不太介意运行一段时间(虽然越快越好) - 但我不能让它在运行时锁定表一个小时，因为有持续的读/写(主要是写) .

我的数据库知识非常基础，因为我是一名编码员而不是 dba。

有人可以给我一个体面的方法来执行这项任务 - 以最有效的方式。

Answer 1

您正在寻找的是基于分区的滑动窗口:How to Implement an Automatic Sliding Window in a Partitioned Table on SQL Server 2005 .按天对表进行分区，您可以在午夜的单个分区切换操作中有效地删除一整天。分区切换基本上是瞬时的。

如果您想要一个开销稍低的解决方案(分区会带来严重的后果并影响整个应用程序，特别是当索引必须对齐时，这是快速切换操作的要求)，那么您必须根据这个操作。以 99.99% 的置信度，我可以说您的 myTable 最左边的聚集键必须是 Created field 。这将允许高效的批量删除( delete top (2500) from myTable where Created < ... )。您希望批量处理的原因有很多(一次最多 2500 个左右)，最重要的是您必须避免锁升级，并且必须将任何单个事务的大小保持在合理的范围内。