mysql8.0.x大表归档到历史表

Posted on Edited on Views:

一、归档操作的目标

把主表（业务表）中 历史数据（如 3 个月前的数据）迁移到一个 历史表（history 表）中，以：

  • 降低主表体积、提升查询性能
  • 避免数据删除带来的锁表或性能问题
  • 保留历史数据以供审计或分析

二、基本思路

一般流程如下：

02cfafddbb0a2c21c4df982e81c7ca2a.png

三、操作步骤

假设：

  • 主表：orders
  • 历史表：orders_history
  • 按字段 create_time 归档，保留最近 90 天数据

3.1创建历史表（结构完全一致）

1
CREATE TABLE IF NOT EXISTS orders_history LIKE orders;

如果是分区表或带触发器、索引的表，请确认归档表结构保持一致，但可以 删除不必要索引 提高写入效率。

3.2插入归档数据

例如归档 3 个月前的数据：

1
2
3
4
INSERT INTO orders_history
SELECT *
FROM orders
WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

建议一次归档一个时间段（如一周或一个月），避免大事务。

3.3删除主表中的已归档数据

1
2
DELETE FROM orders
WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

大表注意：

  • 不要一次性删除太多行，会锁表。
  • 建议 分批删除，每次 1 万或 10 万行。
1
2
3
4
-- 分批删除版本
DELETE FROM orders
WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
LIMIT 10000;

然后循环执行直到删除完毕。

3.4优化表空间

清理完后压缩表空间：

1
OPTIMIZE TABLE orders;

四、自动化实现方式

方式一：存储过程 + 定时事件（MySQL Event）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELIMITER //
CREATE PROCEDURE archive_orders()
BEGIN
  INSERT INTO orders_history
  SELECT * FROM orders
  WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);

  DELETE FROM orders
  WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
  LIMIT 100000;
END //
DELIMITER ;

-- 每天凌晨2点执行
CREATE EVENT IF NOT EXISTS event_archive_orders
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE, '02:00:00')
DO CALL archive_orders();

前提：event_scheduler=ON

方式二：外部脚本（Python/Golang/Shell）

这种适合运维自动化任务，比如定时归档 + 日志记录。

例：Shell 简版

1
2
3
4
5
#!/bin/bash
mysql -uroot -p'xxx' -e "
INSERT INTO orders_history SELECT * FROM orders WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);
DELETE FROM orders WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 3 MONTH) LIMIT 100000;
OPTIMIZE TABLE orders;"

五、针对大表的优化策略

问题 推荐方案
删除慢、锁表 使用 pt-archiver 工具（Percona 工具包）
写入归档慢 临时关闭归档表索引再重建索引
主从复制延迟 归档在从库执行
死锁风险 按主键范围批量处理，如 WHERE id BETWEEN x AND y