sql server删除大表数据总结

sql server删除大表数据总结

1412发表于2019-03-31

sql server对于大表数据(百万、千万级别)的删除操作一定要注意,尤其是线上的数据更加要谨慎,一不注意可能要hang住整个数据库导致网站或系统不可用。

因为删除操作会记录日志,如果删除的数据比较多可能还会锁表,这时其它的查询如果没有nolock就会阻塞,系统会卡住。

这里我把删除分为两种情况。

一、要删除的数据占用总数据比例少,比如只占10%。

这个时候可以直接用delete,操作。使用分批,一次少量的删除。比如以下是我们一个历史表,要删除200天以前的数据。

WHILE 1 = 1
BEGIN
DELETE TOP(5000) FROM His_Order WHERE CreateDate<convert(char(10),GETDATE()-200,120);
IF @@rowcount < 5000
BREAK;
END
一次最多删除5000条。

优点:基本上不影响线上业务,操作简单。

缺点:操作时间长,生成的日志文件可能会很大。

二、要删除的数据占用总数比较多,比如占用60%以上

也就是保留数据少数,大部分数据都是要删除的。

这个时候我们可以采用把要保留的数据筛选出来插入到新表

select * into His_Order2 WHERE CreateDate>=convert(char(10),GETDATE()-200,120)

把新表和当前表重命名。

--把不当前表重命名为备份表
EXEC sp_rename 'His_Order', 'His_Order_Bak'
--把要保留的新表重命名为线上可用的表
EXEC sp_rename 'His_Order2', 'His_Order'
优点:操作速度快速,效率高

缺点:在重命名表的过程中会有短暂不可用或报错情况,要处理要在转移要保留数据的过过程中,要考虑和处理His_Order中的数据变更(要求不高的系统可以锁表再操作)。


小编蓝狐