历史表(history table)清理方案：如何删除存储超过1周的旧数据

前提条件 #

• 历史表table_h必须包含数据写入时间字段，推荐字段名create_time，类型设为datetime/timestamp，默认值设置为当前时间（CURRENT_TIMESTAMP），用于判断数据是否超过7天留存周期

方案1：同事务直接清理（适合历史表数据量较小的场景） #

直接在原有存储过程的插入逻辑后追加清理逻辑，保证同步和清理在同一个事务中完成：

INSERT INTO table_h
    SELECT * FROM table;

-- 清理7天前的历史数据
DELETE FROM table_h 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY);

COMMIT;

该方案实现简单、逻辑闭环，缺点是如果历史表数据量超过百万级，DELETE操作会触发全表扫描、产生大量行锁，占用过多数据库资源，甚至影响正常业务读写。

方案2：分批次删除（适合中大规模历史表场景） #

为避免单次DELETE删除大量数据导致长事务、锁表问题，采用循环分批删除的逻辑，每次删除固定条数（比如1000条），直到没有符合条件的过期数据：

-- 插入同步逻辑
INSERT INTO table_h
    SELECT * FROM table;

-- 分批清理7天前的历史数据
WHILE ROW_COUNT() > 0 DO
    DELETE FROM table_h 
    WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)
    LIMIT 1000;
END WHILE;

COMMIT;

注意提前给create_time字段加普通索引，可大幅提升筛选过期数据的效率，避免全表扫描。

方案3：分区表直接丢弃分区（海量历史表最优方案） #

如果历史表数据量极大，推荐将table_h按周做范围分区，清理过期数据时直接DROP对应分区，性能是DELETE的百倍以上，几乎不产生额外日志：

假设历史表按自然周做范围分区，每周对应一个分区，清理上周数据只需要执行分区删除语句

-- 插入同步逻辑
INSERT INTO table_h
    SELECT * FROM table;

-- 直接删除7天前对应的分区，效率极高
ALTER TABLE table_h DROP PARTITION p_xxxxxx;

COMMIT;

额外优化建议 #

• 清理任务可拆分到业务低峰期独立执行，不需要和同步逻辑强绑定，避免高峰时段占用数据库资源
• 正式执行清理前，可先执行SELECT COUNT(*) FROM table_h WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY)验证待删除数据量是否符合预期，避免误删
• 如果对历史数据有归档需求，可在删除前先将过期数据导出到冷存储，再执行删除操作

内容的提问来源于stack exchange，提问作者banana_99