千万级大表怎么清理数据：分批删除、分区与空间回收
一条
DELETE FROM t WHERE created_at < ?在测试环境几秒就跑完了，放到生产上却让 replica（从库）延迟了半小时，期间还有一批业务更新因为锁等待超时失败。删除大量数据本身不难，难的是删的过程中不影响线上。
本文用一张确定性生成的 300 万行事件表，在 MySQL 8.4.11 上实测了三种清理方式：一次性删除、分批删除、删除分区。重点观察三件事：锁住了哪些数据、产生了多少 Binlog、磁盘空间有没有还回来。
一、先说结论
- 一次性删除大量数据，风险在事务的大小。 实测删除 100 万行产生 175MB Binlog，是一个事务；replica 要把这个大事务完整回放一遍，期间持有同样多的锁，后面的事务全部排队。
- 删除条件没有索引，等于锁表；有索引也不一定会用。 实测删除 31 万行（全表约 10%）时，优化器放弃了
created_at上的索引、选择全表扫描，结果与没有索引一样，300 万行全部被锁住。 - 分批删除把一个大事务拆成许多小事务。 实测 100 批共 6.7 秒，单批最长 89ms，每批约 1.75MB Binlog，锁很快释放。
- 按时间分区的表，清理可以直接
DROP PARTITION。 实测删除 100 万行的分区只用 9ms，Binlog 只写入 219 字节。 DELETE不会缩小表文件。 实测删除三分之一的数据后文件大小不变，需要OPTIMIZE TABLE重建表才能回收空间，重建本身也是一次昂贵操作。
二、一次性删除：问题出在哪
实验表：
CREATE TABLE event_log (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
biz_id BIGINT NOT NULL,
content VARCHAR(200) NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_created (created_at)
);
-- 300 万行，每天 1 万行，时间从 2025-01-01 到 2025-10-27；content 为 150 个字符；表文件约 664MB删除最早 100 天的数据：
DELETE FROM event_log WHERE created_at < '2025-04-11';
-- 100 万行，3.4 秒3 秒多看起来不长，但要一起算上这几笔账：
| 影响 | 实测或原理 |
|---|---|
| Binlog | 这一个事务写入 174.7MB（SHOW BINLOG EVENTS 中只有 1 个 Xid） |
| 复制 | replica 要在这个事务提交后才开始回放它，回放期间后续事务排队，表现为复制延迟；replica 机器更弱或负载更高时，延迟可能远超 3 秒，见 MySQL 复制与延迟 |
| Undo | 删除前的行全部记入 Undo，事务提交前不能清理；如果中途失败，回滚同样耗时 |
| 锁 | 事务提交前，被删除的行和相关间隙一直被锁住，见下一节 |
binlog_row_image 默认为 FULL，行格式 Binlog 会记录每一行被删除前的完整内容，所以删除越宽的行，Binlog 越大。一个事务的 Binlog 超过 max_binlog_cache_size 还会直接报错失败。
三、删除事务持有期间锁住了什么
为了看清锁的范围，让删除事务在提交前保持 40 秒，期间读取 performance_schema.data_locks，并用另一个会话探测（innodb_lock_wait_timeout = 2）：
-- 会话 A
BEGIN;
DELETE FROM event_log_copy WHERE created_at < '2025-02-01'; -- 31 万行
DO SLEEP(40);
ROLLBACK;同一个删除范围，实测了三种情况：
| 探测语句 | 条件列有索引，优化器选全表扫描 | 用提示强制走 idx_created | 条件无索引 biz_id <= 310000 |
|---|---|---|---|
| 执行计划 | type=ALL | type=range，key=idx_created | type=ALL |
| 主键上的记录锁 | 3,037,976 个 Next-Key | 310,001 个记录锁 | 3,037,976 个 Next-Key |
| 更新范围内的一行 | 阻塞，超时 | 阻塞，超时 | 阻塞，超时 |
| 更新范围外的一行 | 阻塞，超时 | 1.9ms 完成 | 阻塞，超时 |
| 在删除范围内插入 | 阻塞，超时 | 阻塞，超时 | 阻塞，超时 |
在范围边界 2025-02-01 插入 | 阻塞，超时 | 2.3ms 完成 | — |
| 在很远的位置插入 | 阻塞，超时 | 2.0ms 完成 | 阻塞，超时 |
第一列是最容易误判的情况：created_at 上明明有索引，但 31 万行约占全表的 10%，优化器估算走索引再回表的代价比全表扫描更高，于是选择了全表扫描。在 REPEATABLE READ 下，扫描到的每一行都被加锁并持有到事务结束，不管它是否满足条件，效果和没有索引完全一样。锁的数量比 300 万多出约 3.8 万个，是每个数据页末尾的 supremum 伪记录。
只有真正按索引范围扫描时（第二列），锁才局限在删除范围和其中的间隙内。所以清理数据之前，不仅要确认条件列有索引，还要用 EXPLAIN 确认这条删除实际走了索引；删除范围占比越大，越容易被优化器放弃。分批删除天然解决了这个问题：每批只删一万行，索引范围扫描总是更便宜。
四、分批删除
4.1 写法
把一个大删除拆成许多小事务，每批删一万行左右，删完立即提交：
-- 循环执行，直到影响行数为 0
DELETE FROM event_log_copy
WHERE created_at < '2025-04-11'
ORDER BY created_at
LIMIT 10000;实测（存储过程中循环执行，每条 DELETE 自动提交；每批耗时记在临时表中，不写入 binlog）：
| 指标 | 一次性删除 | 分批删除 |
|---|---|---|
| 删除行数 | 1,000,000 | 1,000,000 |
| 事务数 | 1 | 100 |
| 总耗时 | 3.4s | 6.7s |
| 单个事务最长耗时 | 3.4s | 89ms |
| 单个事务 Binlog | 174.7MB | 约 1.75MB |
| Binlog 总量 | 174.7MB | 174.8MB |
总耗时翻倍，Binlog 总量不变，但每个事务的锁持有时间和 replica 回放压力都降了两个数量级。这笔交易几乎总是值得的。
4.2 按条件 LIMIT，还是按主键区间
另一种常见写法是先查出主键范围，再按区间删除：
SELECT MIN(id), MAX(id) FROM event_log WHERE created_at < '2025-04-11';
DELETE FROM event_log WHERE id >= ? AND id < ? + 10000 AND created_at < '2025-04-11';两种写法的取舍：
| 写法 | 优点 | 注意 |
|---|---|---|
WHERE 条件 ORDER BY 索引列 LIMIT n | 简单，每批都删满 | 需要条件列有索引；后面的批次要先跳过前面已经标记删除、尚未被 purge 的记录 |
| 按主键区间 | 每批定位稳定，便于记录进度、断点续跑 | 主键不连续或范围内夹杂不该删的行时，会出现很多空批次 |
区间法有一个容易踩的坑：业务里偶尔会有「补写的旧数据」，时间很早、主键却在表尾。实测在表尾插入一条 created_at = 2025-01-05 的记录后，MIN(id)、MAX(id) 变成 1 和 3,000,001，每 1 万个主键一批切出了 301 批，其中 200 批是空的。用区间法时，最大值要按业务条件查准，或者在删除过程中动态推进。
4.3 限速：让清理任务知道什么时候该停
每批之间检查水位，超过阈值就暂停：
- 复制延迟：replica 上业务心跳的落后量或 GTID 集合的差距；
SHOW REPLICA STATUS的Seconds_Behind_Source只能作参考，原因见 MySQL 复制与延迟； - History List Length：
information_schema.INNODB_METRICS中的trx_rseg_history_len，持续上涨说明 purge 跟不上； - 业务指标：source 的 CPU、活跃连接数、核心接口延迟。
清理任务安排在业务低峰期执行，并支持从中断的位置继续。开源工具 pt-archiver（Percona Toolkit）实现了「分批读取、写入归档表、删除源数据、按 replica 延迟限速」这一整套流程，云厂商的数据管理服务通常也提供类似的定时清理任务。
五、分区表：把删除变成删文件
如果数据天然按时间过期，用范围分区可以让清理几乎不花成本：
CREATE TABLE event_log_p (
id BIGINT NOT NULL AUTO_INCREMENT,
biz_id BIGINT NOT NULL,
content VARCHAR(200) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at), -- 主键必须包含分区键
KEY idx_created (created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
PARTITION p2025q1 VALUES LESS THAN ('2025-04-11'),
PARTITION p2025q2 VALUES LESS THAN ('2025-07-20'),
PARTITION pmax VALUES LESS THAN (MAXVALUE)
);ALTER TABLE event_log_p DROP PARTITION p2025q1; -- 100 万行，9ms实测 Binlog 位点只从 158 变成 377，记录的是一条 DDL 语句，而不是 100 万行数据。每个分区是独立的 .ibd 文件，删除分区就是删除文件，空间立即归还。
代价见 单表多大该拆分 中的分区表一节：主键和唯一索引必须包含分区键，不带分区键的查询要扫描所有分区。另外需要一个定时任务提前创建新分区，否则数据会全部落进 pmax。
六、删完之后，空间回来了吗
| 状态 | event_log_copy.ibd 文件大小 |
|---|---|
| 300 万行 | 664MB |
| 删除 100 万行之后 | 664MB（不变） |
OPTIMIZE TABLE 之后（耗时 4.5s） | 508MB |
DELETE 只是把记录标记为删除，再由 purge 线程回收成页内的空闲空间，供后续插入复用，但不会把空间还给文件系统。要缩小文件，需要重建表：
OPTIMIZE TABLE event_log_copy;
-- InnoDB 会提示 "Table does not support optimize, doing recreate + analyze instead"
-- 等价于 ALTER TABLE ... ENGINE=InnoDB，在线重建重建是一次完整的表复制：需要额外的磁盘空间，大表上耗时很长，也会产生复制延迟。另外，删除之后如果马上要做恢复演练，要记得空间回收不影响 binlog：被删的数据只能从备份与 binlog 找回，见 MySQL 误删恢复。如果删除后的空间很快会被新数据填满，就不必重建；只有长期不会再增长时才值得做，并且要像大 DDL 一样安排窗口、使用在线变更工具。
七、一份清理方案检查表
- 确认条件走索引：
EXPLAIN删除语句，type不能是ALL；删除范围占比大时优化器可能放弃索引，改成分批。 - 先备份或先归档：需要保留的数据先写入历史表或导出，确认条数一致后再删。
- 估算规模：要删的行数、行宽，据此估算 Binlog 总量和批次数。
- 分批与限速：每批的行数按单批耗时和 binlog 大小确定（本文实验用 1 万行），批间检查复制延迟与 History List Length。
- 支持断点续跑：记录进度，任务中断后可以继续。
- 低峰执行，持续观察：source 的 CPU、锁等待、复制延迟、核心接口延迟。
- 评估是否回收空间：需要时安排在线重建。
- 从根上解决：长期需要清理的表，考虑按时间分区，或在写入侧就设计好冷热分离。
八、常见误区
- 「删除很快，一条 SQL 就行」：source 上快，不代表 replica 回放和锁等待没有影响。
- 「条件列有索引，就只锁删除范围」：实测删除约 10% 的行时优化器选了全表扫描，全部记录都被锁住。
- 「分批删除更慢，没必要」：总耗时变长，但单个事务的锁时间和 Binlog 大小降了两个数量级。
- 「删完数据磁盘就空出来了」：表文件不会变小，需要重建表。
- 「
TRUNCATE可以代替按条件删除」：TRUNCATE清空整张表，不能带条件。 - 「用分区表以后就不需要清理任务了」：仍然需要定时创建新分区、删除旧分区。
小结
清理大表的关键是控制单个事务的大小：条件必须走索引，大删除拆成小批次，每批之后看复制延迟和 purge 水位。数据按时间过期的表，最好在设计时就按时间分区，让清理变成秒级的 DROP PARTITION。删除之后是否重建表回收空间，要看空间是否还会被复用。
配套实验
- codesphere-labs/storage/mysql-large-table-cleanup：300 万行事件表的一次性删除、三种锁范围、分批删除、主键区间、
DROP PARTITION与空间回收（验证记录）
参考资料