MySQL 数据归档实战指南：从中小表到大表的全场景方案

MySQL 数据归档实战指南：从中小表到大表的全场景方案

线上表越来越大，查询变慢、备份变久、磁盘告急——归档几乎是每个跑过 3 年以上的业务都会遇到的问题。
本文按 数据量级、业务约束、表结构 逐步展开，给出可落地的归档方案，并澄清常见误区（DELETE 不缩表、何时需要触发器、如何做部分保留等）。

---

目录

1. 归档要解决什么问题
2. 先定策略：四类决策
3. 数据量维度：四档场景与选型
4. 方案一：分批 DELETE + 归档库（中小表）
5. 方案二：分区表 + EXCHANGE / DROP PARTITION（大表首选）
6. 方案三：热数据重建 + 双表 RENAME（超大单表瘦身）
7. 方案四：整表轮换 RENAME（按月一张表）
8. 部分保留：同一条件下只归档一部分
9. 数据正确性保障体系
10. 迁移窗口内的增量同步：何时需要触发器
11. 空间回收：DELETE 之后还要做什么
12. 运维配套：任务表、监控、回滚
13. 方案选型速查表
14. 总结

---

1. 归档要解决什么问题

归档不是简单的「把旧数据挪走」，通常要同时满足：

目标	说明
在线表变小	热查询只扫近期数据，索引更小、缓存命中率更高
冷数据可查	客服、审计、纠纷仍能通过 ID / 时间查到历史
可恢复	归档失败可重跑，误删可找回
对业务影响小	尽量不停写，锁表窗口可控
磁盘真释放	逻辑删行 ≠ 文件缩小（后文详述）

常见误区：

• ❌ 以为 DELETE 旧数据后在线表文件会变小
• ❌ 表还在写就要给热表挂永久触发器做归档
• ❌ 用裸 LIMIT 表示「部分归档」
• ❌ 先删源表再插归档库

---

2. 先定策略：四类决策

动手写脚本前，先回答四个问题：

2.1 归档边界（什么数据算「冷」）

-- 典型组合
WHERE status IN ('CLOSED', 'CANCELLED')
  AND create_time < @cutoff   -- 如 90 天前
  AND keep_flag = 0

• 任务开始时 固定 cutoff，整次任务不变
• 优先归档 业务上已封闭 的数据（已关单、已结算），避免冷数据仍被 UPDATE

2.2 归档去向（放哪里）

去向	适用
归档库（同结构表）	仍需 SQL 查询历史
备份表（RENAME 留下的整表）	表级瘦身、少查冷数据
文件（CSV / Parquet）+ 对象存储	极少查询、成本优先
按月分表	天然按时间隔离

2.3 在线表是否还要保留冷数据

• 全量迁出：冷数据只存在于归档侧
• 部分保留：满足冷条件但仍留在线（VIP、大额、keep_flag）——见 第 8 节

2.4 可接受的切换窗口

窗口	可选方案
无停写	分批 DELETE、分区 DROP、gh-ost 类在线重建
秒级～分钟级停写	热数据重建 + 追增量 + RENAME
可维护窗口	OPTIMIZE、整表导出导入

---

3. 数据量维度：四档场景与选型

数据量 / 特征                    推荐路径
─────────────────────────────────────────────────────────
S  < 500 万、非分区、日增可控      → 分批 DELETE + 归档库
M  500 万～5000 万、可改表结构     → 时间分区 + EXCHANGE/DROP
L  单表亿级、短期难分区           → 热数据重建 + 双表 RENAME
XL 按月暴涨、整月可封闭           → 整表 RENAME 轮换

3.1 渐进式演进（推荐路径）

很多团队不是一步到位，而是：

阶段 1：S 档 — 脚本分批 DELETE + 归档库（快速上线）
阶段 2：M 档 — 在线表加分区，新方案切分区归档
阶段 3：L 档 — 历史膨胀后做一次 RENAME 瘦身，之后走分区

不必等「完美架构」才做归档；先止住在线表增长，再优化手段。

