一位做后端的朋友凌晨两点在群里发消息一条DELETE FROM access_log WHERE create_time 2023-01-01跑了四十分钟还没结束从库延迟飙到三千多秒业务侧的报表全部读到旧数据最后只能把主库连接掐掉重来。这不是个例凡是接手过几千万行日志表、订单历史表、消息投递表的人几乎都经历过批量删除数据这个看似最基础的 DML 操作带来的翻车现场。它难的地方从来不是 SQL 语法而是删除背后牵连的事务体积、锁范围、undo 膨胀、主从复制链路以及磁盘空间回收。这篇内容围绕 MySQL 批量删除大量数据展开重点讲清楚三件事小批量删除该怎么拆、拆到什么粒度、脚本怎么写才不中断中大规模数据百万到千万行为什么单靠 DELETE 调参数救不回来得换成分区表回收或者影子表重建以及那些文档里不会写、只有真删过线上大表的人才知道的坑。适合正在处理数据清理任务的开发和运维同学也适合刚接触 MySQL 想搞明白为什么 DELETE 会卡库的新手。1. 一条 DELETE 卡住整个库批量删除真正的风险点在哪单纯从语法看删除数据比插入和更新都简单一个 WHERE 条件就完事了。但从数据库内核的角度看DELETE 是成本最高的那类操作它要写 undo 记录、写 redo 日志、维护所有二级索引、加行锁并且把锁持到事务提交为止。行数一旦上去这些成本是乘数级叠加的而不是线性叠加。1.1 大事务是怎么把 undo、锁和 redo 一起拖下水的先给一个直观的类比。假设你要把一栋楼的家具全搬走一次性塞进一辆卡车车装不下就得边装边卸中间还得保证整栋楼在搬运期间不许别人进出——这就是单个大事务删除两百万行的真实写照。具体连锁反应是这样的删除两百万行的单条 SQL 会生成一个巨大的事务undo 日志持续累积回滚段无法被 purge 线程清理Innodb_history_list_length一路飙升。同时被删的每一行都要加排他锁虽然不满足条件的行在 RC 隔离级别下会释放锁但满足条件的行锁必须持有到 commit。更麻烦的是 redo 日志如果innodb_log_file_size只有 512M而这次删除产生的 redo 超过这个容量就会触发强制 checkpoint把脏页刷盘压力转嫁到磁盘上其他正常业务的响应时间同步恶化。最容易被忽略的是回滚成本。很多人觉得删错了再回滚就行但两百万行的事务回滚耗时往往比删除本身还长期间锁同样不释放。所以判断标准不是能不能删完而是删到一半出问题我能不能承受。1.2 动手前先算三笔账行数、耗时、影响面第一笔账是行数。不要用SELECT COUNT(*)去数大表上这个语句本身就是一次全表扫描。可以用EXPLAIN看优化器估算的 rows或者用区间采样反推-- 采样 1000 个主键区间估算目标行数 SELECT COUNT(*) FROM big_table WHERE id % 997 0 AND create_time 2024-01-01;这个结果乘以 997 就是大致行数误差在可接受范围内耗时却只有全表扫描的千分之一。第二笔账是耗时。窄表单行几十字节、一个主键加一个二级索引的删除速度单线程大概在一万到五万行每秒具体取决于磁盘类型和索引数量。每增加一个二级索引删除开销大致增加 30% 到 50%因为每个索引都要做页内删除和可能的页合并。可以用一百行的测试批次实测一下速率再乘以总行数。第三笔账是影响面也就是这张表被谁在读、有没有下游的 CDC 订阅、删除是否会触发外键级联。下面这张表是我自己判断该不该拆批的经验阈值单事务删除行数典型表现建议做法一万以内基本无感秒级完成直接删无需处理一万到五十万锁等待上升undo 膨胀从库延迟几十秒拆批控制速率五十万到一千万大概率触发告警回滚极慢主从延迟分钟级拆批加限速放到低峰期一千万以上已经不是 DELETE 的战场分区表回收或影子表重建1.3 删之前必须确认的三个问题第一这批数据到底能不能删。保留期要求、合规要求、下游数仓是否已经同步完成、有没有别的服务在按时间范围查这张表。我见过一次事故运维把看起来没人查的历史订单删了结果财务对账任务依赖三年前的月度汇总直接报错。第二有没有可用的备份和 binlog。删除前确认最近的物理备份时间点和 binlog 保留时长如果是误删靠 binlog 回滚的时间窗口就是你的安全绳。第三删除条件的执行计划长什么样。这一步用EXPLAIN就能看EXPLAIN DELETE FROM access_log WHERE create_time 2024-01-01;如果 type 是 ALL说明走的是全表扫描那么无论拆不拆批每次删除都会扫全表这时候要先解决索引问题而不是急着拆批这一点在第 3 节会展开说。2. 把大删除拆成小批次主键切片与循环脚本的完整写法拆批的核心思路很朴素把一个巨型事务切成成百上千个小事务每个事务删几百到几千行中间留出空隙让 redo 刷盘、让 purge 线程追赶、让从库慢慢消化。真正的难点在于怎么切和切多大。2.1 为什么按主键区间切片比按时间直接删更稳很多人的第一版脚本是这样写的DELETE FROM access_log WHERE create_time 2024-01-01 LIMIT 2000;然后外面套一个循环反复执行。这个写法在数据量小的时候能用行数一多就露馅每次执行都要从create_time索引的最左端重新定位删掉头部的两千行后下一次又是从头开始扫前面已经删过的位置可能有大量的索引空洞要跳过。删得越多每一批的实际扫描成本越高整体接近二次复杂度。正确的切法是先找出主键边界然后按主键区间推进-- 第一步拿到边界注意条件列没索引时这一步也会慢 SELECT MIN(id), MAX(id) FROM access_log WHERE create_time 2024-01-01; -- 第二步按主键区间循环删除 DELETE FROM access_log WHERE id BETWEEN 1000000 AND 1001999 AND create_time 2024-01-01 LIMIT 2000;按主键推进走的是聚簇索引的顺序扫描每一批都是连续页读取删除位置不重叠、不回退效率稳定。附加的create_time条件保证不会误删边界内不该删的行LIMIT则是一个保险丝防止某批区间内行数超出预期时一次性删太多。2.2 单批 SQL 的几种写法与各自的坑写法示例优点坑点条件加 LIMITDELETE FROM t WHERE cond LIMIT 2000写法最短每批从头重扫索引效率递减主键区间加 LIMITDELETE FROM t WHERE id BETWEEN a AND b AND cond顺序扫描效率稳定需要先取边界值先查 ID 再按 IN 删SELECT id ... LIMIT 2000后DELETE ... WHERE id IN (...)完全可控便于日志记录多一次往返IN 列表过长解析慢同表子查询DELETE FROM t WHERE id IN (SELECT id FROM t WHERE cond LIMIT 2000)一条语句搞定直接写会报 1093 错误重点说最后一个。MySQL 不允许在 DELETE 的子查询里直接引用被删的表会抛出ERROR 1093 (HY000): You cant specify target table t for update in FROM clause。绕过办法是用派生表包一层让优化器把它物化成临时结果DELETE FROM access_log WHERE id IN ( SELECT id FROM ( SELECT id FROM access_log WHERE create_time 2024-01-01 ORDER BY id LIMIT 2000 ) AS tmp );这个写法能跑通但每次都要物化一次中间结果开销比主键区间法大。我一般只在需要严格按时间顺序删除、或者条件特别复杂的时候才用它。2.3 批大小、休眠时间与限速的取值推演批大小不是拍脑袋定的它由三个因素共同决定单行大小、索引数量、从库的消化能力。行宽在 1KB 以上的表比如带 JSON 字段或者多个 varchar一批建议 200 到 500 行。普通业务表十几列两三个索引用 1000 到 2000 行。窄表比如只有主键加时间戳的埋点表可以放到 5000 行一批。判断标准可以看单批耗时控制在 100 到 300 毫秒之间比较舒服太短说明批太小效率低太长说明事务偏大。休眠时间要靠主从延迟反推。假设主库单批删除 2000 行耗时 150 毫秒主库的删除速率大约是每秒 13000 行。如果从库的单线程应用速率只有每秒 8000 行那就必须把主库速率压下来。用这个公式算目标速率 从库应用速率 × 0.7 实际速率 批大小 / (单批耗时 sleep) sleep 批大小 / 目标速率 - 单批耗时代入上面的数字目标速率是 5600 行每秒sleep 约等于 2000/5600 - 0.15得出约 0.2 秒。这就是为什么很多批量删除脚本里写的是sleep 0.2而不是sleep 1太长的 sleep 会让整体执行时间成倍拉长太短又压不住延迟。2.4 一份可以直接改参数就用的 Python 循环脚本下面这个脚本我用了两三年核心特性是主键区间推进、单批独立小事务、实时读主从延迟、延迟超阈值自动熔断以及把进度落到文件支持断点续跑import time import pymysql CONF dict(host10.0.0.1, port3306, userdba, password***, databaseappdb, charsetutf8mb4) TABLE access_log COND create_time 2024-01-01 BATCH 2000 SLEEP 0.2 MAX_LAG 30 # 从库延迟超过 30 秒就暂停 PROGRESS /tmp/del_access_log.id conn pymysql.connect(autocommitTrue, **CONF) cur conn.cursor() # 断点续跑优先读上次进度 try: with open(PROGRESS) as f: last int(f.read().strip()) except (IOError, ValueError): cur.execute(fSELECT MIN(id) FROM {TABLE} WHERE {COND}) last cur.fetchone()[0] or 1 cur.execute(fSELECT MAX(id) FROM {TABLE} WHERE {COND}) hi cur.fetchone()[0] if hi is None: print(没有需要删除的数据); raise SystemExit print(f区间 {last} - {hi}, 批大小 {BATCH}) total 0 while last hi: cur.execute( fDELETE FROM {TABLE} WHERE id BETWEEN %s AND %s AND {COND} LIMIT %s, (last, last BATCH - 1, BATCH)) total cur.rowcount # 检查从库延迟 lag 0 try: cur.execute(SHOW REPLICA STATUS) row cur.fetchone() if row: cols [d[0] for d in cur.description] lag row[cols.index(Seconds_Behind_Source)] or 0 except Exception: pass if lag and lag MAX_LAG: print(f从库延迟 {lag}s暂停 10 秒) time.sleep(10) continue last BATCH with open(PROGRESS, w) as f: f.write(str(last)) if total % 100000 BATCH: print(f已删除 {total} 行当前 id{last}) time.sleep(SLEEP)几个细节值得单独说。autocommitTrue是关键它保证每一条 DELETE 自己就是一个事务脚本被中断时最多只回滚当前这一批。进度文件记录的是下一批的起始 id重启后从断点继续不会重复删也不会漏删。熔断逻辑用continue而不是break因为延迟是暂时性的等一会儿就能恢复直接退出反而要人工再起一次。还有一个容易踩的点Seconds_Behind_Source在某些场景下并不准确主库没有新写入时它会一直显示为 0掩盖真实的延迟。更可靠的做法是用pt-heartbeat打心跳时间戳或者对比主从位点的时间差。如果环境里装了 Percona Toolkit我建议直接用pt-heartbeat。3. 删除慢的真正原因索引、外键与复制链路拆批能把风险摊平但摊不平每一批本身就慢的问题。如果每删一批都要扫几百万行才能命中两千行再怎么拆也是白搭。这一节讲的就是让删除变慢的几类根因。3.1 WHERE 条件没索引会变成全表扫加全表加锁EXPLAIN里 type 显示为 ALL 的时候InnoDB 只能一行行扫过去判断条件是否满足。这个过程不只是慢更要命的是加锁行为在 RC 隔离级别下不满足条件的行锁会在判断后立即释放但扫描过程中仍然要对每一行尝试加锁高并发下会大量消耗锁资源Innodb_row_lock_waits会明显上升在 RR 隔离级别下扫描过的记录锁会更长时间地保留锁膨胀更严重。解决办法就是给删除条件加索引ALTER TABLE access_log ADD INDEX idx_create_time (create_time);但这里有个反直觉的现象当你要删除的数据占总量的比例很高比如删掉 80%优化器可能认为走二级索引回表再删除的代价比直接全表扫描还大于是依然选择全表扫。这种情况下与其纠结索引不如直接考虑第 4 节讲的分区表或者影子表方案。还有一种情况更隐蔽条件字段有索引但索引的选择性极差。比如status 0这种状态字段如果表里 99% 的行都是 status0索引基本没用。这时候应该用组合索引把高选择性字段放前面比如(create_time, status)。3.2 外键与 ON DELETE CASCADE 的隐性开销如果表之间存在外键约束并且定义了级联删除那么删父表的一行数据库会自动去子表删对应的行。这条隐式 DML 不写在你的 SQL 里但它同样加锁、同样写 undo、同样记 binlog。风险在于你根本不知道子表里挂了多少行一次删除可能引发链式反应。我见过一张配置表被删一条记录级联删掉了下游五张表的关联数据总共几十万行。有几个处理原则。大批量删除前先查清楚外键关系SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME your_table AND REFERENCED_TABLE_SCHEMA appdb;删除顺序上先删子表再删父表避免依赖级联。在 session 级别设置SET foreign_key_checks 0可以跳过外键检查级联删除也不会触发但这会留下孤儿数据只适合父子表都要清空的场景而且必须清楚自己在做什么。3.3 从库延迟与 binlog 体积的放大效应ROW 格式的 binlog 下删除多少行就记录多少行的前镜像除非设置了binlog_row_imageminimal。删一千万行如果每行 200 字节binlog 就是接近 2GB 的写入量。这个量级会同时压垮三件事磁盘 IO、从库的回放线程、以及 binlog 文件本身的磁盘占用。能调的参数里binlog_row_imageminimal效果最直接它只记录主键和变化列能把 binlog 体积压到原来的十分之一左右。前提是表的唯一索引完整否则从库定位行会有问题。另外binlog_group_commit_sync_delay也能减少刷盘次数但会影响事务提交延迟要权衡。监控上我一般盯这几个指标指标采集方式关注阈值行锁等待次数Innodb_row_lock_waits的增量持续增长说明有竞争历史链长度Innodb_history_list_length超过一百万要警惕 purge 跟不上主从延迟pt-heartbeat 或位点时间差超过 10 秒考虑暂停活跃线程数Threads_running突增说明有阻塞从库 IO 使用率系统层 iostat长期 100% 说明回放是瓶颈4. 千万级以上的删除换思路比调参数更有效当数据量到千万级甚至上亿级拆批加限速能把事做完但耗时可能是一整天而且期间一直占着磁盘 IO 和复制带宽。这时候需要换思路不要再删数据而是直接丢掉数据。4.1 分区表把 DELETE 换成 DROP PARTITION如果表的删除维度是时间按月或按天分区是最优雅的方案。删除历史数据从删几百万行变成丢弃一个分区后者是元数据操作秒级完成CREATE TABLE access_log ( id BIGINT NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL, url VARCHAR(255), PRIMARY KEY (id, create_time) ) ENGINEInnoDB PARTITION BY RANGE (MONTH(create_time)) ( PARTITION p202401 VALUES LESS THAN (2), PARTITION p202402 VALUES LESS THAN (3), PARTITION p202403 VALUES LESS THAN (4), PARTITION pmax VALUES LESS THAN MAXVALUE ); -- 删除 2024 年 1 月数据秒级完成 ALTER TABLE access_log DROP PARTITION p202401;有几个前提必须清楚。InnoDB 分区表要求分区键是每一个唯一索引的一部分所以主键得写成(id, create_time)这种复合形式这会带来额外的存储开销和索引体积。分区数量不宜过多几百个分区以内还行上千个分区的元数据管理和打开表的开销会明显上升所以按月分区时记得定期合并或者提前建好未来的分区。查询想走分区裁剪WHERE 里必须带分区键否则还是全表扫所有分区。还有一个容易忽略的点DROP PARTITION后虽然数据页被丢弃了但如果表不是innodb_file_per_tableON磁盘文件不会立即缩小。确认一下这个参数的状态再动手。4.2 影子表加 RENAME用一次重建换十小时删除当要删掉的数据占比很高比如只保留最近三个月历史数据要全清更快的做法是直接把要保留的数据复制到新表然后改名切换-- 1. 建同构新表 CREATE TABLE access_log_new LIKE access_log; -- 2. 分块导入要保留的数据 INSERT INTO access_log_new SELECT * FROM access_log WHERE create_time 2024-01-01 ORDER BY id LIMIT 100000; -- 循环推进避免大事务 -- 3. 增量追平应用仍在写入时记录切换点补一次增量 -- 4. 原子切换 RENAME TABLE access_log TO access_log_old, access_log_new TO access_log; -- 5. 确认无误后删除旧表 DROP TABLE access_log_old;RENAME TABLE是原子的执行瞬间会申请一次元数据锁只要没有长事务占用切换体验是毫秒级的。但有几件事必须在切换前准备好视图、触发器、存储过程、外键引用、授权语句都要跟着重建否则切换后应用会报表不存在增量追平的时间窗口要算准切换时最好短暂停写或者用双写过渡。这套方案适合保留比例低的场景。如果只删 5% 的数据把 95% 的数据复制一遍反而是浪费老老实实分批删更划算。4.3 逻辑删除、归档表与专业工具的取舍逻辑删除是最省事也最容易埋雷的一种。加一列is_deleted删除时改成UPDATE ... SET is_deleted 1查询时全部带上is_deleted 0。方便是方便但所有查询都得改漏一处就会读到已删数据索引设计也更麻烦is_deleted基数太低放在组合索引最左边会拖累选择性一般放在最后或者改用状态值配合时间。它适合数据量不大、且删除后还需要保留审计痕迹的场景。归档方案是逻辑删除的升级版热表只保留最近数据历史数据定期搬到归档库或者归档表主库的查询范围被自然限制住了。很多团队是把归档和分区组合起来用热表按月分区冷分区定期导出到对象存储后 DROP 掉。如果不想自己写脚本Percona Toolkit 里的pt-archiver是业内的标准工具它内置了分批、限速、主从延迟熔断、边归档边删除等能力pt-archiver \ --source h10.0.0.1,Dappdb,taccess_log \ --where create_time 2024-01-01 \ --limit 2000 \ --commit-each \ --sleep 0.2 \ --max-lag 20 \ --purge \ --statistics 10000参数里的--commit-each保证每批独立提交--max-lag是延迟熔断阈值--purge表示归档完成后删除源表数据--statistics每隔一万行打印一次进度。它在源库上同样是按主键推进的比手写脚本省心不少。选型上我习惯用这张表来判断场景特征推荐方案理由删除比例低于 10%普通表分批 DELETE 或 pt-archiver改造成本最低可控删除比例高于 30%保留数据可整块筛选影子表重建一次重建比长时间删除快得多删除维度是自然时间表结构可调整按月/天分区DROP PARTITION秒级释放长期收益最大需要保留删除痕迹或合规审计逻辑删除加归档表数据可追溯但查询需配合改造5. 线上删除的踩坑记录与上线前检查前面讲的都是方法论这一节记录几个我在真实环境里踩过的坑以及现在每次做批量删除前都会过一遍的检查清单。5.1 脚本跑一半断了断点续跑与事务粒度设计最常见的中断原因有三个SSH 会话断开、客户端wait_timeout超时、以及机器被重启。前两个可以用nohup加日志输出解决nohup python3 del_access_log.py /tmp/del.log 21 tail -f /tmp/del.log第三个必须靠进度持久化。脚本每隔一批把下一个起始 id 写进文件或数据库的进度表重启后读出来继续。这里有个设计要点进度记录的是下一批要处理的起点而不是已处理到的位置这样即使中断发生在写进度之后、执行删除之前重启后也只是重复删一个空区间不会漏数据。事务粒度上务必保证每批一个独立事务。如果图快把所有批次塞进一个事务里最后一起提交那就等于退回成了大事务前面的拆分全白费。用 mysql 客户端手动执行时注意默认是 autocommit 开启的每一条 DELETE 自动提交这正好符合需求但如果脚本里显式写了BEGIN一定要记得在循环里 commit。还有一个操作习惯上的建议不要用 CtrlC 去中断正在跑的删除脚本。在每批一个事务的设计下CtrlC 只会回滚当前批次是安全的但如果脚本里有大事务中断触发的回滚可能比删除还慢正确做法是让脚本自然跑完当前批或者提前在脚本里加一个信号处理接到信号后处理完当前批再退出。5.2 删完磁盘没变小表空间碎片与 OPTIMIZE 的真实代价删除大量数据后执行df -h发现磁盘占用几乎没有变化这是正常的。InnoDB 的 DELETE 只是把记录标记为删除并加入空闲链表页本身仍留在表空间里等待复用。用下面这条语句可以看到碎片情况SELECT TABLE_NAME, ROUND(DATA_LENGTH / 1024 / 1024) AS data_mb, ROUND(DATA_FREE / 1024 / 1024) AS free_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA appdb AND TABLE_NAME access_log;DATA_FREE就是可复用的空闲空间。如果它占比很高而你又确实需要把文件缩小可以重建表OPTIMIZE TABLE access_log; -- 等价于 ALTER TABLE access_log ENGINEInnoDB, ALGORITHMINPLACE;代价要说清楚。重建表需要额外的磁盘空间大约等于当前表大小会产生大量 IOMySQL 8.0 下虽然是 online DDL但最后切换阶段仍然需要短暂的排他元数据锁长事务会阻塞这个切换。所以千万不要在大表上随手执行 OPTIMIZE最好放到低峰期并且先用df确认磁盘剩余空间足够。另外一个反直觉的结论如果表后续还会持续写入重建表带来的空间收益很快会被新数据吃掉收益有限。只有当表进入只读归档状态、或者碎片率极高且磁盘告急时重建才有明显价值。5.3 每次动手前我都会过的检查清单检查项具体动作出问题的后果数据可删性确认保留期、下游依赖、CDC 订阅误删导致业务报错恢复困难备份可用性确认最近物理备份时间与 binlog 保留时长误删无法回滚到删除前执行计划EXPLAIN 确认走索引type 不是 ALL每批都全表扫限速也救不回来外键依赖查 information_schema 确认级联关系隐式级联删除引发链式数据丢失批大小实测用小批量实测单批耗时批过大导致锁等待和延迟延迟熔断脚本内置延迟检测与暂停逻辑从库延迟持续拉大影响读业务进度持久化进度写文件或进度表支持断点续跑中断后只能从头再来或人工定位空间确认检查 DATA_FREE 与磁盘剩余需要重建时空间不足执行窗口放到业务低峰期提前通知相关方高峰期执行放大故障影响5.4 我现在的默认做法做了几年数据清理之后我的默认选择变得很固定。小表百万行以内直接分批删批大小两千sleep 零点二秒脚本里带延迟熔断日志类、埋点类这种天然带时间维度的表建表时就按月分区删除历史数据统一走DROP PARTITION从源头上就不给批量删除留机会订单、账单这类不能丢也不能随便改结构的表用逻辑删除加归档库热表只留最近一年。真正让人头疼的从来不是某条 SQL 写得好不好而是这张表在建模阶段有没有为未来怎么删数据留出余地——这一点想清楚了后面九成的删除事故都能提前避开。
