先说个真实场景。我去年接手一套订单系统的时候发现一张操作日志表在半年内从不到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 Schedulercrontab 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、主从复制追赶留出时间。如果你在一个批量循环里不停顿地连续DELETEbinlog产生的速度可能会让副本来不及应用尤其是在大事务之后。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锁——所谓“锁了不该锁的行”这个状态在并发高峰期很致命。所以在写任何删除策略之前先跑一条EXPLAINEXPLAIN 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点刚好撞上业务高峰。解决办法是在容器启动时指定TZAsia/Shanghai或者启动后在MySQL里统一设置time_zone 08:00。删除数据这种任务时间偏差不会致命但会让人非常抓狂。第二个是执行窗口的选择。我当时给线上库定的执行时间是凌晨2点半而不是头一天的凌晨2点。为什么因为这个业务有个每小时整点跑批的统计任务凌晨2点整有一波集中IO。把删除任务往后挪半小时避开跑批高峰实测下来冲突少了很多。别只盯着“业务低谷”还要看实例上有没有其他定时任务把所有夜间的定时任务拉出来排个序给删除任务留一个错峰窗口。另外如果你们有日常备份任务也要把删除任务安排在备份任务之后。因为备份会读取大量数据文件如果删除任务和备份任务同时抢占IO两者都会受影响。写在最后这套“事件调度器分批删除存储过程索引检查执行监控”的组合是我在MySQL8.0上做过多次迭代后沉淀下来的相对稳妥的一套模式。它不一定是最优解但对单实例、固定保留周期、数据量千万到亿级的中等规模业务表来说足够可靠、也足够简单。我再分享一个小技巧算是个人习惯。给事件创建语句写进数据库的初始化脚本里连同建表语句一起做版本管理。这样新环境部署的时候不需要手工去敲事件自动化和可追溯性都会好很多。哪怕团队里换了人也能一眼看出这个清理任务是干什么的、多久跑一次、保留多少天。如果你正在为一张持续膨胀的业务表发愁不妨从今天开始先给表建好created_at索引再开一个Event Scheduler把第一个版本跑起来。