MySQL8.0定时删除数据实战：Event Scheduler与分批清理大表日志-平芜编程栈

先说个真实场景。我去年接手一套订单系统的时候，发现一张操作日志表在半年内从不到1GB涨到了近30GB。业务方最初说“日志不删也不影响主流程”，直到某天大促脚本在凌晨跑批卡了十几分钟，磁盘IO被日志表的数据文件打到接近100%，清理这件事才被摆上台面。MySQL8.0定时删除数据，听起来是个很基础的操作，但真到生产环境里，牵扯出来的是索引选择、事务拆解、主从延迟监控、周边工具选型一整串问题。这篇文章就把我从方案选型到落地、再到验证的完整路子捋一遍，适合正在被大表拖累、或者刚接触MySQL8.0定时清理任务的运维和开发朋友参考。

1. 表从几百MB膨胀到几十GB，问题远比你想象的严重

1.1 先看清一张日志表是怎么悄悄长残的

操作日志、访问记录、任务流水这类表，业务初期根本不起眼。一天几十万条写入，单条几KB，一天也就几百MB。到了半年后，上亿行数据堆在那里，表现为几个地方开始不对劲：带条件的分页查询变慢，因为优化器可能放弃精确索引去走范围扫描；备份时间翻倍，逻辑导出加物理备份的时间成本都在涨；InnoDB缓冲池里全是冷门历史数据，热数据的缓存命中率被稀释，所有查询都能感觉到一种“迟滞感”。

最麻烦的是磁盘空间。一张30GB的表，看起来只是占了30GB，但binlog、临时排序文件、复制的中继日志会围绕它的变更产生大量额外IO。所以大表清理不是一个“有空再说”的问题，而是越早设计删除策略，后续止损成本越低。

1.2 为什么“哪天想起来了删一次”是最差方案

有人会问：我每季度人工跑一次DELETE不行吗？我见过太多生产事故恰恰出在这种“人工定时”上。

第一个问题是不可靠。真正的线上环境里，DBA要处理的事情太多了，日志清理这种低优先级的任务很容易被无限期搁置。第二个问题是不可控。手工执行DELETE时，大概率随手写一条DELETE FROM op_log WHERE created_at < '2023-01-01'，没有LIMIT，没有分批，一条几十万行的大事务直接甩给数据库，行锁范围、undo log膨胀、主从延迟三座大山直接压下来。第三个问题是没有可观测性。删了多少行、跑了多久、有没有报错，全靠大脑记忆，事后根本追溯不了。

所以，把删除数据变成一个有固定调度周期、可监控、可手动触发的自动化任务，才是正解。

2. 定时删除的三个方案选型，我为什么推荐先用Event Scheduler

实现“定时删除”的路径不止一条。有人用Linux的crontab，有人用应用层的Quartz或xxl-job，有人用MySQL自带的Event Scheduler。坦白说，这三条路我都跑过，适用场景确实不一样。

对比维度	MySQL Event Scheduler	crontab + mysql命令	应用层定时任务
对数据库的依赖	完全内置，零外部依赖	需要额外脚本和客户端	需要应用服务常驻
时间基准	直接使用MySQL系统时区	受Shell环境和系统时区影响	看应用所在时区配置
失败重试与追溯	可通过事件状态和错误日志排查	需要自己写日志逻辑	依赖任务框架的补偿机制
适合场景	单实例、固定周期、逻辑简单	多实例批量执行统一清理脚本	删除前需要业务状态校验、依赖外部接口

我自己在大多数单实例场景下的选择是Event Scheduler优先。理由很朴素：数据库自己的活，最好让数据库自己干，少一层外部依赖，就少一个故障点。你不需要额外维护脚本分发通道，不需要考虑crontab环境变量里PATH少了mysql客户端，更不用为应用服务的定时任务空跑而操心。

当然，如果有几十套MySQL实例需要统一管理，那crontab或者Ansible批量下发脚本会更顺手；如果删除前要往消息队列发通知、要调业务接口校验状态，那就该用应用层任务。方案本身没有绝对优劣，只有匹配不匹配。

另外提一句，Percona Toolkit里的pt-archiver也是个非常成熟的归档工具，支持分批删除、限速、自动提交，在复杂归档场景里会好用很多。但它是外部工具，需要额外安装，而且学习、调参成本比Event Scheduler高。如果你的需求就是简单清理过期日志，Event Scheduler足够。

3. 事件调度器落地：从开启参数到分批删除存储过程

3.1 一个参数的坑：Event Scheduler默认是关闭的

MySQL 8.0里，Event Scheduler在运行时的默认状态是OFF。很多人建好了存储过程、建好了事件，满怀期待地等它晚上自动跑，结果第二天一看，一条数据没删，翻日志才发现事件压根没执行过。

开启方式分两步，缺一不可。

