SQL选择性快速删除大量数据千万亿级方法

Love The Way You Lie 2023-10-04 16:58 19阅读 0赞

--SQL选择性分批删除大量数据千万亿级
    DECLARE @OneCount int
    set @OneCount=100000 --每批删除的记录数10W
    print Convert(varchar(50),getdate(),120)
    while 1=1
    begin
         Delete Top(@OneCount) from TankMonitor where TnkM_GenTime<'2021-06-07 0:0:01';
         if (@@RowCount<@OneCount) Break;
         print Convert(varchar(50),getdate(),120)
         WaitFor DELAY '00:00:02' --等待几秒再继续
    end
    print '删除完成'
    
    --说明 :@OneCount 每次删除的数据量，此处设置10w，可根据实际情况调整。
    --SQL Server中@@RowCount返回受上一语句影响的行数
    --优化说明:尽量让删除语句使用索引,前提是where子句中用索引列做筛选
    --执行前:先删除或禁用表中无关索引（即对当前DELETE语句无用的索引），删除前先生成对应的重建SQL，以便完成数据删除后，重新创建索引

**实测：**

索引多了，在对大表IO的时候严重影响性能啊，最开始用原表的4个索引，我分批删除了42分钟才删除690万条，然后我取消操作。清除掉表中全部索引，单独建了 一个where 用到的聚集性索引。删除速度提高了至少10倍。11分钟即删除掉了4900万条

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2g0NDUzMDY_size_20_color_FFFFFF_t_70_g_se_x_16][]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAd2g0NDUzMDY_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/20210907225608883.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAd2g0NDUzMDY=,size_20,color_FFFFFF,t_70,g_se,x_16