★ FEATURED ARTICLE 今天想认真聊聊MySQL批量删除海量数据这件事。我最早被这类问题折腾是因为一张积累了三年多的订单明细表单表接近两亿行需要按业务规则删掉约八成历史数据。当时第一版方案很朴素直接一条 DELETE 加上 create_time 的条件扔过去结果十分钟后事务还在跑binlog 涨了好几 GB业务库瞬间出现大量锁等待最后不得不把事务回滚掉。那之后我把常见的几种删除思路各自真实跑过一遍也在模拟项目X上反复验证过参数所以这篇把方法、原理和坑一起讲清楚。适合正在被慢删除、锁等待、磁盘告警折磨的后端开发和兼任 DBA 的运维同学参考。1. 先看DELETE在InnoDB里到底干了什么为什么越删越慢很多人觉得 DELETE 就是把行标记一下、空间等一下回收但实际上 InnoDB 的删除动作远比想象中复杂。如果不先把这个问题看清楚后面用什么方案都容易翻车。1.1 数据页、索引与回表中的“隐形工作量”InnoDB 的表数据按聚簇索引组织每一行都藏在 B 树的叶子节点里。执行 DELETE 的时候MySQL 需要先定位到目标行这个定位过程本身就可能是灾难如果 WHERE 条件上没有合适的索引引擎只能扫全表每读一个数据页都要判断里面的行是否符合条件再逐行回表。所谓回表就是根据二级索引找到主键再走主键索引找到完整记录。删除一百万行可能意味着反复扫描和定位几十万个数据页。定位到行之后也不是直接抹掉。InnoDB 会把记录标记为删除真正物理清理由后台 purge 线程负责。标记删除的同时所有包含这行数据的二级索引也要同步更新因为二级索引里同样存着指向该行的指针删除时要把这些索引项也处理掉。换句话说删一行可能触发主键索引和多个二级索引的写入涉及多个 B 树页面的修改。这就是为什么“我就删了几行怎么磁盘 IO 这么高”的原因。批量删除时大部分时间都消耗在扫描、回表和索引维护上真正“标记删除”的那一下反而只是很小的操作。所以越到后面你越会发现优化删除的核心不是怎么删得快而是怎么让扫描和回表的范围变小。1.2 undo log、binlog、redo log三重放大删除操作另一个容易被忽略的点是日志放大。InnoDB 要保证事务可回滚所以删除前会把旧值写进 undo log事务不提交这些 undo 信息就不能释放。大事务意味着大量 undo 堆积回滚段膨胀等到你想回滚的时候更痛苦。redo log 负责崩溃恢复物理页面的每次修改都要记录这部分逃不掉。最要命的是 binlog如果 binlog_format 是 ROW每删除一行都要记录这行的完整“前镜像”有些情况还会记录“后镜像”。删除几百万行binlog 可能多出几个 GB磁盘分分钟报警。而主从复制又依赖 binlog主库删得慢从库追日志更慢延迟一路飙升。可以这样理解删除一行数据实际上同时在写好几份账本。如果事务特别大账本写不完还不敢撒手所有资源都被它攥着。这也是为什么所有靠谱的海量删除方案核心思路都是同一个——拆小事务、分批提交让日志和锁能及时释放。1.3 大事务一锅端的连锁反应从锁等待到主从告警一次 DELETE 几千万行在事务提交之前它碰过的每一行都持有锁。业务里只要还有别的请求在修改相同范围的数据就会陷入锁等待。等技术事务跑完再提交那些等待的请求可能已经超时连带着应用层报错、重试进一步放大压力。事务不结束undo log 也不能被 purge历史版本越积越多。InnoDB 里有个概念叫 History List Length就是用来衡量未清理历史版本的链表长度大删除期间这个值会飞快上涨。回滚段膨胀说明什么问题说明旧版本数据还在占用空间哪怕你删除了行物理文件也不会立刻变小。主从延迟通常和大事务绑定出现。主库的 binlog 串行吐出几 GB 日志从库只有一个 SQL 线程按顺序回放相当于一边是水龙头放水一边只有一根小管子接延迟只会越拉越大。我见过一个案例主库删除跑了半小时从库延迟跑到两小时最后只能先停掉删除任务等从库追平。所以在设计任何删除方案之前先默认一个前提绝不能让一个大事务长时间霸占数据库。2. 方案一按主键范围分批删把大事务拆成小步走这是最通用、最不需要前期改造的方法也是我把各种复杂方案之前默认的保底手段。它不需要分区表不需要改表结构只要有一张主键表就能跑。2.1 一把梭的教训为什么千万行级批处理被叫停我在某内容平台处理过一次活动日志清理表里积累了几千万行三个月前的数据。当时觉得很简单直接写了一条 DELETE FROM activity_log WHERE create_time 某个时间点然后在低峰期执行。结果不到十分钟监控里锁等待开始飙升大量业务查询变慢binlog 文件眼看着往上涨。最终只能发 kill 把那条语句杀掉等事务回滚前后折腾了一个多小时数据一行没删掉。那次之后我把执行策略完全改成分批。同样一张表按主键 id 范围每批只删除 1000 行每批之间 sleep 0.2 秒。同样的目标范围虽然总耗时变长了但全程锁等待几乎消失从库延迟也一直压在极低水平。这件事给我最大的教训是批量删除的指标不是“删得快”而是“不影响在线业务”。2.2 分批删除SQL模板与批次大小计算如果只是删除一张表里满足某个条件的部分数据一个比较稳的模板长这样-- 假设已经确定要删除 create_time 2023-01-01 的数据 -- 每轮先取一批 id拿到这批的边界 SELECT id FROM big_table WHERE id last_id AND create_time 2023-01-01 ORDER BY id LIMIT 1000; -- 然后删除这批 id 对应的行 DELETE FROM big_table WHERE id IN (...);这里的 last_id 是上一批的最大 id下一轮从它后面继续。删除条件里同时带 id 范围和 create_time 条件既能保证进度不重不漏也能防止把新写入的高位数据误删。批次大小怎么定没有万能数字。需要根据每行平均大小、磁盘性能和从库延迟来试。我的经验是普通服务配置、单行 200 字节左右的数据每批 1000 到 5000 行都是合理区间如果单行很大几百字节甚至带 text 字段那就把批次压到 200 到 500 行。先跑一轮统计单批耗时如果一批超过 2 秒说明批次太大或索引不够好需要减小。每批提交后建议 sleep 一小段时间比如 0.2 秒到 1 秒。sleep 不是为了装样子而是给后台 purge 留时间清旧版本也帮从库追 binlog。全程可以盯 SHOW REPLICA STATUS 里的 Seconds_Behind_Source一旦明显上涨就拉长 sleep。2.3 主键断裂、数据写入冲突、sleep设置三个实操细节第一个坑是主键并不连续。如果用 id 等分区间比如把 1 到 1 亿分成 100 段每段 100 万很可能有的区间实际没有数据有的区间却堆着几百万行。正确做法是始终用“上一批最大 id 作为下一批起点”让引擎天然跳过空洞。第二个坑是删除条件和写入冲突。如果业务在删除期间还会插入 create_time 更早的数据那 DELETE 的时间条件可能把新插入的数据也删掉。通常我会先和业务约定好删除窗口或者干脆把删除边界时间固定住业务方保证不再写这个边界之前的数据。若实在无法约定就要用临时归档表方案先把待删除的主键 id 存下来再按 id 关联删除。第三个坑是 sleep 的长度。设太短从库延迟会越积越高设太长删除窗口拉长可能一个晚上都跑不完。建议脚本里把 sleep 做成可动态调整的变量延迟高了自动加长延迟正常就减少。另外可以顺手监控 InnoDB 的 History List Length这个值持续快速上涨说明 UNDO 清理跟不上需要放慢节奏。-- 监控参考 SHOW ENGINE INNODB STATUS\G -- 关注 History list length SHOW REPLICA STATUS\G -- 关注 Seconds_Behind_Source3. 方案二游标与keyset翻页删除避开LIMIT OFFSET翻车分批删除里最关键的其实是“怎么取下一批”。上面我用了 id last_id 的方式这在工程上有一个更正式的名字叫 keyset pagination也就是用上一批的边界值当游标而不是用 LIMIT OFFSET。3.1 LIMIT 100000,1000为什么越翻越慢很多人写分页查询的时候习惯了 LIMIT 100000, 1000但这样的查询意味着引擎要先把前 10 万行找出来再丢掉只留下后面的 1000 行。每翻一页代价都在累计offset 越大扫描的行越多。用在普通查询上都可能卡用在删除上就更糟。删除场景还有个特殊问题offset 不够稳定。你删了一批之后表的行数变了如果还用同样 offset 往下翻要么漏删数据要么重复删除。所以用 LIMIT OFFSET 做批量删除基本是拿自己的数据在赌。正确的方式是用一个“锚点”比如上一批处理到的最大主键 id下一批只取 id 大于这个锚点的记录。这样无论前面的数据怎么变化游标都不会受影响。3.2 keyset游标写法用上一批的最大主键做锚点一个可以直接借鉴的存储过程版本是这样的。它利用 MIN、MAX 和子查询每次只取 id 段内满足条件的数据删完再推进游标DELIMITER $$ CREATE PROCEDURE sp_batch_delete() BEGIN DECLARE v_last_id BIGINT DEFAULT 0; DECLARE v_max_id BIGINT; DECLARE v_rows BIGINT; REPEAT -- 取当前批的 id 上限 SELECT MAX(id) INTO v_max_id FROM ( SELECT id FROM big_table WHERE id v_last_id AND create_time 2023-01-01 ORDER BY id LIMIT 5000 ) t; IF v_max_id IS NULL THEN SET v_rows 0; ELSE DELETE FROM big_table WHERE id BETWEEN v_last_id 1 AND v_max_id AND create_time 2023-01-01; SET v_rows ROW_COUNT(); SET v_last_id v_max_id; END IF; IF v_rows 0 THEN DO SLEEP(0.2); END IF; UNTIL v_rows 0 END REPEAT; END$$ DELIMITER ;调用它之前确保 big_table 上有一个合适的索引。如果删除条件主要基于 create_time最佳索引通常是 (create_time, id)这样既能过滤时间又能利用 id 排序。没有这个索引的话每批按 id 范围扫描之后再回表判断 create_time代价会大不少。这个存储过程中v_last_id 默认从 0 开始适用于主键 id 从 1 开始自增的表。如果主键可能出现 0 或者非正数需要先调整初始值。3.3 边删边有新增数据时的幂等处理keyset 方法虽然避开了 offset 的不稳定但还有个隐藏问题如果删除过程中业务还在不停插入新数据而新数据的 id 比当前游标大本轮不会碰到它们但下一轮就可能会被扫到并删掉——前提是新数据也满足 create_time 条件。如果业务遵守了“不再写入历史时间点数据”的约定那没问题如果没遵守就可能误删。我给自己的规则很简单删除期间让目标表变成只读或者至少保证不会插入满足删除条件的数据。如果这一点做不到就采用临时归档表方案。大致思路是先建一张临时表把需要删除的 id 分批发到临时表里再从主表 JOIN 临时表删除。CREATE TEMPORARY TABLE tmp_to_delete (id BIGINT PRIMARY KEY); -- 每轮填充一批待删除 id INSERT INTO tmp_to_delete (id) SELECT id FROM big_table WHERE id last_id AND create_time 2023-01-01 ORDER BY id LIMIT 5000; -- 然后按 id 精确删除 DELETE t FROM big_table t JOIN tmp_to_delete d ON t.id d.id; -- 清空临时表继续下一轮 TRUNCATE TABLE tmp_to_delete;这样删除条件只和 id 相关即使业务在这个窗口插入了更老的数据也不会被误伤。代价是多一层临时表交互脚本复杂一点但幂等性安全很多。4. 方案三新表接力与分区摘除适合删掉绝大多数数据如果要删除的比例很高比如删掉表中 70% 甚至 90% 的数据逐行 DELETE 的成本就会非常离谱。因为我上面说过删除一行要处理索引、写日志而如果你只是想把表里绝大部分数据清掉、留一小部分完全可以用“换表”的思路绕开逐行删除。4.1 反查保数据法CREATE TABLE ... LIKE 并RENAME核心思路不是“删旧数据”而是“建新表只保留需要留下的数据然后把旧表整个扔掉”。步骤看起来不复杂先创建一张新表结构和旧表保持一致CREATE TABLE big_table_new LIKE big_table;把需要保留的数据拷贝进去比如只保留最近 90 天INSERT INTO big_table_new SELECT * FROM big_table WHERE create_time DATE_SUB(NOW(), INTERVAL 90 DAY);确认数据量和空间都没问题后用 RENAME 做原子切换RENAME TABLE big_table TO big_table_old, big_table_new TO big_table;最后确认业务正常再 DROP 掉旧表DROP TABLE big_table_old;这个方案的精髓在于旧表删除的时候是通过 DROP TABLE 完成的不会产生逐行 DELETE 的海量 undo 和 binlog。而拷贝保留数据虽然也有 IO 开销但只针对你真正想留下的那部分数据量小成本比删除海量数据低得多。如果保留字段有特殊类型、索引很多拷贝阶段同样可以分批 INSERT别一次性把大事务撑起来。切换的瞬间有元数据锁RENAME 本身很快但如果有长期运行的查询占着表RENAME 会被阻塞。所以需要提前查一下是否有未结束的长事务并在低峰期操作。4.2 分区表直接DROP PARTITION的秒级清理如果表早就按时间做了分区那清理历史数据会舒服得多。比如按月份分区的表删除某个月的数据只需要一句ALTER TABLE big_table DROP PARTITION p202301;这个操作是直接卸载整个分区速度非常快对线上业务的影响远小于 DELETE。它也不会生成对应行数的 binlog因为本质上是 DDL 级别操作日志量少很多。建表阶段长这样CREATE TABLE big_table ( id BIGINT NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL, ... PRIMARY KEY (id) ) 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)) );这里有一个必须提前讲清楚的限制分区列必须是主键或唯一索引的一部分。上面这个例子如果主键是 idcreate_time 没被包含进主键MySQL 会直接拒绝建表。很多人踩过这个坑解决办法通常是把 create_time 加进主键或者使用复合主键再或者换用类似 id 的业务键分区。这属于建表前就要想好的设计后期改造很贵。DROP PARTITION 只能按整体分区删不能只删分区里的部分行。如果只想清空一个分区里的数据但保留分区本身可以用 ALTER TABLE ... TRUNCATE PARTITION不过这个能力在不同版本里行为不完全一致生产环境要先验证。分区数量也不要搞太多几百个分区管理和查询优化都容易出问题。常规做法是每月维护脚本自动加下月分区删掉超期分区像一个滚动窗口。4.3 外键、视图与触发器带来的隐藏风险换表和分区方案在“快”的同时坑也比分批删除更隐蔽。最大一类是数据库对象依赖问题。先说外键。如果 big_table 被其他表外键引用DROP TABLE 可能会失败或者引发级联删除。RENAME 的时候外键约束指向的表名也会出问题。正确做法是先把相关外键约束删掉或调整再执行切换切换完成后重建。另外 CREATE TABLE LIKE 不会复制外键需要手动补。触发器也经常被忽略。CREATE TABLE LIKE 不会复制触发器RENAME 之后新表其实是赤裸裸的一份结构原来的 INSERT、UPDATE 触发器全没了。我之前替某个业务切换完表才发现归档触发器丢失导致历史归档链路断了一段时间。所以换表前一定要把触发器定义导出来切换后马上重建。视图一般问题不大因为视图通过表名匹配RENAME 之后新表同名视图会直接指向新表。但如果两表结构不一致视图就可能报字段不存在。最后老表的自增计数器也不会自动带过来业务如果依赖 AUTO_INCREMENT 往某个方向走需要手动设置新表的 AUTO_INCREMENT。操作顺序上先备份 SHOW CREATE TABLE、外键定义、触发器定义再动手。切换后先跑一轮核心查询验证再 DROP 旧表避免一失手删了就回不来。5. 不同业务场景下的选型清单与我的实践建议写到这里方法都过了一遍但真正到了生产环境大多数人面临的问题不是“不知道方法”而是“不知道选哪个”。我给几个非常实操的判断依据。5.1 四种方案横向对比表方案删除比例适合量级业务影响实现成本主要风险按主键分批 DELETE40% 以下千万级到亿级低可在线跑低耗时长、binlog 和 undo 增长、从库延迟keyset 游标删除10%~50%数据持续增长的大表低可控中需要好索引删除条件变化时需临时表辅助新表 RENAME70% 以上亿级以上需要短暂停写中外键、触发器、自增值需手工处理分区 DROP PARTITION按分区整体清理分区表任意量级极低秒级前期设计成本高分区列必须包含在主键中无法只删部分行分批 DELETE 和 keyset 本质上同一类思路区别主要在取数方式。keyset 更稳定、更不容易漏删所以只要条件允许我会优先用 keyset 而不是简单固定区间。5.2 我优先级排序的决策逻辑与落地节奏我的判断顺序是这样的第一步先看表有没有分区有分区又有明确保留周期优先 DROP PARTITION这是最省心的路第二步看删除比例如果删除超过七成且能接受短时间停写换表方案比任何逐行删除都快第三步才是逐行分批适合删除比例不高、又不能停写的场景。删除比例这个指标特别关键。为什么比例高时要换表因为每 DELETE 一行的成本包括索引维护、undo、binlog、purge几乎都比 INSERT 一行保留数据高。当你保留的数据不到三成时拷贝保留部分再扔旧表总工作量就是三成数据的写开销而逐行 DELETE 的工作量却是七成数据乘以日志放大系数差距非常明显。执行前的准备工作同样重要。我一般会先跑一次目标行数统计记录删除前后的 COUNT 对比确认到底删了多少。过程中持续观察 History List Length、锁等待、从库延迟和磁盘剩余空间。一旦发现问题脚本要能优雅退出不要硬撑更不要盲目 KILL 正在提交的事务。还有一个小细节删除前把待删除数据范围的 id 或时间点先记录下来放在归档库里作为双保险。真误删了至少还能恢复。不要以为慢删除就不会出事慢本身就是在积累风险。最后分享一个我的观察。如果你发现某张表的删除任务需要以“天”为单位反复执行那说明问题已经不是删除方法而是数据生命周期设计出了问题。与其每次费劲清数据不如趁早规划分区表或归档机制把清理变成例行公事而不是随时会爆的雷。
阅读完成 · 觉得有帮助？
