如何在不使用Truncate Table的情况下高效删除50万行以上的表格行？

Question

假设我们有一个包含30列和500,000行的表Sales。我想删除表中的400,000行(其中"toDelete='1'")。

但是我有一些限制：

• 该表经常被读/写，我不希望长时间运行的“DELETE”占用太长时间并锁定表
• 我需要跳过事务日志(像使用TRUNCATE那样)，但同时又要执行"DELETE ... WHERE..."(我需要设置条件)，但没有找到任何方法来实现这一点...

欢迎任何建议来改变一个

DELETE FROM Sales WHERE toDelete='1'

将其转换为更细分的形式，可能无需使用事务日志。

- Skippy Fastol

---

为什么需要跳过事务日志？ - Abe Miessler

完成后，如果您能发布最佳解决方案（或至少是对您最有效的解决方案），我们将不胜感激。 - Marcel N.

@thecoon：我一定会的。感谢所有人提供的各种互补答案。 - Skippy Fastol

请查看恢复模式。http://msdn.microsoft.com/en-us/library/ms189275.aspx 如果恢复模式为完整，即使分开删除，所有删除操作仍将记录在日志中（这可能是您想要的），直到备份。如果允许脏读取，则可以使用（无锁）进行读取。 - paparazzo

Answer 1

调用 DELETE FROM TableName 会在一个大事务中完成整个删除操作。 这是很昂贵的。

这里有另一种选项，可以分批次删除行：

deleteMore:
DELETE TOP(10000) Sales WHERE toDelete='1'
IF @@ROWCOUNT != 0
    goto deleteMore

- Kevin Aenmey

---

哦，我的天啊！我怎么从来没有意识到在DELETE语句中可以使用TOP()表达式？比我的功能相同的答案更简洁！向您致敬！+1 - MatBailie

坦白说，我甚至都不记得 SQL 2008 可以使用标签。我更愿意看到一个 WHILE 语句。 WHILE 1 = 1 BEGIN; DELETE ...; IF @@RowCount = 0 BREAK; END; 对于下一个编写 SQL 的人来说，这对我来说更清晰，因为它表明了循环而不是使用糟糕的 GOTO 进行推断。 - ErikE

太好了！哦，我可以轻松解析单个GOTO。那些我们在BASIC和FORTRAN中制造的混乱让人难以思考。 - Dennis Cronin

Answer 2

我将在这里留下我的答案，因为我能够测试不同的批量删除和更新方法（我必须更新然后删除超过125百万行，服务器有16GB的RAM，Xeon E5-2680 @2.7GHz，SQL Server 2012）。

TL;DR：始终通过主键更新/删除，永远不要使用任何其他条件。如果不能直接使用PK，请创建一个临时表并用PK值填充它，然后使用该表更新/删除您的表。使用索引进行此操作。

我从上面的解决方案（由@Kevin Aenmey提供）开始，但是这种方法被证明是不合适的，因为我的数据库是在线的，并且每秒处理几百个事务，其中涉及一些阻塞（所有条件字段都有索引，使用WITH（ROWLOCK）没有改变任何事情）。

因此，我添加了一个WAITFOR语句，它允许数据库处理其他事务。

deleteMore:
WAITFOR DELAY '00:00:01'
DELETE TOP(1000) FROM MyTable WHERE Column1 = @Criteria1 AND Column2 = @Criteria2 AND Column3 = @Criteria3
IF @@ROWCOUNT != 0
    goto deleteMore

这种方法能够处理每小时更新约160万行和删除约20万行。

使用临时表会使情况发生很大变化。

deleteMore:
SELECT TOP 10000 Id /* Id is the PK */
  INTO #Temp 
  FROM MyTable WHERE Column1 = @Criteria1 AND Column2 = @Criteria2 AND Column3 = @Criteria3 

DELETE MT
  FROM MyTable MT
  JOIN #Temp T ON T.Id = MT.Id 

/* you can use IN operator, it doesn't change anything
 DELETE FROM MyTable WHERE Id IN (SELECT Id FROM #Temp)

 */
IF @@ROWCOUNT > 0 BEGIN
    DROP TABLE #Temp
    WAITFOR DELAY '00:00:01'
    goto deleteMore
END ELSE BEGIN
    DROP TABLE #Temp
    PRINT 'This is the end, my friend'
END

这个解决方案每小时处理约2500万行进行更新（速度提高了15倍），并且每小时处理约220万行进行删除（速度提高了11倍）。

- Marko Juvančič

---

很好，正是我正在研究的东西。省去了我的研究时间；）非常感谢！ - Didzis Stivriņš

我很好奇：为什么填充自己的临时表比DELETE TOP版本更快？普通的DELETE优化不是也会使用索引按主键查找行吗，这与此类似吗？差异可能是因为您使用了DELETE TOP 1000而不是SELECT TOP 10000吗？（一千与一万？）您能否使用相同的TOP数字再次进行计时？ - Patrick Szalapski

Answer 3

你需要的是批处理。

While (select Count(*) from sales where toDelete =1) >0
BEGIN
Delete from sales where SalesID in
(select top 1000 salesId from sales where toDelete = 1)
END

当然你可以进行实验来确定批量处理时最佳的值，我根据不同的表使用了500至50000不等的数值。如果你使用级联删除，你可能需要使用较小的值，因为你还要删除子记录。

- HLGEM

---

重复调用 COUNT(*) 在使用 @@rowcount 可以替代时似乎是不必要的。但这是一种非常适用于其他关系型数据库管理系统的解决方案... - MatBailie

没错，我匆忙之中完成了这个，没有完全优化。而且我并不习惯使用删除关键字，有时候很难去改变多年的习惯。 - HLGEM

