MySQL数据归档策略：冷热分离、分批搬运与分区表实践

1. 先搞清楚：数据归档到底在解决什么问题

做 MySQL 数据归档这件事，最怕一上来就写脚本。我在几个不同体量的业务库里都干过这件事，踩坑最多的往往不是 SQL 写得对不对，而是没想清楚"为什么要归档、归档到什么程度算完"。业务方看到的是一张表越来越慢，运维看到的是磁盘水位一路涨，DBA 看到的是备份时间从半小时涨到三小时——这三个诉求其实是同一个根因，但对应的归档策略并不完全一样。

数据归档策略本质上是把"低频访问、但还可能有查询和审计需求"的历史数据，从主业务表里剥离出去，让主表保持在一个可控的体量上，同时把这些数据搬运到一个成本更低、压力更小的存储位置。它解决的核心问题是三件事：单表体量过大导致的查询与写入性能衰减、存储与备份成本的线性增长、以及维护操作（加索引、改结构、DDL）越来越难以执行。适合读这篇文章的人包括：手里有千万级以上单表、正在被慢查询和磁盘告警折磨的后端开发，负责数据库容量规划的运维或 DBA，以及正在设计数据生命周期管理的架构同学。

但这里有个前提必须先说清楚：归档不等于删除。很多人把"清理历史数据"和"数据归档"混为一谈，结果删完之后业务方来要三个月前的对账数据，只能去翻备份，翻备份又要恢复一个全量实例，折腾一整天。真正的归档是一条完整链路——判定哪些数据该走、把它们搬到哪儿、怎么保证搬的过程不丢不重、搬完之后源表怎么收尾、归档数据将来怎么查。这条链路上任何一环没设计好，都会在中后期以事故的形式暴露出来。

我在一个交易流水类的库上做过一次完整的归档改造，源表t_biz_record大约 4.2 亿行、单表 620GB，日增 180 万行左右。归档前，按时间范围查询近 7 天的数据要走 3 秒以上，索引维护和备份都很难受。归档后主表稳定在 4000 万行以内，同样的查询降到 80ms 级别。这个过程中真正花时间的不是写搬运脚本，而是反复确认归档边界、依赖关系、以及对下游的影响。下面把整个思路和实现过程摊开讲。

1.1 冷热数据的边界怎么划才靠谱

划边界这件事，纯按时间一刀切是最省事但最容易出问题的做法。我在实际项目里用的是"时间 + 状态 + 依赖"三重判定。

时间维度是主干：比如"创建时间早于 180 天"。这个 180 天不是拍脑袋，而是从业务侧问出来的——客服工单能回溯多久、财务对账要查多久、风控要追溯多久，取其中最大值再加一个安全余量。我当时的做法是拉了三个月的慢查询日志和业务方的取数需求，统计出实际查询落在 90 天内的占 99.6%，180 天外的查询基本是个位数，这才敢把线定在 180 天。

状态维度是保险：有些数据虽然时间久，但状态没终结，比如"未完成""待审核""有争议"的记录，这些绝对不能归档。判定条件里必须加上status IN (终态集合)，否则会出现归档完之后业务方发现一笔挂着的单子查不到了。

依赖维度是最容易被忽略的：归档前必须扫一遍这张表被哪些外键、视图、定时任务、报表 SQL 引用。我的做法是开一个只读实例，把performance_schema.events_statements_summary_by_digest里的 SQL 摘要捞出来，按表名过滤，人工过一遍涉及的查询。这一步大概花了两天，但后面省掉了至少三次"归档完某报表出不来数"的救火。

提示：边界值宁可保守一点。多留 30 天的热数据，代价是多占几十 GB；边界划早了，代价是业务中断加数据回捞，两者完全不对等。

1.2 三条主流路线的取舍

归档的实现路线大致三条，各有各的适用面，我在不同场景下都用过：

