如何在SQL中删除大量数据而无需记录日志？

Question

我有一个大型数据表格。这个表格中有一千万条记录。

查询时最佳的方式是什么？

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())

- user3107343

---

除非您愿意编写某种ETL程序，将所有行的readTime >= dateadd（MONTH，-7，GETDATE（））读取到另一个表中，然后使用ETL发出Truncate table命令并将数据放回，否则无法防止它写入日志。 - TMNT2014

记录日志是具有弹性事务的全盘功能。对于某些操作而言，没有日志记录就毫无意义，否则日志就是无用的。 - Erik Philips

导出您想要保留的数据，截断表格，然后重新导入。 - Bohemian

另一个选择是使用未记录的表变量。因此，将您的readTime >= dateadd（MONTH，-7，GETDATE（））数据存储在表变量中，然后截断原始表并从表变量中复制回数据。但是，我建议在表被意外截断时备份数据。:) 并且始终在较小的环境中运行脚本进行测试。 - TMNT2014

可能是如何在不使用Truncate Table的情况下高效删除500,000+行表中的行的重复问题。 - NoWar

Answer 1

1. 如果你想删除该表的所有行，最简单的方法是使用Truncate table命令，类似于：

 TRUNCATE TABLE LargeTable
 GO

截断表将仅清空表，您无法使用WHERE子句限制要删除的行数，并且不会触发任何触发器。

2. 另一方面，如果您要删除的数据超过80-90％，例如您总共有1100万行，并且您想要删除1000万行，则另一种方法是将这100万行（要保留的记录）插入到另一个暂存表中。然后截断该大表并重新插入这100万行。

3. 或者，如果具有此大表作为其基础表的权限/视图或其他对象不受删除此表的影响，则可以将这些相对较小数量的行转移到另一个表中，删除此表并创建具有相同模式的另一个表，并将这些行导入回此已经变小的表中。

4. 我能想到的最后一个选项是将数据库的恢复模式更改为SIMPLE，然后使用类似以下内容的while循环以较小的批次删除行：

 DECLARE @Deleted_Rows INT;
 SET @Deleted_Rows = 1;


 WHILE (@Deleted_Rows > 0)
   BEGIN
    -- Delete some small number of rows at a time
      DELETE TOP (10000)  LargeTable 
      WHERE readTime < dateadd(MONTH,-7,GETDATE())

   SET @Deleted_Rows = @@ROWCOUNT;
 END

不要忘记将恢复模式改回完整模式，我认为你必须备份才能使其完全生效（更改或恢复模式）。

- M.Ali

---

请记住，如果您截断一张表，就不能有任何与该表相关联的外键。 - HLGEM

但是如何确保你删除了80-90%的数据？假设我只有应该删除的值范围。还有几个表格。因此，我必须检查每一个并计算百分比，如果约为30％，那么我猜这种方法不是非常有效... 我正在尝试找到未知情况的最佳解决方案。 - Archont

“最理想的解决方案是针对未知情况的”，这不是我们的梦想吗？不幸的是，你无法用同一颗药治愈所有疾病；我已经为不同的情况提出了一些可能的解决方案。不幸的是，这里没有万能药。 - M.Ali

选择选项4时需注意：根据表的使用情况，一次删除少于5000行可能是更好的选择，以避免锁升级。 - Daniel

如果要删除的记录数比表中保留的记录数大得多，我发现将要保留的记录简单地选择到临时表中，然后删除原始表并重命名临时表会更快。前提是您没有在其他地方使用自增 Id 外键。 - Vladimir Bozic

Answer 2

@m-ali的回答是正确的，但也要记住，如果不在每个块之后提交事务并执行检查点，日志可能会增长很多。这是我会这样做的，并参考这篇文章http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes，其中包括性能测试和图表：

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;


WHILE (@Deleted_Rows > 0)
  BEGIN

   BEGIN TRANSACTION

   -- Delete some small number of rows at a time
     DELETE TOP (10000)  LargeTable 
     WHERE readTime < dateadd(MONTH,-7,GETDATE())

     SET @Deleted_Rows = @@ROWCOUNT;

   COMMIT TRANSACTION
   CHECKPOINT -- for simple recovery model
END

- Francisco Goldenstein