第一步是运行时开启：

SET GLOBAL event_scheduler = ON;

第二步是写入配置文件，让实例重启后依然生效。MySQL 8.0的配置文件一般是my.cnf或my.ini，在[mysqld]段落下加一行：

[mysqld] event_scheduler = ON

如果你是用Docker部署的MySQL8.0，记得把配置文件目录挂载出来再改。我见过不少Docker环境里的朋友，SET GLOBAL执行成功就以为万事大吉，结果容器一重建，参数全部还原。正确做法是在宿主机上准备一份自定义my.cnf，通过-v挂载到容器内/etc/mysql/conf.d/下，重启容器再验证一下参数。

验证参数是否生效，最直接的方式是：

SHOW VARIABLES LIKE 'event_scheduler';

看到ON，才算真正打开。

3.2 分批删除存储过程：怎么写得安全又可控

事件本身只是个调度外壳，真正干体力活的通常是存储过程。我强烈不建议在事件里写一条裸的DELETE语句，原因就是前文说的单条大事务问题。生产环境里，我会把删除逻辑封装成一个存储过程，用循环分批处理。

下面是一个我在线上用过的基础模板，按保留天数删数据，每批2000行，批间停顿1秒：

DELIMITER $$ CREATE PROCEDURE clean_op_log(IN p_keep_days INT, IN p_batch_size INT) BEGIN DECLARE v_deadline DATETIME; DECLARE v_affected INT DEFAULT 1; DECLARE v_loop_count INT DEFAULT 0; DECLARE v_max_loops INT DEFAULT 10000; -- 计算删除截止时间 SET v_deadline = DATE_SUB(NOW(), INTERVAL p_keep_days DAY); -- 循环删除，直到没有更多可删行，或达到最大循环次数 WHILE v_affected > 0 AND v_loop_count < v_max_loops DO DELETE FROM op_log WHERE created_at < v_deadline LIMIT p_batch_size; SET v_affected = ROW_COUNT(); SET v_loop_count = v_loop_count + 1; -- 给主从复制和IO一点喘息时间 IF v_affected > 0 THEN DO SLEEP(1); END IF; END WHILE; END$$ DELIMITER ;

这里有几个细节值得展开说。

为什么用LIMIT p_batch_size而不是按主键范围？因为created_at通常是业务查询条件，写入时的时间分布不一定是严格单调的，LIMIT分批最简单通用。但要注意，WHERE created_at < v_deadline这个条件上必须建有索引，否则每一轮DELETE都会触发一次全表扫描，这是这种方案最大的性能杀手。

为什么用ROW_COUNT()判断有没有删到数据？它是MySQL返回上一条语句影响行数的函数。如果上一批删了100行，返回100；没有数据可删了，返回0，循环自然退出。这里有个容易踩的细节：LIMIT 0或者无匹配行时ROW_COUNT()返回0，但在某些版本里，如果DELETE删了0行，它是返回0的，逻辑上没问题。

为什么加SLEEP(1)？核心是给InnoDB刷脏页、清理undo、主从复制追赶留出时间。如果你在一个批量循环里不停顿地连续DELETE，binlog产生的速度可能会让副本来不及应用，尤其是在大事务之后。1秒一个批次，2000行一批，实际体验是“润物细无声”，对业务基本无感。

为什么设v_max_loops上限？这是个很关键的保护机制。理论上每天产生10万条待删数据，每批2000行，50轮就结束了。但如果哪天应用出bug，一天写入了上亿条脏数据，这个while循环可能会跑几个小时，拖垮业务高峰。设一个最大循环次数，配合告警，能避免清理任务本身变成线上故障。

3.3 把存储过程挂到事件上：周期、起点、保留策略

存储过程写好后，创建事件就简单了。我一般这样建：

CREATE EVENT IF NOT EXISTS ev_clean_op_log ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY ON COMPLETION PRESERVE DO CALL clean_op_log(7, 2000);

解释一下关键参数。

EVERY 1 DAY表示执行频率，可以按需调整，比如日志量特别大就改成EVERY 12 HOUR甚至EVERY 1 HOUR，量很小的按月跑也行。

STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY表示从创建时间往后推一天开始执行。这样设计是为了避免事件创建后“立即执行一次”给你一个猝不及防的压力测试。我建议先用手动CALL clean_op_log(7, 2000);验证一遍存储过程，确认执行时间和性能都OK，再让它进入自动调度。

ON COMPLETION PRESERVE这个参数容易被忽略。默认情况下，事件执行完成后会被自动删除。如果你用的是一次性事件（EVERY之外的另一种调度方式），务必加上PRESERVE让它保留，否则第二天你会收到“事件不见了”的告警。而EVERY循环事件本身会保留，但养成加PRESERVE的习惯没坏处。

查看事件状态：

