前阵子有个客户找我说他们的历史数据清理任务把核心库拖垮了。我上去一看凌晨两点发起的 DELETE 删到现在还没结束业务查询早就堵成一片从库延迟到了几百秒磁盘也被撑得告警。问题本身并不复杂——就是一张积累了上亿行的流水表旧数据需要批量删除但负责的同学直接跑了一条大 DELETE 就等着它结束。这个场景估计很多人都不陌生MySQL 里处理海量数据批量删除从来不是“执行一条 DELETE 等它跑完”这么简单。这篇东西把我这些年处理过的几种批量删除方案做个系统整理手工分批 DELETE、临时表 RENAME、分区表 DROP PARTITION、以及 pt-archiver 工具。每种方案我都会讲清楚底层原理、适用场景和实操细节特别是那些文档里不会写的坑。如果你正在被大表清理问题困扰或者想提前做数据生命周期的架构设计这篇应该能帮你省不少冤枉时间。1. 为什么一条 DELETE 会拖垮业务锁、undo 与主从延迟正式开始给方案之前我必须先把 InnoDB 里 DELETE 的真实成本讲透。因为你如果不知道一条 DELETE 到底“贵”在哪里后面所有方案都会变成知其然不知其所以然的照猫画虎。1.1 锁的影响范围远比你以为的大InnoDB 默认隔离级别是 RR可重复读在这种隔离级别下写操作除了对命中行加排他锁还要在索引扫描区间上加间隙锁或临键锁用来防止幻读。只要你的 DELETE 条件不是精确命中唯一索引那么扫描过的每一段索引区间都可能被锁覆盖。我举一个常见例子你要删create_time 2023-01-01的历史数据对应的普通索引扫描范围可能覆盖几百万个 key。删除过程中不仅这些行被锁期间对这些索引区间的插入也会被阻塞。更糟的是如果 DELETE 条件设计得不好比如查询计划走了全表扫描那基本等于把整张表写操作全部锁死。这就是为什么你会看到“一条 DELETE 把数据库搞挂了”的现场——不是 DELETE 本身多恐怖而是它持锁时间太长把业务写流量全部堵在后头排队。1.2 undo log 膨胀删除不是真正的物理删除很多人不知道InnoDB 的 DELETE 并不会立刻把数据从磁盘上物理抹掉。它的实际动作是把目标记录标记为删除同时生成相应的 undo log 和 redo log后面再由后台 purge 线程逐步清理物理记录和索引项。这个设计是为了支持 MVCC多版本并发控制但它带来一个很实际的问题大批量删除产生的 undo log 不会立即消失而是在 undo 表空间里持续堆积。我处理过一个案例一条 DELETE 删了两千万行undo 表空间直接从初始大小涨了快 200GB。此时如果你的业务有需要走一致性读的长查询它们会被迫沿着越来越长的版本链找回旧版本查询性能直线下降。更直接的后果是即使删除事务成功提交了表文件的大小也不会立刻缩小。因为空间回收依赖 purge 线程慢慢做而 purge 的清理速度通常赶不上海量删除的产生速度。我曾经删完数据后发现表文件还是几百 GB询问是不是删错表了后来查了一遍才发现是物理文件根本没及时收缩。1.3 binlog 与主从延迟大事务在备库的放大效应第三个隐藏炸弹是 binlog特别是 row 格式。主库执行一条大 DELETE 时binlog 会逐行记录变更前后的镜像。删除一行数据就要在 binlog 里写入这一整行的完整内容。假设你的行平均 1KB删一千万行binlog 就要膨胀到 10GB 级别。这个量级的 binlog 写到磁盘、传到从库、从库再逐条回放任何一个环节都会卡住。statement 格式下情况也好不了太多主库是一条 SQL 秒级提交但从库回放时依然是同一个大事务SQL 线程要跑同样长时间。结果就是从库延迟从 0 一路飙升到几千秒期间从库读到的都是过期数据。理解了这三层成本你就明白为什么所有靠谱方案都在做同一件事把“一个超大事务”拆成“多个提交快、锁周期短的小事务”或者干脆绕开 DELETE 本身。2. 分批 DELETE性价比最高的手工控制方案对于单次清理千万级以内的数据手工分批 DELETE 是最直接、最可控的方案。它不需要改表结构不需要引入额外工具只要写对循环逻辑就不会出现锁死或延迟爆表的情况。2.1 按主键范围滑窗而不是盲目 LIMIT先泼一盆冷水网上很多教程让你“DELETE … LIMIT 1000 循环删”这个写法在大表上是有隐患的。原因很简单LIMIT配合无索引扫描时每批删除都需要从头扫描表找到符合条件的 1000 行越到后面扫描成本越高整个删除过程会越来越慢最终慢到不可接受。我的习惯是按主键范围做滑窗。假设有一个自增主键id每次删除一个(last_id, last_id batch_size]区间内的目标数据每批处理完后更新last_id。这样做的好处是每批都能用主键精确命中数据区间不重复扫描历史区间锁的范围被控制在很小的主键区间内业务影响几乎可以忽略方便统计进度也方便在删除中途随时暂停。为什么强调主键因为 InnoDB 的聚簇索引天然有序。你只要记住当前处理到哪个主键位置就能继续往后推就像一把游标在数据里前进每步只看眼前这一段。2.2 一个可以“直接抄”的存储过程样例下面是我实际项目里用过多次的存储过程模板。它的逻辑是把create_time符合清洗条件的历史数据按主键顺序每批删除 5000 行每批之间停顿 0.5 秒让锁释放、redo 落盘、从库也能喘口气DELIMITER $$ CREATE PROCEDURE sp_batch_delete() BEGIN DECLARE v_last_id BIGINT DEFAULT 0; DECLARE v_batch_size BIGINT DEFAULT 5000; DECLARE v_affected BIGINT DEFAULT 1; -- 先取目标数据的最小主键作为起点 SELECT COALESCE(MIN(id), 0) INTO v_last_id FROM big_table WHERE create_time 2023-01-01; WHILE v_affected 0 DO -- 每批按主键正序取 v_batch_size 行物理删除 DELETE FROM big_table WHERE create_time 2023-01-01 AND id v_last_id ORDER BY id ASC LIMIT v_batch_size; SELECT ROW_COUNT() INTO v_affected; IF v_affected 0 THEN SET v_last_id v_last_id v_batch_size; COMMIT; DO SLEEP(0.5); END IF; END WHILE; END$$ DELIMITER ;这里用v_last_id v_last_id v_batch_size推进游标是因为每批理论上都删够了 5000 行。如果某批因为数据稀疏没删满 5000 行ROW_COUNT()会返回真实删除行数此时你想更精确的话更稳妥的做法是先SELECT id FROM … ORDER BY id LIMIT v_batch_size, 1把下一批边界查出来再以id BETWEEN v_start AND v_end执行删除。上例为了可读性做了简化实际项目推荐用“先取边界再删区间”的方式避免边界计算误差。SLEEP(0.5)非常重要它不只是给主机减压更是给从库回放留出追赶时间。还有一件事不能忘存储过程本身是在事务里工作的所以每个批次结束后要显式COMMIT否则所有批次都会并入同一个大事务前面做的拆分努力全部白费。2.3 批次大小和限速节奏怎么定批次大小的选择没有一个万能数字它跟你的单行数据宽度、磁盘随机写能力、redo log 大小、以及 binlog 复制方式都有关系。根据我的测试经验单行 300 字节以内的紧凑表batch_size 5000是比较舒服的单行包含大字段比如带 JSON、TEXT、BLOB建议降到 1000 以下否则单批 binlog 体积就可能上 MB 级事务执行时间应控制在 1 秒内如果你的批次需要跑好几秒说明批次太大。限速节奏方面我最常用的是“看从库延迟自动调速”每批删除后检查一次Seconds_Behind_Master延迟超过阈值就多睡一会儿。你可以把这段逻辑直接写进存储过程也可以用一个外部脚本控制调用频率。原理都是一样的——让主库有节奏地生产 binlog从库才能健康地消费。3. 临时表 RENAME把删除变成“秒级交换”如果删除的数据占比非常大比如说一张表积累了 10 亿行现在只保留最近三个月的数据这种情况下即使分批 DELETE 也需要跑很长时间因为你要逐行清理海量数据。此时更好的思路不是“删”而是“搬”把要保留的数据搬进新表然后把旧表直接丢弃。3.1 一个反直觉的思路数据是“搬”走的不是“删”掉的这个方案的底层逻辑利用了 MySQL 两个特性INSERT … SELECT可以按条件抽取数据RENAME TABLE可以在秒级完成表名交换。因此整个方案的时间成本只取决于“保留数据量”而不是“删除数据量”。举个例子表里有 5000 万行历史数据需要保留的只有 100 万行。分批 DELETE 即使每批删 5000 行也要循环约一万次跑数小时。但临时表方案只需要把 100 万行搬到新表然后通过一次原子性的RENAME TABLE完成切换再DROP TABLE丢弃旧表。理想情况下从开始搬迁到切换完成可能十几分钟就够了。而且这个方案的收益不只是快DROP TABLE对 InnoDB 来说是删除表空间不需要逐行 purge不会产生海量 undo log锁的影响也远小于 DELETE因为无论是INSERT SELECT还是RENAME锁的范围都更可控。3.2 完整执行流程与注意事项我常用的操作步骤如下每一步都有它的目的建新表。推荐CREATE TABLE new_table LIKE old_table;这样索引、自增值定义、默认值都会原样复制过来。如果你用CREATE TABLE AS SELECT索引需要手动一个个补容易漏。分批搬迁保留数据。在低峰期执行INSERT INTO new_table SELECT * FROM old_table WHERE 保留条件;。如果保留数据量大同样建议分批插入每批COMMIT一次避免单个事务过大。原子交换表名RENAME TABLE old_table TO old_table_bak, new_table TO old_table;RENAME TABLE是原子的执行期间其他会话要么看到旧名字要么看到新名字不会出现中间状态。这个操作本身非常快一般只要几十毫秒。验证业务。查询几下新表的行数、抽样数据、执行计划确认核心查询正常后再执行DROP TABLE old_table_bak;。如果担心DROP TABLE大文件造成磁盘 IO 瞬时飙高可以用我后面会说到的硬链接 truncate 技巧。整个过程有一个很重要的前置条件业务在搬迁到切换这个窗口内的写入不能被遗漏。如果表还在持续接收新数据那么INSERT SELECT搬迁完成到RENAME切换之间旧表可能又产生了新写入切换后这些数据会丢失。所以这个方案要么安排在业务停机窗口要么在切换前对旧表加一个短暂写锁或者配合应用侧做切换暂停。3.3 元数据锁、自增值、外键这些容易翻车的细节这个方案看起来简单但实际推进中有一堆细节会让它瞬间翻车元数据锁等待RENAME TABLE需要获取参与交换所有表的元数据锁。如果此时有事务正持有着旧表的访问锁RENAME就会一直等待。我见过最典型的情况是线上有一条跑了很久的慢查询没结束后面跟着一堆 DDL 全卡住了。所以执行RENAME之前要设短一点的lock_wait_timeout比如SET SESSION lock_wait_timeout 5;超时就自动失败不要挂住整个实例。自增列重新计数INSERT SELECT导入数据后新表自增值会从当前最大 ID 继续递增。如果旧表 ID 已经冲到 8000 万新表保留数据最大 ID 只有 100 万那么新表下一步会从 100 万零 1 继续。这在大多数业务场景没问题但如果业务侧有硬编码依赖“ID 必须大于某个值”就需要注意了。外键关联如果有其他表通过外键引用这张表RENAME之后老外键关系会指向新的表名但业务用的还是旧表名可能造成约束错乱。我的建议是有外键关系的表不建议用这个方案或者要先彻底梳理关联关系再执行。触发器与视图基于表创建的视图在RENAME后仍然指向逻辑表名因为视图里的引用是名字业务不会感知变化。触发器如果是BEFORE DELETE这类跟表绑定的也需要在旧表上确认没有依赖再 DROP。4. 分区表把清理动作降级为 DROP PARTITION如果你经常需要做历史数据清理那最优解不是每次临时抱佛脚而是从设计上就把表做成分区表。分区表清理历史数据的速度可以说是“秒杀”其他方案因为它的删除动作根本不是逐行 DELETE。4.1 为什么 DROP PARTITION 能“秒删”历史数据MySQL 分区表的底层实现是每个分区对应独立的物理存储段SQL 层面虽然还是一张表但物理上数据已经被切分到不同的分区文件中。当你执行ALTER TABLE big_table DROP PARTITION p202301;MySQL 直接释放这个分区的物理空间整个过程不逐行扫描、不生成大量 undo log、不会产生大事务的 binlog。在 InnoDB 看来这和DROP TABLE一个分区子集类似速度极快几亿行的历史数据也常常在秒级完成。这个方案天然规避了锁、undo、主从延迟三大问题。业务侧所有查询如果是按分区键条件访问也不会因为删了历史分区而受影响。实现的前提是分区键设计合理。最经典的用法是按时间分 RANGE 分区CREATE TABLE big_table ( id BIGINT NOT NULL, create_time DATETIME NOT NULL, -- 其他字段... PRIMARY KEY (id, create_time) ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS(2023-02-01)), PARTITION p202302 VALUES LESS THAN (TO_DAYS(2023-03-01)), PARTITION p202303 VALUES LESS THAN (TO_DAYS(2023-04-01)) );这里有个关键限制分区键必须包含在主键或唯一键里。如果你的主键只有id那么要按create_time分区就得把create_time也加进主键改成联合主键(id, create_time)。这会让很多开发同学不适应但这是 MySQL 分区表的硬性要求。4.2 存量表改成分区表的现实代价如果表在建库时没做分区现在想补救要付出的代价就不小了。存量表改分区表通常要执行一次全表重建ALTER TABLE big_table PARTITION BY RANGE (TO_DAYS(create_time)) (...);在 5.7 和 8.0 版本里这个 DDL 都不能完全在线完成执行期间需要拷贝数据并重建表。表数据量上亿的话可能要跑很长时间期间占用大量磁盘空间和 IO业务写入会被阻塞或限制。所以对存量超大表做分区改造一般建议配合“临时表 RENAME”方案来做先建分区新表把数据按分区规则搬过去最后切换。这样可以把停机时间压缩到一个可接受的窗口。我的建议是如果这张表未来还会持续积累数据、且存在周期性清理需求尽早改造成分区表如果只是临时清理一次用临时表方案更划算。4.3 按月滚动清理的常态化玩法分区表真正香的地方在于它可以常态化清理。我维护过一张日志流水表每个月月初定时任务自动执行ALTER TABLE big_table DROP PARTITION p_last_year_month;旧分区一删数据空间即释放新分区继续接收数据整个清理过程不会对业务产生任何干预。这种玩法相当于把“清数据”从一次性的高危操作变成了自动化的日常运维动作。使用中还有两个细节容易被忽略。第一DROP PARTITION至少要保留一个分区不能把表所有分区删光。第二如果业务偶尔要查已删分区的历史数据你得提前把需要审计的数据备份走否则分区 DROP 后就真的找不回来了。5. pt-archiver专业清理工具的完整实操如果你既不能停机改造又不能忍受手工写存储过程还想安全地在线清理几千万行数据那 Percona Toolkit 里的pt-archiver是实战中最可靠的工具。它本来就是为“大表归档 清理”场景设计的。5.1 一条最常用的在线删除命令下面这条命令是我线上的标准用法。先--dry-run模拟确认无误后再去掉参数执行pt-archiver \ --source h127.0.0.1,P3306,uroot,pxxxx,Dtest,tbig_table \ --where create_time 2023-01-01 \ --limit 1000 \ --txn-size 1000 \ --purge \ --sleep 0.5 \ --max-lag5 \ --primary-key-only \ --dry-run参数含义按使用频率排序解释一下--where删除条件这是最重要也最容易出问题的地方。条件必须能走索引否则工具每次切块扫描都会拖垮库。--limit每批读取的行数。它决定每次查询从源端拿多少行。--txn-size每个事务处理的行数默认等于--limit。一个事务提交一次大事务就这么被拆掉了。--purge只删除不做归档。如果要去归档到新表就用--dest指定目标表。--sleep每批之间的休眠秒数控制删除节奏。--max-lag从库延迟超过这个秒数时工具自动暂停等待延迟回落。--primary-key-only只检查主键相关列减少 SELECT 的数据量。如果你只是删数据这个参数能明显降低扫描成本。去掉--dry-run执行时工具会按主键把整个 WHERE 条件切分成多个小数据块chunk每块读取后逐行删除按事务提交。整个过程既不会出现大事务也会自动根据从库延迟限速。5.2 参数背后对应的限速与事务策略为什么 pt-archiver 比手工循环更让人放心因为它把“批量删除”这个动作真正打包成了有策略的调度--limit和--txn-size组合起来控制每个事务的生命周期--sleep控制主库生产 binlog 的速率--max-lag则让工具具备“感知从库压力并自我暂停”的能力。这跟手工分批 DELETE 的本质区别不在 SQL 写法而在反馈机制。手工方案你得自己写监控、自己判断当前延迟、自己手动调睡眠时间。pt-archiver 把这些逻辑内置了你只需要设置好阈值它就能在一个比较健康的节奏上推进。另一个容易忽略的好处是连接控制。手工循环删除的时候如果代码写得不好很容易在每个批次都新建连接造成连接数瞬间暴涨。pt-archiver 从源端读取和目标端写入都只维护少数几个连接并且是串行任务对实例的连接压力非常友好。5.3 实操中容易踩的三个坑尽管 pt-archiver 很成熟我也在实战中踩过几次坑这里列出来给你避雷。坑一没有主键的表不能用。pt-archiver 的核心机制是按主键切块没有主键或唯一键的表它在切块阶段就会报错。我记得 3.x 版本对这类表会警告或强制降级为低效模式无论如何都不建议。所以清理之前先确认表有主键。坑二WHERE 条件不走索引会让工具变成全表扫描。前面提到过--where条件最好与索引匹配。我第一次跑时条件选了普通字段且没有索引结果每切一块都全表扫一次跑了一个多小时才删了几万行CPU 打满。后来在该字段上建了索引同样的条件几分钟内就完成了清理。这是最典型的一个性能陷阱。坑三--dry-run不是万能的。它只能验证语法、权限、连接参数是否正确不会模拟真实数据量下的资源消耗。我建议先在测试环境用全量数据压一次或者先在主库上跑一小段观察延迟确认节奏适合后再放开跑。6. 方案对比与我的选型习惯前面四种方案各有适用场景最后我把自己选的决策逻辑和对比经验整理出来方便你直接对号入座。6.1 一张表理清四种方案的差异方案锁影响主从延迟是否需要停机实施难度最适用场景手工分批 DELETE小范围行锁可控可通过 sleep 控制低峰即可低一次性清理百万到千万级数据临时表 RENAMERENAME 瞬间元数据锁搬迁期锁可控低需要短维护窗口中保留数据量远小于删除量分区表 DROP PARTITION几乎无锁极低无需停机中高周期性滚动清理历史数据pt-archiver小事务行锁低自动限速无需停机低中在线长时间清理千万级以上数据如果你问我的个人偏好我一般是这么判断的看这张表未来 6 个月还要不要再清理。如果是一次性的临时表方案或手工分批二选一如果以后每个季度都要清一次我会直接推分区表方案前期哪怕花点代价改造都值。6.2 遇到具体业务我是怎么快速做决定的我判断的顺序大概是这样先看保留/删除数据比例。保留很少能安排维护窗口那就用临时表 RENAME省时省力。不能停窗口、又要在线删大量的上 pt-archiver把阈值调好让它挂机跑。数据量其实不大千万以内而且只是偶尔清一次手工分批 DELETE 就够了不引入新工具。业务对历史数据的查询有明确时间边界而且未来持续累积劝业务侧尽快改分区表一劳永逸。最后分享一个我自己的实操习惯无论用哪种方案动手之前我都会先看三样东西——目标表的数据量和索引情况、当前主从延迟状态、预留的删除窗口时长。数据量决定方案延迟决定下限窗口时长决定批次大小。这三样理清了批量删除这件事就成功了一大半。