你们有没有遇到过这种场景一张日志表每天几万行往上涨业务方说“超过 90 天的数据不要了”你顺手写了句DELETE FROM ops_log WHERE create_time NOW() - INTERVAL 90 DAY然后一跑几百万行一次删结果数据库连接池被打满线上报表查询全部卡死。这不是个例我见过至少三次因为“定时删除数据”写得过于粗暴导致的生产事故。MySQL 8.0 做历史数据清理难点从来不是“会不会写 DELETE”而是“删多少、怎么删、什么时候删”。单条大 DELETE 会把锁、undo log、binlog 全部顶到极限后面我会逐个拆开讲。这篇文章主要聊三件事分批删除的正确写法、用事件调度器把清理自动化、以及从分区表层面做更彻底的块级清理。适合正在维护线上 MySQL 8.0 的 DBA、后端研发也适合刚接触 MySQL 性能优化、想搞清楚“为什么 DELETE 越删越卡”的初学者。1. 单条大 DELETE 的连锁反应为什么不能在线上“一把梭”先把最反直觉的结论放在前面在 InnoDB 里删除 500 万行所花的时间往往不是“删除 500 万行”本身的时间而是“处理删除产生副作用”的时间。你看着一条 SQL 干掉了半张表实际上同一时间里MySQL 内部要处理锁冲突、回滚段膨胀、binlog 复制放大、后台 purge 线程积压这一整套链路才是真正的性能杀手。1.1 一次“清理”引发的锁等待假设ops_log表有 2000 万行你要删掉其中 500 万行旧数据。直接执行DELETE FROM ops_log WHERE create_time NOW() - INTERVAL 90 DAY;这条语句在 InnoDB 内部会逐行加排他锁然后在事务结束前一直持有。更麻烦的是如果create_time上有普通二级索引InnoDB 在隔离级别为 REPEATABLE READ 时还会加间隙锁。换句话说删除过程中凡是涉及这些索引区间的新插入、更新、删除全部排在锁后面等。我见过的一个真实场景就是这样凌晨 2 点跑清理任务删了大概 200 万行跑了 40 多分钟。业务方早上 8 点开始上班所有写操作全部堆积在锁等待里最后 DBA 手动 KILL 了会话回滚又花了十几分钟。那次事故之后我们立了个规矩超过 1 万行的删除一律不允许单条 SQL 直接执行。1.2 undo log、binlog 和命令执行时间一起膨胀一个 500 万行的大事务意味着整个删除过程中的旧镜像都要先写进 undo log。如果期间有查询需要读这些行的旧版本回滚段还得保留这部分数据不能提前清理。事务越大undo log 膨胀越严重purge 线程在后面追得越吃力。binlog 的问题更直接。MySQL 8.0 默认的 binlog_format 通常是 ROW 模式一条 DELETE 会为每一行生成一条删除前的镜像事件。500 万行就是 500 万个 binlog event主库写入 binlog 的同时从库也要一个 event 一个 event 地回放。如果你的从库硬件和主库差不多主库删 40 分钟从库可能要回放更久复制延迟就是这么被拉开的。1.3 “删除”之后空间并没有马上归还InnoDB 的 DELETE 是先把记录标记为删除再由后台 purge 线程完成物理清理。直接结果就是表文件在操作系统层面几乎不会变小你会看到information_schema.tables里的data_free越来越大。如果长期用大 DELETE 清理数据表内碎片会越积越多。等到某个时间点你会发现即使行数没怎么增长全表扫描一次却越来越慢。这也是为什么我会反复强调清理任务不仅要管“删”还要管“删完之后的空间整理”。2. 分批删除的写法与索引前提把 DELETE 切成上百个小事务把大事务拆成小事务是解决上述问题的核心思路。每个事务只删几百到几千行锁持有时间缩短undo 也只在当前批次内膨胀binlog 虽然总量没变但不会再出现一个超大 event 把主从链路瞬间拖垮。2.1 第一批要做的确认索引和主键顺序先看表结构通常这类清理表都有一个自增主键id一个时间字段create_time。最理想的情况是id和create_time大体正相关也就是“越早插入的行 id 越小”。绝大多数日志表、流水表都满足这个条件但不要想当然最好先验证一下SELECT COUNT(*) AS cnt, MIN(id) AS min_id, MAX(id) AS max_id, MIN(create_time) AS min_time, MAX(create_time) AS max_time FROM ops_log WHERE id 100000;如果min_time和max_time的范围和id顺序基本一致就可以放心用“按 id 加时间条件分批”的方案。如果时间乱序很严重比如业务端可以补录历史数据那创建时间索引(create_time, id)要额外留意避免每批查询走全表扫描。2.2 最简单可靠的分批删除存储过程下面这个存储过程是我线上常用的模板参数化处理适合大多数场景DELIMITER // CREATE PROCEDURE purge_ops_log( IN p_keep_days INT, IN p_batch_size INT, IN p_max_batches INT ) BEGIN DECLARE v_cutoff DATETIME; DECLARE v_affected_rows BIGINT DEFAULT 1; DECLARE v_loop_count INT DEFAULT 0; SET v_cutoff NOW() - INTERVAL p_keep_days DAY; WHILE v_affected_rows 0 AND v_loop_count p_max_batches DO START TRANSACTION; DELETE FROM ops_log WHERE create_time v_cutoff ORDER BY id LIMIT p_batch_size; SET v_affected_rows ROW_COUNT(); COMMIT; SET v_loop_count v_loop_count 1; -- 每 20 批让 InnoDB 喘口气顺便把后台 purge 的机会让出来 IF MOD(v_loop_count, 20) 0 THEN DO SLEEP(1); END IF; END WHILE; END // DELIMITER ;调用方式CALL purge_ops_log(90, 2000, 500);这里90表示保留 90 天2000表示每批删 2000 行500表示最多循环 500 批也就是单次最多删 100 万行。这个上限很重要防止出现某些异常情况时任务跑不停。几个关键点我单独说一下ROW_COUNT()必须在COMMIT之前取否则提交后结果会被重置。每批都是独立事务即使中间失败了最多只回滚当前批次。ORDER BY id配合自增主键可以让每批尽量按顺序扫描避免重复读取已删除区间的页。如果 p_max_batches 跑完还没删完下一次调用接着删就行因为条件create_time v_cutoff是幂等的。2.3 大批量场景下的进阶游标写法上面的过程简单但在“过期数据很多且和未过期数据穿插”的情况下每次从头按create_time过滤仍然会做不少无效扫描。如果你的表有上亿行想进一步控制每批扫过的范围可以用一个“记录上一批最大 id”的游标方式我把核心循环逻辑列一下SET v_last_id 0; WHILE v_affected_rows 0 AND v_loop_count p_max_batches DO START TRANSACTION; DELETE FROM ops_log WHERE create_time v_cutoff AND id v_last_id ORDER BY id LIMIT p_batch_size; SET v_affected_rows ROW_COUNT(); COMMIT; -- 取下一条待删除的最小 id推进游标 SELECT COALESCE(MIN(id), 0) INTO v_next_id FROM ops_log WHERE create_time v_cutoff AND id v_last_id; SET v_last_id v_next_id; SET v_loop_count v_loop_count 1; END WHILE;这种模式的核心是用id v_last_id把每批扫描范围限制在主键顺序的后半段而不是每次从头开始找符合条件的行。代价是多一次查询但在超大数据量下表现在的收益通常远大于多出来的查询开销。注意游标方式依赖“数据大致按时间顺序写入”。如果时间乱序严重条件create_time v_cutoff AND id v_last_id可能会漏删某些“旧时间但大 id”的行。真遇到乱序写入老老实实用第一版存储过程或者在create_time上建好索引。2.4 为什么每批不能太大也不能太小批次大小是一个需要权衡的参数。我用下来线下低峰期、白天业务影响可控的情况下每批 2000 到 5000 行比较合适业务高峰期或从库压力大的时候降到 500 到 1000 行。批次太小循环次数太多每条 DELETE 都有额外的事务提交开销反而浪费。批次太大又回到了大事务的老路。可以参考下面这张表选初始值场景建议批次大小每批执行时间参考普通日志表无复杂索引5000毫秒到秒级表上有四五个二级索引1000~2000秒级上下从库延迟敏感主库高峰期500~1000尽量控制在 1 秒内超大表单行很宽含 text 字段500视 IO 而定3. 用事件调度器把清理固化成每日任务存储过程写好了接下来要让它每天自动跑。MySQL 自带的事件调度器就是干这个的不需要额外装 crontab也不需要外部脚本直接挂在数据库内部配合 binlog 还能在从库上保持同样的调度逻辑。3.1 打开 event_schedulerMySQL 8.0 里事件调度器默认可能是关闭的。在命令行先确认SHOW VARIABLES LIKE event_scheduler;如果是 OFF可以临时开启SET GLOBAL event_scheduler ON;但临时开启在 MySQL 重启后会恢复成配置值所以要写进配置文件my.cnf的[mysqld]段event_schedulerON然后重启 MySQL 或等下次重启生效。如果不想重启临时开启也可以但记得在监控里确认配置是否持久化。提示不要把event_scheduler只设在会话里那样其它连接不受影响而且会话断开就失效了。3.2 创建每日清理事件假设想在每天凌晨 2 点调用上面的存储过程可以这样建CREATE EVENT IF NOT EXISTS ev_purge_ops_log ON SCHEDULE EVERY 1 DAY STARTS 2026-01-01 02:00:00 ON COMPLETION PRESERVE ENABLE DO CALL purge_ops_log(90, 5000, 100);简单解释一下EVERY 1 DAY表示每天执行一次。STARTS从指定时间开始如果没有这个参数事件创建后立即执行。ON COMPLETION PRESERVE表示事件执行完不自动删除保留定义方便下次继续调度。ENABLE表示创建后直接启用。如果你希望事件创建后立刻先跑一遍可以写成CREATE EVENT IF NOT EXISTS ev_purge_ops_log ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP DO CALL purge_ops_log(90, 5000, 100);3.3 查看事件状态和上次执行结果事件创建后可以通过下面几条命令检查SHOW EVENTS\GSELECT EVENT_NAME, STATUS, LAST_EXECUTED, LAST_ALTERED FROM information_schema.EVENTS WHERE EVENT_SCHEMA your_db;如果STATUS是SLAVESIDE_DISABLED通常是因为当前实例是从库事件调度器在从库上默认不执行这是正常的。如果LAST_EXECUTED一直不更新就要检查event_scheduler是否开启、存储过程是否报错。事件内部具体执行情况可以看 MySQL 错误日志或者在存储过程里加一张执行日志表每次调用记录时间、影响行数和耗时。这个日志表非常关键后面第 5 节我会细说。4. 分区块级清理时间维度上的另一种快路径如果你是维护日志表、流水表这类“天然带时间属性”的数据分批 DELETE 其实还是慢了。更彻底的做法是用 MySQL 8.0 的分区表把不同时间的数据放到不同分区里清理时直接对整个分区做操作秒级完成根本不走行级删除。4.1 为什么分区清理能秒级完成分区表在物理上会把数据按分区规则拆到不同文件段。按时间做 RANGE COLUMNS 分区后每个分区对应一个时间窗口。要清理超过保留期的数据只需要ALTER TABLE ops_log TRUNCATE PARTITION p202501;或者直接删掉整个分区ALTER TABLE ops_log DROP PARTITION p202501;TRUNCATE PARTITION是清空分区数据但保留分区定义适合你还想继续用这个时间段分区的情况DROP PARTITION是连分区带数据一起删之后还需要ADD PARTITION再补上新分区。无论哪种操作都是在元数据和文件层完成的实际代价远小于逐行 DELETE。对于日志清理这条路径才是真正的“性能优化”。4.2 按月份分区的建表参考下面是一个按create_time做月度分区的例子CREATE TABLE ops_log ( id BIGINT NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL, user_id BIGINT DEFAULT NULL, action_type VARCHAR(32) DEFAULT NULL, message VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id, create_time) ) PARTITION BY RANGE COLUMNS (create_time) ( PARTITION p202501 VALUES LESS THAN (2025-02-01), PARTITION p202502 VALUES LESS THAN (2025-03-01), PARTITION p202503 VALUES LESS THAN (2025-04-01), PARTITION p202504 VALUES LESS THAN (2025-05-01), PARTITION p202505 VALUES LESS THAN (2025-06-01), PARTITION p202506 VALUES LESS THAN (2025-07-01), PARTITION p202507 VALUES LESS THAN (2025-08-01), PARTITION p202508 VALUES LESS THAN (2025-09-01), PARTITION p202509 VALUES LESS THAN (2025-10-01), PARTITION p202510 VALUES LESS THAN (2025-11-01), PARTITION p202511 VALUES LESS THAN (2025-12-01), PARTITION p202512 VALUES LESS THAN (2026-01-01), PARTITION p202601 VALUES LESS THAN (2026-02-01), PARTITION p202602 VALUES LESS THAN (2026-03-01), PARTITION p202603 VALUES LESS THAN (2026-04-01), PARTITION p202604 VALUES LESS THAN (2026-05-01) );注意几个关键点主键必须包含分区键create_time否则建表会报错。VALUES LESS THAN是前闭后开区间2025-02-01之前的所有数据都进p202501。按月分区后查询如果带上create_time条件可以走分区裁剪只扫对应月份速度更快。4.3 月度分区表的日常维护思路用分区表之后“定时删除历史数据”就变成了“定时删过期分区 定时建新分区”。比如保留 90 天那么当前时间往前数 3 个月的分区都可以清掉。维护逻辑可以拆成两个事件每个月 1 号凌晨执行ALTER TABLE ops_log DROP PARTITION p_oldest;每个月月底执行ALTER TABLE ops_log ADD PARTITION (PARTITION p_next VALUES LESS THAN (...));新建分区可以用 SQL 拼接也可以用一个存储过程来生成下几个月的分区定义。分区数量不建议无限制增长日志表保留 6 个到 12 个月的分区就够了超过的部分直接删掉。分区方案唯一的痛点是如果业务偶发需要跨大量历史月份统计删除分区后就查不到了。所以上分区前一定要和业务确认清楚“数据真的不用了”否则宁可用分批 DELETE 把数据搬到归档表也别直接删。5. 上线前要检查的潜规则与避坑清单定时删除数据看着简单实际运行起来坑不少。我挑几个最常踩的写在这里尤其是想要长期稳定跑的任务上线前一定要过一遍。5.1 幂等设计与任务重叠事件调度器每天跑一次但如果某天任务积压没跑完第二天又触发了一次就可能出现两个清理会话同时删同一张表。虽然 DELETE 会加锁不至于删错数据但两个任务相互抢资源会把本来就不宽裕的 IO 打满。解决方法是在存储过程开头加一个检查用一个全局变量或者一张purge_running表来标记任务正在执行。比如CREATE TABLE purge_running ( job_name VARCHAR(64) PRIMARY KEY, start_time DATETIME, end_time DATETIME );存储过程开头尝试插入一条记录如果发现同一个 job 已经存在且end_time IS NULL就说明上一个任务还没结束直接退出。如果不想建表也可以借助 MySQL 的锁等待机制用GET_LOCK和RELEASE_LOCK实现互斥SELECT GET_LOCK(purge_ops_log_lock, 0);拿到锁再跑跑完RELEASE_LOCK。这个方案更轻量但要注意连接断开时锁会自动释放稍有不慎可能失去互斥效果生产上我更推荐记录表。5.2 不要忽略死锁和锁等待分批删除每批是一个事务多个事务之间如果同时删到相邻索引区间还是可能触发死锁。遇到死锁时MySQL 会自动回滚某个事务存储过程里的ROW_COUNT()可能返回异常值。我建议在存储过程里对死锁做重试处理循环里捕获异常重试当前批次。如果你用的是上面那个简单版本可以改成这样DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;更稳妥的做法是每次删除前先查一下锁等待SELECT * FROM performance_schema.data_lock_waits\G如果发现大量锁等待就停止当前批等下一轮再继续。5.3 执行日志表不能省无论用哪种清理方案我都强烈建议在存储过程里写执行日志。日志表结构不用复杂关键字段就几个CREATE TABLE purge_log ( id INT AUTO_INCREMENT PRIMARY KEY, job_name VARCHAR(64) NOT NULL, start_time DATETIME NOT NULL, end_time DATETIME DEFAULT NULL, deleted_rows BIGINT DEFAULT 0, batches INT DEFAULT 0, status VARCHAR(32) DEFAULT NULL, error_msg TEXT );每次调用存储过程时开头插入一条 start_time 记录全部跑完后更新 end_time、deleted_rows、status。这样第二天早上只需要查这张表就知道昨晚清理任务跑了没有、删了多少行、用了多长时间。有了这张表排查“为什么还有旧数据”就不再是盲猜了。先看有没有执行记录再看影响行数是否正常最后才去查事件状态和索引扫描情况。5.4 批量删除期间的从库延迟处理如果你的架构是一主多从分批删除虽然已经比单条大 DELETE 温和但仔细算一下5000 行一批删 100 万行就是 200 个事务每个事务都要通过 binlog 同步到从库。IO 压力还是有的。处理办法有几个把清理任务安排在业务最低峰从库负载最低的时候。控制批次大小和每批之间DO SLEEP(1)让从库有喘息时间。如果复制延迟超过阈值存储过程里主动停止下一批等延迟恢复再继续。有的团队会临时把某个从库摘掉清理完再挂回来这也是一种常见做法但操作复杂适合对可用性要求极高的场景。6. 我实际跑下来的一些体会这些方案我都实际落地过最后分享几个项目上的真实体会。第一清理任务一定要设上限不管是p_max_batches还是执行时长上限都必须有。没有上限的清理任务是生产环境的一颗定时炸弹谁知道哪天数据量异常增长凌晨的任务就会一直跑到天亮。第二一张表如果已经积累了上亿行不要指望第一批删除任务把所有历史数据清完。我的习惯是先跑一个“追赶模式”也就是临时把批次调大、循环上限调大先连续跑几天把积压数据追完然后再恢复为正常的每日小批次清理。这样才能长期保持干净。第三分批删除和分区表不是互斥的关系。像ops_log这种大表我通常还是建议直接上分区日常维护成本最低但如果业务上没办法动表结构那分批删除配合事件调度器就是最实际的办法。两者本质上都是“不要让 InnoDB 一次性处理过量的删除工作”这个原则的体现。最后一个私藏的小技巧如果你发现同一个清理任务越跑越慢不要急着调大批次先看表碎片和索引情况。DELETE 删除大量数据之后二级索引的叶子节点可能留下大量空洞批量扫描会明显变慢。此时在维护窗口执行一次OPTIMIZE TABLE或者ALTER TABLE ops_log ENGINE InnoDB往往比调批次参数有效得多。清理是一条长期路不是一次性的冲刺跑得稳比跑得快重要。
你们有没有遇到过这种场景一张日志表每天几万行往上涨业务方说“超过 90 天的数据不要了”你顺手写了句DELETE FROM ops_log WHERE create_time NOW() - INTERVAL 90 DAY然后一跑几百万行一次删结果数据库连接池被打满线上报表查询全部卡死。这不是个例我见过至少三次因为“定时删除数据”写得过于粗暴导致的生产事故。MySQL 8.0 做历史数据清理难点从来不是“会不会写 DELETE”而是“删多少、怎么删、什么时候删”。单条大 DELETE 会把锁、undo log、binlog 全部顶到极限后面我会逐个拆开讲。这篇文章主要聊三件事分批删除的正确写法、用事件调度器把清理自动化、以及从分区表层面做更彻底的块级清理。适合正在维护线上 MySQL 8.0 的 DBA、后端研发也适合刚接触 MySQL 性能优化、想搞清楚“为什么 DELETE 越删越卡”的初学者。1. 单条大 DELETE 的连锁反应为什么不能在线上“一把梭”先把最反直觉的结论放在前面在 InnoDB 里删除 500 万行所花的时间往往不是“删除 500 万行”本身的时间而是“处理删除产生副作用”的时间。你看着一条 SQL 干掉了半张表实际上同一时间里MySQL 内部要处理锁冲突、回滚段膨胀、binlog 复制放大、后台 purge 线程积压这一整套链路才是真正的性能杀手。1.1 一次“清理”引发的锁等待假设ops_log表有 2000 万行你要删掉其中 500 万行旧数据。直接执行DELETE FROM ops_log WHERE create_time NOW() - INTERVAL 90 DAY;这条语句在 InnoDB 内部会逐行加排他锁然后在事务结束前一直持有。更麻烦的是如果create_time上有普通二级索引InnoDB 在隔离级别为 REPEATABLE READ 时还会加间隙锁。换句话说删除过程中凡是涉及这些索引区间的新插入、更新、删除全部排在锁后面等。我见过的一个真实场景就是这样凌晨 2 点跑清理任务删了大概 200 万行跑了 40 多分钟。业务方早上 8 点开始上班所有写操作全部堆积在锁等待里最后 DBA 手动 KILL 了会话回滚又花了十几分钟。那次事故之后我们立了个规矩超过 1 万行的删除一律不允许单条 SQL 直接执行。1.2 undo log、binlog 和命令执行时间一起膨胀一个 500 万行的大事务意味着整个删除过程中的旧镜像都要先写进 undo log。如果期间有查询需要读这些行的旧版本回滚段还得保留这部分数据不能提前清理。事务越大undo log 膨胀越严重purge 线程在后面追得越吃力。binlog 的问题更直接。MySQL 8.0 默认的 binlog_format 通常是 ROW 模式一条 DELETE 会为每一行生成一条删除前的镜像事件。500 万行就是 500 万个 binlog event主库写入 binlog 的同时从库也要一个 event 一个 event 地回放。如果你的从库硬件和主库差不多主库删 40 分钟从库可能要回放更久复制延迟就是这么被拉开的。1.3 “删除”之后空间并没有马上归还InnoDB 的 DELETE 是先把记录标记为删除再由后台 purge 线程完成物理清理。直接结果就是表文件在操作系统层面几乎不会变小你会看到information_schema.tables里的data_free越来越大。如果长期用大 DELETE 清理数据表内碎片会越积越多。等到某个时间点你会发现即使行数没怎么增长全表扫描一次却越来越慢。这也是为什么我会反复强调清理任务不仅要管“删”还要管“删完之后的空间整理”。2. 分批删除的写法与索引前提把 DELETE 切成上百个小事务把大事务拆成小事务是解决上述问题的核心思路。每个事务只删几百到几千行锁持有时间缩短undo 也只在当前批次内膨胀binlog 虽然总量没变但不会再出现一个超大 event 把主从链路瞬间拖垮。2.1 第一批要做的确认索引和主键顺序先看表结构通常这类清理表都有一个自增主键id一个时间字段create_time。最理想的情况是id和create_time大体正相关也就是“越早插入的行 id 越小”。绝大多数日志表、流水表都满足这个条件但不要想当然最好先验证一下SELECT COUNT(*) AS cnt, MIN(id) AS min_id, MAX(id) AS max_id, MIN(create_time) AS min_time, MAX(create_time) AS max_time FROM ops_log WHERE id 100000;如果min_time和max_time的范围和id顺序基本一致就可以放心用“按 id 加时间条件分批”的方案。如果时间乱序很严重比如业务端可以补录历史数据那创建时间索引(create_time, id)要额外留意避免每批查询走全表扫描。2.2 最简单可靠的分批删除存储过程下面这个存储过程是我线上常用的模板参数化处理适合大多数场景DELIMITER // CREATE PROCEDURE purge_ops_log( IN p_keep_days INT, IN p_batch_size INT, IN p_max_batches INT ) BEGIN DECLARE v_cutoff DATETIME; DECLARE v_affected_rows BIGINT DEFAULT 1; DECLARE v_loop_count INT DEFAULT 0; SET v_cutoff NOW() - INTERVAL p_keep_days DAY; WHILE v_affected_rows 0 AND v_loop_count p_max_batches DO START TRANSACTION; DELETE FROM ops_log WHERE create_time v_cutoff ORDER BY id LIMIT p_batch_size; SET v_affected_rows ROW_COUNT(); COMMIT; SET v_loop_count v_loop_count 1; -- 每 20 批让 InnoDB 喘口气顺便把后台 purge 的机会让出来 IF MOD(v_loop_count, 20) 0 THEN DO SLEEP(1); END IF; END WHILE; END // DELIMITER ;调用方式CALL purge_ops_log(90, 2000, 500);这里90表示保留 90 天2000表示每批删 2000 行500表示最多循环 500 批也就是单次最多删 100 万行。这个上限很重要防止出现某些异常情况时任务跑不停。几个关键点我单独说一下ROW_COUNT()必须在COMMIT之前取否则提交后结果会被重置。每批都是独立事务即使中间失败了最多只回滚当前批次。ORDER BY id配合自增主键可以让每批尽量按顺序扫描避免重复读取已删除区间的页。如果 p_max_batches 跑完还没删完下一次调用接着删就行因为条件create_time v_cutoff是幂等的。2.3 大批量场景下的进阶游标写法上面的过程简单但在“过期数据很多且和未过期数据穿插”的情况下每次从头按create_time过滤仍然会做不少无效扫描。如果你的表有上亿行想进一步控制每批扫过的范围可以用一个“记录上一批最大 id”的游标方式我把核心循环逻辑列一下SET v_last_id 0; WHILE v_affected_rows 0 AND v_loop_count p_max_batches DO START TRANSACTION; DELETE FROM ops_log WHERE create_time v_cutoff AND id v_last_id ORDER BY id LIMIT p_batch_size; SET v_affected_rows ROW_COUNT(); COMMIT; -- 取下一条待删除的最小 id推进游标 SELECT COALESCE(MIN(id), 0) INTO v_next_id FROM ops_log WHERE create_time v_cutoff AND id v_last_id; SET v_last_id v_next_id; SET v_loop_count v_loop_count 1; END WHILE;这种模式的核心是用id v_last_id把每批扫描范围限制在主键顺序的后半段而不是每次从头开始找符合条件的行。代价是多一次查询但在超大数据量下表现在的收益通常远大于多出来的查询开销。注意游标方式依赖“数据大致按时间顺序写入”。如果时间乱序严重条件create_time v_cutoff AND id v_last_id可能会漏删某些“旧时间但大 id”的行。真遇到乱序写入老老实实用第一版存储过程或者在create_time上建好索引。2.4 为什么每批不能太大也不能太小批次大小是一个需要权衡的参数。我用下来线下低峰期、白天业务影响可控的情况下每批 2000 到 5000 行比较合适业务高峰期或从库压力大的时候降到 500 到 1000 行。批次太小循环次数太多每条 DELETE 都有额外的事务提交开销反而浪费。批次太大又回到了大事务的老路。可以参考下面这张表选初始值场景建议批次大小每批执行时间参考普通日志表无复杂索引5000毫秒到秒级表上有四五个二级索引1000~2000秒级上下从库延迟敏感主库高峰期500~1000尽量控制在 1 秒内超大表单行很宽含 text 字段500视 IO 而定3. 用事件调度器把清理固化成每日任务存储过程写好了接下来要让它每天自动跑。MySQL 自带的事件调度器就是干这个的不需要额外装 crontab也不需要外部脚本直接挂在数据库内部配合 binlog 还能在从库上保持同样的调度逻辑。3.1 打开 event_schedulerMySQL 8.0 里事件调度器默认可能是关闭的。在命令行先确认SHOW VARIABLES LIKE event_scheduler;如果是 OFF可以临时开启SET GLOBAL event_scheduler ON;但临时开启在 MySQL 重启后会恢复成配置值所以要写进配置文件my.cnf的[mysqld]段event_schedulerON然后重启 MySQL 或等下次重启生效。如果不想重启临时开启也可以但记得在监控里确认配置是否持久化。提示不要把event_scheduler只设在会话里那样其它连接不受影响而且会话断开就失效了。3.2 创建每日清理事件假设想在每天凌晨 2 点调用上面的存储过程可以这样建CREATE EVENT IF NOT EXISTS ev_purge_ops_log ON SCHEDULE EVERY 1 DAY STARTS 2026-01-01 02:00:00 ON COMPLETION PRESERVE ENABLE DO CALL purge_ops_log(90, 5000, 100);简单解释一下EVERY 1 DAY表示每天执行一次。STARTS从指定时间开始如果没有这个参数事件创建后立即执行。ON COMPLETION PRESERVE表示事件执行完不自动删除保留定义方便下次继续调度。ENABLE表示创建后直接启用。如果你希望事件创建后立刻先跑一遍可以写成CREATE EVENT IF NOT EXISTS ev_purge_ops_log ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP DO CALL purge_ops_log(90, 5000, 100);3.3 查看事件状态和上次执行结果事件创建后可以通过下面几条命令检查SHOW EVENTS\GSELECT EVENT_NAME, STATUS, LAST_EXECUTED, LAST_ALTERED FROM information_schema.EVENTS WHERE EVENT_SCHEMA your_db;如果STATUS是SLAVESIDE_DISABLED通常是因为当前实例是从库事件调度器在从库上默认不执行这是正常的。如果LAST_EXECUTED一直不更新就要检查event_scheduler是否开启、存储过程是否报错。事件内部具体执行情况可以看 MySQL 错误日志或者在存储过程里加一张执行日志表每次调用记录时间、影响行数和耗时。这个日志表非常关键后面第 5 节我会细说。4. 分区块级清理时间维度上的另一种快路径如果你是维护日志表、流水表这类“天然带时间属性”的数据分批 DELETE 其实还是慢了。更彻底的做法是用 MySQL 8.0 的分区表把不同时间的数据放到不同分区里清理时直接对整个分区做操作秒级完成根本不走行级删除。4.1 为什么分区清理能秒级完成分区表在物理上会把数据按分区规则拆到不同文件段。按时间做 RANGE COLUMNS 分区后每个分区对应一个时间窗口。要清理超过保留期的数据只需要ALTER TABLE ops_log TRUNCATE PARTITION p202501;或者直接删掉整个分区ALTER TABLE ops_log DROP PARTITION p202501;TRUNCATE PARTITION是清空分区数据但保留分区定义适合你还想继续用这个时间段分区的情况DROP PARTITION是连分区带数据一起删之后还需要ADD PARTITION再补上新分区。无论哪种操作都是在元数据和文件层完成的实际代价远小于逐行 DELETE。对于日志清理这条路径才是真正的“性能优化”。4.2 按月份分区的建表参考下面是一个按create_time做月度分区的例子CREATE TABLE ops_log ( id BIGINT NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL, user_id BIGINT DEFAULT NULL, action_type VARCHAR(32) DEFAULT NULL, message VARCHAR(255) DEFAULT NULL, PRIMARY KEY (id, create_time) ) PARTITION BY RANGE COLUMNS (create_time) ( PARTITION p202501 VALUES LESS THAN (2025-02-01), PARTITION p202502 VALUES LESS THAN (2025-03-01), PARTITION p202503 VALUES LESS THAN (2025-04-01), PARTITION p202504 VALUES LESS THAN (2025-05-01), PARTITION p202505 VALUES LESS THAN (2025-06-01), PARTITION p202506 VALUES LESS THAN (2025-07-01), PARTITION p202507 VALUES LESS THAN (2025-08-01), PARTITION p202508 VALUES LESS THAN (2025-09-01), PARTITION p202509 VALUES LESS THAN (2025-10-01), PARTITION p202510 VALUES LESS THAN (2025-11-01), PARTITION p202511 VALUES LESS THAN (2025-12-01), PARTITION p202512 VALUES LESS THAN (2026-01-01), PARTITION p202601 VALUES LESS THAN (2026-02-01), PARTITION p202602 VALUES LESS THAN (2026-03-01), PARTITION p202603 VALUES LESS THAN (2026-04-01), PARTITION p202604 VALUES LESS THAN (2026-05-01) );注意几个关键点主键必须包含分区键create_time否则建表会报错。VALUES LESS THAN是前闭后开区间2025-02-01之前的所有数据都进p202501。按月分区后查询如果带上create_time条件可以走分区裁剪只扫对应月份速度更快。4.3 月度分区表的日常维护思路用分区表之后“定时删除历史数据”就变成了“定时删过期分区 定时建新分区”。比如保留 90 天那么当前时间往前数 3 个月的分区都可以清掉。维护逻辑可以拆成两个事件每个月 1 号凌晨执行ALTER TABLE ops_log DROP PARTITION p_oldest;每个月月底执行ALTER TABLE ops_log ADD PARTITION (PARTITION p_next VALUES LESS THAN (...));新建分区可以用 SQL 拼接也可以用一个存储过程来生成下几个月的分区定义。分区数量不建议无限制增长日志表保留 6 个到 12 个月的分区就够了超过的部分直接删掉。分区方案唯一的痛点是如果业务偶发需要跨大量历史月份统计删除分区后就查不到了。所以上分区前一定要和业务确认清楚“数据真的不用了”否则宁可用分批 DELETE 把数据搬到归档表也别直接删。5. 上线前要检查的潜规则与避坑清单定时删除数据看着简单实际运行起来坑不少。我挑几个最常踩的写在这里尤其是想要长期稳定跑的任务上线前一定要过一遍。5.1 幂等设计与任务重叠事件调度器每天跑一次但如果某天任务积压没跑完第二天又触发了一次就可能出现两个清理会话同时删同一张表。虽然 DELETE 会加锁不至于删错数据但两个任务相互抢资源会把本来就不宽裕的 IO 打满。解决方法是在存储过程开头加一个检查用一个全局变量或者一张purge_running表来标记任务正在执行。比如CREATE TABLE purge_running ( job_name VARCHAR(64) PRIMARY KEY, start_time DATETIME, end_time DATETIME );存储过程开头尝试插入一条记录如果发现同一个 job 已经存在且end_time IS NULL就说明上一个任务还没结束直接退出。如果不想建表也可以借助 MySQL 的锁等待机制用GET_LOCK和RELEASE_LOCK实现互斥SELECT GET_LOCK(purge_ops_log_lock, 0);拿到锁再跑跑完RELEASE_LOCK。这个方案更轻量但要注意连接断开时锁会自动释放稍有不慎可能失去互斥效果生产上我更推荐记录表。5.2 不要忽略死锁和锁等待分批删除每批是一个事务多个事务之间如果同时删到相邻索引区间还是可能触发死锁。遇到死锁时MySQL 会自动回滚某个事务存储过程里的ROW_COUNT()可能返回异常值。我建议在存储过程里对死锁做重试处理循环里捕获异常重试当前批次。如果你用的是上面那个简单版本可以改成这样DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;更稳妥的做法是每次删除前先查一下锁等待SELECT * FROM performance_schema.data_lock_waits\G如果发现大量锁等待就停止当前批等下一轮再继续。5.3 执行日志表不能省无论用哪种清理方案我都强烈建议在存储过程里写执行日志。日志表结构不用复杂关键字段就几个CREATE TABLE purge_log ( id INT AUTO_INCREMENT PRIMARY KEY, job_name VARCHAR(64) NOT NULL, start_time DATETIME NOT NULL, end_time DATETIME DEFAULT NULL, deleted_rows BIGINT DEFAULT 0, batches INT DEFAULT 0, status VARCHAR(32) DEFAULT NULL, error_msg TEXT );每次调用存储过程时开头插入一条 start_time 记录全部跑完后更新 end_time、deleted_rows、status。这样第二天早上只需要查这张表就知道昨晚清理任务跑了没有、删了多少行、用了多长时间。有了这张表排查“为什么还有旧数据”就不再是盲猜了。先看有没有执行记录再看影响行数是否正常最后才去查事件状态和索引扫描情况。5.4 批量删除期间的从库延迟处理如果你的架构是一主多从分批删除虽然已经比单条大 DELETE 温和但仔细算一下5000 行一批删 100 万行就是 200 个事务每个事务都要通过 binlog 同步到从库。IO 压力还是有的。处理办法有几个把清理任务安排在业务最低峰从库负载最低的时候。控制批次大小和每批之间DO SLEEP(1)让从库有喘息时间。如果复制延迟超过阈值存储过程里主动停止下一批等延迟恢复再继续。有的团队会临时把某个从库摘掉清理完再挂回来这也是一种常见做法但操作复杂适合对可用性要求极高的场景。6. 我实际跑下来的一些体会这些方案我都实际落地过最后分享几个项目上的真实体会。第一清理任务一定要设上限不管是p_max_batches还是执行时长上限都必须有。没有上限的清理任务是生产环境的一颗定时炸弹谁知道哪天数据量异常增长凌晨的任务就会一直跑到天亮。第二一张表如果已经积累了上亿行不要指望第一批删除任务把所有历史数据清完。我的习惯是先跑一个“追赶模式”也就是临时把批次调大、循环上限调大先连续跑几天把积压数据追完然后再恢复为正常的每日小批次清理。这样才能长期保持干净。第三分批删除和分区表不是互斥的关系。像ops_log这种大表我通常还是建议直接上分区日常维护成本最低但如果业务上没办法动表结构那分批删除配合事件调度器就是最实际的办法。两者本质上都是“不要让 InnoDB 一次性处理过量的删除工作”这个原则的体现。最后一个私藏的小技巧如果你发现同一个清理任务越跑越慢不要急着调大批次先看表碎片和索引情况。DELETE 删除大量数据之后二级索引的叶子节点可能留下大量空洞批量扫描会明显变慢。此时在维护窗口执行一次OPTIMIZE TABLE或者ALTER TABLE ops_log ENGINE InnoDB往往比调批次参数有效得多。清理是一条长期路不是一次性的冲刺跑得稳比跑得快重要。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