---

如果可用磁盘空间有限，则应该接受此答案。如果没有使用 COMMIT TRANSACTION 和 CHECKPOINT，则日志仍然会增长。感谢明确解释这一点。 - gkoul

+1。只需注意将@Deleted_Rows与10000进行比较，否则由于无限删除小数据集而导致无限循环。因此，WHILE (@Deleted_Rows = 10000)-一旦没有完整的“页面”可删除，它就会停止。在您的实现中，WHILE (@Deleted_Rows > 0)，即使仅删除了一行，while循环也会再次执行，并且下一次执行可能还会找到一行或两行要删除-导致无限循环。 - dutoitns

@NSduToit WHERE子句考虑的是至少7个月前的记录，因此在执行删除操作时不会有新记录满足该条件。 - Francisco Goldenstein

@FranciscoGoldenstein 好的，查询中使用的日期将随着每次迭代而不同，因为您会在 WHILE 循环内部重复计算日期：dateadd(MONTH,-7,GETDATE())。 - dutoitns

@FranciscoGoldenstein 另外，也许除了这种情况之外还有其他用例 - 也许底层表格中添加了新数据，这将导致在 WHILE 循环的不同迭代之间可以删除的新记录。 - dutoitns

Answer 3

您还可以使用GO +要执行相同查询的次数。

DELETE TOP (10000)  [TARGETDATABASE].[SCHEMA].[TARGETTABLE] 
WHERE readTime < dateadd(MONTH,-1,GETDATE());
-- how many times you want the query to repeat
GO 100

- Bunkerbuster

---

我喜欢这个，它对我很有用。我不小心向一个表中插入了同一行2600万次，并需要删除所有出现的情况，在一个单独的删除语句中在服务器上耗尽了内存，所以这很棒。还有一个问题，如果它运行到没有要删除的行时，会停止吗？ - ScottC

@ScottC，这不是一个循环，它只是重复查询（类似批处理），如果你的行用完了，它就不能删除任何东西。但它不会停止。如果它运行到没有可删除的行，你会得到类似于“（0 行受影响）”的结果。 - Bunkerbuster

啊，是的，我在发布问题后大约5分钟就发现了这一点，因为我的删除完成了，谢谢，这非常有帮助！ - ScottC

这个语法 GO xx 应该在哪个版本的 MS SQL Server 中使用？我收到了一个“找不到存储过程''” 的错误。但是如果没有 GO 命令，它就可以正常工作。 - Abel

看起来我可以执行它，并且确实运行了多次，但在MS SQL Mgt Studio中它显示了带有提到的错误的红色卷曲线（但是F5运行然后正常工作）。 - Abel

完全不知道为什么会发生这种情况，但它确实可以运行。 - Bunkerbuster

Answer 4

@Francisco Goldenstein，只需要进行一个小修正。在设置变量之后必须使用COMMIT，否则WHILE将只执行一次：

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;

WHILE (@Deleted_Rows > 0)
BEGIN
    BEGIN TRANSACTION

    -- Delete some small number of rows at a time
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())

    SET @Deleted_Rows = @@ROWCOUNT;

    COMMIT TRANSACTION
    CHECKPOINT -- for simple recovery model

END

- Cassio Veras

Answer 5

这个M.Ali的变体对我来说运行良好。它删除一些内容，清除日志并重复执行。我正在观察日志增长、减少和重新开始。

DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
  BEGIN
   -- Delete some small number of rows at a time
    delete top (100000) from InstallLog where DateTime between '2014-12-01' and '2015-02-01'
    SET @Deleted_Rows = @@ROWCOUNT;
    dbcc shrinkfile (MobiControlDB_log,0,truncateonly);
END

- Ken Koehler

---

这非常有用！我修改了它，使得可以参数化每次删除的“行数”和“WHERE”子句。现在运行得非常好！ - Shiva

Answer 6

如果您愿意（并且有能力）实现分区，那么这是一种有效的技术，可在很少的运行时间开销下删除大量数据。虽然对于一次性操作来说不够划算。

- Michael Green

Answer 7

我能在几分钟内从我的 2100 万行的表中删除了 1900 万行。我的做法如下：

如果这个表有一个自动递增主键，那么你可以利用这个主键。