如果表是新建的、或者还有改造窗口，我强烈建议直接上分区表，后期的归档成本几乎为零。但现实情况是大部分需要归档的表都已经跑了两三年、几个亿的数据，这时候做分区改造等于重建一张表，窗口期和磁盘空间都是问题。所以我的默认选择是"分批搬运"作为过渡方案，同时规划分区表作为长期方案。

还有一种混合路线值得提一句：源表保持不动，只把查询入口做收敛——历史查询走归档库，业务查询走主库。这种做法在读写分离架构里很自然，但要确保没有遗漏的查询还打在主库的历史区间上，否则归档就白做了。

1.3 归档真正带来的收益在哪里

很多人以为归档的收益就是"省磁盘"，这个认知会误导后面的方案选择。磁盘当然省，但省磁盘最便宜的方式是加盘，不是归档。归档真正值钱的地方在于：

第一，索引高度变矮。一张 4 亿行的表和一张 4000 万行的表，同样一个二级索引，B+ 树高度可能从 4 层降到 3 层，一次等值查询少一次随机 IO。这个提升在 QPS 高的场景下非常明显。

第二，写放大降低。InnoDB 的二级索引维护、change buffer、undo 日志都和表的体量相关。表小了之后，同样的写入压力下 redo 和 undo 的生成量会下降，purge 线程的压力也跟着降。

第三，运维操作变得可行。给 4 亿行的表加一个索引，用在线 DDL 也要跑几个小时，期间还要盯着磁盘和主从延迟。4000 万行的表，同样的操作几十分钟就完了，心理负担完全不一样。

第四，备份与恢复时间缩短。这是最容易被低估的一项。全量备份时间从三小时降到四十分钟，意味着恢复演练可以更频繁地做，真出事的时候 RTO 也更有保障。

理解了这四点，后面设计归档规则时就会知道该优先保什么：不是把数据删得越干净越好，而是让主表保持在一个"索引不深、DDL 可行、备份可控"的体量区间。

2. 归档方案的整体设计：从判定规则到执行链路

方案设计这部分，我的习惯是先画一条数据流，再往每个节点上填约束。归档的数据流其实很简单：源表 → 采集待归档集合 → 搬运到目标 → 校验 → 删除源数据 → 收尾。但每个箭头背后都有一堆细节。

2.1 归档规则的三要素与配置化

规则我一般抽成三个字段：time_column（按哪个时间字段判定）、cutoff_expr（截止条件表达式）、extra_where（额外的状态过滤）。这样同一条链路可以服务多张表，不用为每张表写一套脚本。

举个具体的配置：

{ "job_name": "biz_record_archive", "source": {"db": "db_biz", "table": "t_biz_record", "pk": "id"}, "target": {"db": "db_arch", "table": "t_biz_record_arch"}, "time_column": "create_time", "cutoff_days": 180, "extra_where": "status IN (3, 4, 5)", "batch_size": 2000, "sleep_ms": 500, "max_lag_sec": 3 }

这里pk单独抽出来是有讲究的。后面做游标分页必须有一个单调递增且唯一的列，绝大多数表用自增主键最合适；如果表用的是 UUID 或者联合主键，就得退化成"时间范围 + 主键范围"的双游标，实现复杂度会上去一截。

max_lag_sec是给主从环境准备的保护阈值——每批执行前检查一下从库延迟，超过阈值就暂停。这个参数在没有 DBA 值守的环境里几乎是必选项。

2.2 目标表的结构设计与索引裁剪

归档表不是源表的复制粘贴。源表为了支撑高频点查和范围查，可能挂了七八个二级索引；归档表主要是给低频的范围查询和审计用，索引可以砍到一两个。

我的做法是：保留主键 + 一个时间索引 + 一个业务单号类的唯一索引（如果业务上需要对账），其余全部去掉。索引少了，搬运时的写入成本能降一半以上。这一点在数据量大时差别非常明显——同样搬 1000 行，目标表 8 个索引和 2 个索引的耗时能差 3 倍。

