SQLServer

SQLServer通过临时表进行数据批量删除

目前数据库中存在一张几千万行的日志表，按照数据保留策略仅需要保留最近一个月的数据，因此需要对历史数据进行清理。测试了几种方案最后选择了通过临时表来进行关联删除

1、在数据表上创建索引

1

CREATE INDEX IDX_ID ON dbo.Log(ID);

2、创建临时表并创建索引

1
2
3
4
5

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
CREATE TABLE #tmp(
ID bigint not null
);
CREATE CLUSTERED INDEX idx_temp_id on #tmp(ID);

3、将要删除的记录ID填充到临时表中

1

Insert into #tmp(id) select id from dbo.Log  WHERE [Date] < DATEADD(MONTH, -1, GETDATE())

4、关联进行删除

1

delete a from dbo.Log a ,#tmp b where a.id=b.id

经过测试4分钟删除了150w行记录，但问题在于该方式会造成阻塞，因此可以改写为下列方式，降低锁持有的时间

1
2
3
4
5
6
7

while 1=1
begin
     delete top(10000) A from dbo.Log  A,#apitmp b where a.id=b.id;
     --此处不能写任何语句，否则可能导致rowcount计数异常
     IF (@@rowcount<10000) BREAK;
end
GO

这样每次只删除10000行，当最后一次删除数量不到10000行则退出循环

思考题： 表中存在自增值，是否可以通过查询最近一个月中最小的ID，限制删除范围，然后根据ID构建分区，分批删除呢？类似于ORACLE中通过ROWID批量做DELETE的方式

comments powered by Disqus