0%

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的方式