千万级大表瘦身实录：冷热分离的分区表设计与归档流水线落地

订单表滚到三亿行之后，团队发现三件事同时恶化：B+ 树变高导致点查也变慢，缓冲池命中率下降引发周期性抖动，每天六小时的备份窗口已经快要覆盖到白天。这些问题的共同根源是热数据被海量冷数据稀释——一年前的订单几乎没有查询，却占据着索引、缓冲池和备份窗口。解法是冷热分离：热数据留在主表保持精瘦，冷数据按策略归档到专属存储。这篇实录把承载形态选择、归档流水线设计与限速实操一次讲清。

先回答「哪些表值得做」。冷热分离不是所有大表的万能药：访问模式高度均匀、任何时间段都有等概率查询的表，分离后反而要多一跳联合查询。判断标准很朴素——统计近三个月的查询时间分布，如果九成以上查询集中在最近三个月的数据，剩余体量只贡献不到一成的访问，这张表就值得分离；反之则先优化索引与缓冲池，不必急着动存储结构。

承载形态先想清楚：分区表、归档表与分工

推荐的双层结构是：主表按时间 RANGE 分区，近月分区承载热数据；归档库建同构的非分区表承接历史数据。先看主表设计：

sql

-- 按月 RANGE 分区：查询携带 create_time 才能命中分区裁剪
CREATE TABLE order_info (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no    VARCHAR(32)     NOT NULL COMMENT '业务订单号',
  user_id     BIGINT UNSIGNED NOT NULL,
  amount      DECIMAL(10, 2)  NOT NULL,
  status      TINYINT         NOT NULL DEFAULT 0,
  create_time DATETIME        NOT NULL COMMENT '创建时间，分区键',
  PRIMARY KEY (id, create_time),                -- 唯一键必须包含分区键，这是分区表的硬约束
  UNIQUE KEY uk_order_no (order_no, create_time),
  KEY idx_user (user_id)
) ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (create_time) (
  PARTITION p202601 VALUES LESS THAN ('2026-02-01'),
  PARTITION p202602 VALUES LESS THAN ('2026-03-01'),
  PARTITION p202603 VALUES LESS THAN ('2026-04-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

三个硬约束写进设计文档，别到上线才发现：所有唯一键（含主键）必须包含分区列，业务唯一键 order_no 也要被迫带上 create_time；InnoDB 分区表不支持外键，关联完整性要靠应用层保证；查询不携带分区键时优化器会扫描所有分区，分区裁剪完全依赖 SQL 写法。此外要建好分区滚动机制：定期 ALTER TABLE 新增下月分区，避免数据涌进 pmax 导致分区失去意义。分区数量也要有节制——单表几百上千个分区会让优化器与文件句柄的开销显著上升，按月分区保留两到三年、配合归档流水线持续输出，通常比无限堆分区更健康。

归档流水线的五个环节：扫描、迁移、删除、对账、清理

归档不是「一条 DELETE 挂后台跑」，而是一条带安全措施的流水线：扫描环节用主键区间把存量切成小批次；迁移环节分批写入归档库并保证幂等；删除环节用与迁移完全一致的主键集合小批量删除；对账环节在删除前校验两边行数一致；清理环节维护分区滚动与归档侧生命周期。核心 SQL 骨架如下：

sql

-- 迁移：按主键区间分批写入归档库，INSERT IGNORE 保证任务重跑幂等
INSERT IGNORE INTO shop_archive.order_info
SELECT * FROM shop.order_info
WHERE create_time < '2026-01-01'
  AND id BETWEEN 1 AND 50000;

-- 对账：删除前按同一集合比对行数，不一致立即终止流水线人工介入
SELECT COUNT(*) AS src_rows FROM shop.order_info
WHERE create_time < '2026-01-01' AND id BETWEEN 1 AND 50000;
SELECT COUNT(*) AS dst_rows FROM shop_archive.order_info
WHERE create_time < '2026-01-01' AND id BETWEEN 1 AND 50000;

-- 删除：与迁移条件严格一致，单批删除再 LIMIT 限流，循环到影响行数为 0
DELETE FROM shop.order_info
WHERE create_time < '2026-01-01'
  AND id BETWEEN 1 AND 50000
LIMIT 5000;

调度程序要承担并发控制：每批一个独立事务，批间 sleep 50 到 100 毫秒，同时轮询主从延迟，超过阈值自动暂停——归档任务引发主从延迟雪崩是这套方案最常见的翻车点。「先写后删、两步用完全相同的主键集合」是安全底线：只按时间条件先后两次执行，两次之间新落入区间或被修改的行会造成两边数据不一致。

归档库自身的生命周期也要管起来。归档不是终点：按月分文件的归档表要在保留期到达后压缩转储到对象存储，再从在线归档库下线；对账报表要按月自动生成并留痕，作为数据一致性的长期凭证。这套机制一次搭好，之后每年都省下数不清的人工核对时间。

秒级搬走一整个月：EXCHANGE PARTITION 与 pt-archiver

对按月分区的表，还有一条远快于逐行搬运的路：分区交换。把整个旧分区与一张结构完全一致的归档表做原子交换，本质是元数据操作，秒级完成：

sql

-- 前提：归档表结构与分区定义完全一致（含索引），且为非分区表
ALTER TABLE shop.order_info EXCHANGE PARTITION p202601
WITH TABLE shop_archive.order_info_202601;

交换完成后 p202601 分区变成空壳，直接 DROP 即可；被换出的归档表再按需转储到对象存储或数据仓库。存量已超大的表不想一次性改造分区，可以用 pt-archiver 做持续归档，它自带限速与主从延迟感知：

bash

# pt-archiver 持续归档：小批量搬运、批间限速、联动从库延迟自动暂停
# 密码通过环境变量或配置文件注入，不要写进脚本
pt-archiver \
  --source h=127.0.0.1,D=shop,t=order_info \
  --dest h=10.0.0.8,D=shop_archive,t=order_info \
  --where "create_time < '2026-01-01'" \
  --limit 1000 --commit-each \
  --max-lag 1 --check-slave-lag h=10.0.0.9 \
  --progress 10000 --statistics

避坑总结

分区键与唯一键的冲突要在建表期解决。 存量普通表改造成分区表等于整表重建，业务唯一键还必须带上分区列，索引设计与查询写法都要适配，所以分区方案优先在新表新规则上落地，改造存量要单独立项评估。
批删必须小批量、限速、盯延迟三件套齐活。 单批控制在千行级别，批间 sleep，主从延迟超阈值自动熔断；归档任务跑挂业务写入，比归档慢一天严重得多。
先写后删，且两步共用完全相同的主键集合。 只按时间条件先后两次执行，两次之间数据变动会造成源与归档不一致；用主键区间或固化快照把「迁移集合」与「删除集合」锁成同一个。
归档库要有查询出口。 客诉回查、法务取证、财务对账都可能需要冷数据，归档库的只读授权与查询工具提前就位，别等要用时才发现数据躺在没人能查的库。
DROP PARTITION 与 EXCHANGE PARTITION 也是 DDL。 同样需要获取元数据锁，同样会被长事务卡住并拖垮全表访问，执行前清场长事务、放低峰窗口，与文章 303 里的在线 DDL 纪律完全一致。

写在最后

大表瘦身的技术动作并不复杂：分区定边界、流水线保安全、分区交换提速度。真正拉开差距的是工程纪律——小批量、限速、对账、幂等这些枯燥的约束，才是归档任务能长期无人值守运行的底气。热数据精瘦之后，查询、备份、DDL 会同时受益，这是性价比最高的一次存储治理。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

千万级大表瘦身实录：冷热分离的分区表设计与归档流水线落地 ​

承载形态先想清楚：分区表、归档表与分工 ​

归档流水线的五个环节：扫描、迁移、删除、对账、清理 ​

秒级搬走一整个月：EXCHANGE PARTITION 与 pt-archiver ​

避坑总结 ​

写在最后 ​