我有一个大型数据表。 表中有 1 000 万条记录。
该查询的最佳方法是什么?
Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())
1.如果要删除表中的所有行,最简单的方法是截断表,例如
TRUNCATE 表 LargeTable
GO
Truncate 表仅会清空表,不能使用 WHERE 子句限制被删除的记录,也不会触发任何触发器。
2.另一方面,如果要删除超过 80-90% 的数据,例如,如果总共有 1 100 万行,而你想删除 1 000 万行,另一种方法是将这 100 万行(你想保留的记录)插入到另一个暂存表中。截断这个大表,然后再插入这 100 万行。
3.或者,如果权限/视图或其他以该大表为底层表的对象不会因删除该表而受到影响,则可以将这些相对较少的记录导入另一个表中,删除该表并创建具有相同模式的另一个表,然后将这些记录导入前大表。
4.我能想到的最后一个办法是将数据库的 "恢复模式 "改为 "简单",然后使用类似这样的 while 循环分批删除记录。
DECLARE @Deleted_Rows INT;
SET @Deleted_Rows = 1;
WHILE (@Deleted_Rows > 0)
开始
-- 每次删除少量记录
DELETE TOP (10000) LargeTable
WHERE readTime < dateadd(MONTH,-7,GETDATE())
SET @Deleted_Rows = @@ROWCOUNT;
结束
不要忘记将恢复模式改回完全模式,我认为你必须进行备份才能使其完全生效(更改或恢复模式)。
@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