SHOW EVENTS\G;

表格里能看到status字段，ENABLED表示正常调度，SLAVESIDE_DISABLED表示在从库上被禁了（这是主从复制环境的正常表现），DISABLED则是手动关闭了。

如果某天你临时想停掉清理任务，比如大促期间怕它抢占资源，可以：

ALTER EVENT ev_clean_op_log DISABLE;

之后想恢复再ALTER EVENT ev_clean_op_log ENABLE;，不需要重建整个对象。

3.4 先确认删除条件列的索引

这是我踩得最狠的坑之一。第一次上线这个存储过程的时候，我以为WHERE created_at < v_deadline这种条件，优化器应该能自动处理好。结果事件跑起来后，数据库CPU直接飙到90%，一查慢查询日志，DELETE语句每次执行都要扫全表。

原因无他：op_log表上只有主键和user_id上的索引，created_at列裸奔了。InnoDB场景下，DELETE同样需要先定位到要删的行，没有可用索引，只能全表扫描，而且因为是写操作，扫描过程中锁的粒度会更大。

建索引的方法很简单：

CREATE INDEX idx_created_at ON op_log(created_at);

但如果表已经非常大，直接在原表上建索引也是个不小的工程。这时候就需要评估业务了——如果这张表已经全是历史数据，可以考虑先完成一轮数据清理，再在低峰期建索引；或者建一个组合索引(created_at, id)，为后续按主键排序定位删除提供更好的支点。

4. 删除慢的根因排查：索引、锁、binlog和分区表的取舍

4.1 删除的速度取决于WHERE条件的索引，而不是DELETE动词本身

很多人有个思维惯性：DELETE慢，是数据库“删”这个动作太重。其实不然。DELETE的执行路径和SELECT一脉相承——先做条件定位，再对定位到的行做删除标记。真正拖慢DELETE的，往往不是删除本身，而是定位过程太慢、锁的范围太大、以及事务产生的日志量爆炸。

定位过程太慢，就是上面说的缺索引问题。锁的范围太大，则和优化器的扫描路径有关。如果created_at没有索引，InnoDB只能从第一个数据页开始扫，还没确定要删哪些行，就已经把沿途的行都施加了X锁——所谓“锁了不该锁的行”，这个状态在并发高峰期很致命。

所以，在写任何删除策略之前，先跑一条EXPLAIN：

EXPLAIN DELETE FROM op_log WHERE created_at < '2024-06-01' LIMIT 2000;

看type那一列，如果出现ALL,说明全表扫描；如果是range或ref，说明索引生效了。这条命令就是删除性能的体检报告。

4.2 一个大事务删100万行 vs 一百个小事务删100万行

这是我在方案设计时经常被问到的问题。差异非常大。

单次删除行数	事务持续时间	锁持有范围	binlog涨幅	主从延迟风险
1000行	毫秒级	几十个索引页	很小	极低
10万行	秒级	大量数据页	大	高
100万行	分钟级	大段表空间	爆炸式增长	极高

我自己实测过一张千万级的表，单条DELETE删100万行，binlog在ROW模式下的增量轻易能到10GB以上。这个量级传到从库，基本可以确定复制延迟会瞬间飘红。

MySQL 8.0的binlog默认就是binlog_format = ROW，它记录的是每一行数据的变更前后镜像，删除1万行就记录2万条行事件。所以分批删除不仅是给数据库减负，更是给复制链路减负。把一个大事务拆成一百个小事务，每个小事务提交后，从库就可以应用一批，主从延迟自然被控制在极小范围内。

这里额外提醒一句：如果你是用的云数据库，甚至有按binlog大小计费或者限制的规格，单条大DELETE产生的binlog量是需要直接关注成本的。

4.3 终极手段：利用分区表把“删除”变成“丢弃”

分批DELETE解决了事务太大、锁太久的问题，但它本质上还是在“逐行标记删除”。对于数据量特别大、保留周期又很固定的场景，还有更漂亮的路子——按时间分区。

比如运营报表表按月分区：