---

4. 方案一：分批 DELETE + 归档库（中小表）

4.1 适用

• 表 < 500 万行，或 DELETE 一批在可接受时间内完成
• 尚未分区，改造成本可接受
• 需要冷数据在归档库可查

4.2 基本流程

定 cutoff → 分批选取 → INSERT 归档库 → 校验 → DELETE 源表 → 记日志 → 全局对账

4.3 核心 SQL（同事务、同条件）

START TRANSACTION;

INSERT INTO archive_db.orders_archive
  (id, user_id, amount, status, create_time, archive_batch_id)
SELECT id, user_id, amount, status, create_time, '20240818_001'
FROM prod_db.orders
WHERE status = 'CLOSED'
  AND create_time < '2024-05-01 00:00:00'
  AND keep_flag = 0
  AND id > 1000000 AND id <= 1010000;

DELETE FROM prod_db.orders
WHERE status = 'CLOSED'
  AND create_time < '2024-05-01 00:00:00'
  AND keep_flag = 0
  AND id > 1000000 AND id <= 1010000;

COMMIT;

4.4 持续写入时为何不需要触发器

• 新数据 create_time >= cutoff → 不在 WHERE 内，不会被碰
• 本批 INSERT 与 DELETE 条件一致，同事务内 InnoDB 行锁保证一致
• 不需要 给热表挂永久触发器

4.5 注意

• 单批不宜过大（建议 5k～5w 行），避免长事务、大 binlog
• DELETE 后 文件未必缩小——见 第 11 节
• 归档表对 id 建 唯一键，任务可幂等重跑

---

5. 方案二：分区表 + EXCHANGE / DROP PARTITION（大表首选）

5.1 适用

• 按时间访问明显（订单、日志、流水）
• 可接受一次 DDL 加分区（或新建分区表迁移）
• 希望 删冷数据 = 真释放空间

5.2 分区设计示例

CREATE TABLE orders (
  id BIGINT NOT NULL,
  create_time DATETIME NOT NULL,
  ...
  PRIMARY KEY (id, create_time)  -- 分区键必须进主键/唯一键
) PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  ...
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

5.3 归档方式 A：交换分区（冷数据进归档库，可查询）

CREATE TABLE orders_archive_202401 LIKE orders;

ALTER TABLE orders
  EXCHANGE PARTITION p202401
  WITH TABLE orders_archive_202401;

• 元数据级交换，秒级
• orders_archive_202401 获得整月数据；在线表该分区为空

5.4 归档方式 B：直接 DROP（不需再查）

ALTER TABLE orders DROP PARTITION p202401;

• 空间回收最彻底
• 不可逆，务必先备份或先 EXCHANGE 到归档表

5.5 正确性要点

• 只 DROP 已封闭月份（如 2 月 1 日后才 DROP 1 月分区）
• EXCHANGE 前校验分区行数；交换后归档表行数应一致
• 分区表与归档表 结构、索引、约束一致

5.6 与持续写入

当月分区持续写入；历史分区只读封闭 → 天然无 UPDATE 同步问题，无需触发器。

---

6. 方案三：热数据重建 + 双表 RENAME（超大单表瘦身）

6.1 适用

• 单表亿级，历史上 未分区，DELETE 无法让文件变小
• 在线表只需保留热数据，冷数据整表留存即可
• 可接受一次 短切换窗口（或迁移期临时触发器）

6.2 思路（你描述的方案）

1. 把「要保留的热数据」复制到 orders_temp
2. RENAME orders        → orders_backup_20240818   （整表变备份，含全量快照）
3. RENAME orders_temp   → orders                   （新活动表，仅热数据）

结果：

表	内容
orders（新）	仅热数据，文件小
orders_backup_20240818	切换时刻全量（热+冷）

冷数据在备份表中；新活动表不再承载冷行。这是 表级归档，比 DELETE 更利于 在线表物理变小。

6.3 阶段一：不停机复制热数据

CREATE TABLE orders_temp LIKE orders;