表结构还有几个细节：

• 分区或者分库设计：如果归档数据预估超过 500GB，归档表本身也要考虑按年分区，否则过几年又面临同样的问题。
• 字段类型严格对齐，尤其是DATETIME的精度（DATETIME(3)和DATETIME混用会导致数据被截断）。
• 字符集和排序规则要和源表一致，否则跨库 JOIN 时会走不了索引。
• 如果归档库是独立实例，表可以用COMPRESSED行格式，压缩比通常能到 2 到 3 倍，代价是查询时 CPU 稍高。归档场景查询频率低，这个交换很划算。

2.3 执行链路的顺序：先写后删

这条规则我写死在自己所有的归档脚本里：必须先成功写入目标库，再删除源库数据。反过来做，一旦中间失败就是数据丢失；按这个顺序做，最坏情况是重复归档，而重复是可以靠幂等写入解决的。

整条链路的顺序是：

1. 读取水位（上次处理到的最大主键或时间点）。
2. 按条件拉取一批数据。
3. 写入目标库，使用INSERT IGNORE或ON DUPLICATE KEY UPDATE保证幂等。
4. 确认写入行数与拉取行数一致（或目标库已存在）。
5. 按主键范围删除源库对应数据，并限制删除行数。
6. 更新水位，提交。
7. 检查主从延迟与错误日志，决定是否继续下一批。

第 4 步的确认很重要。如果目标库写入因为网络抖动部分失败，而脚本直接进入删除环节，那部分数据就永久丢了。我的做法是比对affected_rows，如果小于拉取行数就终止本批次并告警，人工介入。

2.4 调度与并发控制：别和业务高峰撞车

归档任务本质上是后台批处理，最忌讳在业务高峰期跑。我一般把执行窗口放在凌晨 1 点到 5 点，具体做法是：

• 用系统 crontab 或者内部任务平台触发，启动时间避开凌晨的备份窗口。备份和归档同时跑，磁盘 IO 会互相拖累。
• 单实例同时只允许一个归档任务运行，用文件锁或者数据库行锁实现，避免手动触发和定时触发撞在一起。
• 任务内部每批之间主动 sleep，把单批的 IO 压力摊平。
• 设置一个总的运行时长上限（比如 4 小时），到点就优雅退出并记录水位，第二天接着跑。

这里有个容易忽略的点：归档任务的"完成"不是一次性的。数据每天都在增长，所以它是一个长期运行、按天推进的任务。水位表里记录的不只是位置，还有每天的推进量，可以用来做趋势分析——如果某天推进量突然掉到平时的十分之一，基本可以确定是遇到了锁等待或者主从延迟。

3. 分批搬运的核心实现：SQL 与脚本怎么写

这是整个方案里最"硬"的部分，也是坑最多的地方。我见过太多脚本用LIMIT offset, size做分页，跑到几千万行之后慢得没法看；也见过用单条DELETE删几百万行，直接把自己锁死。下面把我验证过的写法拆开讲。

3.1 主键游标分页，永远别用 OFFSET

LIMIT 10000000, 2000这种写法，MySQL 需要先扫描并丢弃前 1000 万行，越往后越慢，最后直接变成全表扫描。正确的做法是主键游标：

SELECT id, order_no, amount, status, create_time, update_time FROM t_biz_record WHERE create_time < '2025-01-01 00:00:00' AND status IN (3, 4, 5) AND id > ? ORDER BY id LIMIT 2000;

拿到这一批的最大id，作为下一批的起点。这样每次都是索引范围扫描，时间复杂度恒定，跑多久都不会变慢。

前提是这个查询能走索引。我的表上建了一个idx_time_status (create_time, status)，但游标用的是id，两个条件叠加时优化器可能选错索引。实际做法是建一个联合索引idx_status_time_id (status, create_time, id)，让"状态 + 时间 + 主键"三个条件在一个索引里完成，避免回表扫描后再过滤。

