如何使用 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的条件可以通过这里进行优化，那么操作效率会更快。