1. 获取大表 primary key 的最小值，其中 readTime < dateadd(MONTH,-7,GETDATE())。如果没有加上 readTime 索引，请添加索引，此索引会在第三步与表一起被删除。我们将它存储在变量 'min_primary' 中。 2. 将所有 primary key > min_primary 的行插入到暂存表中（如果行数不多，则使用内存表）。 3. 删除大表。 4. 重新创建表格。将所有行从暂存表复制到主表。 5. 删除暂存表。

- Arpan Jain

---

不需要删除大表，使用截断也可以，而且速度很快。 - MonkeyDreamzzz

Answer 8

更简洁的语法

select 1
WHILE (@@ROWCOUNT > 0)
BEGIN
  DELETE TOP (10000) LargeTable 
  WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

- paparazzo

Answer 9

您可以使用 while 循环批量删除，示例代码如下：

while(条件) { 删除语句; }

该方法适合小批量的数据删除。

DELETE TOP (10000)  LargeTable 
WHERE readTime < dateadd(MONTH,-7,GETDATE())
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (10000)  LargeTable 
    WHERE readTime < dateadd(MONTH,-7,GETDATE())
END

- Fábio Nascimento

Answer 10

如果您使用的是SQL Server 2016或更高版本，并且您的表是基于您要删除的列创建的分区（例如时间戳列），则可以使用此新命令按分区删除数据。TRUNCATE TABLE WITH (PARTITIONS( { | }[, ...n]))将仅删除所选分区中的数据，应该是从表的部分删除数据的最有效方法，因为它不会创建事务日志，并且与常规截断一样快速完成，但不会将所有数据从表中删除。缺点是如果您的表没有设置分区，则需要使用常规方法删除数据，然后重新创建具有分区的表，以便将来可以执行此操作，这就是我所做的。我将分区创建和删除添加到插入过程中。我的表有5亿行，所以这是减少删除时间的唯一选择。

有关更多详细信息，请参阅以下链接： https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-2017

SQL server 2016带分区截断表

以下是我在重新创建包含所需数据的分区表之前首先执行的删除数据的步骤。此查询将在指定时间窗口内运行数天，直到数据被删除。

:connect <<ServerName>>
use <<DatabaseName>>

SET NOCOUNT ON;
DECLARE @Deleted_Rows INT;
DECLARE @loopnum INT;
DECLARE @msg varchar(100);
DECLARE @FlagDate datetime;
SET @FlagDate =  getdate() - 31;
SET @Deleted_Rows = 1;
SET @loopnum = 1;

/*while (getdate() < convert(datetime,'2018-11-08 14:00:00.000',120))
BEGIN
    RAISERROR( 'WAIT for START' ,0,1) WITH NOWAIT   
    WAITFOR DELAY '00:10:00'
END*/
RAISERROR( 'STARTING PURGE' ,0,1) WITH NOWAIT   

WHILE (1=1)
BEGIN
    WHILE (@Deleted_Rows > 0 AND (datepart(hh, getdate() ) >= 12 AND datepart(hh, getdate() ) <= 20)) -- (getdate() < convert(datetime,'2018-11-08 19:00:00.000',120) )
      BEGIN
       -- Delete some small number of rows at a time
         DELETE TOP (500000)  dbo.<<table_name>>
         WHERE timestamp_column < convert(datetime, @FlagDate,102)
         SET @Deleted_Rows = @@ROWCOUNT;
         WAITFOR DELAY '00:00:01'
         select @msg = 'ROWCOUNT' + convert(varchar,@Deleted_Rows);
         set @loopnum = @loopnum + 1
         if @loopnum > 1000
             begin 
                 begin try
                        DBCC SHRINKFILE (N'<<databasename>>_log' , 0, TRUNCATEONLY)
                        RAISERROR( @msg ,0,1) WITH NOWAIT
                 end try
                 begin catch
                     RAISERROR( 'DBCC SHRINK' ,0,1) WITH NOWAIT  
                 end catch
                 set @loopnum = 1
             end
        END
WAITFOR DELAY '00:10:00'
END 
select getdate()

- digitally_inspired

---

仍然无法与外键一起使用，这是一个令人烦恼的限制。 - user2864740