提示：上线前一定用EXPLAIN看一眼执行计划。如果type是ALL或者rows是几千万，说明索引没走对，赶紧调整，否则跑到后面会变成全表扫描。

3.2 批大小与休眠时间怎么算

批大小的选择是个平衡：批太小，网络往返和事务开销占比高，整体慢；批太大，单事务的锁持有时间长，主从延迟风险高，undo 也容易堆积。

我的经验值是这样的：单行数据在 1KB 以内时，批大小取 1000 到 2000；单行超过 5KB（比如带大字段的表），批大小降到 200 到 500。给出的理由是：InnoDB 单事务修改的数据量最好控制在一个可控范围，超过这个范围，purge 线程追不上，undo 表空间会膨胀。

休眠时间用主从延迟来倒推。假设目标是主从延迟不超过 3 秒，一批产生的 binlog 大约 2MB，那么需要保证从库有足够时间消化。我的实际配置是每批 sleep 500ms，在千兆内网、半同步复制的环境下，主从延迟稳定在 1 秒以内。

还有一个更精细的做法：动态调整。每批执行完读一次SHOW REPLICA STATUS的Seconds_Behind_Source，超过 3 秒就把 sleep 增加到 2 秒，低于 1 秒就逐步恢复到 500ms。这个逻辑实现起来十来行代码，但在网络抖动或者从库压力大时非常有用。

3.3 水位表设计：断点续传的基础

水位表是整条链路的大脑，设计得好，脚本怎么崩都能恢复；设计得糙，出了问题只能靠人肉对账。

