MySQL批量删除海量数据：5种实测有效的方案与原理详解

MySQL 批量删除海量数据这几招是我实测下来真正管用的干数据库这行早晚会遇到同一个头疼场景一张表里躺着几千万甚至上亿行数据其中大部分已经成了垃圾你要把它们清掉还不能让业务停摆。直接DELETE FROM table WHERE xxx我劝你千万别这么干。真执行下去轻则锁表锁到业务超时报错重则binlog爆炸、主从延迟拉到天边运气不好还能把磁盘撑爆直接把线上库干趴下。这篇文章我把这些年实际用过的批量删除方案按场景整理出来每种方法都会讲清楚它的原理、适用条件、实际操作用的SQL或命令以及我在生产环境踩过的坑。希望你看完能少走点弯路直接抄作业就行。1. 为什么海量数据不能用一条 DELETE 直接删先讲清楚底层原理你就明白后面那些方案为什么存在了。1.1 一条 DELETE 背后的连锁反应MySQL的InnoDB引擎在执行DELETE FROM table WHERE status0假设命中了5000万行时不是简单地把这5000万行标记为删除就完事。它的完整动作是这样的逐行读取符合条件的数据在索引中定位到每条记录然后标记删除每删一行都会生成对应的undo log用于事务回滚和redo log用于崩溃恢复所有deleted行并不会物理消失而是被标记为“可复用”状态留在原数据页里更大的坑是如果这5000万行分布在不同的数据页上每个涉及的数据页都会先在buffer pool里被加载和修改然后定期刷盘。这就带来三个后果第一这个大事务会一直持有行锁而且随着删除行数增加锁的范围可能不断扩张其他事务的读写会被长时间阻塞第二undo log和redo log的写入量极其惊人尤其是binlog如果你用的是ROW格式每一行删除都会记录完整的前后镜像几十GB的binlog文件很快就能把磁盘干满第三主从架构下这些binlog还要同步到从库回放从库根本追不上主从延迟从几秒拉大到几小时都是常事。1.2 什么才算是“海量数据”我个人的判断标准很简单单表数据量超出服务器内存缓存能力或者一次要删除的行数超过千万级就应该把它定义为海量删除场景。这时候就不能用“普通业务删除”的思路去处理了你需要的是分批、节制、有节奏地删除。还有一个更要命的情况如果这张表上有多个二级索引每删一行都要同时维护所有索引的结构删除成本会成倍上升。所以很多时候你会发现明明只是删数据CPU和IO却比平时跑大查询还夸张。记住这个结论海量删除的核心问题不是“删不掉”而是“一次性删除带来的连锁影响太大了”。所以后面所有方案本质都是在做同一件事——控制单次操作的影响范围。2. 方案选型五种批量删除思路的对比和取舍这些年实际用下来我接触过的批量删除方案归纳起来就是五类。它们没有绝对的优劣之分只有合不合适的区别。下面这张表先给你一个整体印象。方案核心思路适合场景主要优势主要风险基于主键分段 DELETE通过LIMIT控制每批删除行数表结构简单删除条件能快速定位主键范围对业务影响小实施灵活删除速度相对较慢需要循环执行临时表/新表替换保留需要的数据重建表原表废弃保留数据占比小需要彻底清理表删除极快几乎不受原表数据量影响需要锁表或在线DDL工具占用额外空间分区表TRUNCATE PARTITION按分区直接清除整块数据表本身按时间等字段做了分区速度最快秒级完成要求建表时就有分区设计老表改造成本高pt-archiver工具利用Percona工具流式分批归档/删除复杂删除条件需要精细控制速度灵活可控自动处理锁和延迟需要安装Percona Toolkit额外学习成本冷数据归档后删除先把数据迁移到历史表/归档库数据生命周期管理合规要求兼顾删除与保留双赢流程复杂需要额外的存储从实际运维的角度出发我一般建议按这个顺序决策如果表当初就做了分区优先用分区删除如果删除条件适合用主键索引快速定位直接写脚本分批DELETE如果一次性要清掉的数据占比极大考虑重建表方案如果场景复杂且你有条件安装额外工具pt-archiver最省心。下面我逐个展开讲具体怎么做。3. 方案一基于主键ID分段循环删除这是我最常用、也最推荐给大多数团队的方案。原理很简单把一条大DELETE拆成无数条小DELETE每次只删除一小批比如1000行删除后程序暂停一小会儿再继续让数据库有时间完成刷盘和日志写入。3.1 核心SQL写法和脚本实现假设我们有一张订单表orders要删除status0且created_at 2023-01-01的过期订单总行数约3000万。先确认主键范围这样可以避免全表扫描定位起点SELECT MIN(id), MAX(id) FROM orders WHERE status0 AND created_at 2023-01-01;然后写循环删除。可以用存储过程在MySQL里直接执行我提供一个比较通用的版本DELIMITER $$ CREATE PROCEDURE batch_delete_orders() BEGIN DECLARE batch_size INT DEFAULT 1000; DECLARE affected_rows INT DEFAULT batch_size; DECLARE sleep_seconds DECIMAL(3,1) DEFAULT 0.1; WHILE affected_rows batch_size DO DELETE FROM orders WHERE status 0 AND created_at 2023-01-01 LIMIT batch_size; SET affected_rows ROW_COUNT(); -- 每次删除后暂停给InnoDB缓冲池和IO一些喘息时间 DO SLEEP(sleep_seconds); END WHILE; END$$ DELIMITER ; CALL batch_delete_orders();几个关键点说明一下LIMIT batch_size是必须的没有它SQL会一次删全表ROW_COUNT()拿到本次实际删除行数当它小于batch_size时说明已经删除完毕循环结束SLEEP不是可有可无的它在主动控制删除节奏防止删除速度过快导致从库回放跟不上。另一个写法是利用主键范围分段这种方式的删除条件能准确利用到主键索引效率更高-- 记录当前起始ID每次删除一个ID区间段 SET last_id 0; SET batch_size 1000; WHILE 1 DO DELETE FROM orders WHERE id BETWEEN last_id AND last_id batch_size AND status 0 AND created_at 2023-01-01; SET last_id last_id batch_size; IF ROW_COUNT() 0 AND last_id (SELECT MAX(id) FROM orders) THEN LEAVE; END IF; DO SLEEP(0.1); END WHILE;用BETWEEN方式的好处是不管这批数据里有多少行符合删除条件SQL都能稳定地利用主键索引进行范围扫描不会因为某个区间内符合条件的数据特别多而一次删太多。3.2 每批删除多少行最合适LIMIT 1000不是我拍脑袋定的数这是基于单次删除对锁、undo log和主从延迟的综合考量。我实测过的几种批量大小对比如下每批行数平均时延对主从影响适用环境500较慢几乎无感核心业务表极其敏感1000适中可接受大多数场景推荐5000较快可能有短暂延迟冷表、夜间维护窗口10000以上很快延迟明显不推荐失去分批意义如果你的需求是通过脚本删除除了存储过程用Shell脚本配合MySQL命令行也很常见。这里有个小经验脚本里每次执行完一批最好加一个sleep 0.1~0.5的停顿这样对数据库的压力曲线是平滑的不会形成尖刺。很多团队最终用Python脚本去跑核心逻辑一样就是封装成一个函数循环调用。我把Python版本的框架也放出来。import pymysql import time conn pymysql.connect(host127.0.0.1, userroot, passwordxxx, dbtest, charsetutf8mb4) cursor conn.cursor() batch_size 1000 sleep_seconds 0.2 while True: sql DELETE FROM orders WHERE status 0 AND created_at 2023-01-01 LIMIT %s affected cursor.execute(sql, (batch_size,)) conn.commit() print(fdeleted {affected} rows) if affected batch_size: break time.sleep(sleep_seconds) cursor.close() conn.close()这个版本我建议你在测试环境先跑通再放到生产。核心点在于每批删除后必须commit否则事务会越积越大又走回大事务的老路了。3.3 这个方案的坑和应对方式第一个坑删除条件的索引问题。如果你WHERE后面的字段没有合适的索引哪怕每次只删1000行MySQL也要全表扫描才能找到这1000行几千个批次下来全表扫描的次数会让你崩溃。所以执行之前务必用EXPLAIN验证一下删除条件的执行计划。没有索引的就先建索引删完再考虑要不要把索引去掉。第二个坑主从延迟。即使每批只删1000行如果从库硬件配置比主库差或者从库上还在跑报表查询延迟照样会越积越多。我的对策是在脚本里获取当前从库延迟指标Seconds_Behind_Master当它超过设定阈值时自动休眠更长时间。很多现成的删除工具都内置了这个功能自己写的话要注意加上。第三个坑别忘了删除后的表空间问题。InnoDB的DELETE并不会归还磁盘空间给操作系统只是把数据页标记为可复用。如果这张表删除后会长期保留大量空洞即使里面没数据文件还是占着几个GB。如果确认这批数据彻底删除、不会再写入同范围的ID删除完成后可以执行一次ALTER TABLE orders ENGINEInnoDB或OPTIMIZE TABLE orders来整理表空间。这个操作会锁表必须放在业务低峰期。注意ALTER TABLE可以重建表来整理碎片但它本质上是拷贝整张表数据耗时和表数据量成正比。这个操作必须放到维护窗口执行。4. 方案二临时表/新表替换删除法这个方法的思路是彻底绕开DELETE操作直接“重建”一张干净的表。它的适用场景很鲜明你只需要保留原表中很少的一部分数据比如1%其余99%都可以丢掉。4.1 传统做法和雷区传统做法是建一张新表把需要保留的数据插入新表然后RENAME TABLE完成切换-- 第一步创建新表结构和原表一致 CREATE TABLE orders_new LIKE orders; -- 第二步插入需要保留的数据 INSERT INTO orders_new SELECT * FROM orders WHERE status 1 OR created_at 2023-01-01; -- 第三步切换表名 RENAME TABLE orders TO orders_old, orders_new TO orders; -- 第四步确认无误后删除旧表 DROP TABLE orders_old;这个流程看起来没问题但有一个致命细节INSERT INTO ... SELECT的过程中原表数据可能还在被业务写入。如果这期间有新数据插入orders切换后这些数据就丢了。所以很多团队的做法是先对原表加写锁这等于业务停写一段时间。如果保留的数据量大插入耗时就会很长业务停写窗口根本等不起。对于MySQL 5.6以上的版本推荐用在线DDL的方式规避这个问题ALTER TABLE orders ADD COLUMN delete_flag TINYINT NOT NULL DEFAULT 0 COMMENT 0保留,1删除, ALGORITHMINPLACE, LOCKNONE;不过在线DDL只能解决结构变更的问题解决不了数据搬运的问题。所以这个方法真正的工程化落地通常配合pt-archiver或者gh-ost这类工具来做数据搬迁。如果你没有这类工具而且允许短时间停写传统做法依然是可行的。4.2 新表替换后别忘了重建索引很多人创建orders_new LIKE orders之后以为索引也一并复制过来了。LIKE建表确实会复制原表的索引结构、自增属性等这一步没问题。但有个容易漏掉的地方外键和触发器不会通过LIKE复制。如果原表有外键约束或者触发器新表必须手动补充。还有一点切换完表之后业务代码如果依赖自增ID的连续性和最大值RENAME TABLE切换过去的新表其AUTO_INCREMENT值是从插入的最大ID1继续的这一点倒是没问题。但如果原表上有依赖表名如表名作为分表键的配置切换后要同步检查配置。4.3 实际工程中我建议的做法如果数据量真的很大比如几亿行我一般会把这个过程拆成两步第一步先把需要保留的数据用pt-archiver或者分批INSERT INTO ... SELECT迁移到新表这一步不用锁原表业务照常写原表迁移时间可以拉长白天跑也行只要控制好速度。第二步在业务低峰期短暂停写快速对账确认原表数据没有新增或新增数据已同步然后执行原子切换。切换窗口可以压缩到秒级对业务的影响很小。这个方案的核心价值在于它把“删除大量数据”变成了“迁移少量数据”工作量和风险都大幅降低了。5. 方案三利用分区表实现秒级清理如果你在建表之初就有意识地按时间字段做了分区那么删除“过期数据”就会变成一件非常简单的事。因为你根本不需要逐行删除直接把整个分区TRUNCATE掉或者DROP掉速度是秒级的。5.1 分区表删除的基本操作假设我们有一张日志表access_log按月份做了RANGE分区CREATE TABLE access_log ( id BIGINT NOT NULL AUTO_INCREMENT, log_time DATETIME NOT NULL, content TEXT, PRIMARY KEY (id, log_time) ) PARTITION BY RANGE (TO_DAYS(log_time)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)), PARTITION p202404 VALUES LESS THAN (TO_DAYS(2024-05-01)), PARTITION p_future VALUES LESS THAN MAXVALUE );要删除2024年1月的日志只需ALTER TABLE access_log TRUNCATE PARTITION p202401;注意这里用的是TRUNCATE PARTITION而不是DROP PARTITION。两者的区别在于TRUNCATE PARTITION清空分区内的所有行但分区定义保留DROP PARTITION分区和数据一起删除后续如果还要写入1月的数据需要重新ADD PARTITION。如果你确定这个分区以后不会再写入数据直接用DROP PARTITION更干脆还能释放表空间。否则用TRUNCATE PARTITION。5.2 为什么分区删除这么快这个问题的答案是分区表在物理存储上就是多个独立的数据段。TRUNCATE PARTITION本质上是对一个独立的数据文件做快速截断它不产生逐行删除的binlog对如果你开启了binlogTRUNCATE TABLE只记录一条DDL语句不会记录几千万条删除明细也不产生undo log更不会逐行加锁。所以它的速度和性能是前面所有方案都望尘莫及的。也正是因为不产生逐行binlog如果你依赖binlog做数据恢复或同步到其他系统比如通过Canal同步到消息队列要特别留意分区被TRUNCATE后下游接收到的是一条DDL事件而不是逐行的删除事件如果你的下游是根据binlog明细做聚合统计的数据口径会发生变化。5.3 没有分区想改造成分区怎么办这是实际操作中遇到最多的情况表在建的时候没做分区现在数据量大了想享受分区删除的红利怎么办MySQL支持在线修改表的分区结构ALTER TABLE access_log PARTITION BY RANGE (TO_DAYS(log_time)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)), PARTITION p202404 VALUES LESS THAN (TO_DAYS(2024-05-01)), PARTITION p_future VALUES LESS THAN MAXVALUE );但这个操作有个前置条件分区键必须是主键或唯一索引的一部分。上面例子里我把主键建成了(id, log_time)联合主键就是为了满足这个要求。如果你的原表主键是单列id那么直接按时间分区是行不通的需要先调整主键结构这又涉及在线DDL了。另一个注意点是给无分区表添加分区时需要全表扫描并重建表期间对IO和CPU的压力很大。建议在维护窗口执行并确保磁盘有足够空间。实操建议时间字段类型优先用DATETIME或TIMESTAMP计算方式用TO_DAYS()函数。如果你用UNIX_TIMESTAMP()做分区表达式分区裁剪在某些版本的优化器上可能不够精准查询性能会打折扣。6. 方案四pt-archiver 工具流式删除前面几个方案都有各自的局限性如果想要一个通用性强、能精细控制删除节奏、自动感知主从延迟的工具那就得请出Percona Toolkit里的pt-archiver了。这是我个人在复杂生产环境中用得最放心的工具。6.1 工具安装和基本用法安装Percona Toolkit的方式不再赘述主流Linux发行版都有源或者可以直接下载RPM包。关键看怎么用。它的核心功能是“归档”和“删除”既能从一张表里按条件读取数据写入另一张表归档也能只删除数据不落任何地方清理。基本用法pt-archiver \ --source h127.0.0.1,P3306,uroot,pxxx,Dtest,torders \ --where status0 AND created_at 2023-01-01 \ --limit 1000 \ --bulk-delete \ --bulk-size 500 \ --commit-each \ --max-lag 5 \ --sleep 0.1 \ --purge \ --progress 10000其中各参数的含义和选型理由如下参数作用我的经验值--limit每次SELECT读取的行数1000~2000--bulk-delete批量删除模式减少交互次数在确认删除条件可控时使用--bulk-size每条批量DELETE的大小500~1000--commit-each每处理一批就提交一次事务必须开启--max-lag从库延迟超过阈值时自动暂停5秒视环境调整--sleep每批之间的休眠时间0.1~1--purge只删除不归档删除场景必加6.2 几个细节你必须知道第一pt-archiver删除时默认是通过主键或唯一索引逐行定位的这对源表的索引要求比较高。如果它的--where条件不能快速定位到符合条件的数据工具会跑得很慢甚至长时间占用CPU。第二工具默认会检查从库延迟但前提是--source里指定的账号有权限访问从库的SHOW SLAVE STATUS。实际生产环境中如果你在连接串里指定了--source为主库它有时不能自动发现从库。这种情况有两个解决办法一是临时指定--slave-user等参数让工具连到从库去检查二是干脆显式指定--max-lag为0来关闭自动延迟感知但这样就把控制主从延迟的主动权交给了你自己不太推荐。第三--bulk-delete模式下工具会先把选中的行主键收集起来然后用多条DELETE ... WHERE id IN (...)的方式批量删除效率比逐行删高很多。但要注意如果IN列表过大生成的单条SQL很长需要配合--bulk-size限制大小。第四如果你的需求是归档而不是删除去掉--purge加上--dest就能把数据流式插入到另一张表或另一个库中。这个功能在做数据冷热分离时特别实用。pt-archiver \ --source h127.0.0.1,P3306,uroot,pxxx,Dtest,torders \ --dest h10.0.0.5,P3306,uarchive,pxxx,Dwarehouse,torders_archive \ --where created_at 2023-01-01 \ --limit 1000 \ --commit-each \ --sleep 0.16.3 pt-archiver 不如其他方案的地方工具虽好也不是万能的。第一它依然会产生逐行的binlog日志因为底层还是DELETE操作。对于要把binlog同步到大数据平台如Kafka、Hive的场景一次大清理会产生海量的变更事件下游消费可能出现明显延迟。第二它在删除大量历史数据时速度不如分区TRUNCATE快毕竟它受限于逐行定位的模式。第三需要额外安装Percona Toolkit并维护它的运行环境在一些安全要求严格的银行、政企环境里引入额外工具要走流程审批比较麻烦。所以如果表已经有合适的分区设计优先TRUNCATE PARTITION表结构简单、删除条件清晰、团队不想引入额外工具优先自己写分批DELETE脚本需要精细控制删除速度且条件复杂或者需要同步归档选pt-archiver删除的数据占比极大且可以接受短时间停写选“新表替换”方案。这样组合使用基本能覆盖绝大多数场景了。7. 通用问题排查与避坑清单不管用上面哪种方案有几个问题是跨方案通用的。这里整理成速查表遇到问题的时候可以直接对照排查。典型问题出现原因排查方法解决措施执行删除后主从延迟飙升单批删除量过大或binlog回放跟不上SHOW SLAVE STATUS查看Seconds_Behind_Master减小批量值增加sleep暂停删除等待延迟回落删除速度越来越慢删除条件无索引全表扫描次数过多EXPLAIN查看执行计划在WHERE条件字段上建立索引磁盘空间不减反增InnoDB空洞和undo log膨胀df -h查看磁盘SHOW TABLE STATUS查看Data_length删除完成后执行ALTER TABLE ... ENGINEInnoDB整理碎片删除过程中业务超时锁冲突或IO竞争查看SHOW PROCESSLIST确认是否有长时间运行的事务减小批量值错峰执行确保事务及时提交DELETE执行到一半报锁等待超时大事务与其他DML冲突查看错误日志和innodb_lock_wait_timeout分批删除避免长事务必要时适当调大超时值binlog文件暴涨逐行删除产生大量binlog事件查看binlog大小和增长趋势改用分区删除或新表替换减少逐行删除量删除导致主库CPU/IO过高未控制删除速度top、iostat观察资源占用增加sleep调小batch_size限制并发最后再分享几个我自己的习惯可能对你有帮助第一正式执行大删除之前先在测试环境把同量级的数据、同结构的表完整跑一遍。不要只测SQL能不能跑通要看它整体的耗时、资源占用曲线、binlog增量。第二执行删除前把表结构和当前最大ID、数据分布情况记录下来。万一出了问题至少能知道这张表在执行前是什么状态方便恢复。第三操作生产表之前检查一下磁盘空间。很多大删除中途失败都是因为binlog把磁盘撑满了这个坑我踩过记忆犹新。第四注意MySQL的SQL_SAFE_UPDATES模式。如果你的会话开启了sql_safe_updates或者云厂商默认开启不带WHERE条件的DELETE和UPDATE会被拒绝执行。批量删除脚本里如果用了LIMIT但没有合适的WHERE一样会被拦下来。遇到这种情况不要慌检查一下当前会话的sql_safe_updates变量。SHOW VARIABLES LIKE sql_safe_updates;如果确认是它导致的可以临时关闭SET SESSION sql_safe_updates0;但我不建议全局关闭这个安全选项尤其是生产环境它能在你手误的时候救你一命。8. 写在最后我的一些经验总结跑过这么多删除任务以后我对“批量删除海量数据”最核心的体会是删除操作的难点从来不在SQL本身而在于你如何管理它对系统产生的连锁影响。我在实际工作中最常使用的组合拳是如果表结构支持分区优先TRUNCATE PARTITION如果不支持就看保留数据占比占比很小就考虑新表替换占比大就用pt-archiver或者自己写脚本分批删。还有一个容易被忽略的小技巧使用pt-archiver删除大量数据时加上--statistics参数跑完它会打印整体耗时、平均吞吐量等指标。这些数据对今后的容量规划和方案选型非常有价值建议保留下来。另外建议所有做数据删除的团队都建立自己的“删除操作checklist”确认删除条件、确认索引、确认磁盘空间、确认主从状态、确认binlog增长预期、确认业务低谷窗口、确认回滚方案。数据恢复往往是很困难的能在删除前多想一步比事后折腾半天要划算得多。