mysql删除大量数据的三种方案

方案概述

• delete quick … limit + optimize table
• drop + rename
• 表分区

关键字回顾

	回滚	删除范围	释放空间	使用场景
DELETE	DML	按条件删除行	不立即释放，会标记为删除，需执行OPTIMIZE TABLE	按条件删除
TRUNCATE	DDL	固定删除表中所有行	立即释放	快速清空表但保留结构
DROP	DDL	固定删除表中所有行、结构、数据	立即释放	删除表相关所有数据

delete与delete quick

• 相同点：都会标记为删除
• 不同点：delete会立即进行数据整理，涉及磁盘IO和数据移动，速度较慢。delete quick不会立即整理，只能通过optimize table来整理，速度较快

方案明细

delete quick … limit + optimize table

具体代码

DECLARE @BatchSize INT = 10000;
WHILE (1=1)
BEGIN
    DELETE FROM table WHERE create_time < '2024-01-01' LIMIT @BatchSize;
    
    IF @@ROWCOUNT = 0
    BEGIN
    	OPTIMIZE TABLE table
        BREAK;
    END
END

适用场景

删除数据量不大，数据产生速度不大

drop + rename

具体代码

create table t_name_new like t_name;
rename table t_name to t_name_drop;
rename table t_name_new to t_name;
drop table t_name_drop;

适用场景

表内数据可全部删除。存储冗余数据，但业务仍需写入该表

表分区

具体代码

-- 创建新表指定分区
create table t_name_new like t_name;
ALTER TABLE t_name_new
PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p_202407 VALUES LESS THAN (TO_DAYS('2024-07-01 00:00:00')),
		PARTITION p_202408 VALUES LESS THAN (TO_DAYS('2024-08-01 00:00:00')),
    PARTITION p_max VALUES LESS THAN (TO_DAYS('2024-08-01 00:00:00'))
);
-- 确认分区是否创建成功
show create table t_name_new;
explain partitions select * from t_name_new;
-- 插入数据(省略)
-- 确认分区功能是否正常
select count(*) from ota_applog PARTITION(p_202408);
alter table ota_applog drop partition p_202407;

-- 停止数据写入
-- 备份数据
create table t_name_bak like t_name;
insert into t_name_bak select * from t_name;
-- 迁移保留数据
insert into t_name_new
select * from t_name where create_time > '2024-07-31 00:00:00';

-- drop+rename清楚无用数据
rename table t_name to t_name_drop;
rename table t_name_new to t_name;
drop table t_name_drop;
-- 恢复数据写入

适用场景

表内部分数据需要删除，同时需要删除的数据量和剩余数据量过大，并且数据写入量大。重新规划表结构，后续可通过alter table ota_applog drop partition p_name;来快速删除某一分区数据。