CREATE TABLE t_archive_watermark ( job_name VARCHAR(64) NOT NULL PRIMARY KEY, last_pk BIGINT NOT NULL DEFAULT 0, last_run_at DATETIME NOT NULL, rows_total BIGINT NOT NULL DEFAULT 0, status TINYINT NOT NULL DEFAULT 0 COMMENT '0待运行 1运行中 2已完成 3异常', err_msg VARCHAR(512) DEFAULT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB;

关键点是last_pk必须在删除成功后才更新，且和水位更新放在同一个事务里（如果水位表和源表在同一个库）。如果不在同一个库，就只能保证"删除成功 → 更新水位"的先后顺序，最坏情况是重复处理一小批，靠目标表的幂等写入兜住。

这里有个细节容易被忽略：水位推进的粒度。如果按批次逐次更新水位，每批一次写操作，会额外产生 binlog，量不大但也是开销。我的做法是每 10 批更新一次水位，代价是最坏情况下重复处理 10 批数据（约 2 万行），这个重复量完全可以接受。

3.4 一个可以直接抄的搬运脚本

下面是我在用的 Python 版本，去掉了业务相关的部分，保留核心逻辑。依赖pymysql，参数从配置文件读入。

import time import pymysql from pymysql.cursors import DictCursor def archive_once(cfg, conn_src, conn_dst): pk_col = cfg["source"]["pk"] batch = cfg["batch_size"] last_pk = get_watermark(conn_src, cfg["job_name"]) sql_sel = f""" SELECT * FROM {cfg['source']['table']} WHERE {cfg['time_column']} < %s AND ({cfg['extra_where']}) AND {pk_col} > %s ORDER BY {pk_col} LIMIT %s """ with conn_src.cursor(DictCursor) as cur: cur.execute(sql_sel, (cfg["cutoff_time"], last_pk, batch)) rows = cur.fetchall() if not rows: return 0, last_pk cols = list(rows[0].keys()) placeholders = ",".join(["%s"] * len(cols)) sql_ins = ( f"INSERT IGNORE INTO {cfg['target']['table']} " f"({','.join(cols)}) VALUES ({placeholders})" ) values = [tuple(r[c] for c in cols) for r in rows] with conn_dst.cursor() as cur: affected = cur.executemany(sql_ins, values) conn_dst.commit() # affected 为 -1 或小于行数时，说明有冲突或部分失败 if affected > 0 and affected < len(rows): log_warn(cfg["job_name"], f"部分写入冲突: expect={len(rows)} actual={affected}") max_pk = max(r[pk_col] for r in rows) sql_del = ( f"DELETE FROM {cfg['source']['table']} " f"WHERE {pk_col} >= %s AND {pk_col} <= %s " f"AND {cfg['time_column']} < %s" ) with conn_src.cursor() as cur: deleted = cur.execute(sql_del, (rows[0][pk_col], max_pk, cfg["cutoff_time"])) conn_src.commit() if deleted < len(rows): log_warn(cfg["job_name"], f"删除行数少于拉取行数: {deleted} < {len(rows)}") return deleted, max_pk

主循环里把延迟检查和 sleep 加上，就是完整的执行体。注意DELETE语句里同时带了主键范围和create_time条件——这是双保险，防止在拉取和删除之间有人修改了create_time导致误删。

3.5 删除阶段的三个禁忌

删除是整个流程里对线上影响最大的动作，有三条经验必须遵守。

禁忌一：不用无限制的DELETE。DELETE FROM t WHERE create_time < '2024-01-01'这种语句，如果匹配几百万行，会持有一个巨大的事务，undo 膨胀、binlog 暴涨、锁范围扩散，主从延迟能飙到几百秒。必须带LIMIT或者主键范围。

禁忌二：不在业务高峰做大批量删除。哪怕加了LIMIT，密集的删除产生的 binlog 和磁盘 IO 也会影响同实例的其他表。我的窗口是凌晨，且每批之间强制 sleep。

禁忌三：不迷信DELETE能立刻释放空间。InnoDB 标记删除只是把空间变成可复用，表文件不会缩小。这一点在下一章展开。

4. 分区表路线：把"删除"变成"丢分区"

如果说分批搬运是"外科手术"，分区表就是"提前规划好的城市规划"。两者不冲突，最优解往往是——存量数据用搬运处理，新建表或者能安排改造窗口的表直接上分区。

4.1 按时间做 RANGE 分区

CREATE TABLE t_biz_record ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, amount DECIMAL(14,2) NOT NULL, status TINYINT NOT NULL, create_time DATETIME NOT NULL, PRIMARY KEY (id, create_time), KEY idx_ct (create_time) ) ENGINE=InnoDB PARTITION BY RANGE COLUMNS(create_time) ( PARTITION p202501 VALUES LESS THAN ('2025-02-01'), PARTITION p202502 VALUES LESS THAN ('2025-03-01'), PARTITION p202503 VALUES LESS THAN ('2025-04-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE) );

几个关键约束必须说清楚：

• 分区键必须包含在所有唯一索引里。所以主键从(id)变成了(id, create_time)。这会影响到依赖单列主键的业务逻辑，比如ON DUPLICATE KEY UPDATE的行为、以及某些 ORM 的主键推断。
• 分区键不能是表达式，RANGE COLUMNS直接作用于列值，避免TO_DAYS()这类函数分区在跨年时踩边界。
• 永远留一个pmax兜底分区。没有它，插入超出范围的数据会直接报错，业务写入中断。

分区粒度按月最合适。按天分，分区数量会膨胀到几千个，每次打开表都要读一堆分区元数据，反而变慢；按年分，单个分区太大，归档时一次丢一年，粒度又太粗。月分区在"分区数量可控"和"归档粒度够细"之间平衡得最好。

4.2 新增、删除与合并分区

日常维护三个动作：

-- 新增下个月分区（在 pmax 之前插入） ALTER TABLE t_biz_record REORGANIZE PARTITION pmax INTO ( PARTITION p202504 VALUES LESS THAN ('2025-05-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE) ); -- 归档：直接丢弃整个分区（秒级） ALTER TABLE t_biz_record DROP PARTITION p202410; -- 或者保留为独立表（归档到别处） ALTER TABLE t_biz_record EXCHANGE PARTITION p202410 WITH TABLE t_biz_record_202410;

DROP PARTITION是分区表最大的价值点——它不走逐行删除，不产生逐行 binlog，只修改元数据并释放对应表空间，几百万行的分区删除耗时通常在几百毫秒到几秒。这一点和逐行DELETE完全不是一个量级。

但要注意，DROP PARTITION会持有 MDL 锁，如果此时有长事务正在访问这张表，ALTER语句会阻塞，进而阻塞后续所有查询。所以执行时机同样要避开长事务窗口，最好先SELECT一下information_schema.innodb_trx看有没有超过 60 秒的事务。

4.3 EXCHANGE PARTITION 的用法与坑

如果归档数据需要保留在另一张表里，EXCHANGE PARTITION是最优雅的方案：把分区数据"换"成一张普通表，然后这张表可以随便搬、随便压缩。

-- 前提：目标表结构完全一致、没有外键、没有触发器 CREATE TABLE t_biz_record_202410 LIKE t_biz_record; -- 注意：LIKE 会复制分区定义，需要手动去掉分区 ALTER TABLE t_biz_record_202410 REMOVE PARTITIONING; ALTER TABLE t_biz_record EXCHANGE PARTITION p202410 WITH TABLE t_biz_record_202410;

踩过的坑有几个：目标表如果有自己的分区定义，EXCHANGE会失败，必须先REMOVE PARTITIONING；目标表如果有外键约束，会直接报错；EXCHANGE过程中会短暂持有 MDL 锁，大分区换出时可能阻塞一秒左右，需要评估业务能否接受。

还有一点容易想当然：EXCHANGE之后分区p202410变成空表，但这个空分区仍然占着元数据。要么后续DROP PARTITION，要么让它继续存在等待下一次复用。我的做法是换出后立刻DROP PARTITION，保持分区列表干净。

4.4 分区表不是万能药

分区表的短板也很明确。第一，改造存量大表的代价极高，等于重建一张表加一次全量数据搬迁，几个小时的窗口期加上一倍以上的临时磁盘空间。第二，分区裁剪只在查询条件包含分区键时生效，如果业务大量按order_no查询而不带create_time，分区表对这类查询没有任何加速，甚至因为分区元数据开销而略慢。第三，某些历史遗留的INSERT ... SELECT、REPLACE INTO在分区表上的行为需要重新验证。

所以我的判断标准是：新表默认上分区，存量表先评估业务查询模式再决定。如果按时间查询占比超过 70%，值得做改造；低于 30%，老老实实用分批搬运。

5. 归档后的收尾：空间回收、索引与统计信息

很多人做到"数据删完了"就以为结束了，其实收尾没做好，前面的工作只发挥了一半效果。我见过最常见的情况是：删了 3 亿行，表还是那么大，磁盘一点没降，然后被领导追问"归档到底有没有用"。

5.1 为什么表空间没有变小

InnoDB 的删除是"标记可复用"，不是"立即归还磁盘"。一张 600GB 的表删掉 400GB 数据后，文件大小可能还是 600GB，只是内部有 400GB 的空闲页可以被后续插入复用。

释放空间有两条路：

第一条是OPTIMIZE TABLE。它本质上是ALTER TABLE ... FORCE，会重建表并回收空间。代价是：需要额外一块和表大小相当的磁盘空间（MySQL 5.7 之后用临时文件在线重建，不需要双倍，但仍然占空间），执行期间持有 MDL 锁的临界窗口，以及大量的 IO 和 binlog（如果主从架构，重建操作会在主库执行后逐行同步到从库，延迟非常大）。

第二条是不重建，依靠后续写入逐渐复用空闲页。如果表还在持续增长，这个过程是自然发生的，不需要额外操作，只是磁盘水位短期内不会下降。

我的选择标准是：如果归档后表缩小到原来的 20% 以下，且磁盘确实紧张，就安排一次低峰期重建；如果表还有稳定增长，就不单独重建，让空闲页自然消化。重建 4000 万行的表，实测耗时约 40 分钟，期间主从延迟峰值到 90 秒，必须在有主从延迟监控和可暂停机制的前提下做。

5.2 统计信息与执行计划

删完大量数据后，优化器手里的统计信息是过期的。它可能还认为这张表有 4 亿行，从而选错索引或者走错 JOIN 顺序。

处理方式：

ANALYZE TABLE t_biz_record;

这条语句对 InnoDB 是采样统计，默认采样 20 个页（innodb_stats_persistent_sample_pages）。如果表的数据分布不均匀（比如按时间递增，最新的数据集中在少数页），默认采样可能失真，可以临时把采样页数调高到 100 再执行，执行完再调回去。

我遇到过一次典型问题：归档后某条按状态查询的 SQL 突然从走索引变成全表扫描。原因就是统计信息显示status = 3的行占比 60%，优化器认为走索引不如全表扫。ANALYZE之后占比降到 8%，执行计划恢复正常。这类问题不监控执行计划变化是发现不了的。

5.3 归档数据的可查询性

归档不是终点，归档数据还要能被查。这里有两个常见的设计缺失：

一是归档库没有对外查询入口。数据搬过去了，但业务方查不了，最后还是要回主库查（虽然查不到），归档等于白做。我的做法是在归档库上建一个只读账号，配一个简单的查询页面或者直接给业务方 API 接口，按时间范围和业务单号查询。

二是归档库没有索引规划。前面说了归档表要砍索引，但也不能一个都不留。至少要保证"按业务单号精确查"和"按时间范围查"这两类查询能走索引。我的归档表固定保留三个：主键、create_time、order_no（或对应的业务唯一键）。

至于留存期限，这个要和业务、法务一起定。技术上能做的是按年分区归档库，到期直接DROP PARTITION，比逐行删除干净得多。

6. 监控与验收：怎么证明这套东西是稳的

归档任务上线之后，最怕的是"静默失败"——脚本每天跑，日志没报错，但实际上没搬多少数据，或者搬了但没删，或者删了但没搬。我见过一个案例：脚本因为主键类型是BIGINT UNSIGNED，而游标变量用了有符号整型，跑到 21 亿行时溢出，任务静默停止，两周后才被发现。

6.1 必须盯住的几个指标

这张表我在三个不同的项目里都用过，基本覆盖了 90% 的异常场景。其中"源表行数趋势"这一项最容易被忽略——它不依赖脚本自报，是从数据库层面直接观测，脚本怎么撒谎都骗不过它。

6.2 数据一致性怎么验

搬运链路的正确性，靠"目标表行数 = 源表减少行数"只能验个大概，真正的校验要做内容比对。我的做法是分层校验：

每天任务结束后，随机抽 1000 行已归档数据，从归档库查出，和备份库或者从库的延迟快照做逐字段比对。这个成本很低，但能发现字段截断、字符集转换、时间精度丢失这类隐蔽问题。

每周做一次汇总校验：按天统计源表减少的行数和归档表增加的行数，两边做差分。差值超过 0.1% 就人工介入排查。

每月做一次全量 checksum：对归档表按时间分区计算CHECKSUM TABLE或者SUM(CRC32(CONCAT_WS(...)))，和源库历史数据（如果有保留快照）比对。这一步比较重，但能兜住前面所有校验漏掉的问题。

注意：CHECKSUM TABLE对大表会长时间持有共享锁，务必在只读从库或者归档库上执行，绝不能在主库跑。

6.3 一次真实问题的复盘

某次归档任务在周五凌晨卡住了，早上发现水位没动，但脚本进程还在。排查过程是这样的：

先看进程状态，ps显示 Python 进程活着，CPU 占用接近 0，说明在等待。连上数据库看SHOW PROCESSLIST，发现一条DELETE语句状态是updating，已经跑了 40 分钟。

再看information_schema.innodb_trx，这个事务的trx_rows_locked是 12 万，trx_rows_modified是 8 万。问题定位了：某一批拉取的数据量远超预期，同时删除条件命中的行数也远超预期，导致单个事务过大。

根因是那天业务侧做了一次批量数据导入，把大量历史数据的create_time更新成了旧日期，导致符合条件的行数激增；而脚本的LIMIT只限制了查询返回，删除语句用的是主键范围，一旦主键范围覆盖的行数特别多（中间有大量不满足条件的行被更新成满足条件），删除就会命中大量行。

修复方式：把删除语句也加上LIMIT，并且在拉取和删除之间做行数校验——如果删除行数超过拉取行数的 1.5 倍，立即回滚并告警。这个改动上线后，同类问题再没出现过。

这个案例的教训是：永远不要假设数据分布是稳定的。归档脚本里所有涉及行数的操作都要有上限保护，尤其是DELETE和UPDATE。

7. 常见问题与排查速查表

把这两年遇到过的问题整理成一张表，遇到类似现象可以对照着查。

除了这张表，还有几个不够成"故障"但很影响体验的细节：

删除阶段的 sleep 不要用固定值。白天跑和夜里跑，从库的压力完全不同，我后来改成了根据当前延迟动态调整，稳定性提升明显。

归档表的自增主键不要和源表共用。有些方案为了"看起来一致"把源表id直接写进归档表，一旦遇到源表主键重置或者分库场景，就会冲突。归档表用独立自增主键，源表id作为一个普通唯一索引列保存，更安全。

归档任务也要有"熔断"机制。连续 3 批出现删除行数和拉取行数不一致，自动停止并告警，避免在小问题的基础上滚出大问题。

如果归档库和主库在同一个实例（很多小团队为了省资源会这么干），那归档的收益会打折扣——表小了，但实例整体的 IO 压力没变。这种情况优先考虑把归档库拆到独立实例，哪怕配置低一点。

8. 一些踩完坑之后的体会

做了几轮归档之后，我最大的感受是：这件事的技术难度不高，但工程细节特别多，而大部分失败都发生在细节上。一个LIMIT忘了加，一次统计信息没更新，一个字符集不一致，都能让整条链路的效果打对折。

如果让我重来一次，我会在动手写脚本之前多花两天做三件事：把线上真实 SQL 的查询模式统计出来、把表和下游依赖的关系图画出来、把归档之后的验收标准（行数、性能、磁盘、查询可用性）写清楚。这三件事做完，后面的实现其实是体力活。

还有一个值得分享的小技巧：归档任务的第一次运行，一定要在从库或者测试实例上做全流程演练。用pt-archiver或者自己写的脚本都行，跑一遍完整链路，看耗时、看 binlog 量、看延迟曲线、看目标表数据。这个演练能暴露 80% 的问题，成本可能只是一下午时间。

关于工具，pt-archiver确实省事，它的--max-lag、--purge、--commit-each几个参数基本覆盖了我自己脚本的核心逻辑。但它的灵活性不如自研，比如多表联合归档、复杂的断点续传逻辑、和目标表结构不一致的场景，还是得自己写。我的建议是：单表、结构一致、无复杂依赖，直接用pt-archiver；多表、有转换逻辑，自己写脚本，但把幂等、限速、断点这三件事做扎实。

最后提一句分区表。如果现在让我重新设计一套新的业务表，我会默认给它加上按月 RANGE 分区，即使当时数据量很小。因为等到数据量真的上来了再改造，成本和风险完全不是一个量级。提前规划分区的代价只是主键多一列、几个查询要带上时间条件，而它换来的是一条几乎零成本的长期归档通道。