千万级数据删除

[yanian]

MSSQL 2005，数据千万级，删除14年之前的数据(delete 语句)，非常缓慢，求大神们指教

[yanian]

引用 34 楼 DBA_Huangzj 的回复:

[quote=引用 33 楼 yanian 的回复:] [quote=引用 32 楼 DBA_Huangzj 的回复:] [quote=引用 29 楼 yanian 的回复:] [quote=引用 23 楼 DBA_Huangzj 的回复:] 如果可以的话，把2014年的数据插入新表，然后truncate之后再导回去。如果不行的话，控制每次删除的量，循环delete

trun不太现实，每次删除的量小的话好删很久，量大的话有时候job执行时间被拉得太久，不知道怎么搞了。。。[/quote]你的这个表和其他表有很严密的关联不？[/quote] 关联不大[/quote]那你先用truncate的方法移除2014年之前的数据，以后每年delete的方式删除前一年的数据，量不会大[/quote] truncate不是对整个表操作的么？能只移除2014之前的数据？

[發糞塗牆]

哥，先把需要保留的数据移到一个新表，实体表或临时表均可，然后把原表truncate，再把那个新表的数据插回去

[yanian]

引用 36 楼 DBA_Huangzj 的回复:

哥，先把需要保留的数据移到一个新表，实体表或临时表均可，然后把原表truncate，再把那个新表的数据插回去

这个是线上的库，14年的数据需要用，而且数据比较多，这样做可行么？

[yanian]

14年好几百万

[發糞塗牆]

如果可以的话，把2014年的数据插入新表，然后truncate之后再导回去。如果不行的话，控制每次删除的量，循环delete

[發糞塗牆]

几百万的数据如果没人用的前提下，插入临时表可能只需要4分钟左右，truncate接近实时，插回去可能只需要几分钟，整个过程可能不到15分钟，所有时间都是经验而已，没有理论支撑，仅供参考，如果这个时间是可接受的，那做好备份之后在系统闲时就可以做，这个方法会导致系统无法使用这个表，直到操作完毕，这个需要提醒你，如果不能接受，那就循del，几千万数据如果不停地小批量删除其实不会很慢的。

[小灰狼]

干嘛要删除呢 为了节省磁盘空间，还是想让查询更快一点？ 如果是为了磁盘空间的话，劝楼主不要做了，因为单纯的 delete 语句不但不会释放磁盘空间，它还会把删除操作写到日志里去，反而会占用更大的空间 为了查询的话，俺木有什么建议

[xiaoxiangqing]

主要是看删除的数据多还是保留的数据多，如果删除的数据多，可以参考1楼的

[twtiqfn]

太不可思义了，这么大的数据量

[Neo_whl]

循环批量删试试

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

[jxwangjm]

我觉得楼主可以先建一个新表t_new，把需要保留的数据插入到新表中，然后把旧表改名t_old，把新表t_new改为t

[刘兄弟]

几百万的数据如果没人用的前提下，插入临时表可能只需要4分钟左右，truncate接近实时，插回去可能只需要几分钟，整个过程可能不到15分钟，所有时间都是经验而已，没有理论支撑，仅供参考，如果这个时间是可接受的，那做好备份之后在系统闲时就可以做，这个方法会导致系统无法使用这个表，直到操作完毕，这个需要提醒你，如果不能接受，那就循del，几千万数据如果不停地小批量删除其实不会很慢的。 说的好。 其实 你要告诉领导的是 这个删除需要多久多久 ，然后发个通告。。。到时候停机1个小时，做完就好了。

[yanian]

引用 39 楼 DBA_Huangzj 的回复:

几百万的数据如果没人用的前提下，插入临时表可能只需要4分钟左右，truncate接近实时，插回去可能只需要几分钟，整个过程可能不到15分钟，所有时间都是经验而已，没有理论支撑，仅供参考，如果这个时间是可接受的，那做好备份之后在系统闲时就可以做，这个方法会导致系统无法使用这个表，直到操作完毕，这个需要提醒你，如果不能接受，那就循del，几千万数据如果不停地小批量删除其实不会很慢的。

这个库是24小时需要用到的，貌似只能慢慢删除了，还有个问题请教下，一次删除1K和5W的数据时间差不多都在15分钟左右(删除条件上没有索引)，有哪些优化方法？

[發糞塗牆]

这个涉及锁升级的问题，一般来说，5000个行锁会升级成表/分区锁。所以一般批量删除的时候删除的数量不宜过多，1000比较合适，你可以考虑在删除条件上加个非聚集索引，加快查找速度，毕竟删除的步骤还是需要先把需要删除的数据找出来再操作。所以查德曼，删得也慢

[yanian]

引用 42 楼 DBA_Huangzj 的回复:

这个涉及锁升级的问题，一般来说，5000个行锁会升级成表/分区锁。所以一般批量删除的时候删除的数量不宜过多，1000比较合适，你可以考虑在删除条件上加个非聚集索引，加快查找速度，毕竟删除的步骤还是需要先把需要删除的数据找出来再操作。所以查德曼，删得也慢

感谢黄大大和各位的赐教，我尝试下各种方法，做个测试比较

[發糞塗牆]

期待你的测试结果

[Tiger_Zhao]

靠 DELETE 删数据总运行时间是减少不了的，循环分批删主要的目的是减少对其他会话的影响。
而且即使强制终止循环任务，最多只回滚一批，前面的批次都已经成功删除了，不至于中断后又得从头做起。

[yanian]

引用 44 楼 DBA_Huangzj 的回复:

期待你的测试结果

如45楼所说，不动原表，直接删除的效率比较低，忍无可忍了，要求停机维护，trun 半小时搞定

[yanian]

引用 45 楼 Tiger_Zhao 的回复:

靠 DELETE 删数据总运行时间是减少不了的，循环分批删主要的目的是减少对其他会话的影响。 而且即使强制终止循环任务，最多只回滚一批，前面的批次都已经成功删除了，不至于中断后又得从头做起。

恩 delete很耗时间

[yanian]

引用 46 楼 jadilee 的回复:

几百万的数据如果没人用的前提下，插入临时表可能只需要4分钟左右，truncate接近实时，插回去可能只需要几分钟，整个过程可能不到15分钟，所有时间都是经验而已，没有理论支撑，仅供参考，如果这个时间是可接受的，那做好备份之后在系统闲时就可以做，这个方法会导致系统无法使用这个表，直到操作完毕，这个需要提醒你，如果不能接受，那就循del，几千万数据如果不停地小批量删除其实不会很慢的。 说的好。 其实 你要告诉领导的是 这个删除需要多久多久 ，然后发个通告。。。到时候停机1个小时，做完就好了。

就是这个做的了，不然受不了