MySQL 大表清理历史数据怎么分批删：主键游标、锁等待与回滚窗口

线上订单表、日志表一旦积累到几千万行，直接执行一条大范围 DELETE 往往不是“删得快”，反而会把锁等待、undo 日志和主从复制延迟一并拉满。更稳妥的做法是按主键游标切成小批次操作，先确认查询能命中索引，每批提交后实时观察耗时和锁等待情况，碰到业务高峰直接停在批次边界就好，不会出现半半拉拉的状态。

清理历史数据优先采用“主键范围 + 小批事务 + 批次日志”的方式，不要用 OFFSET 翻页，也不要把整个保留周期的数据都塞进同一个事务里；需要回滚撤回的时候也要提前留好归档或备份窗口。

先确认保留边界和可用索引

以订单操作日志 order_events 为例，业务规则是保留最近 180 天的数据。先把时间边界算成固定的时间值，再核对表结构和索引配置，不要在删除语句的筛选字段外层套函数，导致索引完全失效。

SHOW CREATE TABLE order_events;
SHOW INDEX FROM order_events;

EXPLAIN SELECT id
FROM order_events
WHERE id > 8000000
  AND created_at 

理想的执行效果是走主键，或者走能完全覆盖筛选条件的联合索引，扫描行数和本批预期删除的行数接近。如果只有 created_at 索引，也可以先按时间筛选，但最好还是通过主键来推进游标，避免每批执行的时候重新扫描已经处理过的历史数据区域。先拿只读查询跑一遍执行计划确认没问题，别急着直接上手删。

为什么主键游标比 OFFSET 更适合清理

LIMIT 10000 OFFSET 500000 要先跳过前面已经扫过的行，翻页越深，扫描和排序的成本波动就越大，完全不可控。主键游标则是把上一批最后一条记录的ID存成 last_id，下一批直接从这个ID往后开始扫就行，完全不用重复走前面的行。

DELETE FROM order_events
WHERE id > :last_id
  AND id 

这里的 upper_id 可以在任务启动时就取好一个上限值，避免清理过程中不断把新写入的有效数据误卷进删除范围里。每次删除完成后读取受影响的行数，再把本批最后检查到的ID写入 cleanup_checkpoint。如果业务主键不是单调递增的，先建好适配的联合索引再设计游标逻辑。

用小批事务控制锁和日志体积

大事务的问题不只是长期占着锁不放，InnoDB 还要保留全量旧版本，回滚段和复制日志体积会同步暴涨，中途如果出问题，回滚花费的时间甚至可能比删除本身的时间还要长。

START TRANSACTION;

DELETE FROM order_events
WHERE id > 8000000
  AND id 

实际写清理脚本的时候，要把每批的开始时间、last_id、upper_id、affected_rows 和耗时都写入任务日志。批次事务成功提交之后再推进游标记录进度，不能先写检查点再提交事务，不然进程意外中断会出现“日志显示已经删完，表里数据还在”的不一致问题。

把暂停和回滚设计成任务原生能力

清理脚本至少要预留三个可观测状态：running、paused、failed。检测到锁等待超过预设阈值、复制延迟持续走高或是业务进入高峰区间时，等当前批次执行完后自动进入 paused 状态，不要在一条大删除语句里强行中断。

删除操作本身没办法像普通更新那样直接回滚几小时前的全量数据，要恢复数据通常走两类方案：先把待删行写入归档表校验完行数之后，再删除原表里的对应数据；或者提前做好可恢复的全量备份，提前跑通恢复演练确认流程没问题。归档表也要控制索引数量，不然“先归档再删”的方案会把写放大问题转嫁到另一张大表上。

三种清理方案如何取舍

如果表天然就是按天或按月写入，后续数据生命周期规则也很明确，可以把分区方案作为长期优化方向。已经建好的非分区大表，不要为了赶一次临时清理任务就大动干戈改全表结构，先用游标分批清理先把性能问题止住，后续再评估迁移分区的必要性。

执行前后的核对清单

1. 提前固定cutoff删除边界和upper_id上限，记录任务版本，避免任务每次重启边界自动偏移。
2. 用EXPLAIN核对执行计划里的索引命中情况、扫描行数和排序情况。
3. 从最小的批量起步跑，全程观察 SHOW PROCESSLIST、锁等待时长、主从复制延迟和磁盘剩余空间的变化。
4. 只有在事务提交成功之后才能更新检查点进度，失败的批次完整保留错误信息和当时的输入范围。
5. 清理完成后抽查cutoff边界前后的记录，核对归档数据或是备份文件的可恢复性。

相关问题

清理任务能不能用 OFFSET 分页？

不建议这么做。删除操作会动态改变结果集的行数，OFFSET 在批次跳转的时候很容易漏掉记录；用固定上限加主键游标的方案，更容易确认整个扫描过程不会漏扫数据。

每批删除多少行不会锁表？

没有通用的固定数值。可以从 5000 或 10000 行起步测试，以锁等待时长、事务耗时和复制延迟作为判断依据逐步往上调整。

删除旧数据前必须归档吗？

如果这批数据后续还有审计、对账或是找回的需求，就一定要先归档或是提前确认备份文件可恢复；只是随便复制一份文件但没跑过恢复验证，不算完成数据保护。

小结

MySQL 大表清理的核心思路，就是把一次不可控的大删除，拆成有明确边界、有操作日志、支持随时暂停的连续小操作。主键游标用来保证推进过程稳定无重复无遗漏，小批事务用来控制影响面不会波及全库，检查点和备份机制用来保证出问题之后也能有迹可循。先在业务低峰做一轮压测，再把阈值和自动暂停条件写到任务配置里，跑起来就会很稳。