-- 分批复制，避免长事务
INSERT INTO orders_temp
SELECT * FROM orders
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
   OR status NOT IN ('CLOSED', 'CANCELLED')
   OR keep_flag = 1;
-- 按 id 分批 + sleep

记录 copy_start_time 和已复制 max_id。

6.4 阶段二：追增量 + 原子切换

选项 A：短暂停写（推荐，简单可靠）

-- 应用停写或只读
LOCK TABLES orders WRITE;

INSERT INTO orders_temp
SELECT * FROM orders
WHERE (create_time >= ... OR status NOT IN (...) OR keep_flag = 1)
  AND (id > @max_copied_id OR updated_at >= @copy_start_time)
ON DUPLICATE KEY UPDATE
  user_id = VALUES(user_id),
  amount = VALUES(amount),
  status = VALUES(status),
  updated_at = VALUES(updated_at);

UNLOCK TABLES;

RENAME TABLE
  orders TO orders_backup_20240818,
  orders_temp TO orders;

-- 校正自增，避免新 id 与备份表冲突
SET @next_ai = (SELECT MAX(id) + 1 FROM orders_backup_20240818);
SET @sql = CONCAT('ALTER TABLE orders AUTO_INCREMENT = ', @next_ai);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

选项 B：迁移期临时触发器（不能停写时）

在阶段一复制期间，对 orders 挂 临时 触发器，把 INSERT/UPDATE/DELETE 同步到 orders_temp；切换前校验一致、删除触发器、再 RENAME。详见 第 10 节。

6.5 切换后

• 不需要 永久触发器——只有一个活动表 orders
• orders_backup_* 可迁归档库、压缩存储，或确认无查询后 DROP
• 备份表里热数据有 冗余副本（全量快照），属正常

6.6 与 gh-ost / pt-osc

在线改表工具本质也是：建新表 → 复制 + 追增量（触发器或 binlog）→ RENAME 切换。本方案是同一模式的手动版。

---

7. 方案四：整表 RENAME 轮换（按月一张表）

7.1 适用

• 业务天然按月分表，或表名带月份 orders_202401
• 每月整表「下线」，无部分行归档

7.2 流程

-- 月初：新表已建好 orders_202402
RENAME TABLE
  orders_202401 TO orders_archive_202401,
  orders_202402 TO orders;           -- 或应用改指向新表名

• 元数据操作，极快
• 旧表整表保留或 DROP

7.3 注意

• 应用路由或表名策略要统一
• 跨月查询需扫多表或汇总视图

---

8. 部分保留：同一条件下只归档一部分

定义： 满足冷数据条件（如 90 天前且已关单）的行里，只搬走一部分，其余继续留在线表。

8.1 条件拆分

归档集合 A = status='CLOSED' AND create_time < cutoff
实际搬走   B = A AND NOT 保留规则 R
仍留在线   = A AND R

WHERE status = 'CLOSED'
  AND create_time < @cutoff
  AND keep_flag = 0
  AND user_id NOT IN (SELECT user_id FROM vip_users)  -- 示例
  AND amount < 100000

8.2 实现方式

方式	说明
keep_flag	关单时按 VIP/金额写入，脚本只动 keep_flag=0
白名单表	NOT EXISTS (archive_keep_orders)
稳定比例	MOD(CRC32(CONCAT(id,'salt')),100) < 80 约 80% 归档（salt 固定）

8.3 不要用裸 LIMIT 表示「留一部分」

-- ❌ 危险：每批删哪些行不确定，无法对账重跑
DELETE FROM orders WHERE ... LIMIT 5000;

限量应用 主键游标 id > @last_id ORDER BY id LIMIT N，保留语义 仍由 keep_flag 等表达。

8.4 对账

-- 应归档且未保留：任务结束后源表应为 0
SELECT COUNT(*) FROM orders
WHERE status='CLOSED' AND create_time < @cutoff AND keep_flag = 0;

-- 应保留：仍在源表
SELECT COUNT(*) FROM orders
WHERE status='CLOSED' AND create_time < @cutoff AND keep_flag = 1;

