方案概述
- 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;来快速删除某一分区数据。