数据归档与冷热分离实战：把 3 亿行历史表从主库搬出去
数据归档与冷热分离实战：把 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：先把家底量出来，别靠猜
-- 分年份统计行数、数据空间、索引空间、平均行长
SELECT
YEAR(created_at) AS yr,
COUNT(*) AS rows_cnt,
ROUND(SUM(data_length)/1024/1024/1024, 2) AS data_gb,
ROUND(SUM(index_length)/1024/1024/1024, 2) AS index_gb,
ROUND(SUM(data_length)/COUNT(*), 1) AS avg_row_bytes
FROM information_schema.tables t
JOIN t_order o ON t.table_name = 't_order'
WHERE t.table_schema = 'shop'
GROUP BY YEAR(created_at)
ORDER BY yr;
统计口径要以 information_schema.tables 为准，不要用 SHOW TABLE STATUS 的估算值——InnoDB 的行数估算是采样出来的，偏差可以到 40%。
再花十分钟翻一遍慢查询，确认带时间条件的语句能不能落到老数据上：
SELECT COUNT(*) AS slow_cnt,
SUM(query_sample_text LIKE '%created_at < %') AS with_time_pred
FROM performance_schema.events_statements_summary_by_digest
WHERE avg_timer_wait > 500000000; -- 平均超过 0.5s
这一步做完，你应该能拿出一句话结论：「X 年前的数据占 Y% 体积、承接 Z% 查询」。有了它，后面跟业务对齐边界才有底气。
步骤 2：把归档边界写成可执行的条件
-- 归档判定条件（唯一一份，应用与作业共用）
-- 边界：2024-01-01 之前，且状态已终态（4=已完成 5=已取消）
-- created_at < '2024-01-01 00:00:00'
-- AND status IN (4, 5)
-- AND updated_at < '2024-06-01' -- 终态后 6 个月未再变更，兜住反向流程
SELECT COUNT(*) FROM t_order
WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01';
updated_at 这个条件是我吃了亏才加上的。曾经有一批"已完成"的订单，半年后又被补偿流程改成了"退款中"，结果归档表里是旧状态，联合视图查出来两个版本不一致。加了"终态后再静置 N 个月"这道闸，就不会出现这种反向变更。
步骤 3：建归档表，结构对齐但不要外键
CREATE TABLE t_order_archive (
-- 与源表列定义完全一致（避免类型隐式转换）
id BIGINT NOT NULL,
order_no VARCHAR(32)NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status TINYINT NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
-- 归档专用列
archive_batch BIGINT NOT NULL COMMENT '归档批次号',
archived_at DATETIME NOT NULL COMMENT '入库时间',
PRIMARY KEY (id), -- 保留与源表一致的主键，便于幂等重跑
KEY idx_user_created (user_id, created_at),
KEY idx_created (created_at),
KEY idx_batch (archive_batch)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
-- PostgreSQL：CREATE TABLE ... (LIKE t_order INCLUDING DEFAULTS)，压缩用 ALTER TABLE SET
-- SQL Server：先 CREATE TABLE 同构，再考虑压缩页 WITH (DATA_COMPRESSION = PAGE)
归档表要保留主键，这是幂等重跑的前提。用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 就能随时中断随时重跑，不用怕重复。
步骤 4：分批搬数据，每批都留痕
-- 一个批次搬 20000 行，可重复执行；batch 号每次递增
SET @batch := UNIX_TIMESTAMP();
SET @batch_rows := 0;
REPEAT
INSERT IGNORE INTO t_order_archive
(id, order_no, user_id, amount, status, created_at, updated_at, archive_batch, archived_at)
SELECT o.id, o.order_no, o.user_id, o.amount, o.status, o.created_at, o.updated_at, @batch, NOW()
FROM t_order o
WHERE o.created_at < '2024-01-01'
AND o.status IN (4,5)
AND o.updated_at < '2024-06-01'
AND NOT EXISTS (SELECT 1 FROM t_order_archive a WHERE a.id = o.id)
ORDER BY o.id
LIMIT 20000;
SET @batch_rows := ROW_COUNT();
SELECT @batch, @batch_rows; -- 落日志，断点续跑靠它
UNTIL @batch_rows = 0 END REPEAT;
ORDER BY o.id LIMIT 20000 配 NOT EXISTS 的组合，比游标式 WHERE id > @last_id 更省事，代价是每批都要回查归档表。数据量在千万级以内这个代价可以接受；上亿行建议换成游标推进：
-- 游标推进版：依赖归档表主键有序，速度快得多
SELECT MAX(id) INTO @last_id FROM t_order
WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01';
-- 循环：每次取 id > @last_id 的 20000 行，插入归档表，然后 @last_id = 本批最大 id
PostgreSQL 用 DELETE ... RETURNING 可以一条语句搬走并返回，但必须显式分批，否则长事务会把 WAL 和 autovacuum 压力顶上去：
WITH moved AS (
SELECT id FROM t_order
WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01'
ORDER BY id LIMIT 20000
)
INSERT INTO t_order_archive
SELECT o.*, extract(epoch from now())::bigint, now()
FROM t_order o JOIN moved m ON o.id = m.id;
DELETE FROM t_order o USING moved m WHERE o.id = m.id;
每批之间脚本体里 sleep 0.3，给主从复制留出追赶时间。别小看这 300 毫秒，它是"归档期间主从不断链"的关键。
步骤 5：校验一致才允许删
-- 主表待删集合与归档表的行数必须相等
SELECT
(SELECT COUNT(*) FROM t_order
WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01') AS src_rows,
(SELECT COUNT(*) FROM t_order_archive) AS arc_rows;
-- 再抽 100 笔做金额校验和（聚合校验比逐行比对便宜得多）
SELECT SUM(amount), COUNT(*) FROM t_order
WHERE id IN (SELECT id FROM t_order_archive ORDER BY id DESC LIMIT 100);
SELECT SUM(amount), COUNT(*) FROM t_order_archive ORDER BY id DESC LIMIT 100;
两个数字对不上就停下查原因，绝对不要抱着"差不多"的心态开始删。上一个同事就是因为归档表少了一万行，删完才发现，最后只能从备份里捞。
步骤 6：分批删除，控制 undo 与 binlog
-- 用主键推进，避免大事务
-- 循环体：每次删 5000 行，删完 sleep 0.2
DELETE FROM t_order
WHERE created_at < '2024-01-01' AND status IN (4,5) AND updated_at < '2024-06-01'
ORDER BY id
LIMIT 5000;
MySQL 8.0 建议同时限制 binlog_row_image = MINIMAL，能省下大量 binlog 体积。SQL Server 上对应的做法是把 DELETE TOP (5000) 放进 WHILE 循环，并留意日志文件的自动增长——如果 log_reuse_wait_desc 一直是 LOG_BACKUP，说明你该做一次日志备份了。
步骤 7：回收空间
删完只是逻辑释放，物理文件不会自动变小，这一步不做前面全白干。
-- MySQL 8.0：先查碎片率
SELECT table_name, ROUND(data_free/1024/1024,1) AS free_mb
FROM information_schema.tables
WHERE table_schema='shop' AND table_name='t_order';
-- 在线整理（8.0 支持 ALTER ... ALGORITHM=INPLACE 的写法更快，但大表仍建议低峰执行）
OPTIMIZE TABLE t_order;
-- PostgreSQL：不要用 VACUUM FULL（排他锁），用 pg_repack
-- pg_repack -h 127.0.0.1 -d shop -t t_order --no-superuser-check
-- SQL Server：若已分区，用 SWITCH 秒级把老分区挪走
ALTER TABLE t_order SWITCH PARTITION 3 TO t_order_archive_part3;
-- Oracle：ALTER TABLE t_order DROP PARTITION p2023 UPDATE GLOBAL INDEXES;
步骤 8：把访问路径补上，别让业务方查不到
最省事的做法是建联合视图，对应用透明：
CREATE OR REPLACE VIEW v_order_all AS
SELECT id, order_no, user_id, amount, status, created_at, updated_at, 'hot' AS src FROM t_order
UNION ALL
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'，优化器才会只扫归档表分支。如果时间条件是个变量、优化器抽不到，就会两边全扫——比归档前更慢。解决办法是应用层改成双路查询：
-- 双路查询：先按时间判断走哪张表，代码里路由
-- if (queryEnd < '2024-01-01') { sql = "... FROM t_order_archive WHERE ..." }
-- else { sql = "... FROM t_order WHERE ..." }
这一步做完，再给归档库配上独立的只读账号，把财务和客服的查询直接指过去，主库就彻底清净了。
步骤 9：挂上定时作业，让它自己跑
归档不是一次性的活。写成一个每月 1 号凌晨的作业：自动按"上月边界 + 静置期"算出条件 → 分批搬 → 校验 → 分批删 → 记录批次与行数到 ops_archive_log。作业里必须带三个保护：单次运行最多归档 N 行（防止边界写错搬走整表）、校验不过自动告警退出、每批之间限速。
治理前后的对比：
指标归档前归档后
主表行数3.24 亿4100 万
主表含索引空间881 GB118 GB
订单列表分页 P951.9 s140 ms
每日全备耗时3 h 10 min38 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，两件都要做。
归档这件事，技术难度不高，难的是边界判断和过程可控。把这三步拆开（复制、校验、删除），每一步都能中断、能重跑、能对账，剩下的就只是耐心跑完它。
页:
[1]