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

1、使用批量删除

一次性删除大量数据可以减少数据库的I/O操作次数，提高删除性能。在SQL中，可以使用DELETE FROM table_name WHERE condition语句进行批量删除。

例如

代码语言：sql复制

DELETE FROM table_name WHERE condition LIMIT 2000000;

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

2、使用TRUNCATE TABLE语句

TRUNCATE TABLE语句是一种删除表中所有行的高效方法。与DELETE语句不同，TRUNCATE TABLE不记录日志和触发器，并且不会逐行删除数据。相反，它将表截断为零行并释放存储空间。

代码语言：sql复制

TRUNCATE TABLE table_name;

注意：TRUNCATE破坏性较大，不推荐使用

3、禁用日志和约束

禁用日志和约束可以大大提高删除大量数据的速度，因为这些操作会消耗大量的系统资源。可以通过设置数据库的一些参数来实现禁用日志和约束的目的。禁用日志：可以通过设置数据库参数binlog_format为ROW或者设置session的sql_log_bin参数为0来禁用二进制日志记录。另外，可以暂时禁用慢查询日志和错误日志。

禁用约束：可以通过使用以下语句禁用外键约束、唯一约束和触发器：

代码语言：sql复制

  SET FOREIGN_KEY_CHECKS = 0;
  SET UNIQUE_CHECKS = 0;
  SET TRIGGER_CHECKS = 0;

4、在低峰时段执行删除操作

在数据库负载较低的时间段执行删除操作可以减少对系统性能的影响，从而提高删除速度。通常，数据库的低峰时段是在使用量较少的凌晨或周末。使用计划任务（cron job）或延时执行删除操作可以在低峰时段自动执行

5、使用 create-table-as-select (CTAS)

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

代码语言：sql复制

create table table_name_temp
select * from source_table where XXXX = ?

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

6、5步骤改进

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

代码语言：sql复制

-- 复制表结构 
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，但是考虑到数据安全，毕竟是大数量数据删除，还是多操作一步，替换后自己检查下，然后再删除旧表，较为稳妥。

数据库 sql

0 人点赞