-- 主键无交集
SELECT COUNT(*) FROM orders o INNER JOIN orders_archive a ON o.id = a.id;

---

9. 数据正确性保障体系

9.1 四个维度

维度	手段
不丢	先 INSERT 归档再 DELETE；禁止先删后插
不重	归档表 id 唯一；幂等 INSERT IGNORE / 按 batch 校验
不错	行数 + 校验和（COUNT/SUM 或关键字段 CRC）
可恢复	archive_job_log + 备份表/分区可回导

9.2 每批门控（脚本内）

1. 计算本批 source_count、checksum_source
2. INSERT 归档
3. 计算 archive_count、checksum_archive
4. 不一致 → ROLLBACK / 不 DELETE、告警
5. 一致 → DELETE 或 COMMIT
6. 写入 job_log

9.3 全局收尾对账

• 源表：应归档条件行数 = 0
• 归档表：同条件行数 = 历史累计
• 源表与归档表 主键交集 = 0
• 子表（订单明细）按 order_id 同步归档或对账

9.4 并发 UPDATE

场景	处理
分批 DELETE 归档	同事务 + 同 WHERE + 可选 FOR UPDATE
热数据 RENAME 切换	仅 迁移窗口 追增量，切换后单表
分区封闭后归档	历史分区无写，无问题
冷数据业务禁止修改	最强约束，优先采用

---

10. 迁移窗口内的增量同步：何时需要触发器

10.1 结论一览

阶段	是否需要触发器
分批 INSERT 归档 + DELETE 源表	否
分区 EXCHANGE / DROP	否
RENAME 切换 完成之后	否
热数据复制到 orders_temp 期间（长耗时）	视情况：停写追增量 优先；不能停写可用 临时触发器 或 gh-ost

10.2 临时触发器示例（仅迁移期）

CREATE TRIGGER tr_orders_ins_sync
AFTER INSERT ON orders FOR EACH ROW
BEGIN
  IF NEW.create_time >= DATE_SUB(NOW(), INTERVAL 90 DAY)
     OR NEW.status NOT IN ('CLOSED','CANCELLED')
     OR NEW.keep_flag = 1 THEN
    INSERT INTO orders_temp VALUES (...);
  END IF;
END;

-- UPDATE / DELETE 同理；切换前 DROP TRIGGER，再 RENAME

缺点： 热表每笔写放大；与批量复制叠加时负载高。
替代： 短停写 + ON DUPLICATE KEY UPDATE 追增量；或 binlog CDC（长期双写镜像场景）。

10.3 「复制到 staging 后长期双存在」才需要持续同步

若流程是「复制 → RENAME staging → 很久以后才删源表」，则存在双份且源表仍 UPDATE——这是 镜像 问题，应用 CDC 优于永久触发器。
推荐改流程： 复制 → 校验 → 删源（或整表 RENAME 一切换）→ 结束双存在。

---

11. 空间回收：DELETE 之后还要做什么

11.1 InnoDB 行为

• DELETE：逻辑删除，表文件通常不缩小（空洞可给同表 INSERT 复用）
• 还给操作系统：需 DROP 分区/表 或 重建表

11.2 各方案的空间效果

方案	在线表文件
分批 DELETE	往往不变，需 OPTIMIZE / 换表
DROP PARTITION	明显缩小
热数据 RENAME 换新表	新表小文件
整表 RENAME 轮换	在线表始终新文件

11.3 非分区表 DELETE 后的回收

-- 低峰执行，大表会锁表或耗时很长
OPTIMIZE TABLE orders;
-- 或 ALTER TABLE orders ENGINE=InnoDB;

大表可用 gh-ost 做在线重建。分区表优先 DROP PARTITION，避免全表 OPTIMIZE。

---

12. 运维配套：任务表、监控、回滚

12.1 任务日志表