刚刚删除了最后一个deleteMore标签的冒号，因为它给我报错说标签已经被声明过了。这个小改动之后程序就正常运行了。 - Adrian Hedley

作为条件，WHILE EXISTS (SELECT * FROM sales WHERE toDelete = 1) 怎么样？ - Jamie Lester

Answer 4

过去我有一种方法是使用存储过程或脚本来删除n条记录。重复此过程直到完成。

DELETE TOP 1000 FROM Sales WHERE toDelete='1'

- Cylindric

---

每次你收缩数据库，一只小猫就会死掉！[http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx] - Joe Stefanelli

为了澄清（因为这有点像黑客行为），我当时是出于日志目录磁盘空间不足的原因而这样做的，而不是进行锁定。我想使用这种方法锁定的时间相同，只是时间更长了 :) - Cylindric

也许在理想的世界里是这样。但我们并不都拥有无限的数据存储空间，有时如果你已经删除了数据库的90％，清理数据文件也没问题。如果你谈论的是索引碎片，那么只需要重新建立索引即可。 - Cylindric

在一次不典型的完整操作之后，您知道您不会再次需要那个空间吗？也许可以清理数据文件，但这只是一个极端情况。每次删除后都清理？我完全不同意。 - Aaron Bertrand

@Cylindric，请修改你的回答中的措辞。"删除n条记录，然后收缩数据库。重复直到完成。" 这是非常糟糕的建议。 - Aaron Bertrand

Answer 5

你应该尝试给它一个ROWLOCK提示，这样它就不会锁定整个表。然而，如果你删除了很多行，锁升级会发生。

此外，请确保在toDelete列上有一个非聚集的过滤索引（仅针对1值）。如果可能，将其设置为位列，而不是varchar（或现在的内容）。

DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'

最终，您可以尝试迭代表格并分块删除。

更新

由于while循环和分块删除在这里非常流行，我也会提供我的版本（与我的先前答案结合使用）：

SET ROWCOUNT 100
DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'

WHILE @@rowcount > 0
BEGIN
  SET ROWCOUNT 100
  DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'  
END

- Marcel N.

---

"SET ROWCOUNT" 在 SQL 2012 中已经被弃用。 - ErikE

Answer 6

我对这个功能的看法是这样的。这样做没有重复的代码，你可以管理你的块大小。

DECLARE @DeleteChunk INT = 10000
DECLARE @rowcount INT = 1

WHILE @rowcount > 0
BEGIN

  DELETE TOP (@DeleteChunk) FROM Sales WITH(ROWLOCK)

  SELECT @rowcount = @@RowCount
END

- WaitForPete

---

应该是：SET @rowcount = @@RowCount 吗？ - Juan Ignacio Barisich

Answer 7

使用while和@@rowcount的简短版本，无需任何临时变量等。

WHILE 1 = 1 --endless loop
BEGIN
    DELETE TOP 1000 FROM Sales WHERE blahblah
    IF (@@ROWCOUNT = 0) BREAK;
END

你可以在循环内部选择添加延迟：

WAITFOR DELAY '00:00:00.500' --500ms delay

- Alex from Jitbit

Answer 8

我认为删除大量记录的最佳方式是通过主键进行删除。（什么是主键 请看这里）

因此，您需要生成包含要删除的所有行的tsql脚本，并在执行此脚本之后执行它。

例如，下面的代码将生成该文件：

GO
SET NOCOUNT ON

SELECT   'DELETE FROM  DATA_ACTION WHERE ID = ' + CAST(ID AS VARCHAR(50)) + ';' + CHAR(13) + CHAR(10) + 'GO'
FROM    DATA_ACTION
WHERE  YEAR(AtTime) = 2014

输出文件将会有如下记录：

DELETE FROM  DATA_ACTION WHERE ID = 123;
GO
DELETE FROM  DATA_ACTION WHERE ID = 124;
GO
DELETE FROM  DATA_ACTION WHERE ID = 125;
GO

现在您需要使用SQLCMD实用程序来执行此脚本。

sqlcmd -S [Instance Name] -E -d [Database] -i [Script]

您可以在这里找到该方法的解释 https://www.mssqltips.com/sqlservertip/3566/deleting-historical-data-from-a-large-highly-concurrent-sql-server-database-table/

- NoWar

Answer 9

当我大致知道迭代次数时，这是我的做法：

delete from Activities with(rowlock) where Id in (select top 999 Id from Activities 
(nolock) where description like 'financial data update date%' and len(description) = 87 
and User_Id = 2);
waitfor delay '00:00:02'
GO 20

编辑：对我而言，这比选择顶部更好、更快。

declare @counter int = 1
declare @msg varchar(max)
declare @batch int = 499

while ( @counter <= 37600)

begin
    set @msg = ('Iteration count = ' + convert(varchar,@counter))
    raiserror(@msg,0,1) with nowait
    delete Activities with (rowlock) where Id in (select Id from Activities (nolock) where description like 'financial data update date%' and len(description) = 87 and User_Id = 2 order by Id asc offset 1 ROWS fetch next @batch rows only)
    set @counter = @counter + 1
    waitfor delay '00:00:02'
end

- gabeyww

Answer 10

我已经使用以下代码删除了大约5000万条记录 -

BEGIN TRANSACTION     
     DeleteOperation:
     DELETE TOP (BatchSize)
     FROM  [database_name].[database_schema].[database_table] 

     IF @@ROWCOUNT > 0
     GOTO DeleteOperation
COMMIT TRANSACTION

请注意，保持BatchSize小于5000可以节省资源成本。

- Ankush

---

为什么回答与被接受的答案相同？ - Yeronimo