大批量delete 优化方案

超过100万以上数据 删除的时候 会非常慢且产生大量日志文件 最大的问题是内存爆表 导致得多次重启服务才能删除整个库 暂时提出初步优化方案

1.设置日志为简单模式,处理完后恢复

ALTER DATABASE tcgpsdb SET RECOVERY SIMPLE --简单模式
ALTER DATABASE tcgpsdb SET RECOVERY FULL --还原为完全模式

2.delete的时候 会造成整表锁定,所以采用分批删除

@onecount 这里设置为一百万一次,可以自由调节

declare @onecount int
set @onecount=1000000
print getdate()
while 1=1
begin
     delete top(@onecount) from XX where FSendTime<'2019-1-01'
     IF (@@rowcount<@onecount) BREAK;
     print getdate()
end

删除一千多万条数据耗时 注意:搜索时间字段有加索引

02  1 2019 11:25AM

(1000000 行受影响)
02  1 2019 11:26AM

(1000000 行受影响)
02  1 2019 11:27AM

(1000000 行受影响)
02  1 2019 11:27AM

(1000000 行受影响)
02  1 2019 11:28AM

(1000000 行受影响)
02  1 2019 11:28AM

(1000000 行受影响)
02  1 2019 11:29AM

(1000000 行受影响)
02  1 2019 11:29AM

(1000000 行受影响)
02  1 2019 11:30AM

(1000000 行受影响)
02  1 2019 11:31AM

(1000000 行受影响)
02  1 2019 11:31AM

(636657 行受影响)

 

另外还有 插入到新表然后 drop 老表 把新表改为原来表名 这种涉及停机 所以暂不考虑

以上方案还是不够理想,这里抛砖迎玉 希望有更好的同学能给出 更快速 更省内存的解决方案