MySQL 历史数据归档实战：按时间分批搬迁，避免大表越查越慢

业务跑久以后，MySQL 热表里的历史数据会越来越多。订单表、日志表、流水表最常见：最近一个月的数据经常查，三年前的数据很少查，但它们还挤在同一张表里，占空间、拖慢统计、增加备份压力。

归档不是简单地把旧数据删掉。更稳的做法是：先设定归档边界，把旧数据按小批次搬到归档表，校验通过后再清理热表。本文用 orders 订单表举例，演示一套可落地的 MySQL 历史数据归档流程。

适合人群

适合负责 MySQL 表容量治理、订单流水归档、后台报表优化的开发者和 DBA。你需要了解基础 SQL、主键范围、索引和事务的概念。

目录

• 为什么不能直接全表搬迁
• 确定归档边界和索引条件
• 按主键分批写入归档表
• 校验通过后再清理热表
• 常见坑位和上线建议

为什么不能直接全表搬迁

很多人第一次做归档，会写一条大 SQL 把几百万行一次性搬走。这样风险很高：事务太大、锁时间太长、binlog 暴涨、主从延迟明显，甚至影响线上写入。

更可控的流程是把归档任务拆成小批次，每次只处理一个主键区间。失败时只需要重跑当前批次，不会让整张表长时间处于高压力状态。

下面这张图展示推荐链路：热表先按时间筛出旧数据，再按主键批量搬到归档表，做数量和金额校验，最后清理热表中的旧数据。

确定归档边界和索引条件

归档前先明确两个问题：

• 保留多久的热数据：例如热表只保留最近 180 天。
• 按哪个字段判断历史数据：通常用 created_at 或业务完成时间。

假设订单表结构简化如下：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_created_id (created_at, id)
);

这里建议保留 (created_at, id) 联合索引。归档任务会先用时间过滤，再按主键推进批次，这样可以减少无效扫描。

按主键分批写入归档表

归档表可以先用相同结构创建，再补充归档时间字段：

CREATE TABLE orders_archive LIKE orders;

ALTER TABLE orders_archive
  ADD COLUMN archived_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

正式搬迁时，不要一次性处理所有旧数据。可以先取一个批次的最大主键，再把这个区间写入归档表：

SELECT id
FROM orders
WHERE created_at 

假设当前批次边界是 last_id=10000、next_id=11000，可以这样写入归档表：

INSERT INTO orders_archive (id, user_id, amount, status, created_at)
SELECT id, user_id, amount, status, created_at
FROM orders
WHERE created_at  10000
  AND id 

真实任务里，批次大小可以从 500 或 1000 行开始观察，再根据延迟、锁等待和磁盘写入情况调整。

校验通过后再清理热表

写入归档表后，不要立刻删除热表数据。先做批次校验，至少校验行数和关键金额汇总：

SELECT COUNT(*) AS rows_count, SUM(amount) AS amount_sum
FROM orders
WHERE created_at  10000
  AND id  10000
  AND id 

只有两边结果一致，再清理热表旧数据：

DELETE FROM orders
WHERE created_at  10000
  AND id 

下面这张图展示上线时的检查顺序：先看批次大小，再看归档表数量，再看金额汇总，最后观察热表体积是否下降。

常见坑位和上线建议

第一，先做只读演练。 先跑统计和写入归档表，不急着删除热表数据，确认边界条件没有误伤。

第二，批次不要太大。 批次过大会放大锁等待和主从延迟。可以从小批次开始，根据监控逐步调大。

第三，归档表也要有查询索引。 如果归档表后续要按用户、订单状态、创建时间查询，就要补齐对应索引，否则只是把慢查询换了个地方。

第四，清理热表后关注空间回收。 行删除后，表空间不一定马上缩小。是否需要重建表，要结合 MySQL 版本、存储引擎和维护窗口评估。

第五，保留归档任务日志。 每个批次记录边界、行数、金额汇总和处理时间。后续追查数据问题时，这些记录比口头说明可靠得多。

总结

MySQL 历史数据归档的关键不是“搬走旧数据”，而是把风险拆小：确定时间边界，按主键分批写入归档表，做行数和金额校验，再小批量清理热表。

这套流程虽然比一条大 SQL 慢一些，但更适合线上系统。它能降低锁等待、减少主从延迟风险，也让每个批次都可核对、可重跑、可追踪。