MSSQL 2005,数据千万级,删除14年之前的数据(delete 语句),非常缓慢,求大神们指教
------解决思路----------------------
将14年以后的数据导入到临时表
然后TURNCATE 整个表 然后再将临时表的数据导入到这个表
------解决思路----------------------
以前试过,删除用Job+循环语句执行,删除了2、3天
楼主可以试尝这方法,每次删除1000条记录
------解决思路----------------------
1)备份清日志
2)日期字段要有索引
3)统计下要删除的记录数,最小的日期
4)从最小的日期开始,按月/周/天分段删除,尽量把分段数据量控制在千数量级,一次性删太多锁就多了。
5)备份清日志
------解决思路----------------------
上次ORACLE里面有个2-3亿行记录的删除情况。写个作业吧。
比如把时间作为变量。先取出来最小时间。
给你个例子
declare @mydate date,@int int
select @mydate=min(orderdate) from tablename
--tmp_table 是自己建立的控制表。只有一行一列,初始是1。
select @int =int from tmp_table
while @mydate <'2010-01-01' and @int =1
begin
delete tablename where orderdate=@mydate
set @mydate=dateadd(day,1,@mydate)
end
如果需要停止删除。就把临时表里面的值改成不是1就好了。
------解决思路----------------------
根据主键做循环删除
----把需要删除的数据主键插入临时表
SELECT ID INTO ##DEL_TMP_tb FROM TB
WHERE ...
CREATE CLUSTERED INDEX IDX_ID ON ##DEL_TMP_tb(ID)
CREATE TABLE #TMP_BATCH(ID BIGINT)
WHILE EXISTS (SELECT TOP 1 1 FROM ##DEL_TMP_tb)
BEGIN
DELETE FROM #TMP_BATCH
--每个batch 1000条
INSERT INTO #TMP_BATCH
SELECT TOP 1000 ID FROM ##DEL_TMP_tb
--删除数据
DELETE FROM TB WHERE ID IN (SELECT ID FROM #TMP_BATCH)
DELETE FROM ##DEL_TMP_tb WHERE ID IN (SELECT ID FROM #TMP_BATCH);
--- waitfor delay '00:00.00.050'
END
DROP TABLE ##DEL_TMP_tb
DROP TABLE #TMP_BATCH
------解决思路----------------------
将14年以后的数据导入到新表
1、修改原表名
2、修改新表名为原表名
做成一个事务的话,应该不会太久,主要看你的2014年后的数据量了。
lz参考。 如果这个仍然不行,2005上只能循环小批量删除数据了。
------解决思路----------------------
你现在主要是定位慢。
既然以时间为条件,建索引是必须的。
因为是在线库,分段成千级比万级对其他的会话影响要小。有索引循环速度应该差不多,当然是尽量细分了。
------解决思路----------------------
分批次删除,每次1千条....这符合绝大多数情况...也可视情况删除之间暂停N毫秒/秒
------解决思路----------------------
如果可以的话,把2014年的数据插入新表,然后truncate之后再导回去。如果不行的话,控制每次删除的量,循环delete
------解决思路----------------------
循环批量删试试
declare @count bigint,@sum_count bigint
select @sum_count=count(1) from tb
set @count=0
while @count<@sum_count
begin
delete top(@count) from tb where 限制条件
set @count=@count+10000
end
------解决思路----------------------
那你先用truncate的方法移除2014年之前的数据,以后每年delete的方式删除前一年的数据,量不会大
------解决思路----------------------
几百万的数据如果没人用的前提下,插入临时表可能只需要4分钟左右,truncate接近实时,插回去可能只需要几分钟,整个过程可能不到15分钟,所有时间都是经验而已,没有理论支撑,仅供参考,如果这个时间是可接受的,那做好备份之后在系统闲时就可以做,这个方法会导致系统无法使用这个表,直到操作完毕,这个需要提醒你,如果不能接受,那就循del,几千万数据如果不停地小批量删除其实不会很慢的。
------解决思路----------------------
我觉得楼主可以先建一个新表t_new,把需要保留的数据插入到新表中,然后把旧表改名t_old,把新表t_new改为t
------解决思路----------------------
这个涉及锁升级的问题,一般来说,5000个行锁会升级成表/分区锁。所以一般批量删除的时候删除的数量不宜过多,1000比较合适,你可以考虑在删除条件上加个非聚集索引,加快查找速度,毕竟删除的步骤还是需要先把需要删除的数据找出来再操作。所以查德曼,删得也慢
------解决思路----------------------
靠 DELETE 删数据总运行时间是减少不了的,循环分批删主要的目的是减少对其他会话的影响。
而且即使强制终止循环任务,最多只回滚一批,前面的批次都已经成功删除了,不至于中断后又得从头做起。
------解决思路----------------------
几百万的数据如果没人用的前提下,插入临时表可能只需要4分钟左右,truncate接近实时,插回去可能只需要几分钟,整个过程可能不到15分钟,所有时间都是经验而已,没有理论支撑,仅供参考,如果这个时间是可接受的,那做好备份之后在系统闲时就可以做,这个方法会导致系统无法使用这个表,直到操作完毕,这个需要提醒你,如果不能接受,那就循del,几千万数据如果不停地小批量删除其实不会很慢的。
说的好。
其实 你要告诉领导的是 这个删除需要多久多久 ,然后发个通告。。。到时候停机1个小时,做完就好了。