数据归档与冷热分离实战：把 3 亿行历史表从主库搬出去

dbaai

数据归档与冷热分离实战：把 3 亿行历史表从主库搬出去

一、具体的问题

上周又碰到一个熟得不能再熟的场景。一张订单主表，3.24 亿行，单表加索引占了 881 GB，跑在 16C64G 的机器上。业务方抱怨三件事：

• 后台的订单列表页，翻到深一点就转圈，P95 到 1.9 秒；
  
• 每天凌晨的全备要跑 3 小时 10 分钟，早上八点半上班时备份还没收尾，磁盘 IO 被打满；
  
• 报表把主库拖慢，DBA 天天被叫去看监控。
  

我先做了一件事：把这 3.24 亿行按年份拆开统计，再看看访问日志里到底有多少查询是真需要老数据的。

结果很扎心——2024 年之前的数据只占全表 71%，却只承接了 4.3% 的查询量。其中真正带时间条件、能走到老数据上的查询，一天不到 300 次，而且基本都是财务对账和客服查历史订单。

问题从来不是"表太大"，而是冷数据留在了热路径上。归档要解决的，就是把这 71% 请出去，同时保证那 4.3% 的查询不至于查不到东西。

二、核心原理

归档和冷热分离经常被混着说，其实是两件事。归档是把数据从生产表挪到别处保存；冷热分离是在归档基础上，让应用按热度走不同的访问路径。做这件事之前，先把三个决策定死：

第一，边界怎么定。 边界不能只看时间，要"时间 + 状态"双条件。一条 2023 年的订单如果还在退款流程里，它就是热数据，不能归档。业界常用的判定是：created_at < 边界时间 AND status IN (已完结状态集合)。这个集合必须是闭区间，也就是这条数据在业务上已经不会再被修改。判断依据很简单——问业务："这条记录还可能被改吗？"如果答案是"可能"，就留在主库。

第二，去向放哪里。 三种选择，代价完全不同：

• 同库归档表（t_order_archive）：改动最小，应用加一个查询路由即可，风险低，但磁盘没省下来（同实例），只解决了单表体积和索引深度的问题。
  
• 独立归档库：主库空间真正释放，归档数据仍可在线 SQL 查询，是绝大多数场景的正解。代价是要维护第二个实例的连接与权限。
  
• 对象存储 / 外部表：成本最低，但查询要走 Parquet + 外部表引擎，只适合"一年查不了几次"的数据，不适合财务对账这种需要精确 SQL 的场景。
  

我的选择是第二种。原因很实在：那 300 次/天的查询虽然少，但都是不能出错的查询。

第三，访问路径怎么改。 要么应用层路由（写明"查历史走归档库"），要么数据库层联合视图（UNION ALL 把主表和归档表拼起来）。前者性能最好但要改代码；后者对应用透明，但要让优化器把条件正确下推，否则会退化成全表扫描。中小团队我一般推荐先上联合视图兜住"能查到"，再逐步把高频查询改成双路查询。

还有一个绕不开的取舍：分区表 vs 分批迁移。如果表还在快速增长、且业务允许停机改造，直接上原生分区（按月/按季度），归档就退化成一个 DROP PARTITION 或 EXCHANGE PARTITION，成本极低。但如果表已经 3 亿行、线上不能停，重建成分区表要重写全表，不现实。这时候老老实实做分批迁移。

分批迁移的核心是三步分离：先复制到归档表，再校验一致，最后才删除源数据。任何一步都不能合并。我见过太多人直接写一条 DELETE FROM t_order WHERE created_at < '2024-01-01'，然后事务日志打满、主从延迟爆掉、回滚又要跑两个小时。

三、实例参考（动手步骤）

下面这套流程是在 MySQL 8.0 上跑通的，PostgreSQL / SQL Server 的差异点我在步骤里单独标注。

步骤 1：先把家底量出来，别靠猜

2. -- 分年份统计行数、数据空间、索引空间、平均行长
3. SELECT
4.   YEAR(created_at)                                    AS yr,
5.   COUNT(*)                                            AS rows_cnt,
6.   ROUND(SUM(data_length)/1024/1024/1024, 2)           AS data_gb,
7.   ROUND(SUM(index_length)/1024/1024/1024, 2)          AS index_gb,
8.   ROUND(SUM(data_length)/COUNT(*), 1)                 AS avg_row_bytes
9. FROM information_schema.tables t
10. JOIN t_order o ON t.table_name = 't_order'
11. WHERE t.table_schema = 'shop'
12. GROUP BY YEAR(created_at)
13. ORDER BY yr;

