如何在没有日志的情况下删除 SQL 中的大量表数据？

我有一个很大的数据表。 这张表里有一千万条记录。

这个查询的最佳方法是什么

   Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())
306162 次浏览
小开
最佳答案

  1. 如果要删除该表中的所有行，最简单的选项是截断表，比如

     TRUNCATE TABLE LargeTable
GO

截断表将简单地清空表，您不能使用 WHERE 子句来限制要删除的行，并且不会触发任何触发器。

  1. 另一方面，如果你正在删除超过80-90% 的数据，假设你总共有1100万行，你想要删除1000万行，另一种方法是将这100万行(你想要保留的记录)插入到另一个暂存表中。截断这个大表并插入回这100万行。

  2. 或者，如果权限/视图或其他将这个大表作为底层表的对象不受删除这个表的影响，那么您可以将这些相对较少的行放到另一个表中，删除这个表并创建另一个具有相同模式的表，然后将这些行导入到这个前大表中。

  3. 我能想到的最后一个选项是修改数据库的 Recovery Mode to 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 的回答是正确的，但也要记住，如果不在每个块之后提交事务并执行一个检查点，日志可能会增长很多。下面是我将如何做到这一点，并以这篇文章 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
小开

这种 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
小开

@ 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
小开

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

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

您可以使用 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
小开

另一个用途:

SET ROWCOUNT 1000 -- Buffer


DECLARE @DATE AS DATETIME = dateadd(MONTH,-7,GETDATE())


DELETE LargeTable  WHERE readTime < @DATE
WHILE @@ROWCOUNT > 0
BEGIN
DELETE LargeTable  WHERE readTime < @DATE
END
SET ROWCOUNT 0

可供选择;

如果启用了事务日志，则禁用事务日志。

ALTER DATABASE dbname SET RECOVERY SIMPLE;
小开

我能够在几分钟内从2100万行的表中删除1900万行。这是我的方法。

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

  1. 获取其中 readTime < dateadd (MONTH，-7，GETDATE ())的大表的主键的最小值。(在 readTime 上添加索引，如果还没有出现，这个索引将随着步骤3中的表一起被删除。).让我们将它存储在一个变量‘ min _ basic’中

  2. 将所有主键 > min _ main 的行插入到一个临时表中(如果没有，则为内存表)。行不大)。

  3. 放下大桌子。

  4. 重新创建表。将临时表中的所有行复制到主表中。

  5. 放下舞台。

小开

简短的语法

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

如果您使用的是 SQL Server 2016或更高版本，并且您的表根据您试图删除的列(例如 Timestamp 列)创建了分区，那么您可以使用这个新命令按分区删除数据。

用(分区({ | }[ ，... 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()
小开

如果我说没有循环，我可以使用 GOTO语句删除大量的记录使用 sql 服务器。 艾克萨。

 IsRepeat:
DELETE TOP (10000)
FROM <TableName>
IF @@ROWCOUNT > 0
GOTO IsRepeat

这样你就可以用较小的删除量来删除大量的数据。

如果需要更多信息请告诉我。

小开

如果要删除具有大量记录的表的记录，但保留一些记录, 您可以将所需的记录保存在类似的表中，并截断主表，然后将保存的记录返回给主表。

小开

这个问题有点老了，但我只是无意中发现了它，想寻求帮助。在保留一些行的同时，删除一大堆行的最快方法是创建一个

  1. 创建一个临时表(我使用了一个表变量)

  2. 选择要保存在临时表中的行

  3. 截断目标表

  4. 将保留的行插入到目标表中。

    开始传送

我总是首先通过选择@tmpSaveTable 中的行并回滚事务来进行测试。我几秒钟就排了1700万行。

Begin tran
DECLARE @tmpSaveTable   table (
...your columns, types, etc. go here )
INSERT @tmpSaveTable (columns here)
SELECT (appropriate columns from target here)
WHERE (which rows to save)
-- appropriate place to test w/ select from @tmpSaveTable
TRUNCATE SourceTable
INSERT SourceTable (columns)
SELECT (all values from @tmpSaveTable)
--Rollback Tran testing
Commit Tran

提交答案