如果你手上有张表里面躺着上亿条历史数据有一天突然需要清掉大半第一反应是不是写一条DELETE FROM 表名 WHERE create_time 2024-01-01我直接说结论生产环境这么做十有八九要出事。轻则锁表拖垮线上查询重则事务日志撑爆磁盘、主从复制中断极端情况直接把实例搞挂。今天我想把这几年在 MySQL 批量删除海量数据上踩过的坑以及实际验证过可行的几种方法一次性讲透。文章会覆盖主键分片、时间字段分片、新表替换、分区表删除四种主流方案还会附上生产环境的踩坑实录和排查思路适合正在做大表清理的后端开发、DBA以及所有被“删数据”折磨过的同行。先说清楚一个观念海量数据删除的目标从来不是“删得快”而是“删得稳”。这里说的稳指的是不影响线上读写、不拖垮主从、不撑爆磁盘、随时能暂停能恢复。所以下面所有方法都是围绕“稳”来设计的。1. 为什么“直接 DELETE 大表”会出大问题批量删除的设计思路1.1 一条大 DELETE 在 InnoDB 里到底干了什么很多人以为 DELETE 就是把数据抹掉其实在 InnoDB 里一次大范围删除的代价远超你想象。我拆开来讲第一锁的范围会不断扩大。InnoDB 默认是行级锁听起来很美好但一条DELETE WHERE create_time 2024-01-01如果匹配了上千万行引擎需要逐行加锁。在 READ COMMITTED 隔离级别下锁会分批释放但在 REPEATABLE READ 下删除大量行时锁和 undo 的维护开销非常大而且相邻记录之间的间隙锁还可能互相干扰。实际表现就是你执行 DELETE 后没多久其他查询全部堵在“Waiting for table metadata lock”或者锁等待上。第二undo log 和 binlog 会疯狂膨胀。InnoDB 为了保证事务回滚每删除一行就会在 undo log 里记录原始数据。删除 5000 万行undo 就是 5000 万条完整行记录。binlog 在 ROW 格式下更恐怖会记录每行删除前后的完整镜像。我见过一个实际案例删了 2 亿行binlog 直接写了几百 GB把数据盘撑满了。这不仅仅是空间问题还会让主从复制从库需要回放巨大的 binlog延迟一路飙升。第三buffer pool 里的热数据会被大量置换。你要删除的行如果正好是刚被查询过的热数据InnoDB 要把这些页读入 buffer pool然后标记删除、做 purge。大量脏页刷盘会导致磁盘 IO 飙升线上查询响应时间瞬间恶化。第四最容易被忽略的是一个超大事务长时间不提交会阻塞 purge 线程。而 purge 线程负责清理已删除记录的版本链。它一旦被阻塞undo log 就不能及时回收表空间越撑越大后续所有更新操作的性能都会被拖累。所以你看一条 DELETE 语句背后牵扯的是锁、日志、内存、IO 四个层面的连锁反应。直接删大表等于把压力全部瞬间砸到实例上生产环境根本兜不住。1.2 批量删除的核心设计原则分片、限速、可观测既然一次性删除不行那就把大事务拆成很多个小事务每个事务只处理一小批数据。这就是“分片删除”的基本盘。我总结成四个原则新手照着做基本不会翻车分片原则每次删除的数据量要控制在一个安全范围内。这个范围由你的行大小、实例配置、binlog 大小共同决定通常每批 500 到 5000 行是比较稳的起步区间。限速原则两批之间加一个 sleep 间隔给主从复制留出回放时间给磁盘留出刷盘时间。一批“咔咔”删完立刻下一批和一次性猛删没有本质区别。窗口原则把任务放在业务低峰期执行。如果业务随时都有高峰就要做成可控并发 可暂停的脚本不能一把梭跑完。可观测原则明确知道当前删到了哪个位置进度是多少主从延迟和磁盘空间的变化趋势如何。千万别闷头执行等到报警了才发现出了问题。这四个原则我几乎在任何一次批量清理中都会用到它们不绑定某个具体方案是通用的方法论。接下来的四种方法本质上都是在这四个原则上做出不同侧重的实现。2. 最通用的方案基于主键 ID 的分批删除2.1 为什么优先用主键 ID 分片如果表的主键是自增 ID那这是最理想的分片键。原因有三主键天然是聚簇索引按 ID 范围过滤可以走索引直接定位到数据页效率最高所有 InnoDB 表都必须有主键不存在“没有主键”的场景如果没有显式主键MySQL 会隐藏生成一个但你也用不到用 ID 作为游标记录进度非常自然删到哪个 ID 一清二楚重启脚本可以从断点继续。哪怕你是按时间条件来筛选要删除的数据也建议先把“要删的 ID 范围”算出来再按 ID 分片而不是直接按时间范围去 DELETE。原因后面在第三节讲时间分片时你会更明白。2.2 常用 SQL 模板与参数计算标准做法是先查目标数据的最小和最大 ID然后在一个循环里每次删除一个固定 ID 区间。我先给一个模板这是去掉存储过程包装后的核心 SQL-- 先定位要删除的数据范围 SELECT MIN(id), MAX(id) FROM your_table WHERE create_time 2024-01-01; -- 假设这个范围是 1000000 到 50000000 -- 每次删除 5000 行的 ID 区间从最小值开始向前推进 DELETE FROM your_table WHERE id BETWEEN 1000000 AND 1004999 AND create_time 2024-01-01;你可能会问为什么WHERE id BETWEEN后面还要加create_time条件这是为了防止在这个区间内混入了不该删的数据也防止边界区间的数据在上一批删除后新插入的数据的 ID 落入了下一批的范围。说白了就是给删除条件加一道保险。每批多少行合适这个值没有标准答案我一般按下面几个口径去估估算单行大小。比如一行 0.5 KB5000 行数据大约是 2.5 MB对应的 undo 和 binlog 大约是这个的几倍也就是十几 MB 的事务量对大多数实例来说比较安全。如果你的业务对锁非常敏感起步先用 500 行测试观察主从延迟和 CPU 波动稳定后可以逐步加到 1000、2000。有个上限提示单批别超过 1 万行。超过 1 万行事务耗时和锁持有时间会显著变长碰到高峰期容易引发锁等待。两批之间 sleep 多久这个必须结合主从延迟来判断。如果从库是秒级同步硬件较好sleep 100 毫秒就行。如果主从延迟已经在上涨sleep 至少 300 到 500 毫秒给从库追平的机会。我实际用下来1000 行一批 200 毫秒 sleep对一个中等配置的实例来说是比较舒服的节奏。下面是一个可以用 Python 脚本控制的示例片段很多 DBA 倾向用外部脚本来控制而不是塞进存储过程因为便于打印进度、控制启停import time import pymysql conn pymysql.connect(hostlocalhost, userapp_user, passwordyour_pass, databaseyour_db) cursor conn.cursor() # 获取要删除的最小和最大id cursor.execute(SELECT MIN(id), MAX(id) FROM your_table WHERE create_time 2024-01-01) min_id, max_id cursor.fetchone() batch_size 1000 current_id min_id while current_id max_id: end_id min(current_id batch_size - 1, max_id) sql DELETE FROM your_table WHERE id BETWEEN %s AND %s AND create_time 2024-01-01 cursor.execute(sql, (current_id, end_id)) conn.commit() print(fdeleted id range: {current_id} - {end_id}, affected: {cursor.rowcount}) current_id end_id 1 time.sleep(0.2) # 控制删除速率这个脚本的生产可用版本还需要加异常重试、进度落表等逻辑但核心就是这个循环结构。用外部脚本还有个大好处程序崩溃后可以记录current_id重启后从断点继续而不用从头开始扫范围。2.3 实操要点与避坑第一种方案虽然最通用但有几个细节不注意照样翻车。先确认隔离级别。如果是 REPEATABLE READ删一批数据也要小心间隙锁。实际中我有一次在默认隔离级别下跑删除业务侧插入刚好卡在被删区间的边界堵了十几秒。后来把会话隔离级别改成 READ COMMITTED锁竞争明显好转。改法很简单SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;。不要用DELETE FROM table WHERE create_time ... LIMIT 1000。MySQL 的 DELETE 支持 LIMIT 语法但它的执行计划往往是先找到满足 WHERE 条件的行然后随机取 1000 行删掉没有稳定的游标边界你可能反复删除到同一批数据而且性能很差因为每次都要重新扫描定位开头的 1000 行。除非表有合适的索引能让优化器稳定走到边界否则不建议。进度表很重要。如果删除任务会持续几小时建议在另一个库里建一张简单的 progress 表记录当前删到了哪个 ID、每批耗时、遇到错误次数。这样即使脚本崩了、终端关了也能查进度并安全续跑。回调压力。如果删的是订单、流水这类业务表应用层可能会有缓存或统计依赖删完一批后线上本来查得到的数据突然少了要提前跟业务侧打招呼。3. 按时间字段分片日志表和流水表的清理套路3.1 什么时候该用时间字段而不是主键分片业务表的主键并不总是按时间递增的。比如订单号可能是雪花算法生成、业务单号是随机字符串这时候你没法用主键来划定“哪些是更早的数据”只能靠create_time来筛选。日志表、流水表基本都属于这种。处理这类表最容易踩的坑是直接在create_time上 DELETE 而没有合适的索引。如果没有索引一次 DELETE 要全表扫描先不说锁的问题光是扫描上亿行就够你受的。所以前提条件是先确认create_time上有索引最好是一个(create_time, id)的复合索引。加 id 列不是闲得慌是为了让每批删除的边界更精准排序更稳定也方便和主键分片方法打通。3.2 用时间范围加 LIMIT 的存储过程实现有了索引之后最稳的做法是一次不要删除整个时间范围而是用LIMIT限制住每批行数配合一个不断向前推进的时间游标。先看一个通用模板这是存储过程版本的思路DELIMITER $$ CREATE PROCEDURE clean_expired_logs(IN cutoff_time DATETIME, IN batch_size INT) BEGIN DECLARE done INT DEFAULT 0; DECLARE affected_rows INT DEFAULT 0; DECLARE v_start DATETIME; DECLARE v_end DATETIME; -- 先拿一条最老的数据作为本次删除的起始游标 SELECT MIN(create_time) INTO v_start FROM log_table WHERE create_time cutoff_time; SET v_end v_start; WHILE v_end IS NOT NULL AND v_start cutoff_time DO -- 每批往前推进一个时间点但通过 LIMIT 控制行数 DELETE FROM log_table WHERE create_time v_start AND create_time DATE_ADD(v_start, INTERVAL 1 HOUR) ORDER BY create_time, id LIMIT batch_size; SELECT ROW_COUNT() INTO affected_rows; -- 如果这一小时内的数据还没删完v_start 不变下一批继续清 -- 如果删完了v_start 推进到下一小时 IF affected_rows batch_size THEN SET v_start DATE_ADD(v_start, INTERVAL 1 HOUR); SET v_end v_start; END IF; -- 每批提交避免长事务 COMMIT; -- 限速 DO SLEEP(0.2); END WHILE; END$$我来说说这个流程里几个关键设计的原因。一是“按小时推进”不是固定死的。如果你的日志表一天的数据量很大千万级按小时推进会导致单批可能超出 LIMIT 太多锁范围变大如果一天数据量很小按小时推进又会让删除粒度太粗一批就删光一天的数据事务还是很大。所以时间间隔要根据数据密度来定我可以先做聚合查询看每个小时的数据量再决定用分钟还是小时作为推进梯度。二是就算按时间区间推进也一定要带LIMIT。因为数据分布不均匀你以为这个小时就几万行结果某次促销堆了百万行没 LIMIT 就等于一次删超大事务。无论时间范围怎么切LIMIT 始终是最后一道保险。三是ORDER BY create_time, id的作用。它保证同一个时间区间内按稳定的顺序删除避免并发插入导致同一批数据被反复扫描。3.3 游标方式的适用场景与局限性有些教程会推荐用游标一行一行删类似DECLARE cur CURSOR FOR SELECT ...然后 FETCH 一行 DELETE 一行。我必须说这种方式只适合删除量很小的场景比如几万条。因为游标循环本身有解释执行开销而且每次 DELETE 会产生一次独立的行操作效率极低。删除百万行以上游标方式往往比直接 DELETE 还慢还更容易产生碎片。我见过的唯一适用场景是数据稀疏分散必须根据某些复合业务规则判断这一行删不删比如某个状态字段加上时间字段共同决定。这种情况下游标做的是业务过滤不是批量清理所以不能跟批量删除画等号。如果你是单纯按时间清理别用游标。4. 新表替换法不改索引不积压事务的“搬家式”清理4.1 思路把“删除数据”变成“保留数据”如果说前两种方案是“原地清理”那这一种就是“搬走重来”。思路很简单与其在原来的大表上删掉大量行不如建一张新表只把需要保留的数据拷进去然后通过改名把两张表互换最后删掉旧表。这种方式在“删除数据占总数据量的大部分”时特别有效。举个例子你的表有 1 亿行只需要保留最近 3 个月的 1000 万行。直接 DELETE 9000 万行参数上可能要走几小时甚至几天而且过程中索引、锁、日志的压力全部真实作用于在线表上。但新表替换法的思路是只迁 1000 万行迁移过程虽然也有写压力但整体更可控而且迁移完旧表直接 DROP不需要再做大量 UPDATE 统计、OPTIMIZE TABLE 之类的收尾工作。4.2 具体操作五步走我自己按下面的顺序操作出错的概率最低第一步创建同结构的新表CREATE TABLE your_table_new LIKE your_table;LIKE会完整复制原表的列定义、索引、约束。注意它不会复制自增计数器的当前值所以切换后如果有自增列别忘了设置一下 AUTO_INCREMENT否则可能新插入数据时主键冲突。第二步分批把要保留的数据插入新表INSERT INTO your_table_new (col1, col2, col3, ...) SELECT col1, col2, col3, ... FROM your_table WHERE create_time 2024-01-01 AND id BETWEEN ? AND ?;这一步最关键。很多人图省事直接INSERT INTO ... SELECT * FROM ... WHERE 保留条件一次性迁几百万行长事务、锁竞争、undo 膨胀的问题全部回来了。必须分批迁移每批几万行或者按时间窗口一段段迁。第三步原子切换表名RENAME TABLE your_table TO your_table_old, your_table_new TO your_table;RENAME TABLE 在 MySQL 中是原子操作执行瞬间只会有一个极短的元数据锁窗口业务无感知或者感知极小。这一步是把旧表退位新表顶上。第四步确认业务正常后删除旧表DROP TABLE your_table_old;删除旧表是很重的操作因为要清理表定义、数据页、索引磁盘 IO 和 buffer pool 都会有波动。这里可以分几步先TRUNCATE旧表如果已不再需要把数据页释放再 DROP能明显减轻最后 DROP 的瞬时压力。第五步检查依赖对象。视图、触发器、存储过程、外键这些对象在切换后都要重新核对。比如视图如果定义了具体的表名切换后需要重新创建或者修改定义触发器同理如果这张表有外键被其他表引用RENAME 可能会影响引用关系需要提前连同外键一起处理。4.3 新表替换法该不该用先回答这几个问题这个方法好但不是万能药。我在评估时通常会问自己几个问题业务能不能接受一个短暂切换窗口虽然 RENAME 本身很快但从“新表开始接收写入”到“旧表被废弃”之间应用可能还连着旧表连接。如果应用有数据库连接池连接池里的旧连接可能仍然指向旧表或者因为表名变化导致后续 statement 报错。线上切换前最好让应用侧的 SQL 层支持自动重连或者安排一个非常短的维护窗口。切换期间如果有写入怎么办如果写入不能停方案就复杂了。要么用透明切换代理要么在切换前把写入流量切到新表但应用代码要改表名这在大型系统里不是随时能做的。所以维护窗口很重要——哪怕只停 10 秒也要提前说明。新表数据迁移期间旧表还在线上服务吗在迁数据的过程中旧表还在正常读写业务会继续往旧表插入新数据。你迁移完旧表数据、切换到新表后这些“切换前最后一两分钟写入的数据”是否已经同步到新表如果不能就要设计一个“增量追平”的步骤。解决“增量追平”有个土办法记录切换前最后一批插入数据的 ID 范围在 RENAME 前再补迁一次这个增量范围或者干脆在切换时停写几秒以确保新表数据完整。对于可以接受短停写的场景这个方案最简单可靠。4.4 什么时候千万别用新表替换法有两类场景我强烈建议放弃这个方法。一类是保留数据占大多数。比如 1 亿行要删 1000 万行留 9000 万行那不如直接在原表删除因为拷贝 9000 万行的代价远高于删除 1000 万行。 另一类是表上有大量外键或复杂关联。RENAME 后外键关系需要全部重建涉及对象很多操作风险极高。5. 分区表删数据如果一开始设计对了就是最省心的5.1 分区表为什么删得快分区表是我个人最喜欢的方案但前提是建表时就想好了数据生命周期管理。比如日志表按月份建 RANGE 分区每个月的数据落在一个独立分区里。删除一个月的历史数据时执行ALTER TABLE log_table DROP PARTITION p202403;这个操作本质上是在物理文件层面删除一个分区InnoDB 只需要丢弃那部分数据和索引页不需要逐行记录 undo、不需要逐行加锁、binlog 里也只是一条 ALTER 记录。执行速度比 DELETE 快好几个量级而且对在线业务的影响小得多。如果不想删除分区只想清空某个分区的数据但保留分区结构可以用ALTER TABLE log_table TRUNCATE PARTITION p202403;TRUNCATE PARTITION 的效果相当于把这个分区的数据文件重置同样不会产生大量行级 undo 和 binlog比 DELETE 快很多。5.2 建表时的分区设计要点如果你现在要设计一张未来会持续增长的数据表我建议认真考虑分区方案。一个典型的按月分区建表语句CREATE TABLE audit_log ( id BIGINT NOT NULL AUTO_INCREMENT, created_at DATETIME NOT NULL, content TEXT, PRIMARY KEY (id, created_at) ) PARTITION BY RANGE (YEAR(created_at) * 100 MONTH(created_at)) ( PARTITION p202401 VALUES LESS THAN (202402), PARTITION p202402 VALUES LESS THAN (202403), PARTITION p202403 VALUES LESS THAN (202404), PARTITION p202404 VALUES LESS THAN (202405) );这里的关键限制是分区键必须包含在主键中。所以上面我把主键从单纯的id改成了(id, created_at)。如果原表主键是id要想按时间分区必须先调整主键结构这是个有一定改造成本的操作经常被开发者忽略。另一种更简单的分区方式是直接用TO_DAYS()函数做范围分区逻辑清晰但要注意函数表达式在分区剪枝时能否被优化器利用。不要图方便直接分区到天分区数量太多会导致表元数据管理开销变大太多小分区会把性能反而拖差。日志类数据按月分区是黄金选择查询频率高而数据量大的业务可以按周或按小时但别轻易超过 200 个分区。5.3 老表改造的可行路径如果一张表已经上亿行但当初没建分区是否值得改造要回答这个问题需要看业务是否允许操作期间有锁表窗口。做ALTER TABLE ... PARTITION BY ...在大部分版本里会重建表执行期间全程锁写线上服务几乎要停摆。如果只是清理历史数据而不要求长期保留分区结构我反而更建议直接用第二、第三种方法。还有一种变通做法新建一张分区表作为归档表把旧表的旧数据按分区迁移到归档表原表保留近期数据。这样至少让后续的定期清理走进分区方案的快车道不用每次都在老表上做大 DELETE。5.4 分区表的常见误区分区不是越多越好这个我前面提了。还有一个常见误区是认为分区表查询一定会更快。实际上分区的主要价值是便于生命周期管理快速删除和归档而不是查询加速。如果查询没有带上分区键优化器仍然会扫描全部分区速度不会比普通表快甚至因为分区元数据管理反而略慢。另一个坑是跨分区更新。如果你按created_at分区但业务上允许修改created_at字段那么一次 UPDATE 可能触发数据从分区 A 移动到分区 B这个操作代价很高。如果有这种需求建议把分区键设计成业务中不会被修改的字段比如日志的写入时间戳。6. 生产环境踩坑实录与排查提速手册6.1 删除过程中的典型故障速查表症状可能原因排查思路紧急处置应用报Lock wait timeout exceeded删除批次过大、锁持有时间过长SHOW ENGINE INNODB STATUS看锁等待information_schema.innodb_trx查事务状态缩小批次开启 READ COMMITTED 会话必要时 KILL 删除会话从库Seconds_Behind_Master持续上涨binlog 回放赶不上主库删除速度观察 binlog 大小与从库 IO/SQL 线程状态增加 sleep 间隔暂停任务等从库追平磁盘被 binlog 占满ROW 格式下 DELETE 产生巨量 binlogSHOW MASTER STATUS看 binlog 文件增长停任务手动 purge 已同步 binlog考虑改 batch 大小删除执行很久没结束然后全部回滚事务过大触发锁等待或死锁最终回滚看错误日志中回滚耗时一定记得先备份回滚后再用小批次重试CPU/IO 飙高业务接口超时大批量删除产生大量脏页刷盘iostat看 IO 使用率SHOW ENGINE INNODB STATUS看 flush 状态降低批次、拉长 sleep避开高峰删除完表空间没有变小InnoDB 表空间是碎片化复用不是立即返回操作系统information_schema.tables的data_length看逻辑大小考虑 OPTIMIZE TABLE 或新表替换法这张表是我觉得全文最值得收藏的部分几乎每一条我都真实遇到过。尤其是“删除完表空间没有变小”这条很多新手以为删完几千万行磁盘肯定释放了几十 GB结果ls -lh一看文件大小没变顿时慌了。这是因为 InnoDB 的表空间文件不会自动收缩删除产生的空闲页会留给后续插入复用。如果非要立即收缩只能走 OPTIMIZE 或者新表替换法。6.2 一个历史订单删除案例的完整复盘我来讲一个印象很深的案例。那是一个模拟环境的电商订单表单表约 1.5 亿行保留最近一年数据需要删除约 1.3 亿行历史订单。第一轮尝试直接DELETE WHERE order_time 2022-06-01跑了 10 分钟后锁死了。业务侧反馈部分接口超时主从延迟也飙到几百秒。我立即 KILL 了删除会话但回滚花了差不多半小时那会儿业务问询电话一直没停。复盘后改用主键分片方案先查出要删除的数据中主键的最小值和最大值每批 800 行服务端 sleep 0.3 秒设置 session 隔离级别为 READ COMMITTED并把任务放在凌晨低峰执行。实际跑起来速度并不是一直恒定。刚开始每批 800 行耗时只有几十毫秒跑到中后段因为表膨胀、页分裂等因素单批耗时上升到几百毫秒。于是我加了监控脚本每 30 秒抓一次主从延迟一旦超过 10 秒就自动 sleep 2 秒。最终整个删除过程大概用了 4 小时删完 1.3 亿行主从延迟峰值控制在 15 秒以内没有影响白天业务。这个案例给我的最大教训是测试环境先演练不是可选项是必选项。当时如果先在测试环境摸清每批删除对主从和锁的影响就不会贸然上一条大 DELETE。现在我做任何批量删除都会先在测试环境模拟线上数据量用脚本打点计算每批耗时、IO 波动再算出适合的参数组合才会动线上。7. 最后分享几个我常用的操作习惯做批量删除这么多次有些习惯已经固化下来了写在这里供你参考。第一任何批量删除开始前先确定备份策略。至少要有最近一次全量备份并且确认 binlog 保留天数足够恢复。这个步骤不能省。第二删除期间别关监控。从库延迟、磁盘空间、CPU 负载、锁等待四个指标至少每 30 秒看一次。一旦发现趋势不对第一时间暂停而不是调参数硬扛。第三如果删除任务持续时间很长考虑把运行和管理分开负责删除的脚本只管删负责监控的脚本只管监控两者通过进度表同步信息。这样某个脚本出问题另一个还能给出有效报警。第四小批量多批次一定优于大批量少批次。刚开始跑的时候宁可慢也要先把稳定性验证出来。删到一半发现参数不行停下来改参数的成本远高于一开始多等几分钟。最后一个小技巧如果你要删除的数据占了表的一大半与其拼命调 DELETE 参数不如换个思路走新表替换法。我见过太多人花几个小时调分片大小最后发现只要“搬走保留数据”五分钟就完事了。方案选型永远比参数调优更值钱。