如果你维护的 MySQL 8.0 库里也有一张增长比野草还快的日志表你迟早要面对一个问题怎么在每天固定时间把过期数据清掉还不影响线上业务。这篇文章把我搭建这套清理机制的过程、参数选择和实际踩过的坑一起写出来。文章不绑定某个具体业务而是一套通用思路用 MySQL 事件调度器和存储过程实现分批删除再用监控和运维表兜底并且把“为什么要这么删”讲清楚。适合有一定经验、但不想重复踩坑的 DBA、后端开发和运维同学。1. 为什么定时清理数据能直接影响 MySQL 性能1.1 数据膨胀不只是占磁盘很多人的第一反应是“表太大磁盘要满了”但定时清理的意义远不止省几个 GB。一张表从几百万行涨到几千万行时影响是连环的索引体积变大Buffer Pool 缓存命中率下降查询计划可能要扫更多数据页备份时间拉长恢复时间也拉长出故障时拖不起。更隐蔽的是写入侧的连锁反应——InnoDB 的 B 树变大后索引页分裂和刷盘频率都会上升原本顺畅的 INSERT 和 UPDATE 也可能开始变慢。我们可以用收拾房间做类比屋子堆满杂物时你连日常走动都费劲但整理房间的意义不只是为了“能走路”而是为了让之后找东西、放东西都更高效。数据库里的过期数据就是屋里不再用的旧箱子定时清理就是定期把它们运出去。只要清理任务设计得好查询快、事务顺、备份稳这是一整套收益不是省一块磁盘那么简单。1.2 先想清楚删除还是归档动手删数据之前先回答一个问题这些数据是直接不要了还是暂时挪到别处直接 DELETE 不可逆所以我个人强烈建议在正式清理之前至少保留一段“可追溯期”。最常做的是先归档把过期数据 INSERT 到一张归档表或者导出到离线文件确认业务没有任何地方还在引用后再执行删除。对日志、操作记录这类低频访问数据归档表可以放在同一实例或独立的历史库查历史时去归档表主表只保留热数据。如果表的数据量能预判会持续增长而且清理规则就是按时间滚动那可以考虑分区表方案按月或按周做 RANGE 分区清理旧数据时直接用ALTER TABLE ... DROP PARTITION秒级完成不需要产生大量 DELETE 的 undo 日志和 binlog。但分区表也有约束创建表时就要规划好分区策略后期改分区成本高而且查询如果没带上分区键可能会扫描更多分区。本文后面主要讲 DELETE 路线因为大多数系统是表已经跑起来了建分区表不现实只能靠定时删除来兜底。2. 方案选型事件调度器与 crontab、业务定时任务怎么选2.1 三种方案的优缺点对比定时删除看着简单实现路径其实有好几条。先列一下最常见的三种方案优点缺点适用场景业务应用内定时任务逻辑灵活可以混入业务规则、调用其他服务依赖应用进程应用挂了就没法清理跨环境部署要改多处配置清理逻辑和业务强耦合需要动态判断操作系统 crontab 客户端不依赖应用简单直接任何 DBA 都能维护每台机器都要配 crontab要装 mysql 客户端迁移环境容易漏配单机或少量实例运维习惯偏传统MySQL 事件调度器数据库内置随库走不需要外部进程SQL 层面完成所有事复杂业务逻辑不好写清理逻辑只能停留在数据库内部清理就是一条或几条 SQL适合大多数日志类清理实际项目里我见过太多人第一反应去写应用定时任务但最终那种每天凌晨删点过期数据的场景用事件调度器往往是最省心的。它不用额外部署脚本不用管应用容器是否存活只要 MySQL 在跑调度器就会按计划触发。2.2 为什么我倾向于事件调度器事件调度器Event Scheduler从 MySQL 5.1 就有了到 8.0 已经很成熟。我最看重的是它“数据库对象化”这个特性事件定义在数据库里跟着库走。新环境一迁移只要导入这些对象清理机制就在了而 crontab 和业务定时任务都要单独部署少配一处就容易漏。用事件还有一个好处它可以直接调用存储过程把复杂的分批删除逻辑封装在一个可复用对象里想调试就手动 CALL想自动化就让事件定期 CALL。当然也要说清楚它的边界。如果清理逻辑需要从外部接口读取配置或者删除前要调用别的业务服务那就别硬搬到库里。库里只能做“基于数据库数据和配置表”的清理。除了这条限制绝大部分日志表、操作记录表、临时数据表的清理任务用事件调度器都绰绰有余。3. MySQL 8.0 事件调度器启用、授权与建事件的关键语法3.1 先确认调度器状态MySQL 8.0 里事件调度器默认是 OFF 的所以第一件事就是检查开关SHOW VARIABLES LIKE event_scheduler;如果结果是 OFF运行时开启SET GLOBAL event_scheduler ON;注意这个 SET 操作只对当前实例生效重启后又会回到配置默认值。要让重启后依然开启需要在配置文件 my.cnf 的[mysqld]段写[mysqld] event_scheduler ON这里补一个容易误解的细节event_scheduler 有 ON、OFF、DISABLED 三种值。DISABLED 是在启动阶段就禁用比如某些发行版编译时加了限制这种状态运行中无法切换只能在配置里改成 ON 后重启。日常遇到的基本只有 ON 和 OFF所以大多数情况下一条SET GLOBAL event_scheduler ON就够。3.2 权限准备我不建议用 root 账号去建事件更不建议用 root 去执行定时删除。生产环境的低危操作也要遵循最小权限原则。可以单独建一个清理账号CREATE USER cleanup_userlocalhost IDENTIFIED BY 这里写一个足够强的密码; GRANT EVENT, SELECT, DELETE ON mydb.* TO cleanup_userlocalhost; FLUSH PRIVILEGES;EVENT 权限负责创建和维护事件DELETE 和 SELECT 权限负责真正删数据。如果事件还需要调用存储过程而存储过程里又涉及 UPDATE 配置表还要把 UPDATE 权限加上。这里有个实际经验事件一旦创建执行时会以 DEFINER定义者身份运行所以账号做了权限调整之后记得重新测试一下整个清理流程别等事件半夜执行才发现没有权限。3.3 创建事件的基础语法CREATE EVENT 的完整语法结构是这样的CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [PRESERVE | NOT PRESERVE]] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 说明文字] DO sql_statement;schedule 部分有两种形式-- 指定某个时间点执行一次 AT timestamp [ INTERVAL interval] -- 每隔一段时间循环执行 EVERY interval [STARTS timestamp] [ENDS timestamp]interval 常见写法有1 DAY、2 HOUR、1 MONTH等。如果要做一个每天凌晨 3 点执行的清理事件基础写法如下CREATE EVENT IF NOT EXISTS ev_cleanup_log ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 03:00:00 ON COMPLETION PRESERVE ENABLE COMMENT 每天凌晨清理 90 天前的操作日志 DO DELETE FROM operation_log WHERE create_time DATE_SUB(NOW(), INTERVAL 90 DAY);几个参数解释一下ON COMPLETION PRESERVE表示事件执行完后保留定义不自动删除。如果是一次性的 AT 事件默认执行完就 DROP 了循环事件我建议都加上 PRESERVE万一想重新启用或查看配置也不会找不到对象。ENABLE表示创建后立刻可用如果还想先建好不跑可以改用DISABLE调试完成后再ALTER EVENT ... ENABLE。4. 实战用存储过程 事件实现日志表的每日批量清理4.1 先说需求背景用一个具体的例子来演示完整的搭建过程。假设某业务系统有张登录日志表login_log结构大概是CREATE TABLE login_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, login_time DATETIME NOT NULL, login_ip VARCHAR(64) DEFAULT NULL, login_status TINYINT DEFAULT 0, KEY idx_login_time (login_time) ) ENGINEInnoDB;这张表每天新增 80 万行业务要求保留 180 天数据每天凌晨 3 点把超过 180 天的记录清掉。这是一个很典型的“可预期、重复、可容错”的清理需求。4.2 建议加一张清理配置表我不太建议在事件里硬编码“保留 180 天”这个数字。业务策略会变而且删除任务的批量大小、每次上限也应该能被调节。项目里我习惯先建一张清理配置表CREATE TABLE system_cleanup_config ( table_name VARCHAR(64) PRIMARY KEY, cleanup_field VARCHAR(64) NOT NULL, retention_days INT NOT NULL, enabled TINYINT DEFAULT 1, batch_size INT DEFAULT 5000, max_cleanup_rows INT DEFAULT 200000, last_run_time DATETIME NULL, last_deleted_rows BIGINT NULL, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) COMMENT定时清理配置表;插入 login_log 的配置INSERT INTO system_cleanup_config (table_name, cleanup_field, retention_days, enabled, batch_size, max_cleanup_rows) VALUES (login_log, login_time, 180, 1, 5000, 200000);这表的含义很直观对哪张表、按哪个字段判断过期、保留多少天、单批删多少、单次任务最多删多少。以后要改成保留 90 天只需要 UPDATE 一行配置不用改事件和存储过程。配置表还能顺便记录最后一次执行时间和删除行数省了一张多余状态表的开销。4.3 存储过程实现批量删除为什么要用存储过程而不是直接在事件里写 DELETE第一事件里写多语句可读性差第二存储过程可以单独调试手动 CALL 一下就知道逻辑有没有问题第三批删需要循环和变量控制存储过程比事件体更适合。推荐写法如下DELIMITER $$ CREATE PROCEDURE proc_cleanup_login_log() BEGIN DECLARE v_retention_days INT DEFAULT NULL; DECLARE v_batch_size INT DEFAULT 5000; DECLARE v_total_limit INT DEFAULT 200000; DECLARE v_deleted INT DEFAULT -1; DECLARE v_total_deleted INT DEFAULT 0; -- 从配置表读取保留天数 SELECT retention_days INTO v_retention_days FROM system_cleanup_config WHERE table_name login_log AND enabled 1; IF v_retention_days IS NOT NULL THEN -- 循环批量删除直到删出的小于一批或达到总上限 WHILE v_deleted 0 AND v_total_deleted v_total_limit DO DELETE FROM login_log WHERE login_time DATE_SUB(NOW(), INTERVAL v_retention_days DAY) LIMIT v_batch_size; SET v_deleted ROW_COUNT(); SET v_total_deleted v_total_deleted v_deleted; -- 每批间隔 0.2 秒给其他事务喘息机会 DO SLEEP(0.2); END WHILE; -- 写回执行状态 UPDATE system_cleanup_config SET last_run_time NOW(), last_deleted_rows v_total_deleted WHERE table_name login_log; END IF; END$$ DELIMITER ;这段逻辑有几个关键点v_deleted初始值是 -1进入循环后执行 DELETEROW_COUNT()拿到本批实际删除行数。删到最后一轮如果不足 batch_sizev_deleted会小于 batch_size循环结束。但更准确的退出条件是v_deleted 0表示已经没有符合条件的记录了。max_cleanup_rows是本任务单次运行的总删除预算。为什么要限制这个数因为凌晨任务如果一次把几百万行全删完事务很大、锁持有时间长可能和备份或其他维护任务撞车。控制一个总预算让清理任务多次运行都行而不是一次打满。删除跟不上产生速度时再考虑调大预算或迁移到分区表方案。SLEEP(0.2)是我个人很喜欢的细节。批量删除如果没有一点停顿InnoDB 的刷盘压力会非常集中虽然每批只有 5000 行但连续删几十批也会把磁盘 IO 打得很剧烈。加一个极短的 sleep代价可忽略但对整体稳定的收益很明显。4.4 创建事件与生命周期管理有了存储过程事件就非常简单了CREATE EVENT ev_cleanup_login_log ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 03:00:00 ON COMPLETION PRESERVE ENABLE COMMENT 每天凌晨 3 点清理 login_log 过期数据 DO CALL proc_cleanup_login_log();事件日常维护的命令也列一下-- 查看所有事件 SHOW EVENTS; -- 临时停用 / 重新启用 ALTER EVENT ev_cleanup_login_log DISABLE; ALTER EVENT ev_cleanup_login_log ENABLE; -- 修改执行时间 ALTER EVENT ev_cleanup_login_log ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 03:30:00; -- 删除事件 DROP EVENT IF EXISTS ev_cleanup_login_log;关于主从复制我多说一句。事件本身在从库上通常不需要重复运行因为主库事件执行产生的 DELETE 语句会通过 binlog 同步到从库。如果因为架构原因从库也被手工建了同名事件就会导致同一批数据被两个实例各清一遍白白增加复制压力。稳妥做法是在事件定义上加DISABLE ON SLAVE明确告诉实例“这个事件只在主库跑作为从库时不执行”。复制环境里我给所有清理事件都会带上这个选项。5. 批量删除的四个性能陷阱与解决办法5.1 陷阱一一次性全删导致长事务和锁扩散最直观的错误写法是事件里直接写一句DELETE FROM login_log WHERE login_time DATE_SUB(NOW(), INTERVAL 180 DAY);看起来没问题但数据量大时这是灾难。DELETE 在 InnoDB 里不是物理删除它要把旧版本标记为删除同时生成 undo log事务提交前所有被删行都要持有锁。一次性删上百万行这个事务会非常大undo 日志可能撑爆 undo 表空间而且整个删除过程会持续占用大量行锁其他业务线程的 UPDATE、DELETE 都会被阻塞或等待。解决办法就是第 4 节里的分批删除思路把一个大事务拆成多个小事务每批只执行 DELETE ... LIMIT 5000提交后立刻释放锁。这样操作系统看到的是一串小峰值而不是一座大山。5.2 陷阱二删除字段没索引每批都在全表扫描批量 DELETE 的 WHERE 条件依赖字段如果该字段没有索引就会退化成全表扫描。比如删 5000 行可能扫描几千万行才能凑够 5000 条匹配记录成本极高。所以在建存储过程之前一定要确认清理条件字段有索引。用 EXPLAIN 检查一下非常直观EXPLAIN SELECT * FROM login_log WHERE login_time 2024-01-01 00:00:00 LIMIT 5000;如果看到 type 是 ALL就说明在走全表扫描需要加索引ALTER TABLE login_log ADD INDEX idx_login_time (login_time);注意不要为了提速而给清理字段和维护字段各建一套无关索引。清理条件用到的索引应该尽量和业务查询共用。比如业务经常按 login_time 查最近登录记录那么 (login_time, id) 或 (login_time) 就是合理的索引加完 DELETE 和 SELECT 都受益。5.3 陷阱三表上冗余索引太多删除连带更新成本高很多人容易忽略的是DELETE 不只是更新主键索引InnoDB 每删除一行数据还要同步删除所有二级索引中的对应记录。表上有几个二级索引删除成本就要翻几倍。我踩过这种坑有一张日志表为了某几个临时查询加了三个冗余索引清理 100 万行时实际代价比清理同样数据量但只有一个索引的表高出两倍多。如果这张表已经确定只做简单查询该砍的冗余索引就砍掉。尤其日志类表索引数量能控制在两三个以内是最好。索引不是装饰品每多一个写入和删除就多一份成本。5.4 陷阱四忽略了 binlog 和主从复制压力清理任务删出来的数据都会进 binlog尤其在 ROW 格式下几百万行的 DELETE 会生成大量 binlog 事件。这个 binlog 传到从库后从库也要回放删一遍于是主从延迟可能被放大。很多团队清理主库很爽第二天一看从库延迟好几分钟就是这个原因。缓解方法有三条第一把清理时间放在业务低峰期比如凌晨 3 点避开业务高峰和全量备份窗口第二坚持分批小事务让 binlog 大小更均衡不要一把一个大文件第三如果清理量确实很大考虑把表改成按时间分区用 DROP PARTITION 替代 DELETE减少 binlog 压力。还有一个很实用的经验清理任务跑完后顺手看一眼主从延迟监控连续几天记录下来如果每天都有延迟尖峰就需要重新调整 batch_size 和总预算。6. 事件监控、运行记录与日常维护检查表6.1 通过 information_schema 查事件状态事件建好不是就完事了运维期间要定期确认它“活着”。最直接的方式是查系统表SELECT EVENT_NAME, STATUS, LAST_EXECUTED, LAST_ALTERED, STARTS, ENDS, INTERVAL_VALUE, INTERVAL_FIELD FROM information_schema.EVENTS WHERE EVENT_SCHEMA mydb;重点看 LAST_EXECUTED如果它停留在几天前说明事件没触发或者触发失败。STATUS 如果不是 ENABLED也要检查原因是不是被手动 DISABLE 了或者 DDL 报错导致状态异常。我习惯把这条 SQL 放进每周巡检清单里和表空间、慢查询一起看。6.2 把执行记录落到审计表光靠 MySQL 系统表的 LAST_EXECUTED 只能知道“最后执行时间”满足不了追溯需求。我想知道的是某个事件某次运行删了多少行、跑了多久、有没有异常。所以一般还会加一张审计表CREATE TABLE cleanup_run_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, event_name VARCHAR(64) NOT NULL, table_name VARCHAR(64) NOT NULL, deleted_rows BIGINT DEFAULT 0, run_time DATETIME, duration_sec INT DEFAULT 0, status VARCHAR(20) DEFAULT OK, remark VARCHAR(255) DEFAULT NULL ) ENGINEInnoDB COMMENT清理任务执行记录;在存储过程里回写这条记录比如用TIMESTAMPDIFF(SECOND, start_time, NOW())计算耗时。这样一旦线上有疑似数据被误删的反馈我能直接查某天执行了什么清理、删了多少行心里有底。审计表本身也会变大但它一年也就 365 行到 730 行压力可以忽略。6.3 每周/每月复盘检查哪些指标定时清理机制最好配合固定复盘节奏。我建议至少每周看一次这几个指标做成一个简单的检查表检查项正常标准异常信号事件 LAST_EXECUTED在今天或昨天超过 2 天未更新配置表 last_deleted_rows数值接近每日预期新增量连续为 0可能清理没生效主从延迟清理后延迟不超过 10 秒延迟持续走高表数据量 / 磁盘占用增长稳定没有突然飙升表体积线性上涨且清理跟不上审计表状态字段全部为 OK出现 ERROR 或 WARNING如果发现“清理跟不上写入”先看 batch_size 和 max_cleanup_rows 是否偏小再看清理条件字段的索引是否被误删了最后评估是不是该换分区表方案。复盘的价值不是事后补救而是尽早发现清理机制的失效别等到磁盘打满才去救火。7. 常见故障清单事件不执行、卡住、主从延迟怎么查7.1 事件建好却不触发我碰到过好几次“事件建了但第二天发现没删数据”的情况。排查顺序基本是先查调度器开关SHOW GLOBAL VARIABLES LIKE event_scheduler;如果 OFF事件当然不会触发。再看事件状态SHOW EVENTS\GSTATUS 是 DISABLED 的话手动 ENABLE 即可。还要检查 STARTS 时间如果设置了STARTS 2025-12-31 03:00:00那自然是直到那天才第一次执行。另一个容易踩的是时区问题。事件调度的执行时间依赖 MySQL 的时区设置如果服务器时区是 UTC而业务时区是东八区凌晨 3 点的事件实际上会在北京时间上午 11 点跑。遇到这种问题先把SELECT NOW(), global.time_zone, session.time_zone打出来看清楚建议把服务器时区和数据库时区统一避免调度时间上的理解偏差。7.2 删除任务执行到一半卡住如果审计表里看到某次运行状态是 ERROR或者 event 的 LAST_EXECUTED 一直停留在过去多半是删除任务执行中卡住了。最典型的场景是锁等待。清理任务在删除某些行时业务侧正好在更新这些行互相等待锁直到innodb_lock_wait_timeout超时。排查语句SELECT * FROM information_schema.innodb_trx\G SELECT * FROM sys.innodb_lock_waits\G看有没有长时间未提交的事务在操作 login_log。如果有等它结束或者评估这个锁冲突是否能通过把清理时间挪到更深夜来避免。另一个常见原因是单批 batch_size 设得太大导致一批删除就要几秒到几十秒连续多批后磁盘 IO 饱和。解决办法就是把 batch_size 调小比如从 5000 降到 2000把 SLEEP(0.2) 稍微提高到 0.5 秒。7.3 主从延迟被清理任务放大事件跑完主库后从库同步 binlog 时出现明显延迟优先检查清理事件的批量策略。如果单次运行删了上百万行即使分批整体 binlog 量也很大从库回放需要时间。可以先做两件事一是缩小 max_cleanup_rows把单次任务的上限降下来让清理任务拆成几天慢慢清理二是确认从库没有重复运行同名事件我前面提到过从库重复跑清理会让延迟雪上加霜。如果延迟仍然压不住那就果断考虑分区表替代方案。DELETE 的复制压力是物理性的不管怎么调优它产生的 binlog 就是比 DROP PARTITION 大得多。按时间分区的表用ALTER TABLE ... DROP PARTITION p202401这条路在大数据量清理场景下几乎是唯一最终解。7.4 手动执行存储过程来复现问题很多时候直接从事件角度排查不出来我更推荐直接手动触发存储过程看现场CALL proc_cleanup_login_log();手动执行能立刻看到有没有报错、执行了多久、影响了多少行。执行完后再查配置表的 last_run_time 和 last_deleted_rows 是否更新。手动跑完之后再去检查 binlog 大小、慢查询日志、主从延迟整个过程是不是可控一目了然。8. 我的一些维护经验总结定时删除数据这事表面看就是一条 DELETE真正放到生产环境后考验的是你对事务、索引、锁、复制这几个基础概念的理解深度。我早期也犯过错直接用一条大 DELETE 在白天清一张千万级日志表结果把业务查询拖到明显变慢从那以后我把清理任务当成正式的线上变更来对待先建配置表再写带预算的批量存储过程最后建事件并加上监控和审计。“凌晨删除”这个操作并不是把数据清掉就完了清完之后还要能回答“删了多少、删得稳不稳、明天会不会影响业务”这三个问题。我最后补一个很多人会忽略的小技巧清理任务建好后先在测试库跑一遍完整流程特别是确认 DEFINER 权限够不够、时区对不对、索引有没有生效。这套机制一旦上线往往要无人工值守跑很久前期每一分钟的调试都是为了未来运维时少一次半夜惊醒。如果你也维护着日增几十万行甚至更多的表可以把这套“配置表 存储过程 事件 审计表”的思路拿过去结合自己的业务调整参数相信会比裸删稳得多。 
长河君 · 2026/10/19 14:23:12 · 阅读 1.2k MySQL 8.0定时清理日志表：事件调度器+存储过程分批删除实战
如果你维护的 MySQL 8.0 库里也有一张增长比野草还快的日志表你迟早要面对一个问题怎么在每天固定时间把过期数据清掉还不影响线上业务。这篇文章把我搭建这套清理机制的过程、参数选择和实际踩过的坑一起写出来。文章不绑定某个具体业务而是一套通用思路用 MySQL 事件调度器和存储过程实现分批删除再用监控和运维表兜底并且把“为什么要这么删”讲清楚。适合有一定经验、但不想重复踩坑的 DBA、后端开发和运维同学。1. 为什么定时清理数据能直接影响 MySQL 性能1.1 数据膨胀不只是占磁盘很多人的第一反应是“表太大磁盘要满了”但定时清理的意义远不止省几个 GB。一张表从几百万行涨到几千万行时影响是连环的索引体积变大Buffer Pool 缓存命中率下降查询计划可能要扫更多数据页备份时间拉长恢复时间也拉长出故障时拖不起。更隐蔽的是写入侧的连锁反应——InnoDB 的 B 树变大后索引页分裂和刷盘频率都会上升原本顺畅的 INSERT 和 UPDATE 也可能开始变慢。我们可以用收拾房间做类比屋子堆满杂物时你连日常走动都费劲但整理房间的意义不只是为了“能走路”而是为了让之后找东西、放东西都更高效。数据库里的过期数据就是屋里不再用的旧箱子定时清理就是定期把它们运出去。只要清理任务设计得好查询快、事务顺、备份稳这是一整套收益不是省一块磁盘那么简单。1.2 先想清楚删除还是归档动手删数据之前先回答一个问题这些数据是直接不要了还是暂时挪到别处直接 DELETE 不可逆所以我个人强烈建议在正式清理之前至少保留一段“可追溯期”。最常做的是先归档把过期数据 INSERT 到一张归档表或者导出到离线文件确认业务没有任何地方还在引用后再执行删除。对日志、操作记录这类低频访问数据归档表可以放在同一实例或独立的历史库查历史时去归档表主表只保留热数据。如果表的数据量能预判会持续增长而且清理规则就是按时间滚动那可以考虑分区表方案按月或按周做 RANGE 分区清理旧数据时直接用ALTER TABLE ... DROP PARTITION秒级完成不需要产生大量 DELETE 的 undo 日志和 binlog。但分区表也有约束创建表时就要规划好分区策略后期改分区成本高而且查询如果没带上分区键可能会扫描更多分区。本文后面主要讲 DELETE 路线因为大多数系统是表已经跑起来了建分区表不现实只能靠定时删除来兜底。2. 方案选型事件调度器与 crontab、业务定时任务怎么选2.1 三种方案的优缺点对比定时删除看着简单实现路径其实有好几条。先列一下最常见的三种方案优点缺点适用场景业务应用内定时任务逻辑灵活可以混入业务规则、调用其他服务依赖应用进程应用挂了就没法清理跨环境部署要改多处配置清理逻辑和业务强耦合需要动态判断操作系统 crontab 客户端不依赖应用简单直接任何 DBA 都能维护每台机器都要配 crontab要装 mysql 客户端迁移环境容易漏配单机或少量实例运维习惯偏传统MySQL 事件调度器数据库内置随库走不需要外部进程SQL 层面完成所有事复杂业务逻辑不好写清理逻辑只能停留在数据库内部清理就是一条或几条 SQL适合大多数日志类清理实际项目里我见过太多人第一反应去写应用定时任务但最终那种每天凌晨删点过期数据的场景用事件调度器往往是最省心的。它不用额外部署脚本不用管应用容器是否存活只要 MySQL 在跑调度器就会按计划触发。2.2 为什么我倾向于事件调度器事件调度器Event Scheduler从 MySQL 5.1 就有了到 8.0 已经很成熟。我最看重的是它“数据库对象化”这个特性事件定义在数据库里跟着库走。新环境一迁移只要导入这些对象清理机制就在了而 crontab 和业务定时任务都要单独部署少配一处就容易漏。用事件还有一个好处它可以直接调用存储过程把复杂的分批删除逻辑封装在一个可复用对象里想调试就手动 CALL想自动化就让事件定期 CALL。当然也要说清楚它的边界。如果清理逻辑需要从外部接口读取配置或者删除前要调用别的业务服务那就别硬搬到库里。库里只能做“基于数据库数据和配置表”的清理。除了这条限制绝大部分日志表、操作记录表、临时数据表的清理任务用事件调度器都绰绰有余。3. MySQL 8.0 事件调度器启用、授权与建事件的关键语法3.1 先确认调度器状态MySQL 8.0 里事件调度器默认是 OFF 的所以第一件事就是检查开关SHOW VARIABLES LIKE event_scheduler;如果结果是 OFF运行时开启SET GLOBAL event_scheduler ON;注意这个 SET 操作只对当前实例生效重启后又会回到配置默认值。要让重启后依然开启需要在配置文件 my.cnf 的[mysqld]段写[mysqld] event_scheduler ON这里补一个容易误解的细节event_scheduler 有 ON、OFF、DISABLED 三种值。DISABLED 是在启动阶段就禁用比如某些发行版编译时加了限制这种状态运行中无法切换只能在配置里改成 ON 后重启。日常遇到的基本只有 ON 和 OFF所以大多数情况下一条SET GLOBAL event_scheduler ON就够。3.2 权限准备我不建议用 root 账号去建事件更不建议用 root 去执行定时删除。生产环境的低危操作也要遵循最小权限原则。可以单独建一个清理账号CREATE USER cleanup_userlocalhost IDENTIFIED BY 这里写一个足够强的密码; GRANT EVENT, SELECT, DELETE ON mydb.* TO cleanup_userlocalhost; FLUSH PRIVILEGES;EVENT 权限负责创建和维护事件DELETE 和 SELECT 权限负责真正删数据。如果事件还需要调用存储过程而存储过程里又涉及 UPDATE 配置表还要把 UPDATE 权限加上。这里有个实际经验事件一旦创建执行时会以 DEFINER定义者身份运行所以账号做了权限调整之后记得重新测试一下整个清理流程别等事件半夜执行才发现没有权限。3.3 创建事件的基础语法CREATE EVENT 的完整语法结构是这样的CREATE EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [PRESERVE | NOT PRESERVE]] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 说明文字] DO sql_statement;schedule 部分有两种形式-- 指定某个时间点执行一次 AT timestamp [ INTERVAL interval] -- 每隔一段时间循环执行 EVERY interval [STARTS timestamp] [ENDS timestamp]interval 常见写法有1 DAY、2 HOUR、1 MONTH等。如果要做一个每天凌晨 3 点执行的清理事件基础写法如下CREATE EVENT IF NOT EXISTS ev_cleanup_log ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 03:00:00 ON COMPLETION PRESERVE ENABLE COMMENT 每天凌晨清理 90 天前的操作日志 DO DELETE FROM operation_log WHERE create_time DATE_SUB(NOW(), INTERVAL 90 DAY);几个参数解释一下ON COMPLETION PRESERVE表示事件执行完后保留定义不自动删除。如果是一次性的 AT 事件默认执行完就 DROP 了循环事件我建议都加上 PRESERVE万一想重新启用或查看配置也不会找不到对象。ENABLE表示创建后立刻可用如果还想先建好不跑可以改用DISABLE调试完成后再ALTER EVENT ... ENABLE。4. 实战用存储过程 事件实现日志表的每日批量清理4.1 先说需求背景用一个具体的例子来演示完整的搭建过程。假设某业务系统有张登录日志表login_log结构大概是CREATE TABLE login_log ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, login_time DATETIME NOT NULL, login_ip VARCHAR(64) DEFAULT NULL, login_status TINYINT DEFAULT 0, KEY idx_login_time (login_time) ) ENGINEInnoDB;这张表每天新增 80 万行业务要求保留 180 天数据每天凌晨 3 点把超过 180 天的记录清掉。这是一个很典型的“可预期、重复、可容错”的清理需求。4.2 建议加一张清理配置表我不太建议在事件里硬编码“保留 180 天”这个数字。业务策略会变而且删除任务的批量大小、每次上限也应该能被调节。项目里我习惯先建一张清理配置表CREATE TABLE system_cleanup_config ( table_name VARCHAR(64) PRIMARY KEY, cleanup_field VARCHAR(64) NOT NULL, retention_days INT NOT NULL, enabled TINYINT DEFAULT 1, batch_size INT DEFAULT 5000, max_cleanup_rows INT DEFAULT 200000, last_run_time DATETIME NULL, last_deleted_rows BIGINT NULL, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) COMMENT定时清理配置表;插入 login_log 的配置INSERT INTO system_cleanup_config (table_name, cleanup_field, retention_days, enabled, batch_size, max_cleanup_rows) VALUES (login_log, login_time, 180, 1, 5000, 200000);这表的含义很直观对哪张表、按哪个字段判断过期、保留多少天、单批删多少、单次任务最多删多少。以后要改成保留 90 天只需要 UPDATE 一行配置不用改事件和存储过程。配置表还能顺便记录最后一次执行时间和删除行数省了一张多余状态表的开销。4.3 存储过程实现批量删除为什么要用存储过程而不是直接在事件里写 DELETE第一事件里写多语句可读性差第二存储过程可以单独调试手动 CALL 一下就知道逻辑有没有问题第三批删需要循环和变量控制存储过程比事件体更适合。推荐写法如下DELIMITER $$ CREATE PROCEDURE proc_cleanup_login_log() BEGIN DECLARE v_retention_days INT DEFAULT NULL; DECLARE v_batch_size INT DEFAULT 5000; DECLARE v_total_limit INT DEFAULT 200000; DECLARE v_deleted INT DEFAULT -1; DECLARE v_total_deleted INT DEFAULT 0; -- 从配置表读取保留天数 SELECT retention_days INTO v_retention_days FROM system_cleanup_config WHERE table_name login_log AND enabled 1; IF v_retention_days IS NOT NULL THEN -- 循环批量删除直到删出的小于一批或达到总上限 WHILE v_deleted 0 AND v_total_deleted v_total_limit DO DELETE FROM login_log WHERE login_time DATE_SUB(NOW(), INTERVAL v_retention_days DAY) LIMIT v_batch_size; SET v_deleted ROW_COUNT(); SET v_total_deleted v_total_deleted v_deleted; -- 每批间隔 0.2 秒给其他事务喘息机会 DO SLEEP(0.2); END WHILE; -- 写回执行状态 UPDATE system_cleanup_config SET last_run_time NOW(), last_deleted_rows v_total_deleted WHERE table_name login_log; END IF; END$$ DELIMITER ;这段逻辑有几个关键点v_deleted初始值是 -1进入循环后执行 DELETEROW_COUNT()拿到本批实际删除行数。删到最后一轮如果不足 batch_sizev_deleted会小于 batch_size循环结束。但更准确的退出条件是v_deleted 0表示已经没有符合条件的记录了。max_cleanup_rows是本任务单次运行的总删除预算。为什么要限制这个数因为凌晨任务如果一次把几百万行全删完事务很大、锁持有时间长可能和备份或其他维护任务撞车。控制一个总预算让清理任务多次运行都行而不是一次打满。删除跟不上产生速度时再考虑调大预算或迁移到分区表方案。SLEEP(0.2)是我个人很喜欢的细节。批量删除如果没有一点停顿InnoDB 的刷盘压力会非常集中虽然每批只有 5000 行但连续删几十批也会把磁盘 IO 打得很剧烈。加一个极短的 sleep代价可忽略但对整体稳定的收益很明显。4.4 创建事件与生命周期管理有了存储过程事件就非常简单了CREATE EVENT ev_cleanup_login_log ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 03:00:00 ON COMPLETION PRESERVE ENABLE COMMENT 每天凌晨 3 点清理 login_log 过期数据 DO CALL proc_cleanup_login_log();事件日常维护的命令也列一下-- 查看所有事件 SHOW EVENTS; -- 临时停用 / 重新启用 ALTER EVENT ev_cleanup_login_log DISABLE; ALTER EVENT ev_cleanup_login_log ENABLE; -- 修改执行时间 ALTER EVENT ev_cleanup_login_log ON SCHEDULE EVERY 1 DAY STARTS 2025-01-01 03:30:00; -- 删除事件 DROP EVENT IF EXISTS ev_cleanup_login_log;关于主从复制我多说一句。事件本身在从库上通常不需要重复运行因为主库事件执行产生的 DELETE 语句会通过 binlog 同步到从库。如果因为架构原因从库也被手工建了同名事件就会导致同一批数据被两个实例各清一遍白白增加复制压力。稳妥做法是在事件定义上加DISABLE ON SLAVE明确告诉实例“这个事件只在主库跑作为从库时不执行”。复制环境里我给所有清理事件都会带上这个选项。5. 批量删除的四个性能陷阱与解决办法5.1 陷阱一一次性全删导致长事务和锁扩散最直观的错误写法是事件里直接写一句DELETE FROM login_log WHERE login_time DATE_SUB(NOW(), INTERVAL 180 DAY);看起来没问题但数据量大时这是灾难。DELETE 在 InnoDB 里不是物理删除它要把旧版本标记为删除同时生成 undo log事务提交前所有被删行都要持有锁。一次性删上百万行这个事务会非常大undo 日志可能撑爆 undo 表空间而且整个删除过程会持续占用大量行锁其他业务线程的 UPDATE、DELETE 都会被阻塞或等待。解决办法就是第 4 节里的分批删除思路把一个大事务拆成多个小事务每批只执行 DELETE ... LIMIT 5000提交后立刻释放锁。这样操作系统看到的是一串小峰值而不是一座大山。5.2 陷阱二删除字段没索引每批都在全表扫描批量 DELETE 的 WHERE 条件依赖字段如果该字段没有索引就会退化成全表扫描。比如删 5000 行可能扫描几千万行才能凑够 5000 条匹配记录成本极高。所以在建存储过程之前一定要确认清理条件字段有索引。用 EXPLAIN 检查一下非常直观EXPLAIN SELECT * FROM login_log WHERE login_time 2024-01-01 00:00:00 LIMIT 5000;如果看到 type 是 ALL就说明在走全表扫描需要加索引ALTER TABLE login_log ADD INDEX idx_login_time (login_time);注意不要为了提速而给清理字段和维护字段各建一套无关索引。清理条件用到的索引应该尽量和业务查询共用。比如业务经常按 login_time 查最近登录记录那么 (login_time, id) 或 (login_time) 就是合理的索引加完 DELETE 和 SELECT 都受益。5.3 陷阱三表上冗余索引太多删除连带更新成本高很多人容易忽略的是DELETE 不只是更新主键索引InnoDB 每删除一行数据还要同步删除所有二级索引中的对应记录。表上有几个二级索引删除成本就要翻几倍。我踩过这种坑有一张日志表为了某几个临时查询加了三个冗余索引清理 100 万行时实际代价比清理同样数据量但只有一个索引的表高出两倍多。如果这张表已经确定只做简单查询该砍的冗余索引就砍掉。尤其日志类表索引数量能控制在两三个以内是最好。索引不是装饰品每多一个写入和删除就多一份成本。5.4 陷阱四忽略了 binlog 和主从复制压力清理任务删出来的数据都会进 binlog尤其在 ROW 格式下几百万行的 DELETE 会生成大量 binlog 事件。这个 binlog 传到从库后从库也要回放删一遍于是主从延迟可能被放大。很多团队清理主库很爽第二天一看从库延迟好几分钟就是这个原因。缓解方法有三条第一把清理时间放在业务低峰期比如凌晨 3 点避开业务高峰和全量备份窗口第二坚持分批小事务让 binlog 大小更均衡不要一把一个大文件第三如果清理量确实很大考虑把表改成按时间分区用 DROP PARTITION 替代 DELETE减少 binlog 压力。还有一个很实用的经验清理任务跑完后顺手看一眼主从延迟监控连续几天记录下来如果每天都有延迟尖峰就需要重新调整 batch_size 和总预算。6. 事件监控、运行记录与日常维护检查表6.1 通过 information_schema 查事件状态事件建好不是就完事了运维期间要定期确认它“活着”。最直接的方式是查系统表SELECT EVENT_NAME, STATUS, LAST_EXECUTED, LAST_ALTERED, STARTS, ENDS, INTERVAL_VALUE, INTERVAL_FIELD FROM information_schema.EVENTS WHERE EVENT_SCHEMA mydb;重点看 LAST_EXECUTED如果它停留在几天前说明事件没触发或者触发失败。STATUS 如果不是 ENABLED也要检查原因是不是被手动 DISABLE 了或者 DDL 报错导致状态异常。我习惯把这条 SQL 放进每周巡检清单里和表空间、慢查询一起看。6.2 把执行记录落到审计表光靠 MySQL 系统表的 LAST_EXECUTED 只能知道“最后执行时间”满足不了追溯需求。我想知道的是某个事件某次运行删了多少行、跑了多久、有没有异常。所以一般还会加一张审计表CREATE TABLE cleanup_run_log ( id BIGINT AUTO_INCREMENT PRIMARY KEY, event_name VARCHAR(64) NOT NULL, table_name VARCHAR(64) NOT NULL, deleted_rows BIGINT DEFAULT 0, run_time DATETIME, duration_sec INT DEFAULT 0, status VARCHAR(20) DEFAULT OK, remark VARCHAR(255) DEFAULT NULL ) ENGINEInnoDB COMMENT清理任务执行记录;在存储过程里回写这条记录比如用TIMESTAMPDIFF(SECOND, start_time, NOW())计算耗时。这样一旦线上有疑似数据被误删的反馈我能直接查某天执行了什么清理、删了多少行心里有底。审计表本身也会变大但它一年也就 365 行到 730 行压力可以忽略。6.3 每周/每月复盘检查哪些指标定时清理机制最好配合固定复盘节奏。我建议至少每周看一次这几个指标做成一个简单的检查表检查项正常标准异常信号事件 LAST_EXECUTED在今天或昨天超过 2 天未更新配置表 last_deleted_rows数值接近每日预期新增量连续为 0可能清理没生效主从延迟清理后延迟不超过 10 秒延迟持续走高表数据量 / 磁盘占用增长稳定没有突然飙升表体积线性上涨且清理跟不上审计表状态字段全部为 OK出现 ERROR 或 WARNING如果发现“清理跟不上写入”先看 batch_size 和 max_cleanup_rows 是否偏小再看清理条件字段的索引是否被误删了最后评估是不是该换分区表方案。复盘的价值不是事后补救而是尽早发现清理机制的失效别等到磁盘打满才去救火。7. 常见故障清单事件不执行、卡住、主从延迟怎么查7.1 事件建好却不触发我碰到过好几次“事件建了但第二天发现没删数据”的情况。排查顺序基本是先查调度器开关SHOW GLOBAL VARIABLES LIKE event_scheduler;如果 OFF事件当然不会触发。再看事件状态SHOW EVENTS\GSTATUS 是 DISABLED 的话手动 ENABLE 即可。还要检查 STARTS 时间如果设置了STARTS 2025-12-31 03:00:00那自然是直到那天才第一次执行。另一个容易踩的是时区问题。事件调度的执行时间依赖 MySQL 的时区设置如果服务器时区是 UTC而业务时区是东八区凌晨 3 点的事件实际上会在北京时间上午 11 点跑。遇到这种问题先把SELECT NOW(), global.time_zone, session.time_zone打出来看清楚建议把服务器时区和数据库时区统一避免调度时间上的理解偏差。7.2 删除任务执行到一半卡住如果审计表里看到某次运行状态是 ERROR或者 event 的 LAST_EXECUTED 一直停留在过去多半是删除任务执行中卡住了。最典型的场景是锁等待。清理任务在删除某些行时业务侧正好在更新这些行互相等待锁直到innodb_lock_wait_timeout超时。排查语句SELECT * FROM information_schema.innodb_trx\G SELECT * FROM sys.innodb_lock_waits\G看有没有长时间未提交的事务在操作 login_log。如果有等它结束或者评估这个锁冲突是否能通过把清理时间挪到更深夜来避免。另一个常见原因是单批 batch_size 设得太大导致一批删除就要几秒到几十秒连续多批后磁盘 IO 饱和。解决办法就是把 batch_size 调小比如从 5000 降到 2000把 SLEEP(0.2) 稍微提高到 0.5 秒。7.3 主从延迟被清理任务放大事件跑完主库后从库同步 binlog 时出现明显延迟优先检查清理事件的批量策略。如果单次运行删了上百万行即使分批整体 binlog 量也很大从库回放需要时间。可以先做两件事一是缩小 max_cleanup_rows把单次任务的上限降下来让清理任务拆成几天慢慢清理二是确认从库没有重复运行同名事件我前面提到过从库重复跑清理会让延迟雪上加霜。如果延迟仍然压不住那就果断考虑分区表替代方案。DELETE 的复制压力是物理性的不管怎么调优它产生的 binlog 就是比 DROP PARTITION 大得多。按时间分区的表用ALTER TABLE ... DROP PARTITION p202401这条路在大数据量清理场景下几乎是唯一最终解。7.4 手动执行存储过程来复现问题很多时候直接从事件角度排查不出来我更推荐直接手动触发存储过程看现场CALL proc_cleanup_login_log();手动执行能立刻看到有没有报错、执行了多久、影响了多少行。执行完后再查配置表的 last_run_time 和 last_deleted_rows 是否更新。手动跑完之后再去检查 binlog 大小、慢查询日志、主从延迟整个过程是不是可控一目了然。8. 我的一些维护经验总结定时删除数据这事表面看就是一条 DELETE真正放到生产环境后考验的是你对事务、索引、锁、复制这几个基础概念的理解深度。我早期也犯过错直接用一条大 DELETE 在白天清一张千万级日志表结果把业务查询拖到明显变慢从那以后我把清理任务当成正式的线上变更来对待先建配置表再写带预算的批量存储过程最后建事件并加上监控和审计。“凌晨删除”这个操作并不是把数据清掉就完了清完之后还要能回答“删了多少、删得稳不稳、明天会不会影响业务”这三个问题。我最后补一个很多人会忽略的小技巧清理任务建好后先在测试库跑一遍完整流程特别是确认 DEFINER 权限够不够、时区对不对、索引有没有生效。这套机制一旦上线往往要无人工值守跑很久前期每一分钟的调试都是为了未来运维时少一次半夜惊醒。如果你也维护着日增几十万行甚至更多的表可以把这套“配置表 存储过程 事件 审计表”的思路拿过去结合自己的业务调整参数相信会比裸删稳得多。