SQLServer通过临时表进行数据批量删除
目前数据库中存在一张几千万行的日志表,按照数据保留策略仅需要保留最近一个月的数据,因此需要对历史数据进行清理。测试了几种方案最后选择了通过临时表来进行关联删除
1、在数据表上创建索引
CREATE INDEX IDX_ID ON dbo.Log(ID); |
2、创建临时表并创建索引
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp |
3、将要删除的记录ID填充到临时表中
Insert into #tmp(id) select id from dbo.Log WHERE [Date] < DATEADD(MONTH, -1, GETDATE()) |
4、关联进行删除
delete a from dbo.Log a ,#tmp b where a.id=b.id |
经过测试4分钟删除了150w行记录,但问题在于该方式会造成阻塞,因此可以改写为下列方式,降低锁持有的时间
while 1=1 |
这样每次只删除10000行,当最后一次删除数量不到10000行则退出循环
思考题: 表中存在自增值,是否可以通过查询最近一个月中最小的ID,限制删除范围,然后根据ID构建分区,分批删除呢?类似于ORACLE中通过ROWID批量做DELETE的方式
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 DBA学习记录!