SQL Server 快速删除/归档数据方法小结

最近遇到了清理历史数据的需求，整理一下不同场景及对应处理方法

 

一、 可删除整张表数据

这是最简单的，TRUNCATE / DROP TABLE即可。

 
二、日志表或历史信息表

这种情况是，代码会不断往表里插入新数据但是并不会去查询，一般是系统异常时开发手动去查。

这种情况可以停业务将原表重命名为bak表，再按原有表结构创建一个新表让系统插入。bak表根据业务要求时间保留，例如三个月，三个月后删除。

-- 重命名
exec sp_rename 'mytab','mytab_bak';
-- 创建新表
select * into mytab from mytab_bak;
--按原表创建索引、约束

三、需要实际删除部分数据

这其实才是大部分时候会遇到的情况，对于业务表，通常无法使用前面两种讨巧的方法。

首先需要对表的数据量和需删除的数据量做一个统计，计算删除的比例。

sp_spaceused 'dbo.TEST';
SELECT COUNT(*) from TEST WHERE <删除条件>

根据要删除的数据量可以再分为两类

1.  删除表中绝大部分数据

这个绝大部分怎么定义不好量化，所以我们这里就量化为60%。如果删除的数据比例超过60%，就采用下面方法： 

• 停业务，新建临时表并插入待保留数据。

对于有alwayson的数据库，事务日志收缩相当麻烦，必须注意insert数据量和产生的事务日志量。如果实在很大，需要分批insert并手动备份事务日志。

-- 创建临时表
select * into mytab_tmp from mytab where xxx;
DBCC SQLPERF(LOGSPACE);

--按时间批量插入数据
DECLARE @begindate DATETIME = '2020-02-01';

WHILE @begindate <= '2020-08-24'
BEGIN
    INSERT tmp0824 select * from schemalog where logdate>=@begindate and logdate<dateadd(day,7,@begindate)
    SET @begindate = dateadd(day,7,@begindate)
END

• rename原表

exec sp_rename 'mytab','mytab_bak';

• rename新表为原表名

exec sp_rename 'mytab_tmp','mytab';

• 按原表新建索引，检查相关的触发器、约束，进行触发器或约束的重命名
• 启动业务，确认无误后（或者保留一段时间），TRUANCATE或DROP bak表

2. 删除表中少部分数据

如果删除条件字段无索引，可以考虑先建上删除完索引后再删除该索引，否则全表扫描执行时间可能非常长。

用小批量分批次删除通常比一次性删除性能要快很多，同时避免锁粒度过大且锁定的时间非常长，和事务日志变得巨大。

到底一次性删除多少数量的记录SQL效率最高呢？这个真没有什么规则计算，个人测试对比过一次删除10000或100000，没有发现什么特别规律。不过一般用10000，在实际操作过程，可以通过做几次实验对比后，选择一个合适的值即可。

 

案例1

DECLARE @delete_rows INT;
DECLARE @delete_sum_rows INT =0;
DECLARE @row_count INT=100000
 
WHILE 1 = 1
    BEGIN
        DELETE TOP ( @row_count )
        FROM    dbo.[EmployeeDayData]
        WHERE    WorkDate < CONVERT(DATETIME, '2012-01-01 00:00:00',120);
            
        SELECT  @delete_rows = @@ROWCOUNT;         
            SET @delete_sum_rows +=@delete_rows
            IF @delete_rows = 0
            BREAK;
        END;
SELECT @delete_sum_rows;

 案例2

DECLARE @r INT;
DECLARE @Delete_ROWS  BIGINT;
 
SET @r = 1;
SET @Delete_ROWS =0
WHILE @r > 0
BEGIN
    BEGIN TRANSACTION;
        DELETE TOP (10000) -- this will change
           mytab
           WHERE Remark='今日未入' and Operation_Date<CONVERT(datetime, '2019-05-30',120);
          SET @r = @@ROWCOUNT;          
          SET @Delete_ROWS += @r;
    COMMIT TRANSACTION;   
    PRINT(@Delete_ROWS);
END

参考

Break large delete operations into chunks

SQL Server大表如何快速删除数据 - 潇湘隐者 - 博客园