1. 为什么大批量 DELETE 是 MySQL 的隐形风险1.1 一条 DELETE 背后到底做了什么有一次在做数据清理时开发同事直接在主库执行了这么一条语句DELETE FROM 订单流水表 WHERE create_time 2023-01-01;他以为这是一条普通的运维 SQL。执行不到五分钟在线业务全部卡住主库连接数打满从库延迟直接飙到上千秒。最后这条 SQL 被 kill但影响持续了半个多小时后续积压的事务把 IO 拖了很久才恢复。之所以一条 DELETE 能造成这么大的影响是因为在 InnoDB 里删除数据并不是简单地把文件内容抹掉它要做的事情比大多数人想象的多得多。首先引擎要根据 WHERE 条件找到所有需要删除的行。如果筛选条件上恰好没有合适的索引那就是全表扫描如果表有几千万行这个扫描动作本身就够数据库喝一壶的。其次每删除一行InnoDB 都要去维护主键索引和该表所有相关的二级索引一行可能涉及多个索引页的更新。再者事务要把旧版本的记录写入 undo log便于并发事务通过 MVCC 读到删除前的数据还要把所有变更写入 redo log用来保障崩溃恢复。最后相关数据页上的行锁和记录锁在事务未提交之前都不会释放。可以简单理解成你要求仓库里的一台叉车一次性把所有角落的旧货清空。叉车走到哪里通道就要封锁到哪里。你在导航上说“清空旧货”但实际执行时每个货架都要开门、清点、标记、登记。这个过程越长其他要进仓库取货的作业员就越着急。所以真正的问题从来不是“DELETE 本身会不会执行完”而是它在执行期间产生的行锁、undo、binlog 和 IO 压力会不会把同一台实例上的其他业务拖垮。明白了这一点后面所有的方案选择就都有了依据我们要做的不是找到一条最快的 DELETE而是把删除带来的影响控制在业务可接受的范围内。1.2 哪些表最容易因为批量删除而出问题并不是所有大表删除都会出问题。有的表一条 DELETE 跑完影响也就几秒有的表只删一小批线上就哀鸿遍野。区别通常在于这几个特征单表体量巨大比如总行数过亿或者物理文件超过 100GB待删除数据占比高比如一次要删掉总量 30% 以上表上二级索引多三个起步每个索引都要跟着主键记录同步更新业务实时读写频率高同一时刻还有大量 INSERT、UPDATE 在抢资源主从架构采用 ROW 格式的 binlogDELETE 会被放大成每一行的独立变更事件磁盘是机械盘或者 IO 能力本身不足批量刷盘很容易把 IO 打满。我见过最有代表性的场景是一个用户行为日志表每天新增 5000 万行按天滚动保留 30 天也就是说每天要删掉 30 天前的 5000 万行。这种表用传统 DELETE 做肯定不行正解是在一开始就设计成分区表每天只需要 TRUNCATE 一个分区如果当初没分区后面改造涉及的锁表、复制数据成本非常大。还有一种容易被忽略的场景表本身不大只有两三百万行但业务要求删除其中 80% 的过期记录。这种看起来体量不大执行 DELETE 时因为索引维护和锁竞争也可能非常慢。重点不在“绝对体量”而在“删除数据占比”和“是否有可用索引”。1.3 动手删除前先花十分钟做数据体检在我自己的流程里不管接到什么删除需求第一条都是先跑一组信息收集 SQL把基础情况搞清楚再动手SELECT COUNT(*) FROM t1 WHERE 需要删除数据的过滤条件; SELECT MIN(create_time), MAX(create_time) FROM t1 WHERE 需要删除数据的过滤条件; SHOW INDEX FROM t1; SHOW TABLE STATUS LIKE t1;SELECT version, tx_isolation, binlog_format;这几条命令能告诉你几件事符合删除条件的数据到底有多少、对应的 id 范围在哪里、过滤条件上有没有索引、表的总数据量和物理大小是多少、实例版本和复制格式是什么。我犯过的错误就是在一次清理任务里没有先查看过滤字段上有没有索引直接按 create_time 写了一条 DELETE结果一个全表扫描把生产库拖了。后来再处理任何类似需求我都会先确认过滤字段是否走索引。如果过滤条件本身没有索引不管用哪种分批删除方案每一批去定位待删除记录时都可能付出高昂的扫描代价只能想办法绕开。在数据情况摸清之后还要做一件看起来多余但出事时救命的事备份。一次性删除上千万行谁也不能保证 WHERE 条件绝对准确。最稳妥是有一个可切换的从库或者最近一次的物理备份如果实在没有条件至少要把 binlog 保留策略确认好真删错了还可以从 binlog 找回删除前的数据状态。处理到这一步删除本身还没开始但你已经把大部分风险控制在自己手里了。接下来才是具体的删除方案。2. 5 种批量删除方案实操拆解2.1 方法一分批 DELETE LIMIT最稳妥的通用打法这是使用频率最高、适用面最广的方法思路很简单不一次性删除全部目标数据而是以主键或唯一键为边界一次删除一小批提交后循环继续直到删完为止。最朴素的第一版写法长这样DELETE FROM t1 WHERE create_time 2023-01-01 LIMIT 1000;反复执行到影响行数为 0。优点是代码最短缺点是如果 create_time 上没有高效索引每次定位删除范围都可能在扫大表后面的批次会越来越慢。更好的写法是用主键顺序分页DELETE FROM t1 WHERE create_time 2023-01-01 AND id BETWEEN 1 AND 50000;然后下一次把起点更新为 50001继续执行相同的结构直到 id 超过边界。也可以用 LIMIT 搭配 ORDER BY 主键DELETE FROM t1 WHERE create_time 2023-01-01 ORDER BY id LIMIT 2000;这种方式保证每一批都是从待删除集合的最小主键开始删后续批次沿主键递增推进磁盘 IO 相对局部化事务大小也很好控制。为了自动化可以把固定批大小的删除封装成存储过程DELIMITER $$CREATE PROCEDURE sp_batch_delete() BEGIN DECLARE v_rows INT DEFAULT 1; WHILE v_rows 0 DO DELETE FROM t1 WHERE create_time 2023-01-01 LIMIT 500; SELECT ROW_COUNT() INTO v_rows; COMMIT; SELECT SLEEP(0.5); END WHILE; END$$DELIMITER ;如果需要在过程里动态传批大小DELETE 的 LIMIT 不能直接写参数需要拼 SQL 后用 PREPARE/EXECUTE 动态执行或者直接用固定常量。有个细节值得特别强调循环里别用 COUNT() 去判断“还要删多少行”。我见过同事在删除循环里写 SELECT COUNT() FROM t1 WHERE create_time 2023-01-01;每删一批就全表统计一次剩余量。原本删除本身没问题结果被这个统计折腾得每轮都全表扫描。正确做法是用 ROW_COUNT() 拿当前 DELETE 实际影响的行数只要还有行被删就继续影响行数为 0 就退出。分批 DELETE 另一个容易被忽略的点是 sleep。如果业务高峰期不能停机或者主从架构对延迟比较敏感每次循环之间最好 sleep 0.5 到 2 秒给从库应用 binlog 留出追赶的时间也避免 CPU 和 IO 一直被删除任务占住。压测时我一般先 sleep 0.5观察主从延迟没有抬头再考虑收紧。2.2 方法二重建表留下该留的数据而不是去删老数据如果说分批 DELETE 是“把垃圾一点点搬出去”那重建表就是“把有用的家具先挪到新房间再把旧房间整体处理掉”。当你要删的数据比例非常高时重建表比 DELETE 高效得多因为它不用逐行更新大量索引只需要把需要保留的数据重新写入一张新表。常规步骤如下创建与旧表结构一致的新表CREATE TABLE t1_new LIKE t1;把需要保留的数据复制到新表INSERT INTO t1_new SELECT * FROM t1 WHERE create_time 2023-01-01;确认数据一致后换表RENAME TABLE t1 TO t1_bak, t1_new TO t1;确认新表运行稳定后DROP TABLE t1_bak;这个方案最大的优势是把“大批量删除”转化成“按需复制 瞬间改名”在线影响窗口主要集中在新老表切换的那一刻。旧表被改名保留数据并没有立即消失等于多了一重保护出问题还能切回去。但缺点也不能忽视。新旧表同时存在要求磁盘空间要装得下两份数据。比如原表 200GB保留数据 50GB执行期间峰值可能需要占用 250GB 以上空间。磁盘不足时这个方法不能直接用。另外INSERT INTO ... SELECT 本质也是一次大查询加大写入如果业务表还在持续写入就需要停机窗口或者在应用层做双写否则新表数据会与旧表不一致。这个方案适合“表里 90% 都是过期数据、留下来只有很小一部分”的清理场景。一次导出 10 万行有用数据远比重建那份仍在不断增长的大表合适得多。还有一点经验如果保留数据也要几千万行INSERT INTO ... SELECT 的时间会很长建议分多批插入或者先用 SELECT 导出到文件再用导入工具加载。2.3 方法三分区表 TRUNCATE PARTITION规律归档的首选前面两种方法都在“临时清理”的范畴里如果删除需求是固定节奏发生的比如日志表每天归档、流水表按月保留那就应该认真考虑分区表方案。分区表的思路不是把数据删掉而是让数据本身物理落在不同的分区文件里。删除历史数据时直接 TRUNCATE 对应分区速度快到离谱ALTER TABLE t_log TRUNCATE PARTITION p202501;TRUNCATE PARTITION 跟 DELETE 有本质区别。它不逐行解析、不更新二级索引也不会产生海量 undo 日志而是直接把这个分区的数据页整体废弃掉代价相当于在存储层面做一次物理重置。执行过程几乎不影响其他分区上的读写这对海量日志场景是真正可落地的方案。分区的设计要提前做好通常按日期范围是最常用的CREATE TABLE t_log ( id BIGINT NOT NULL AUTO_INCREMENT, content VARCHAR(500), create_time DATETIME, PRIMARY KEY (id, create_time) ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202501 VALUES LESS THAN (TO_DAYS(2025-02-01)), PARTITION p202502 VALUES LESS THAN (TO_DAYS(2025-03-01)) );这里有个关键点分区键必须包含在主键或者唯一索引里。如果你把 id 设为主键、create_time 又作为分区键会直接报错因为 MySQL 要求每个唯一键都必须包含分区键。很多人第一次建分区表就是栽在这个地方。如果已经存在的大表没有分区临时改造也不是不可以通过 ALTER TABLE ... PARTITION BY RANGE 实现但这等同于重建表会长时间锁资源和占用空间需要安排在停机窗口做。每次删除旧分区还要提前创建好后续新分区最好配合定时任务自动加分区。TRUNCATE PARTITION 还有一个要注意的地方它对数据的恢复几乎没有任何回旋余地不做逐行 binlog 记录也没有 undo 可以用来闪回。所以在执行前必须确认这部分数据后续不会产生查询、审计或恢复需求或者已经做过归档备份。2.4 方法四临时范围表 / 归档表做冷热分离有时我们既不想用大批 DELETE又担心分区改造成本太大可以考虑把“删除”变成“写出归档 不再查询”也就是常说的冷热分离。做法不复杂先建一张结构相同的历史归档表 t1_archive把需要删除的老数据定时 INSERT INTO t1_archive SELECT ... 迁移过去原表 t1 中的数据继续留着但业务上通过日期条件只访问最近的数据让旧数据自然沉底。等确认归档完成、业务确实不再依赖在线表里的旧数据之后再找低峰期通过分批 DELETE 把这些已归档数据清理掉。这种做法的好处是删除动作被延后真正影响在线业务的删除被放到了维护窗口而且删除的范围可以通过归档进度精确控制。我遇到过不少团队采用这个方式之后主库的负载曲线立刻平滑了很多。如果不想保留这些过期数据只想起到“缓冲”作用也可以把待删除的主键范围先写入一张临时 id 表后台脚本按这些 id 分批删除。这样就算 DELETE 过程中途失败重新启动脚本也能从临时表里恢复任务进度。比起裸写一条 DELETE这种间接方案多几层步骤但从可观测性和可恢复性上看非常值得。2.5 方法五异步队列 后台消费把删除变成可控调度当删除任务量特别大、单次维护窗口内做不完并且线上业务对资源占用非常敏感时可以考虑把删除任务异步化。核心思想是不在触发删除时直接执行大 SQL而是把删除目标写进队列由后台消费者按照可控速率一批批处理。实现上不一定要引入重量级消息中间件。我见过很实用的做法是建一张 task_ids 表把所有待删除行的主键 id 分批插入然后后台脚本按 id 范围循环删除。删除速率可以通过消费者线程数、每次批大小、批次间隔动态调节也可以根据主从延迟指标自动暂停或加长 sleep做到“让删除尽量隐形”。这个方案比较适合超大规模历史数据清理因为它天然具备断点续跑的容错能力。任务中断了重启消费者继续消费即可删除出现异常了可以随时暂停。不过它的代价是实现复杂度更高相当于为了一次删除维护一个小型任务系统需要业务方明确评估收益。我自己倾向把“异步队列 后台消费”当作最后手段只有前几种方法都被验证过、确实不满足时才上如果只是几千几万行的清理用这种方案反而属于过度设计。3. 批次大小和性能参数怎么定3.1 单批删除行数怎么选才合理很多人拿到方案后第一个问题就是一批删多少行合适这个没有万能答案但可以通过两个指标科学估算一是单批数据量二是单批执行时长。先看单批数据量。假设表中一行记录平均大约 500 字节5000 行一批就是 2.5MB 的 IO 操作SSD 上处理这种量级很轻松机械盘要谨慎一些。如果单行包含多个大文本字段、平均 5KB那一批 5000 行就是 25MB这个量级就不小了应该把批大小降到 1000 行甚至 500 行。再看单批执行时长。理想情况下每一批 DELETE 耗时应该控制在 1 到 3 秒以内。如果某批超过 5 秒说明批太大或者定位条件有问题。我通常的做法是先拿 500 行做首个批次观察耗时然后等比放大到 1000、2000、5000记录每批耗时和主从延迟选择耗时增长变陡之前那一档来使用。此外还要考虑实例配置。如果机器 IO 本身不行或者同一实例上还跑着其他核心业务批大小就往小了选。删除任务没有绩效考核稳定排第一位宁可多跑几轮也别把服务器压垮。3.2 单条 DELETE 的细节优化性能优化能做的点很多这里挑几个踩过坑后沉淀下来的经验。第一能按主键范围删就别按普通索引条件删。在 InnoDB 里主键是聚簇索引数据行本身按主键顺序组织按主键范围删除可以顺序扫描数据页效率远高于通过二级索引定位再回表删除。如果删除条件是从 create_time 来的建议先查一次 MIN(id) 和 MAX(id) 确定边界然后转换成主键范围分批执行。第二每批处理完立即 COMMIT。大批量删除一定要拆事务否则 undo log 会无限膨胀锁的保有时间也会被拉得很长。每次删除的批大小固定事务也尽量固定在一个可控范围内一提交就释放一批锁和日志空间。第三要关注 binlog 格式。如果实例使用 ROW 格式DELETE 每删一行都会在 binlog 里记录完整的行前后映像删除 1000 万行就会产生极其可观的 binlog。从库回放这些事件时同样要执行大量行操作。在这种配置下更要把批大小放小一些同时监控从库延迟。如果业务允许把 binlog 格式调整为 MIXED 或 STATEMENTbinlog 体积会小很多但必须做安全评估确认不会影响主从一致性。第四删除前检查索引是否必要。二级索引在 DELETE 时也要同步更新如果一个索引在删除场景里用不到但又长期存在每次清理都会付出额外代价。不要为了偶发的查询需求保留一个价值不高的索引。当然删索引本身也要谨慎最好通过慢查询日志验证确实用不到再删。3.3 主从延迟和复制格式的取舍主从架构下主库的 DELETE 最终会在从库回放。如果从库是异步复制主库大批删除时从库跟不上就会造成读取延迟。严重的时候从库上会堆积大量复制任务监控画面上 Seconds_Behind_Master 会非常吓人。控制主从延迟的核心仍然是批次大小和删除节奏。我惯用的做法是在删除循环里加一个延迟观察每跑完一批查询一下从库的 Seconds_Behind_Master超过自己设定的阈值比如 300 秒时sleep 时间自动拉长低于阈值时再恢复原节奏。这个逻辑在存储过程里写起来有些繁琐放在外部脚本里更灵活。还要区分 binlog 格式对从库的影响。ROW 格式下从库执行 DELETE 需要一行一行删除主库删 100 万行从库也要删 100 万行延迟积累自然明显STATEMENT 格式下 binlog 只记录一条 DELETE 语句从库回放时相当于重放一个大批量删除同样会有长时间锁定和资源占用。所以无论哪种格式只要是大批量删除主从延迟都躲不开能做的主要是控制节奏和错峰。有条件的场景可以考虑把删除任务放在一个独立的从库上执行或者在删除期间让部分非关键查询切到其他副本保障核心线上查询不受影响。这些都需要提前和业务方对齐不是 DBA 单方面能决定的。4. 实战中踩过的坑和排查方法4.1 锁等待超时先查长事务而不是急着杀 DELETE报错 ERROR 1205: Lock wait timeout exceeded 是批量删除场景里最常见的错误之一。乍一看像是 DELETE 自己出了问题但大部分情况下真正的问题根源不在 DELETE而在于有另一个事务长时间持锁没提交。排查第一步用这两条 SQL 看当前事务和锁等待SELECT * FROM information_schema.innodb_trx\G; SELECT * FROM information_schema.innodb_lock_waits\G;你会发现 DELETE 在等某个事务持有的锁释放。如果这个事务是个跑了很久的未提交写事务甚至是一个不知道哪来的空闲连接还开着事务不提交推荐先把那个长事务 kill再重试 DELETE。曾经碰到一种情况是 DELETE 一直卡着查了半天发现是一个应用连接执行了 SELECT 之后没有关闭事务还持有表上的元数据锁。DELETE 想拿到表结构相关的锁结果一直排队。这种问题不通过锁查询根本看不出来。再补充一个防坑技巧如果线上要跑批量删除可以先把会话里的 innodb_lock_wait_timeout 调小一些比如 5 秒避免 DELETE 无限等待下去。SET SESSION innodb_lock_wait_timeout 5;超时之后脚本报错至少我们知道这里有并发事务的存在而不是让任务一直挂在那里拖一个晚上。4.2 删完之后磁盘空间没变小这不是系统坏了经常有人在跑完大批量 DELETE 后发现表空间文件还是一样大以为删除没生效或者怀疑 MySQL 有 bug。其实这是 InnoDB 的预期行为。普通 DELETE 只是把数据页里的记录标记成已删除状态并把空间标记为可复用文件本身不会立即缩小。InnoDB 在后台通过 purge 线程清理由删除产生的历史版本让那些页重新可用于后续插入。如果后续又有新数据写入这些空间会被复用但如果删除后没有任何写入文件大小保持不变是正常的。如果磁盘水位确实紧张需要重建表来回收空间OPTIMIZE TABLE t1;或者执行ALTER TABLE t1 ENGINEInnoDB;这两者本质都是重建表能够把表压缩紧凑同时整理碎片。但要注意它们都有代价需要额外磁盘空间执行期间对表有较重的锁影响大表上耗时要以小时计。所以一定要安排在维护窗口做并且在执行前确认磁盘剩余空间足够容纳新表。另一个容易被忽略的点是大批 DELETE 期间 binlog 和 undo 都会飞速膨胀。有时候你看到磁盘没降反而升其实不是表空间的问题而是 binlog 文件没来得及清理或者有一个长事务导致 undo 一直没被 purge。处理思路是先排查大事务是否还存在再把 binlog 保留时间和清理策略做好。4.3 主从复制中断怎么恢复和自查批量删除在从库上回放时如果与业务写入的时序产生交集偶尔会出现复制中断。典型的报错是 SQL 线程报 1032 或 1062要么是主库删除的那一行在从库上找不到了要么是从库上插入时主键冲突。遇到这种问题第一步永远是看一下复制状态SHOW SLAVE STATUS\G;重点看 Last_SQL_Errno 和 Last_SQL_Error。根据错误类型选择处理方式如果是 1032确认从库上是不是因为某种原因已经缺失对应行可以跳过该错误继续复制也可以手动补一条 DELETE 再继续如果是 1062通常说明主从数据之前已经出现不一致需要重新对齐数据。这里强调一个经验批量删除任务放到低峰期执行而且批次之间的 sleep 不能省能大幅降低与业务写入发生交叉的概率。另一个更保险的做法是删除前先锁定相关业务写入或者把删除任务放到业务只读的副本上跑完再切换回主库。复制中断的恢复没有统一万能命令关键是要先理解不一致产生的原因再去处理。如果只是跳过错误后续可能继续累积更多不一致数据最后只能重新做数据校验和修复。5. 方案怎么选一张对照表加一点判断逻辑5.1 三个问题决定删除方案在实际项目里做选择我的判断顺序大概是先问这次删除是什么频率一次性还是重复发生再问能接受多长的停机窗口是完全不能停还是可以安排维护最后问删除的数据后续有没有恢复可能性。这三个问题定下来方案基本就清晰了。下面这张表是我常用的对照参考场景首选方案原因几百万行以内、一次性清理分批 DELETE 主键范围改造最小效果可预测表很大、保留数据少重建表 / 导出导入避免大量索引维护长期规律性归档分区表 TRUNCATE PARTITION删除维护成本最低业务无法停机、需要限流异步队列 后台消费可暂停、可恢复数据后续需要审计先归档再做清理防止误删无法恢复如果只是一次性清理我优先分批 DELETE如果是每天都要清历史日志我建议哪怕费点劲也要改造成分区表。如果删除之后马上有一大批新数据要写进来可以用重建表或者配合后续 OPTIMIZE让数据页更紧凑空间复用也更好。5.2 删除完成后的收尾工作同样不能省大批量删除跑完之后很多人以为万事大吉其实还有两件事值得做。第一是空间回收与碎片整理。如果删除的数据量非常大表空间碎片会很多查询性能和后续写入都可能受影响。需要在业务低峰期执行一次 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINEInnoDB把表重建一遍。执行前确认磁盘剩余空间并估算锁表时间最好提前跟业务同步维护窗口。第二是执行记录和效果复盘。把删除任务的总耗时、总行数、binlog 增量、主从延迟峰值、磁盘空间变化记录下来。下次再遇到类似需求时这些数据能让批次大小和 sleep 时间的调整有据可依而不是每次拍脑袋。删除任务如果比较大最好单独开一个低峰期模块去跑或者在独立从库上跑比在主库上硬扛要稳得多。删除不是越快越好而是越稳越好。6. 最后再分享一点个人体会做数据库维护这些年我最大的感受是大批量删除之所以事故频发不是因为它技术上有多难而是因为很多人在执行之前完全没意识到一条 DELETE 背后牵动的是锁、日志、复制、IO 一整条链路。我现在处理任何删除任务必经的流程是先看数据和索引情况再选删除策略然后用小批次试探性能和延迟最后全程盯监控。这套流程看起来笨拙但每次都能安全落地。曾经因为跳过试探步骤直接删除我在深夜处理过一次恢复任务之后就再也没跳过。还有一个小技巧无论用哪种删除方式执行第一条语句之前先在测试环境或者备库上把同样的 SQL 跑一遍观察执行计划和耗时。这一步花不了多长时间但能提前发现过滤条件有没有走索引、批大小是否合理、会不会锁表很多生产事故本来是可以避免的。最后建议大家把删除任务的执行代码和参数写进自动化脚本别用手工 SQL 裸跑。手工操作容易忘掉 COMMIT、容易忘掉 sleep也容易误改 WHERE 条件。自动化脚本虽然前期多花十几分钟但换来的是可重复、可暂停、可回看的整套操作记录对维护长远的数据库健康非常有帮助。
1. 为什么大批量 DELETE 是 MySQL 的隐形风险1.1 一条 DELETE 背后到底做了什么有一次在做数据清理时开发同事直接在主库执行了这么一条语句DELETE FROM 订单流水表 WHERE create_time 2023-01-01;他以为这是一条普通的运维 SQL。执行不到五分钟在线业务全部卡住主库连接数打满从库延迟直接飙到上千秒。最后这条 SQL 被 kill但影响持续了半个多小时后续积压的事务把 IO 拖了很久才恢复。之所以一条 DELETE 能造成这么大的影响是因为在 InnoDB 里删除数据并不是简单地把文件内容抹掉它要做的事情比大多数人想象的多得多。首先引擎要根据 WHERE 条件找到所有需要删除的行。如果筛选条件上恰好没有合适的索引那就是全表扫描如果表有几千万行这个扫描动作本身就够数据库喝一壶的。其次每删除一行InnoDB 都要去维护主键索引和该表所有相关的二级索引一行可能涉及多个索引页的更新。再者事务要把旧版本的记录写入 undo log便于并发事务通过 MVCC 读到删除前的数据还要把所有变更写入 redo log用来保障崩溃恢复。最后相关数据页上的行锁和记录锁在事务未提交之前都不会释放。可以简单理解成你要求仓库里的一台叉车一次性把所有角落的旧货清空。叉车走到哪里通道就要封锁到哪里。你在导航上说“清空旧货”但实际执行时每个货架都要开门、清点、标记、登记。这个过程越长其他要进仓库取货的作业员就越着急。所以真正的问题从来不是“DELETE 本身会不会执行完”而是它在执行期间产生的行锁、undo、binlog 和 IO 压力会不会把同一台实例上的其他业务拖垮。明白了这一点后面所有的方案选择就都有了依据我们要做的不是找到一条最快的 DELETE而是把删除带来的影响控制在业务可接受的范围内。1.2 哪些表最容易因为批量删除而出问题并不是所有大表删除都会出问题。有的表一条 DELETE 跑完影响也就几秒有的表只删一小批线上就哀鸿遍野。区别通常在于这几个特征单表体量巨大比如总行数过亿或者物理文件超过 100GB待删除数据占比高比如一次要删掉总量 30% 以上表上二级索引多三个起步每个索引都要跟着主键记录同步更新业务实时读写频率高同一时刻还有大量 INSERT、UPDATE 在抢资源主从架构采用 ROW 格式的 binlogDELETE 会被放大成每一行的独立变更事件磁盘是机械盘或者 IO 能力本身不足批量刷盘很容易把 IO 打满。我见过最有代表性的场景是一个用户行为日志表每天新增 5000 万行按天滚动保留 30 天也就是说每天要删掉 30 天前的 5000 万行。这种表用传统 DELETE 做肯定不行正解是在一开始就设计成分区表每天只需要 TRUNCATE 一个分区如果当初没分区后面改造涉及的锁表、复制数据成本非常大。还有一种容易被忽略的场景表本身不大只有两三百万行但业务要求删除其中 80% 的过期记录。这种看起来体量不大执行 DELETE 时因为索引维护和锁竞争也可能非常慢。重点不在“绝对体量”而在“删除数据占比”和“是否有可用索引”。1.3 动手删除前先花十分钟做数据体检在我自己的流程里不管接到什么删除需求第一条都是先跑一组信息收集 SQL把基础情况搞清楚再动手SELECT COUNT(*) FROM t1 WHERE 需要删除数据的过滤条件; SELECT MIN(create_time), MAX(create_time) FROM t1 WHERE 需要删除数据的过滤条件; SHOW INDEX FROM t1; SHOW TABLE STATUS LIKE t1;SELECT version, tx_isolation, binlog_format;这几条命令能告诉你几件事符合删除条件的数据到底有多少、对应的 id 范围在哪里、过滤条件上有没有索引、表的总数据量和物理大小是多少、实例版本和复制格式是什么。我犯过的错误就是在一次清理任务里没有先查看过滤字段上有没有索引直接按 create_time 写了一条 DELETE结果一个全表扫描把生产库拖了。后来再处理任何类似需求我都会先确认过滤字段是否走索引。如果过滤条件本身没有索引不管用哪种分批删除方案每一批去定位待删除记录时都可能付出高昂的扫描代价只能想办法绕开。在数据情况摸清之后还要做一件看起来多余但出事时救命的事备份。一次性删除上千万行谁也不能保证 WHERE 条件绝对准确。最稳妥是有一个可切换的从库或者最近一次的物理备份如果实在没有条件至少要把 binlog 保留策略确认好真删错了还可以从 binlog 找回删除前的数据状态。处理到这一步删除本身还没开始但你已经把大部分风险控制在自己手里了。接下来才是具体的删除方案。2. 5 种批量删除方案实操拆解2.1 方法一分批 DELETE LIMIT最稳妥的通用打法这是使用频率最高、适用面最广的方法思路很简单不一次性删除全部目标数据而是以主键或唯一键为边界一次删除一小批提交后循环继续直到删完为止。最朴素的第一版写法长这样DELETE FROM t1 WHERE create_time 2023-01-01 LIMIT 1000;反复执行到影响行数为 0。优点是代码最短缺点是如果 create_time 上没有高效索引每次定位删除范围都可能在扫大表后面的批次会越来越慢。更好的写法是用主键顺序分页DELETE FROM t1 WHERE create_time 2023-01-01 AND id BETWEEN 1 AND 50000;然后下一次把起点更新为 50001继续执行相同的结构直到 id 超过边界。也可以用 LIMIT 搭配 ORDER BY 主键DELETE FROM t1 WHERE create_time 2023-01-01 ORDER BY id LIMIT 2000;这种方式保证每一批都是从待删除集合的最小主键开始删后续批次沿主键递增推进磁盘 IO 相对局部化事务大小也很好控制。为了自动化可以把固定批大小的删除封装成存储过程DELIMITER $$CREATE PROCEDURE sp_batch_delete() BEGIN DECLARE v_rows INT DEFAULT 1; WHILE v_rows 0 DO DELETE FROM t1 WHERE create_time 2023-01-01 LIMIT 500; SELECT ROW_COUNT() INTO v_rows; COMMIT; SELECT SLEEP(0.5); END WHILE; END$$DELIMITER ;如果需要在过程里动态传批大小DELETE 的 LIMIT 不能直接写参数需要拼 SQL 后用 PREPARE/EXECUTE 动态执行或者直接用固定常量。有个细节值得特别强调循环里别用 COUNT() 去判断“还要删多少行”。我见过同事在删除循环里写 SELECT COUNT() FROM t1 WHERE create_time 2023-01-01;每删一批就全表统计一次剩余量。原本删除本身没问题结果被这个统计折腾得每轮都全表扫描。正确做法是用 ROW_COUNT() 拿当前 DELETE 实际影响的行数只要还有行被删就继续影响行数为 0 就退出。分批 DELETE 另一个容易被忽略的点是 sleep。如果业务高峰期不能停机或者主从架构对延迟比较敏感每次循环之间最好 sleep 0.5 到 2 秒给从库应用 binlog 留出追赶的时间也避免 CPU 和 IO 一直被删除任务占住。压测时我一般先 sleep 0.5观察主从延迟没有抬头再考虑收紧。2.2 方法二重建表留下该留的数据而不是去删老数据如果说分批 DELETE 是“把垃圾一点点搬出去”那重建表就是“把有用的家具先挪到新房间再把旧房间整体处理掉”。当你要删的数据比例非常高时重建表比 DELETE 高效得多因为它不用逐行更新大量索引只需要把需要保留的数据重新写入一张新表。常规步骤如下创建与旧表结构一致的新表CREATE TABLE t1_new LIKE t1;把需要保留的数据复制到新表INSERT INTO t1_new SELECT * FROM t1 WHERE create_time 2023-01-01;确认数据一致后换表RENAME TABLE t1 TO t1_bak, t1_new TO t1;确认新表运行稳定后DROP TABLE t1_bak;这个方案最大的优势是把“大批量删除”转化成“按需复制 瞬间改名”在线影响窗口主要集中在新老表切换的那一刻。旧表被改名保留数据并没有立即消失等于多了一重保护出问题还能切回去。但缺点也不能忽视。新旧表同时存在要求磁盘空间要装得下两份数据。比如原表 200GB保留数据 50GB执行期间峰值可能需要占用 250GB 以上空间。磁盘不足时这个方法不能直接用。另外INSERT INTO ... SELECT 本质也是一次大查询加大写入如果业务表还在持续写入就需要停机窗口或者在应用层做双写否则新表数据会与旧表不一致。这个方案适合“表里 90% 都是过期数据、留下来只有很小一部分”的清理场景。一次导出 10 万行有用数据远比重建那份仍在不断增长的大表合适得多。还有一点经验如果保留数据也要几千万行INSERT INTO ... SELECT 的时间会很长建议分多批插入或者先用 SELECT 导出到文件再用导入工具加载。2.3 方法三分区表 TRUNCATE PARTITION规律归档的首选前面两种方法都在“临时清理”的范畴里如果删除需求是固定节奏发生的比如日志表每天归档、流水表按月保留那就应该认真考虑分区表方案。分区表的思路不是把数据删掉而是让数据本身物理落在不同的分区文件里。删除历史数据时直接 TRUNCATE 对应分区速度快到离谱ALTER TABLE t_log TRUNCATE PARTITION p202501;TRUNCATE PARTITION 跟 DELETE 有本质区别。它不逐行解析、不更新二级索引也不会产生海量 undo 日志而是直接把这个分区的数据页整体废弃掉代价相当于在存储层面做一次物理重置。执行过程几乎不影响其他分区上的读写这对海量日志场景是真正可落地的方案。分区的设计要提前做好通常按日期范围是最常用的CREATE TABLE t_log ( id BIGINT NOT NULL AUTO_INCREMENT, content VARCHAR(500), create_time DATETIME, PRIMARY KEY (id, create_time) ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p202501 VALUES LESS THAN (TO_DAYS(2025-02-01)), PARTITION p202502 VALUES LESS THAN (TO_DAYS(2025-03-01)) );这里有个关键点分区键必须包含在主键或者唯一索引里。如果你把 id 设为主键、create_time 又作为分区键会直接报错因为 MySQL 要求每个唯一键都必须包含分区键。很多人第一次建分区表就是栽在这个地方。如果已经存在的大表没有分区临时改造也不是不可以通过 ALTER TABLE ... PARTITION BY RANGE 实现但这等同于重建表会长时间锁资源和占用空间需要安排在停机窗口做。每次删除旧分区还要提前创建好后续新分区最好配合定时任务自动加分区。TRUNCATE PARTITION 还有一个要注意的地方它对数据的恢复几乎没有任何回旋余地不做逐行 binlog 记录也没有 undo 可以用来闪回。所以在执行前必须确认这部分数据后续不会产生查询、审计或恢复需求或者已经做过归档备份。2.4 方法四临时范围表 / 归档表做冷热分离有时我们既不想用大批 DELETE又担心分区改造成本太大可以考虑把“删除”变成“写出归档 不再查询”也就是常说的冷热分离。做法不复杂先建一张结构相同的历史归档表 t1_archive把需要删除的老数据定时 INSERT INTO t1_archive SELECT ... 迁移过去原表 t1 中的数据继续留着但业务上通过日期条件只访问最近的数据让旧数据自然沉底。等确认归档完成、业务确实不再依赖在线表里的旧数据之后再找低峰期通过分批 DELETE 把这些已归档数据清理掉。这种做法的好处是删除动作被延后真正影响在线业务的删除被放到了维护窗口而且删除的范围可以通过归档进度精确控制。我遇到过不少团队采用这个方式之后主库的负载曲线立刻平滑了很多。如果不想保留这些过期数据只想起到“缓冲”作用也可以把待删除的主键范围先写入一张临时 id 表后台脚本按这些 id 分批删除。这样就算 DELETE 过程中途失败重新启动脚本也能从临时表里恢复任务进度。比起裸写一条 DELETE这种间接方案多几层步骤但从可观测性和可恢复性上看非常值得。2.5 方法五异步队列 后台消费把删除变成可控调度当删除任务量特别大、单次维护窗口内做不完并且线上业务对资源占用非常敏感时可以考虑把删除任务异步化。核心思想是不在触发删除时直接执行大 SQL而是把删除目标写进队列由后台消费者按照可控速率一批批处理。实现上不一定要引入重量级消息中间件。我见过很实用的做法是建一张 task_ids 表把所有待删除行的主键 id 分批插入然后后台脚本按 id 范围循环删除。删除速率可以通过消费者线程数、每次批大小、批次间隔动态调节也可以根据主从延迟指标自动暂停或加长 sleep做到“让删除尽量隐形”。这个方案比较适合超大规模历史数据清理因为它天然具备断点续跑的容错能力。任务中断了重启消费者继续消费即可删除出现异常了可以随时暂停。不过它的代价是实现复杂度更高相当于为了一次删除维护一个小型任务系统需要业务方明确评估收益。我自己倾向把“异步队列 后台消费”当作最后手段只有前几种方法都被验证过、确实不满足时才上如果只是几千几万行的清理用这种方案反而属于过度设计。3. 批次大小和性能参数怎么定3.1 单批删除行数怎么选才合理很多人拿到方案后第一个问题就是一批删多少行合适这个没有万能答案但可以通过两个指标科学估算一是单批数据量二是单批执行时长。先看单批数据量。假设表中一行记录平均大约 500 字节5000 行一批就是 2.5MB 的 IO 操作SSD 上处理这种量级很轻松机械盘要谨慎一些。如果单行包含多个大文本字段、平均 5KB那一批 5000 行就是 25MB这个量级就不小了应该把批大小降到 1000 行甚至 500 行。再看单批执行时长。理想情况下每一批 DELETE 耗时应该控制在 1 到 3 秒以内。如果某批超过 5 秒说明批太大或者定位条件有问题。我通常的做法是先拿 500 行做首个批次观察耗时然后等比放大到 1000、2000、5000记录每批耗时和主从延迟选择耗时增长变陡之前那一档来使用。此外还要考虑实例配置。如果机器 IO 本身不行或者同一实例上还跑着其他核心业务批大小就往小了选。删除任务没有绩效考核稳定排第一位宁可多跑几轮也别把服务器压垮。3.2 单条 DELETE 的细节优化性能优化能做的点很多这里挑几个踩过坑后沉淀下来的经验。第一能按主键范围删就别按普通索引条件删。在 InnoDB 里主键是聚簇索引数据行本身按主键顺序组织按主键范围删除可以顺序扫描数据页效率远高于通过二级索引定位再回表删除。如果删除条件是从 create_time 来的建议先查一次 MIN(id) 和 MAX(id) 确定边界然后转换成主键范围分批执行。第二每批处理完立即 COMMIT。大批量删除一定要拆事务否则 undo log 会无限膨胀锁的保有时间也会被拉得很长。每次删除的批大小固定事务也尽量固定在一个可控范围内一提交就释放一批锁和日志空间。第三要关注 binlog 格式。如果实例使用 ROW 格式DELETE 每删一行都会在 binlog 里记录完整的行前后映像删除 1000 万行就会产生极其可观的 binlog。从库回放这些事件时同样要执行大量行操作。在这种配置下更要把批大小放小一些同时监控从库延迟。如果业务允许把 binlog 格式调整为 MIXED 或 STATEMENTbinlog 体积会小很多但必须做安全评估确认不会影响主从一致性。第四删除前检查索引是否必要。二级索引在 DELETE 时也要同步更新如果一个索引在删除场景里用不到但又长期存在每次清理都会付出额外代价。不要为了偶发的查询需求保留一个价值不高的索引。当然删索引本身也要谨慎最好通过慢查询日志验证确实用不到再删。3.3 主从延迟和复制格式的取舍主从架构下主库的 DELETE 最终会在从库回放。如果从库是异步复制主库大批删除时从库跟不上就会造成读取延迟。严重的时候从库上会堆积大量复制任务监控画面上 Seconds_Behind_Master 会非常吓人。控制主从延迟的核心仍然是批次大小和删除节奏。我惯用的做法是在删除循环里加一个延迟观察每跑完一批查询一下从库的 Seconds_Behind_Master超过自己设定的阈值比如 300 秒时sleep 时间自动拉长低于阈值时再恢复原节奏。这个逻辑在存储过程里写起来有些繁琐放在外部脚本里更灵活。还要区分 binlog 格式对从库的影响。ROW 格式下从库执行 DELETE 需要一行一行删除主库删 100 万行从库也要删 100 万行延迟积累自然明显STATEMENT 格式下 binlog 只记录一条 DELETE 语句从库回放时相当于重放一个大批量删除同样会有长时间锁定和资源占用。所以无论哪种格式只要是大批量删除主从延迟都躲不开能做的主要是控制节奏和错峰。有条件的场景可以考虑把删除任务放在一个独立的从库上执行或者在删除期间让部分非关键查询切到其他副本保障核心线上查询不受影响。这些都需要提前和业务方对齐不是 DBA 单方面能决定的。4. 实战中踩过的坑和排查方法4.1 锁等待超时先查长事务而不是急着杀 DELETE报错 ERROR 1205: Lock wait timeout exceeded 是批量删除场景里最常见的错误之一。乍一看像是 DELETE 自己出了问题但大部分情况下真正的问题根源不在 DELETE而在于有另一个事务长时间持锁没提交。排查第一步用这两条 SQL 看当前事务和锁等待SELECT * FROM information_schema.innodb_trx\G; SELECT * FROM information_schema.innodb_lock_waits\G;你会发现 DELETE 在等某个事务持有的锁释放。如果这个事务是个跑了很久的未提交写事务甚至是一个不知道哪来的空闲连接还开着事务不提交推荐先把那个长事务 kill再重试 DELETE。曾经碰到一种情况是 DELETE 一直卡着查了半天发现是一个应用连接执行了 SELECT 之后没有关闭事务还持有表上的元数据锁。DELETE 想拿到表结构相关的锁结果一直排队。这种问题不通过锁查询根本看不出来。再补充一个防坑技巧如果线上要跑批量删除可以先把会话里的 innodb_lock_wait_timeout 调小一些比如 5 秒避免 DELETE 无限等待下去。SET SESSION innodb_lock_wait_timeout 5;超时之后脚本报错至少我们知道这里有并发事务的存在而不是让任务一直挂在那里拖一个晚上。4.2 删完之后磁盘空间没变小这不是系统坏了经常有人在跑完大批量 DELETE 后发现表空间文件还是一样大以为删除没生效或者怀疑 MySQL 有 bug。其实这是 InnoDB 的预期行为。普通 DELETE 只是把数据页里的记录标记成已删除状态并把空间标记为可复用文件本身不会立即缩小。InnoDB 在后台通过 purge 线程清理由删除产生的历史版本让那些页重新可用于后续插入。如果后续又有新数据写入这些空间会被复用但如果删除后没有任何写入文件大小保持不变是正常的。如果磁盘水位确实紧张需要重建表来回收空间OPTIMIZE TABLE t1;或者执行ALTER TABLE t1 ENGINEInnoDB;这两者本质都是重建表能够把表压缩紧凑同时整理碎片。但要注意它们都有代价需要额外磁盘空间执行期间对表有较重的锁影响大表上耗时要以小时计。所以一定要安排在维护窗口做并且在执行前确认磁盘剩余空间足够容纳新表。另一个容易被忽略的点是大批 DELETE 期间 binlog 和 undo 都会飞速膨胀。有时候你看到磁盘没降反而升其实不是表空间的问题而是 binlog 文件没来得及清理或者有一个长事务导致 undo 一直没被 purge。处理思路是先排查大事务是否还存在再把 binlog 保留时间和清理策略做好。4.3 主从复制中断怎么恢复和自查批量删除在从库上回放时如果与业务写入的时序产生交集偶尔会出现复制中断。典型的报错是 SQL 线程报 1032 或 1062要么是主库删除的那一行在从库上找不到了要么是从库上插入时主键冲突。遇到这种问题第一步永远是看一下复制状态SHOW SLAVE STATUS\G;重点看 Last_SQL_Errno 和 Last_SQL_Error。根据错误类型选择处理方式如果是 1032确认从库上是不是因为某种原因已经缺失对应行可以跳过该错误继续复制也可以手动补一条 DELETE 再继续如果是 1062通常说明主从数据之前已经出现不一致需要重新对齐数据。这里强调一个经验批量删除任务放到低峰期执行而且批次之间的 sleep 不能省能大幅降低与业务写入发生交叉的概率。另一个更保险的做法是删除前先锁定相关业务写入或者把删除任务放到业务只读的副本上跑完再切换回主库。复制中断的恢复没有统一万能命令关键是要先理解不一致产生的原因再去处理。如果只是跳过错误后续可能继续累积更多不一致数据最后只能重新做数据校验和修复。5. 方案怎么选一张对照表加一点判断逻辑5.1 三个问题决定删除方案在实际项目里做选择我的判断顺序大概是先问这次删除是什么频率一次性还是重复发生再问能接受多长的停机窗口是完全不能停还是可以安排维护最后问删除的数据后续有没有恢复可能性。这三个问题定下来方案基本就清晰了。下面这张表是我常用的对照参考场景首选方案原因几百万行以内、一次性清理分批 DELETE 主键范围改造最小效果可预测表很大、保留数据少重建表 / 导出导入避免大量索引维护长期规律性归档分区表 TRUNCATE PARTITION删除维护成本最低业务无法停机、需要限流异步队列 后台消费可暂停、可恢复数据后续需要审计先归档再做清理防止误删无法恢复如果只是一次性清理我优先分批 DELETE如果是每天都要清历史日志我建议哪怕费点劲也要改造成分区表。如果删除之后马上有一大批新数据要写进来可以用重建表或者配合后续 OPTIMIZE让数据页更紧凑空间复用也更好。5.2 删除完成后的收尾工作同样不能省大批量删除跑完之后很多人以为万事大吉其实还有两件事值得做。第一是空间回收与碎片整理。如果删除的数据量非常大表空间碎片会很多查询性能和后续写入都可能受影响。需要在业务低峰期执行一次 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINEInnoDB把表重建一遍。执行前确认磁盘剩余空间并估算锁表时间最好提前跟业务同步维护窗口。第二是执行记录和效果复盘。把删除任务的总耗时、总行数、binlog 增量、主从延迟峰值、磁盘空间变化记录下来。下次再遇到类似需求时这些数据能让批次大小和 sleep 时间的调整有据可依而不是每次拍脑袋。删除任务如果比较大最好单独开一个低峰期模块去跑或者在独立从库上跑比在主库上硬扛要稳得多。删除不是越快越好而是越稳越好。6. 最后再分享一点个人体会做数据库维护这些年我最大的感受是大批量删除之所以事故频发不是因为它技术上有多难而是因为很多人在执行之前完全没意识到一条 DELETE 背后牵动的是锁、日志、复制、IO 一整条链路。我现在处理任何删除任务必经的流程是先看数据和索引情况再选删除策略然后用小批次试探性能和延迟最后全程盯监控。这套流程看起来笨拙但每次都能安全落地。曾经因为跳过试探步骤直接删除我在深夜处理过一次恢复任务之后就再也没跳过。还有一个小技巧无论用哪种删除方式执行第一条语句之前先在测试环境或者备库上把同样的 SQL 跑一遍观察执行计划和耗时。这一步花不了多长时间但能提前发现过滤条件有没有走索引、批大小是否合理、会不会锁表很多生产事故本来是可以避免的。最后建议大家把删除任务的执行代码和参数写进自动化脚本别用手工 SQL 裸跑。手工操作容易忘掉 COMMIT、容易忘掉 sleep也容易误改 WHERE 条件。自动化脚本虽然前期多花十几分钟但换来的是可重复、可暂停、可回看的整套操作记录对维护长远的数据库健康非常有帮助。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