复制代码

统计口径要以 information_schema.tables 为准，不要用 SHOW TABLE STATUS 的估算值——InnoDB 的行数估算是采样出来的，偏差可以到 40%。

再花十分钟翻一遍慢查询，确认带时间条件的语句能不能落到老数据上：

2. SELECT COUNT(*) AS slow_cnt,
3.        SUM(query_sample_text LIKE '%created_at < %') AS with_time_pred
4. FROM performance_schema.events_statements_summary_by_digest
5. WHERE avg_timer_wait > 500000000;   -- 平均超过 0.5s

复制代码

这一步做完，你应该能拿出一句话结论：「X 年前的数据占 Y% 体积、承接 Z% 查询」。有了它，后面跟业务对齐边界才有底气。

步骤 2：把归档边界写成可执行的条件

2. -- 归档判定条件（唯一一份，应用与作业共用）
3. -- 边界：2024-01-01 之前，且状态已终态（4=已完成 5=已取消）
4. --   created_at < '2024-01-01 00:00:00'
5. --   AND status IN (4, 5)
6. --   AND updated_at < '2024-06-01'        -- 终态后 6 个月未再变更，兜住反向流程
7. SELECT COUNT(*) FROM t_order
8. WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01';

复制代码

updated_at 这个条件是我吃了亏才加上的。曾经有一批"已完成"的订单，半年后又被补偿流程改成了"退款中"，结果归档表里是旧状态，联合视图查出来两个版本不一致。加了"终态后再静置 N 个月"这道闸，就不会出现这种反向变更。

步骤 3：建归档表，结构对齐但不要外键

