如何使用 SQL 快速删除数百万行数据

描述

删除表大批量数据,这是一个比较少的事件。 但在实际的业务开发中或者数据测试也会遇到这种情况。比如定期从日志大表中删除几百万的数据记录;删除表数据的方式有多种,操作起来也很简单。但是这里存在一个问题, 删除大量行可能会很慢。 并且有可能需要更长的时间,因为另一个会话已锁定您要删除的数据。

根据我们所熟知的使用SQL删除数据有三个方式:
1:DELETE,可以添加where条件,速度较慢,锁表
2:truncate ,会删除表所有数据,速度快
3:drop,删除数据以及表结构,慎用

实践

【1】对于truncate 和drop不在本次的讨论范围,虽然这俩种方式很快,但是破坏性太大。注意日常开发中所有删除操作必须添加条件。

【2】对于几十万以上数据的删除不建议使用DELETE FROM TABLE WHERE的方式,该操作非常耗时,效率很差。

【3】对于大批量数据的删除需求实现可以通过Create-Table-as-Select方式处理,在表中插入行比删除它们更快。 使用 create-table-as-select (CTAS) 将数据加载到新表中的速度更快。

create table table_name_temp
  select * from source_table where XX=?

通过CTAS将不予删除的数据保留到一个临时表中,然后再通过SWAP的方式将临时表作为原表,通过这种方式完成大批量数据删除

【4】个人不建议上述的方式建表,上面的建表方式新表是不会复制原表的索引结构的,如果这个是一个大表那么后面单独加索引也是一个问题。建议使用 CREATE TABLE XXX (LIKE XXX);方式建表,这个会复制相关的索引结构数据

【5】具体操作步骤

-- 复制表结构 
CREATE  TABLE  tableB   (LIKE tableA);
-- 插入筛选数据
INSERT into  tableB    SELECT *  from tableA  where XXX = ?;
-- 重命名,替换
rename  table  tableA to tableC;
rename  table  tableB to tableA;  
-- 删除旧表  
DROP TABLE tableC;

注意:其中俩次rename可以先drop然后一次的rename,但是考虑到数据安全,毕竟是大数量数据删除,还是多操作一步,替换后自己检查下,然后再删除旧表,稳妥些

【6】通过delete删除上百万的数据耗时不清楚具体耗时,反正自己等待了十多钟都没有结果,通过select * from sys.session WHERE conn_id!=connection_id();
查询看一直在执行。通过上面的方式500万的数据不到1分钟,还是比较快的。

【7】小技巧,如果你的大表有递增的ID,删除的或者保留数据的能够以ID作为划分的那么select的条件可以通过这里进行优化,那么操作效率会更快。

【8】如果是oracle,那么还可以使用 alter table … move 来更改存储行的表空间

alter table tableName   move including rows where XXX=

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:博客之星2021 设计师:Hiro_C 返回首页
评论

打赏作者

ZWZhangYu

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值