CREATE TABLE archive_job_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  job_name VARCHAR(64) NOT NULL,
  batch_id VARCHAR(32) NOT NULL,
  cutoff_time DATETIME NOT NULL,
  id_range_start BIGINT,
  id_range_end BIGINT,
  source_count INT,
  archive_count INT,
  checksum_source VARCHAR(64),
  checksum_archive VARCHAR(64),
  status ENUM('running','success','failed') NOT NULL,
  error_msg TEXT,
  started_at DATETIME NOT NULL,
  finished_at DATETIME,
  UNIQUE KEY uk_batch (job_name, batch_id)
);

12.2 监控告警

• 单批 source_count != archive_count
• 全局对账：源表遗漏、主键交集 > 0
• 任务超时、连续 0 行（条件错误）
• 主从延迟超阈值时暂停 DELETE

12.3 回滚思路

方案	回滚
归档库 + DELETE	从归档表 INSERT 回源表（注意幂等）
EXCHANGE	再 EXCHANGE 回去
RENAME 切换	再 RENAME 换回（需未对新表写入或先冻结）
DROP PARTITION	不可回滚，必须先备份或 EXCHANGE 到归档表

12.4 从库策略

• 读压力大的校验可在从库做 COUNT/CHECKSUM
• DELETE 仍在主库执行，关注 binlog 与复制延迟

---

13. 方案选型速查表

场景	数据量	推荐方案	停写	空间回收	触发器
历史可查、表不大	< 500 万	分批 DELETE + 归档库	否	需 OPTIMIZE	否
时间明显、可分区	500 万+	EXCHANGE / DROP PARTITION	否	好	否
亿级单表、DELETE 不缩表	亿级	热数据 RENAME 切换	短窗口	很好	仅迁移期可选
按月整表封闭	任意	整表 RENAME 轮换	否	很好	否
冷数据极少查	大	DROP PARTITION 或导出 OSS	否	最好	否
部分保留（VIP 等）	任意	在上述方案上加 keep 条件	同左	同左	同左

---

14. 总结

1. 归档边界 先于脚本：cutoff、状态、keep_flag，任务内固定 cutoff。
2. 按数据量选型：中小表分批搬迁；大表分区；超大单表热数据 RENAME；按月整表轮换。
3. 正确性 靠同事务同条件、每批门控、全局对账、幂等设计，而不是永久触发器。
4. DELETE 不缩表；要真释放空间用 DROP PARTITION、新表 RENAME 或 OPTIMIZE。
5. 热数据重建 + 双表 RENAME 是亿级单表瘦身的利器；触发器只用于 迁移窗口追增量，切换完即删。
6. 部分保留 用 keep_flag / 白名单表达，不用裸 LIMIT。

归档没有银弹，但路径清晰：先止住在线表膨胀，再按量级升级到分区或 RENAME。把边界、校验、日志做扎实，比追求一次性完美架构更重要。

---

附录：伪代码 — 分批归档任务

def archive_orders(cutoff: str, batch_size: int = 5000):
    if job_already_running("orders_archive"):
        return

    last_id = 0
    batch_no = 0

    while True:
        batch_no += 1
        batch_id = f"{date.today()}_{batch_no:03d}"

        with db.transaction():
            rows = db.query(
                "SELECT id, ... FROM orders WHERE ... AND id > %s ORDER BY id LIMIT %s",
                (last_id, batch_size)
            )
            if not rows:
                break

            ids = [r.id for r in rows]
            checksum_src = checksum(rows)

            db.execute("INSERT INTO archive.orders_archive ...", rows)
            checksum_arc = db.query_one(
                "SELECT COUNT(*), SUM(...) FROM archive.orders_archive WHERE batch_id = %s",
                (batch_id,)
            )

            if not verify(len(rows), checksum_src, checksum_arc):
                raise ArchiveError("batch verify failed")

            db.execute(
                "DELETE FROM orders WHERE id IN (%s)",
                (ids,)
            )

            log_success(batch_id, len(rows), checksum_src)
            last_id = max(ids)

        sleep(0.1)  # 降低主库压力

    reconcile_global(cutoff)