2. CREATE TABLE t_order_archive (
3.   -- 与源表列定义完全一致（避免类型隐式转换）
4.   id            BIGINT       NOT NULL,
5.   order_no      VARCHAR(32)  NOT NULL,
6.   user_id       BIGINT       NOT NULL,
7.   amount        DECIMAL(12,2) NOT NULL,
8.   status        TINYINT      NOT NULL,
9.   created_at    DATETIME     NOT NULL,
10.   updated_at    DATETIME     NOT NULL,
11.   -- 归档专用列
12.   archive_batch BIGINT       NOT NULL COMMENT '归档批次号',
13.   archived_at   DATETIME     NOT NULL COMMENT '入库时间',
14.   PRIMARY KEY (id),                       -- 保留与源表一致的主键，便于幂等重跑
15.   KEY idx_user_created (user_id, created_at),
16.   KEY idx_created (created_at),
17.   KEY idx_batch (archive_batch)
18. ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
20. -- PostgreSQL：CREATE TABLE ... (LIKE t_order INCLUDING DEFAULTS)，压缩用 ALTER TABLE SET
21. -- SQL Server：先 CREATE TABLE 同构，再考虑压缩页 WITH (DATA_COMPRESSION = PAGE)

复制代码

归档表要保留主键，这是幂等重跑的前提。用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 就能随时中断随时重跑，不用怕重复。

步骤 4：分批搬数据，每批都留痕

2. -- 一个批次搬 20000 行，可重复执行；batch 号每次递增
3. SET @batch := UNIX_TIMESTAMP();
4. SET @batch_rows := 0;
6. REPEAT
7.   INSERT IGNORE INTO t_order_archive
8.     (id, order_no, user_id, amount, status, created_at, updated_at, archive_batch, archived_at)
9.   SELECT o.id, o.order_no, o.user_id, o.amount, o.status, o.created_at, o.updated_at, @batch, NOW()
10.   FROM t_order o
11.   WHERE o.created_at < '2024-01-01'
12.     AND o.status IN (4,5)
13.     AND o.updated_at < '2024-06-01'
14.     AND NOT EXISTS (SELECT 1 FROM t_order_archive a WHERE a.id = o.id)
15.   ORDER BY o.id
16.   LIMIT 20000;
18.   SET @batch_rows := ROW_COUNT();
19.   SELECT @batch, @batch_rows;      -- 落日志，断点续跑靠它
20. UNTIL @batch_rows = 0 END REPEAT;

复制代码

ORDER BY o.id LIMIT 20000 配 NOT EXISTS 的组合，比游标式 WHERE id > @last_id 更省事，代价是每批都要回查归档表。数据量在千万级以内这个代价可以接受；上亿行建议换成游标推进：

2. -- 游标推进版：依赖归档表主键有序，速度快得多
3. SELECT MAX(id) INTO @last_id FROM t_order
4. WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01';
6. -- 循环：每次取 id > @last_id 的 20000 行，插入归档表，然后 @last_id = 本批最大 id

复制代码

PostgreSQL 用 DELETE ... RETURNING 可以一条语句搬走并返回，但必须显式分批，否则长事务会把 WAL 和 autovacuum 压力顶上去：

2. WITH moved AS (
3.   SELECT id FROM t_order
4.   WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01'
5.   ORDER BY id LIMIT 20000
6. )
7. INSERT INTO t_order_archive
8. SELECT o.*, extract(epoch from now())::bigint, now()
9. FROM t_order o JOIN moved m ON o.id = m.id;
11. DELETE FROM t_order o USING moved m WHERE o.id = m.id;

复制代码

每批之间脚本体里 sleep 0.3，给主从复制留出追赶时间。别小看这 300 毫秒，它是"归档期间主从不断链"的关键。

步骤 5：校验一致才允许删

2. -- 主表待删集合与归档表的行数必须相等
3. SELECT
4.   (SELECT COUNT(*) FROM t_order
5.     WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01') AS src_rows,
6.   (SELECT COUNT(*) FROM t_order_archive)                                                AS arc_rows;
8. -- 再抽 100 笔做金额校验和（聚合校验比逐行比对便宜得多）
9. SELECT SUM(amount), COUNT(*) FROM t_order
10. WHERE id IN (SELECT id FROM t_order_archive ORDER BY id DESC LIMIT 100);
11. SELECT SUM(amount), COUNT(*) FROM t_order_archive ORDER BY id DESC LIMIT 100;

复制代码

两个数字对不上就停下查原因，绝对不要抱着"差不多"的心态开始删。上一个同事就是因为归档表少了一万行，删完才发现，最后只能从备份里捞。

步骤 6：分批删除，控制 undo 与 binlog

2. -- 用主键推进，避免大事务
3. -- 循环体：每次删 5000 行，删完 sleep 0.2
4. DELETE FROM t_order
5. WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01'
6. ORDER BY id
7. LIMIT 5000;

复制代码

MySQL 8.0 建议同时限制 binlog_row_image = MINIMAL，能省下大量 binlog 体积。SQL Server 上对应的做法是把 DELETE TOP (5000) 放进 WHILE 循环，并留意日志文件的自动增长——如果 log_reuse_wait_desc 一直是 LOG_BACKUP，说明你该做一次日志备份了。

步骤 7：回收空间

删完只是逻辑释放，物理文件不会自动变小，这一步不做前面全白干。

2. -- MySQL 8.0：先查碎片率
3. SELECT table_name, ROUND(data_free/1024/1024,1) AS free_mb
4. FROM information_schema.tables
5. WHERE table_schema='shop' AND table_name='t_order';
7. -- 在线整理（8.0 支持 ALTER ... ALGORITHM=INPLACE 的写法更快，但大表仍建议低峰执行）
8. OPTIMIZE TABLE t_order;
10. -- PostgreSQL：不要用 VACUUM FULL（排他锁），用 pg_repack
11. -- pg_repack -h 127.0.0.1 -d shop -t t_order --no-superuser-check
13. -- SQL Server：若已分区，用 SWITCH 秒级把老分区挪走
14. ALTER TABLE t_order SWITCH PARTITION 3 TO t_order_archive_part3;
15. -- Oracle：ALTER TABLE t_order DROP PARTITION p2023 UPDATE GLOBAL INDEXES;

复制代码

步骤 8：把访问路径补上，别让业务方查不到

最省事的做法是建联合视图，对应用透明：

2. CREATE OR REPLACE VIEW v_order_all AS
3. SELECT id, order_no, user_id, amount, status, created_at, updated_at, 'hot' AS src FROM t_order
4. UNION ALL
5. SELECT id, order_no, user_id, amount, status, created_at, updated_at, 'arc' AS src FROM t_order_archive;

复制代码

注意：MySQL 的 UNION ALL 并不会自动裁剪分支。查询里必须显式带上能下推的条件，比如查历史订单时带上 created_at < '2024-01-01'，优化器才会只扫归档表分支。如果时间条件是个变量、优化器抽不到，就会两边全扫——比归档前更慢。解决办法是应用层改成双路查询：

2. -- 双路查询：先按时间判断走哪张表，代码里路由
3. -- if (queryEnd < '2024-01-01') { sql = "... FROM t_order_archive WHERE ..." }
4. -- else { sql = "... FROM t_order WHERE ..." }

复制代码

这一步做完，再给归档库配上独立的只读账号，把财务和客服的查询直接指过去，主库就彻底清净了。

步骤 9：挂上定时作业，让它自己跑

归档不是一次性的活。写成一个每月 1 号凌晨的作业：自动按"上月边界 + 静置期"算出条件 → 分批搬 → 校验 → 分批删 → 记录批次与行数到 ops_archive_log。作业里必须带三个保护：单次运行最多归档 N 行（防止边界写错搬走整表）、校验不过自动告警退出、每批之间限速。

治理前后的对比：

指标	归档前	归档后
主表行数	3.24 亿	4100 万
主表含索引空间	881 GB	118 GB
订单列表分页 P95	1.9 s	140 ms
每日全备耗时	3 h 10 min	38 min
凌晨备份期间磁盘 IO 峰值	92%	41%
归档数据可查性	全量在线	归档库在线可查（财务/客服直连）
历史数据承接的查询	与热数据混跑	走独立只读库，不影响主库

四、实操检查清单

• [ ] 按年份统计了行数、数据空间、索引空间，拿到"X 年前占 Y% 体积、承接 Z% 查询"的结论
  
• [ ] 归档边界是"时间 + 状态 + 静置期"三条件，且写成唯一一份 SQL，作业与应用共用
  
• [ ] 确认边界内的数据在业务上不会再被修改（已跟业务方书面确认）
  
• [ ] 归档表列定义与源表逐列对齐，保留主键，加 archive_batch / archived_at 便于追溯与重跑
  
• [ ] 搬数据是分批的（每批 ≤ 2 万行），批间有 200~500ms 限速，作业可中断可续跑
  
• [ ] 删除之前做过行数与校验和比对，两个数字完全一致
  
• [ ] 删除也是分批的（每批 ≤ 5000 行），单批事务不超 1 秒，binlog 与 undo 无暴涨
  
• [ ] 归档期间监控过主从延迟，延迟未超过告警阈值
  
• [ ] 空间回收选了低峰窗口执行，PG 用 pg_repack、SQL Server/Oracle 用分区 SWITCH/DROP
  
• [ ] 访问路径已落地：联合视图或双路查询，且验证过历史订单能查到
  
• [ ] 归档库有独立只读账号，财务、客服、报表的查询已切过去
  
• [ ] 定时作业已挂上，含单次上限、校验失败告警、批间限速三重保护
  
• [ ] 归档记录写入了 ops_archive_log（批次号、行数、边界、耗时、执行人）
  
• [ ] 归档库的备份策略已单独确认（归档数据也要能恢复）
  

几个容易踩的坑

坑一：先删后建，中间挂了。 数据既不在主表也不在归档表，只能从备份捞。顺序永远是"先复制、再校验、最后删"。

坑二：只按 created_at 定边界，没考虑业务改单。 老订单被补偿流程改状态后两边数据不一致，加一道静置期条件基本能兜住。

坑三：UNION ALL 视图当万能药。 条件不下推时它比不建更慢，两边都要扫。上线前必须看 EXPLAIN，确认访问计划里只出现一张表。

坑四：归档表没压缩，删完没回收空间。 前者让空间白省不下来，后者让磁盘占用一行不少——ROW_FORMAT=COMPRESSED 加 OPTIMIZE / pg_repack，两件都要做。

归档这件事，技术难度不高，难的是边界判断和过程可控。把这三步拆开（复制、校验、删除），每一步都能中断、能重跑、能对账，剩下的就只是耐心跑完它。