CREATE TABLE report_log ( id BIGINT NOT NULL, biz_data TEXT, created_at DATETIME NOT NULL, PRIMARY KEY(id, created_at) ) PARTITION BY RANGE (TO_DAYS(created_at)) ( 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')) );

需要清理2024年1月的数据时，直接：

ALTER TABLE report_log DROP PARTITION p202401;

这个操作是DDL，不是逐行DELETE，速度极快，几乎瞬时完成，不会产生大量行级binlog，对主从的影响也远小于DELETE。效果上相当于“把整箱过期文件直接扔掉”，而不是“一份一份撕掉”。

但分区表不是银弹，有它的硬性成本。第一，建表时就要设计好分区策略，如果业务表已经跑了大半年再改成分区表，中间的重建过程是一次重量级操作。第二，分区数量需要持续维护，每月要记得ADD PARTITION，否则新数据会因为没有合适分区而报错。第三，分区键必须包含在主键里，MySQL对分区表的主键设计有硬性限制，比如上面例子主键是(id, created_at)。这些成本需要结合自己的运维能力来评估。

我的实际建议是：高频、海量、周期明确的日志流水表，建表时就设计成分区表；已经长残的存量表，优先用分批DELETE解决当下问题，不要轻易在生产环境做原地改分区这种大手术。

4.4 删完数据空间却没释放？这里有个常见认知误区

另一个做清理的朋友经常踩的坑：跑完DELETE，业务数据确实少了，但查看磁盘空间，发现文件大小几乎没有变化。

这是InnoDB的机制导致的。DELETE在默认情况下不会立即把物理文件空间还给操作系统，只是把对应的数据页标记为“可复用”。如果清理后业务继续以正常速率写入新数据，这些空间很快会被新记录复用。但如果清理完就直接不再写了，空间就会一直“悬空”维持原样。

如果你确实需要收缩表空间，比如日志表要归档退役，可以考虑：

OPTIMIZE TABLE op_log;

或者在MySQL 8.0中：

ALTER TABLE op_log ENGINE = InnoDB;

这两个操作本质都是重建表，会重新组织数据页、释放未使用的空间，代价是执行期间会有较大的IO消耗和锁效应。所以这类操作只建议在维护窗口或业务低峰期进行，而且绝对不能和定时删除任务同时执行，否则IO叠加可能直接打满磁盘。

5. 上线后如何验证“半夜删除”没有拖垮数据库

5.1 查看事件是否真的跑了

部署完成后，我们不能等到第二天早上才看结果，最好当天就手动触发一次验证：

CALL clean_op_log(7, 2000);

配合SELECT ROW_COUNT();可以确认刚删了多少行。手动验证时先用小批量，比如CALL clean_op_log(7, 500);，确认执行计划、耗时、对业务的影响都可控后再放大。

自动调度后，通过information_schema.events可以查看到事件的最近执行时间：

SELECT EVENT_NAME, STATUS, LAST_EXECUTED FROM information_schema.events WHERE EVENT_NAME = 'ev_clean_op_log';

LAST_EXECUTED能告诉你事件最近一次触发的时间点。同时检查实例日志和慢查询日志，确认没有因为删除导致的长时间锁等待或全表扫描。

5.2 监控三个关键指标：主从延迟、慢查询、binlog增长

定时删除是“夜间的脏活”，最怕的就是它在半夜把数据库拖垮，而你在早晨才发现。我的实践是盯三个核心指标。

第一个是主从延迟。如果配置了只读副本，重点观察Seconds_Behind_Master。在事件执行窗口前后多查几次：

SHOW REPLICA STATUS\G

这个字段如果长期大于0甚至持续增长，就要考虑把批大小缩小，比如从2000降到1000，或者把sleep间隔从1秒调到2秒。

第二个是慢查询日志。把long_query_time设置到一个合理阈值，比如2秒：

slow_query_log = ON slow_query_log_file = /var/log/mysql/slow.log long_query_time = 2

执行完删除任务后，翻一下慢查询日志，看有没有DELETE语句上榜。如果上榜，多半又是索引、锁等待或者批大小的问题。

第三个是binlog增量。在删除任务执行窗口对比binlog文件大小，或者用SHOW BINARY LOGS;查看当前binlog列表。如果一晚上新增了十几个binlog文件，说明删除量超出了预期，需要优化批大小和执行策略。

5.3 时区和执行窗口，两个容易被忽略的坑

最后说两个我在生产环境踩过的不是SQL层面的坑。

第一个是时区。Event Scheduler的调度时间基于MySQL系统时区，而不是应用所在时区。如果你用Docker部署的MySQL，容器时区默认可能是UTC，那么你设置的凌晨3点实际上是北京时间上午11点，刚好撞上业务高峰。解决办法是在容器启动时指定TZ=Asia/Shanghai，或者启动后在MySQL里统一设置time_zone = '+08:00'。删除数据这种任务，时间偏差不会致命，但会让人非常抓狂。

第二个是执行窗口的选择。我当时给线上库定的执行时间是凌晨2点半，而不是头一天的凌晨2点。为什么？因为这个业务有个每小时整点跑批的统计任务，凌晨2点整有一波集中IO。把删除任务往后挪半小时，避开跑批高峰，实测下来冲突少了很多。别只盯着“业务低谷”，还要看实例上有没有其他定时任务，把所有夜间的定时任务拉出来排个序，给删除任务留一个错峰窗口。

另外，如果你们有日常备份任务，也要把删除任务安排在备份任务之后。因为备份会读取大量数据文件，如果删除任务和备份任务同时抢占IO，两者都会受影响。