MySQL批量删除海量数据的几种方法总结_Mysql

一、问题分析

一次性删除大量数据的主要问题在于：

长时间锁表：大量删除操作会导致数据库长时间加锁，影响其他事务的正常操作。
事务日志暴增：mysql 在删除数据时会记录事务日志，大量删除操作可能导致日志文件过大，甚至撑满磁盘。
影响性能：一次性删除大量数据会占用大量的 cpu 和 io 资源，对数据库整体性能产生严重影响。

为避免这些问题，可以考虑分批删除等策略来减少对数据库的压力。

二、批量删除海量数据的几种方法

方法 1：使用 limit 分批删除

limit 分批删除是一种常用的处理海量数据的方式。每次删除固定数量的数据，循环执行，直至删除完毕。

示例 sql：

假设我们要删除 logs 表中创建时间在某个日期之前的所有数据：

-- 设置每批删除的行数
set @batch_size = 1000;
 
-- 分批删除符合条件的数据
delete from logs 
where create_time < '2023-01-01' 
limit @batch_size;

可以将上述语句放入存储过程或在应用层循环调用。每次删除 batch_size 行数据，减少锁表时间和日志生成量。

优点：

控制单次删除的量，减少锁表时间和日志生成量。

缺点：

需要循环多次操作，逻辑稍复杂。

注意：

分批删除的 limit 值可以根据实际环境调整。通常 500 到 5000 是较合理的选择。

方法 2：通过主键范围分批删除

如果要删除的数据在主键上是连续的（如自增 id），可以按主键范围分批删除。这样能够避免 limit 的偏移开销，提高删除效率。

示例 sql：

假设 logs 表的主键是 id：

-- 设置每批删除的范围
set @start_id = 0;
set @end_id = 1000;
 
while (@start_id < (select max(id) from logs where create_time < '2023-01-01')) do
    delete from logs
    where id between @start_id and @end_id
    and create_time < '2023-01-01';
 
    -- 更新删除范围
    set @start_id = @end_id + 1;
    set @end_id = @end_id + 1000;
end while;

优点：

主键范围分批避免了 limit 偏移带来的开销。

缺点：

需要知道主键范围，且适用于有连续主键的数据表。

方法 3：通过自定义批量删除存储过程

可以将批量删除逻辑封装成存储过程，利用存储过程自动控制批量删除过程。

示例 sql：

delimiter $$
 
create procedure batch_delete_logs()
begin
    declare done int default false;
    declare batch_size int default 1000;
 
    while not done do
        delete from logs 
        where create_time < '2023-01-01' 
        limit batch_size;
 
        -- 检查是否还有剩余数据
        if row_count() < batch_size then
            set done = true;
        end if;
    end while;
end $$
 
delimiter ;

执行存储过程：

call batch_delete_logs();

优点：

存储过程实现自动化，逻辑清晰，避免多次手动执行 sql。

缺点：

适用于支持存储过程的场景，对小批量删除非常适合。

方法 4：创建临时表替换旧表

在某些情况下，删除大表中的大量数据可以通过创建新表的方法完成。即先将需要保留的数据转移到新表，再删除旧表。这种方法可以减少锁表时间和日志开销。

步骤：

创建一个新表（结构与旧表相同）。
将需要保留的数据插入新表。
删除旧表，重命名新表为原表名。

示例 sql：

-- 创建新表
create table logs_new like logs;
 
-- 插入需要保留的数据
insert into logs_new
select * from logs where create_time >= '2023-01-01';
 
-- 删除旧表并重命名新表
drop table logs;
rename table logs_new to logs;

优点：

避免了大规模的删除操作，减少了锁表时间和日志。

缺点：

需要额外的磁盘空间来存放新表数据。
在业务量大的情况下，可能需要进行额外的锁机制控制。

三、性能优化建议

避免在业务高峰期进行大规模删除，可以选择在夜间等业务低峰期执行。
适当设置批量大小。批量删除时，limit 的大小需要根据实际情况调整，不宜过大，防止长时间锁表。
关闭不必要的日志。在某些极端情况下，可以关闭 mysql 的二进制日志（binlog）来减少日志开销，但此操作有风险，应在充分了解后谨慎使用。

总结

方法	适用场景	优点	缺点
`limit` 分批删除	需要简单分批删除	逻辑简单，减少锁表时间	需循环操作
主键范围分批删除	有连续主键的表	高效，无偏移开销	需手动指定范围
自定义批量删除存储过程	小批量删除	自动化操作	需要数据库支持存储过程
临时表替换	删除数据量非常大	避免锁表，减少日志开销	需要额外磁盘空间