增量删除 - 它有什么好处？

Question

如果我有一张包含大量数据的表格...如果我进行增量删除而不是“一次性删除”...有什么好处？

一次性删除

 DELETE table_1
     WHERE BID = @BID
    AND CN = @CN        
     AND PD = @PD;  

Incremental Delete

While (1=1)
Begin
 DELETE TOP (100000) FROM table_1
     WHERE BID = @BID
    AND CN = @CN        
     AND PD = @PD;  

If @@rowcount = 0   -- No row affected.
  BREAK
 ELSE
  Continue
End

我从删除 SQL Server 表中的数据需要很长时间中获得了帮助。

- Relativity

---

一次事务中有4亿条记录会让你的日志爆炸，如果你要这样做，请确保你有足够的空间。 - Dustin Laine

我不会一次删除400万条数据...我有一个包含400万条数据的表格...每次只会删除大约150万条数据...然后再插入一些新数据。 - Relativity

Answer 1

根据配置不同，我曾经看到过大规模删除操作导致事务日志爆炸，并因为磁盘空间不足而失败。

您也可以通过使用较小的批次来避免锁升级。

- Dustin Laine

---

只是补充一下，当持有5,000个锁时，才会尝试进行锁升级，因此即使在增量版本中，批处理大小也需要大幅减少才能获得这种好处。 - Martin Smith

减少批量大小 -> 您的意思是将下面查询中的值从“100000”更改为“100”吗？ DELETE TOP (100000) FROM table_1 WHERE BID = @BID AND CN = @CN AND PD = @PD; - Relativity

@Anish - 如果删除操作需要行锁，那么假设为5,000。但是如果您不必担心并发访问，这就不重要了。 - Martin Smith

@Anish，锁升级是指SQL服务器优化器通过标准将锁定表而不是行。有许多级别的升级，但这是一般的想法。 - Dustin Laine

Answer 2

或者，您可以导出要保留的数据，截断表格，然后重新加载数据。这可能会更快。即使您想保留50％的数据，它仍然可以更快-截断仅最小记录。运行自己的基准测试。

- A-K

---

我需要保留的数据比我想要删除的数据多。所以我认为这种方法不好...是吗？ - Relativity

它仍然可以更快 - 截断只有最小程度的日志记录。运行您自己的基准测试。 - A-K

请注意：如果表涉及复制或日志传送，并且外键引用要被截断的表，则无法截断该表。 - Gennady Vanin Геннадий Ванин

Answer 3

区别在于回滚信息的大小。

SQL Server是事务性的，在提交删除之前应该可以回滚事务。

以以下示例为例：

• 硬盘上的可用空间为10 GB
• 要删除的信息为20 GB

当您开始删除时，事务日志将增长，直到磁盘已满，然后崩溃。

即使有足够的磁盘空间，也会出现其他问题，如数据库锁定或性能下降。如果从实时系统中删除数据，则可能会出现严重问题。

- Shiraz Bhaiji

---

好的...那就意味着增量删除肯定会减少时间（来删除数据）。 - Relativity

@Anish，更多的是有更好的机会让它工作，我们最近不得不从一个实时数据库中删除了150,000,000行数据，我们每次删除500,000行。一次性全部删除会导致系统崩溃。 - Shiraz Bhaiji

Answer 4

我在数据库调优方面是个新手....:(

我也是...但我不需要理解技术细节（如锁升级等）就能感受/猜测分块吃/咬西瓜相对于整个放进嘴里的好处。

- Gennady Vanin Геннадий Ванин

---

我在寻找关于为什么/如何有益的信息时，从一些人那里得到了答案，他们说这是因为SQL Server保留的日志。而当我吃西瓜时，我没有记录它的习惯，因为我从来不会回滚它。 - Relativity