SQL DELETE -最大行数

Question

应该对SQL语句中要删除的行数设置什么限制？

我们需要删除1到几十万行，并需要应用某种最佳实践限制，以便不会在每次清空废纸篓时完全杀死SQL server或填满日志。

这个问题并不特定于任何类型的数据库。

Answer 1

这是一个非常广泛的问题，基本上可以归结为“视情况而定”。影响它的因素包括：

• 您的并发级别是多少？delete语句在受影响的行上放置排它锁。根据数据库引擎，删除的数据分布等，这可能会升级到页面或整个表。您的数据读取器能承受在delete期间被阻塞吗？
• delete语句有多复杂？您还在连接多少其他表，或者是否有复杂的WHERE子句？有时，要删除的行的标识可能比删除本身更“昂贵”，因此一次大的删除可能会“更便宜”。
• 您害怕死锁吗？当您减小删除的大小时，您的死锁“足迹”也会减小。理想情况下，单行删除总是会成功。
• 您关心吞吐量性能吗？与任何SQL语句一样，开销通常是恒定的(连接填充、查询解析、返回结果等)。从单连接的角度来看，1000行的删除将比1000 x 1行的删除更快。
• 不要忘记索引维护开销、碎片清理或任何触发器。它们也会影响您的系统。

不过，一般来说，我的基准测试是每条语句1000行。我使用过的大多数系统(子“企业”)在每次删除500到5000条记录时都会达到最佳状态。我喜欢这样做：

set rowcount 500

select 1    -- Just to force @@rowcount > 0
while @@ROWCOUNT > 0
delete from [table]
     [where ...]

Answer 2

虽然使用set rowcount选项限制受delete影响的行数，然后执行循环是非常好的(我以前使用过很多次)，但请注意，从SQL2012开始，这将不再是一个选项(请参阅BOL)。

因此，另一种选择可能是使用TOP子句限制要删除的行数。即

SELECT 1

WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (#)
    FROM mytable
    [WHERE ...]
END

Answer 3

除非您有很多触发器或完整性约束要验证，否则删除操作应该不会那么昂贵。

但是，如果您非常关心性能，我最初的预感是将适当的行标记为已删除，然后在以后的定期清理过程中将其物理删除。但我对此并不感兴趣，因为您必须更改该表上的任何查询，以排除逻辑上删除的行，而不是物理上删除的行。