如何在MySQL中执行在线数据归档而不影响业务？-mysql教程-PHP中文网

pt-archiver是percona toolkit中专为mysql设计的低影响归档工具，通过分批小事务、智能熔断、可限流、不锁表机制，安全迁移历史数据；其工作原理为“分批查找→搬运插入→确认删除→提交事务”循环执行，避免长事务、间隙锁、主从延迟和io风暴。

如何在mysql中执行在线数据归档而不影响业务？

在线归档必须分批、加锁可控、避开主键竞争，否则必然卡住业务写入。

为什么直接 INSERT ... SELECT + DELETE 会阻塞业务

单条大事务会持有源表的 MDL 锁（metadata lock）和行锁，尤其在 DELETE 阶段若没走索引或扫描全表，会锁住大量无关行；INSERT ... SELECT 在可重复读隔离级别下还会对源表快照加间隙锁，导致新插入的热数据被阻塞。常见现象包括：应用报 Lock wait timeout exceeded、慢查询堆积、主从延迟飙升。

关键点：

时间字段必须有索引，且 WHERE 条件能命中该索引——用 EXPLAIN 确认 type 是 range 或更好
避免在 DELETE 中使用 ORDER BY（MySQL 8.0+ 支持，但低版本不生效，反而误导）
不要依赖自增 id 做归档条件，除非你 100% 确认它与时间严格单调一致（生产环境几乎不可能）

用 pt-archiver 实现真正低干扰归档

pt-archiver 是 Percona 提供的专用于归档的命令行工具，它默认按主键分块、逐批提交、自动休眠、跳过已归档行，天然适配在线场景。比手写 SQL 更可靠。

典型用法：

MySQL（Linux）

MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。

下载

pt-archiver \
  --source h=localhost,D=prod,t=order_log \
  --dest h=archive-host,D=archive,t=order_log_2024 \
  --where "created_at <p>说明：</p>

--bulk-delete 启用批量删除，比单行 delete 快；加上 --no-delete 先试跑，确认无误再删
--sleep 0.2 控制每批间歇，缓解 I/O 和复制压力；值太小易打满 binlog，太大归档周期拉长
目标表 order_log_2024 必须提前建好，结构一致，主键保留，字符集匹配
首次运行建议加 --dry-run（部分旧版用 --print）看语句生成逻辑

自己写存储过程时必须控制事务粒度

如果因合规或安全限制不能用外部工具，需用存储过程，核心是「每次只处理一个确定范围的时间片」，而不是模糊的 NOW() - INTERVAL 3 YEAR。

示例逻辑（注意不是完整代码，仅示意关键结构）：

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE batch_start DATETIME;
  DECLARE batch_end DATETIME;
  DECLARE cur_min_time DATETIME;
<p>-- 先查出待归档的最早时间点
SELECT MIN(created_at) INTO cur_min_time 
FROM order_log WHERE created_at </p><p>WHILE cur_min_time IS NOT NULL DO
SET batch_end = DATE_ADD(cur_min_time, INTERVAL 1 DAY);</p><pre class="brush:php;toolbar:false;">-- 插入归档表（显式指定字段，避免因表结构变更失败）
INSERT INTO archive_db.order_log_archive 
  (id, user_id, amount, created_at, updated_at)
  SELECT id, user_id, amount, created_at, updated_at 
    FROM order_log 
    WHERE created_at >= cur_min_time AND created_at = cur_min_time AND created_at = batch_end;

-- 主动释放锁，避免长事务
COMMIT;
DO SLEEP(0.1);

END WHILE; END

要点：