很多做开发和运维的朋友都遇到过这类需求业务跑着跑着某张表的体积肉眼可见地往上涨查了一下发现全是日志数据、临时缓存或者历史流水留着碍事删了又怕以后要查。尤其是 MySQL 5.7 环境里表动不动几个 G每次手动清理不仅累还得记着定期去执行一不小心就忘了。这篇文章就围绕“MySQL 5.7 定时任务清理表记录”这个具体场景把事件调度器的配置、清理语句的设计、常见坑的排查一次讲清楚给正在被大表和过期数据困扰的朋友一套可以直接抄作业的方案。先说明一下这套方法适合谁手上管着 MySQL 5.7 实例、需要定期清理日志表或流水表、又不想引入额外中间件的同学。你不需要会写复杂的存储过程只需要能看懂 SQL、能登录数据库执行命令就足够了。我尽量把每一步背后的原因也讲明白这样你遇到类似场景能自己变通而不是只会复制粘贴。1. 先想清楚为什么需要定时清理方案怎么选1.1 这个需求的典型场景先说几个我经常遇到的真实场景大家可以对号入座。第一类是日志表滥用。很多应用会把操作日志、访问日志、接口调用记录全部怼进一张表一天几百万条很常见特别是商城、门户这类访问量大的系统一张日志表一个月就能涨到上亿行。第二类是临时数据表比如定时任务产生的中间结果、导入导出的临时表、接口生成的缓存数据任务跑完其实就没用了但没人管它就一直留着。第三类是业务流水表比如支付流水、短信发送记录、登录凭证业务上通常只需要保留最近三个月或半年更早的数据既占空间又拖慢查询。这种表一旦膨胀到一定程度问题就来了。最直接的是磁盘空间告急其次是查询变慢因为 DELETE 老数据之前要先在条件字段上扫描定位没有索引的话就是全表扫。更麻烦的是主表膨胀之后后续的备份、主从同步都要跟着遭殃一个几百万行的表做逻辑备份的时间够喝好几杯茶了。所以“定期清理”这件事不是可做可不做而是必须纳入日常维护的一部分。关键在于怎么做得安全、稳定、不干扰线上业务。1.2 方案对比事件调度器、系统定时任务、应用层定时器聊到定时清理很多人的第一反应是用 Linux 的 crontab 写脚本每天凌晨跑一条 SQL。这个思路没问题但有几个绕不开的麻烦脚本放哪台机器、密钥怎么管理、脚本挂了谁来通知、数据库连接串泄露的风险怎么控制。而且你管的是一个 MySQL 实例不是一台机器如果将来实例迁移了crontab 脚本很容易被落在老机器上。另一个常见做法是在应用层加一个定时调度比如用代码里的定时任务框架每天凌晨调用一个删数据的接口。这个方案的问题是如果应用没部署、服务没起来那清理任务就静默失败了而且清理逻辑和业务代码耦合在一起后期想改保留周期还得发一次版本。相比之下MySQL 自带的事件调度器Event Scheduler就是为这种场景设计的。它就是一个跑在数据库内部的定时闹钟由 MySQL 自己管理不需要额外的脚本、不需要外部依赖数据库启动它就跟着启动默认就会按照你设定的频率去执行 SQL。我个人的习惯是凡是数据库内部能搞定的事情就不去外面绕一圈。下面这张表把三种方案的区别说得很清楚。方案依赖外部脚本数据库迁移影响失败可观测性适用场景事件调度器不依赖事件跟随库走无需额外迁移information_schema 可直接查看单实例或主从清理周期固定系统 crontab依赖服务器脚本容易遗留需单独迁移看脚本日志和任务日志需要跨库、跨机调用外部接口时应用层定时器依赖应用存活和应用部署绑定看应用日志清理逻辑与业务强关联需要应用内处理1.3 为什么 MySQL 5.7 的场景特别适合用事件调度器MySQL 5.7 虽然不像 8.0 那样有不少新特性但事件调度器这个功能其实在 5.1 版本就引入了到 5.7 已经非常成熟稳定。5.7 的事件调度器支持比较丰富的调度规则可以按秒、分钟、小时、天、月来设定执行频率也支持指定开始时间和结束时间基本覆盖了日常清理的所有需求。还有一点很关键5.7 里的information_schema.EVENTS表结构已经比较完整可以看到事件定义、状态、上次执行时间、下次执行时间等元数据排查问题比对黑盒强多了。而且我这里说的是“MySQL 5.7 定时任务”很多朋友手头正好就是这个版本没有条件随意升级到 8.0那用事件调度器就是最务实的选择。你不需要装额外的 agent不需要写 Python 脚本不需要暴露数据库端口给外部调度系统只要在 MySQL 里把事件建好剩下的就是定期观察它有没有在干活。2. 准备工作与核心参数先把事件调度器打开2.1 认识事件调度器它到底是怎么运转的事件调度器可以把它的执行线程理解成数据库内置的一个守护进程。MySQL 在启动的时候会根据配置决定要不要启动这个调度线程如果启动了它就会周期性地检查当前有哪些事件到了“触发时间点”到了就开一个会话去执行事件里定义好的 SQL。这个机制里有一个重点事件调度器本身是全局的有一个开关叫做event_scheduler默认情况在 MySQL 5.7 里通常是关闭的。虽然从 5.7 开始默认值在某些安装包里面可能是 ON但稳妥起见我建议在一开始就检查一遍确保它是 ON 的状态。如果这个开关是 OFF你就算把事件建得再完美它也永远不会触发。为了更容易理解你可以把事件调度器想象成一个手机闹钟列表event_scheduler是总开关闹钟总开关关着列表里定再多闹钟也不会响而每个EVENT就是一条具体的闹钟记录到了预定时间就响一次响的过程就是执行对应的 SQL。2.2 开启 event_scheduler 的三种方式既然开关这么重要那就要掌握开启它的方法。第一种是启动参数方式在my.cnf或my.ini的[mysqld]段落里加上一行这种方式对永久生效最友好[mysqld] event_scheduler ON改完配置文件需要重启 MySQL 服务才能生效。第二种是运行时动态开启不需要重启适合已经跑起来的实例执行一条 SQL 就行SET GLOBAL event_scheduler ON;设置完之后可以通过下面的语句确认状态SHOW VARIABLES LIKE event_scheduler;如果结果里的 Value 是 ON就说明总开关已经打开了。第三种方式是命令行启动时加--event-schedulerON这个通常用在临时起库、调试的时候实际生产环境用配置文件的场景比较多。需要提醒的是SET GLOBAL这种方式在 MySQL 重启之后会恢复默认值所以如果用了动态开启别忘了把配置文件也同步改一下否则下次数据库一重启调度器又悄悄变成 OFF事件又全部哑火了。这是一个很容易被忽略的坑我见过好几个人在测试环境调得好好的一上生产重启后任务就不跑了查了半天发现是这个原因。2.3 权限与账号准备不要用 root 跑定时任务开完开关接下来是账号权限。很多朋友图省事直接用 root 账号建事件这在日常开发环境没问题但生产环境不太推荐原因很简单事件执行的 SQL 可能有误如果权限过大出了问题影响面就大。更好的做法是创建一个专用账号只授予它清理所需的权限。比如要清理某个库里的日志表可以这样建账号CREATE USER cleanerlocalhost IDENTIFIED BY 这里填一个强密码; GRANT EVENT ON *.* TO cleanerlocalhost; GRANT SELECT, DELETE, INSERT ON biz_db.user_log TO cleanerlocalhost;这里有个容易混淆的地方创建事件本身需要 EVENT 权限而事件里执行的 DELETE、INSERT 语句则需要对应表上的 DML 权限。如果你计划的事件里既删除老数据又往归档表插数据那 INSERT 权限也不能少。对权限最小化这件事我的原则是给了 SELECT 和 DELETE 就能完成清理需求就绝不多给 UPDATE 或 DROP。另外账号的 host 部分建议写成localhost或者限定为应用服务器和数据库本机不要用%放开到所有来源。虽然事件调度器执行 SQL 的时候是在 MySQL 内部以这个账号身份做的连接不太受网络访问限制影响但账号本身还是要管紧一点防止被外部工具连上来乱操作。3. 从零开始编写定时清理事件掌握完整流程3.1 第一步看清表结构和数据特征再动手建事件之前先把要清理的表摸清楚。至少要看三样东西表里有没有时间字段、时间字段是什么类型、这个字段上有没有索引。为什么时间字段和索引这么关键因为清理语句几乎都是DELETE FROM 表 WHERE 时间字段 某个时间点。如果你用了一个没有索引的时间字段做条件DELETE 就会走全表扫描每执行一次就把全表翻一遍几千万行的表能把数据库压到报警。这一点我放在前面讲就是因为它是后续所有操作的地基。具体的检查语句很简单用SHOW CREATE TABLE就能看到表结构或者直接用DESC看字段再配合SHOW INDEX FROM 表查看索引。如果发现时间字段没索引先别急着建事件应当先补一个索引。索引本身会占用一些空间但相比 DELETE 频繁全表扫描产生的开销这点空间是完全值得的。ALTER TABLE user_log ADD INDEX idx_created_at (created_at);顺便多看一眼数据分布可以用一条 SQL 看每天的数据量大致是什么量级这能帮你估算清理频率是不是合理。比如按天看最近七天的行数如果每天才几百条那一个月清一次就可以如果每天几十万条可能就得考虑按月分区或者更频繁的清理策略了。3.2 第二步设计清理策略确定保留周期和触发时间清理策略说白了就是三个数保留多少天、多长时间跑一次、每天几点跑。保留周期一般由业务方定比如登录日志保三个月、操作日志保半年。这个周期没有统一标准但有一点需要提醒不要把周期设得太激进删了的数据就真没了。对没有归档的表我宁可多留一个月也不要因为少留几天导致后人想查数据时抓瞎。执行频率则要结合数据增长速度和 DELETE 的代价来定。如果表每天增长量很大可以一天跑一次甚至用事件调度器里更精细的分钟级频率做分批删除如果数据增长很慢两天三天跑一次也行。不过有一点要注意频率越高的调度检查本身也要开销所以不要为了“频繁”而频繁够用就好。触发时间最好选在业务低峰期通常凌晨 2 点到 5 点之间比较合适避开白天查询高峰和批量任务集中的时段。在事件调度器的STARTS参数里可以直接指定下一次开始时间然后按EVERY周期循环执行。比如想每天早上 3 点清一次就可以设置STARTS 2024-01-01 03:00:00之后每 24 小时循环一次。3.3 第三步用一个完整例子掌握 CREATE EVENT 语法光说不练没用我们直接看一个完整的创建语句。假设有一张user_log表日志数据只需要保留 30 天每天凌晨 3 点执行一次清理CREATE EVENT IF NOT EXISTS clear_user_log ON SCHEDULE EVERY 1 DAY STARTS 2024-01-01 03:00:00 ON COMPLETION PRESERVE DO DELETE FROM user_log WHERE created_at NOW() - INTERVAL 30 DAY;逐行拆开来说。CREATE EVENT IF NOT EXISTS后面的名称clear_user_log是事件的名字在这个库里要唯一起名最好带表名和动作将来看到就知道是干什么用的。ON SCHEDULE后面是调度规则EVERY 1 DAY表示每 24 小时执行一次STARTS指定第一次执行的时间可以是具体时间点也可以用CURRENT_TIMESTAMP INTERVAL 1 DAY这种相对写法。ON COMPLETION PRESERVE表示事件执行完之后保留事件本身下次继续执行如果不写这个选项默认是ON COMPLETION NOT PRESERVE意味着事件执行一次之后就被自动删除了这对周期性的清理任务来说显然不是我们想要的效果。DO后面跟的就是要执行的 SQL。这里用的DELETE ... WHERE created_at NOW() - INTERVAL 30 DAY是很多清理任务的标准写法意思很直白把创建时间早于 30 天前的数据删掉。NOW()是当前时间INTERVAL 30 DAY是 30 天的时间间隔两者相减就得到了一个 30 天前的时间点所有早于这个时间点的记录都在清理范围内。看到这里可能有朋友会问为什么清理用的是DELETE而不是TRUNCATE因为TRUNCATE是直接把整张表清空不能带条件不能只删特定时间段的数据。如果你确实想整表清空、一条不留那TRUNCATE当然更快但大多数场景下日志表和流水表都是需要保留最近一段时间的所以DELETE才是正解。3.4 第四步通过元数据验证任务就绪事件建好之后先别急着等它半夜跑先查询确认一下它真的创建成功了。最常用的查看方式是查information_schema.EVENTSSELECT EVENT_NAME, STATUS, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, LAST_EXECUTED FROM information_schema.EVENTS WHERE EVENT_SCHEMA biz_db AND EVENT_NAME clear_user_log \G看到STATUS是ENABLEDEVENT_TYPE是RECURRING就说明事件已经进入正常调度队列了。STARTS字段能看到期望的开始时间LAST_EXECUTED在第一次执行前会是NULL等执行过后再查询就能看到实际执行时间。这里再提醒一个容易犯的错误创建事件的库要和表所在的库一致或者至少在事件定义里把库名写清楚。比如上面例子里的DELETE FROM user_log如果当前连接的库是别的库MySQL 会到当前默认库里找表找不到就报错。稳妥的写法是在表名前面加上库名也就是DELETE FROM biz_db.user_log WHERE ...这样一来不管当前会话在哪个库里事件执行的时候都不会找错表。4. 实战中的坑常见问题与排查技巧实录4.1 事件建了就是不执行问题出在哪这是被问得最多的一个问题。事件创建成功STATUS也是ENABLED但时间到了就是不见数据减少。排查这种事我的习惯是从外到内一步步缩小范围。第一步先看总开关有没有打开执行SHOW VARIABLES LIKE event_scheduler确认是ON。第二步查事件自己的状态用前面那句SELECT ... FROM information_schema.EVENTS看STATUS字段。我突然想到一个很隐蔽的坑如果你的事件设置了ENDS结束时间那到了结束时间之后事件的STATUS会自动变成DISABLED或者EXPIRED这种情况很容易被忽略因为创建事件的时候可能谁也没在意那个结束时间。第三步看LAST_EXECUTED。如果这个字段一直没更新说明调度器确实没跑它如果更新了但数据没删那问题就出在 DELETE 语句本身。第五步用SHOW PROCESSLIST看一下有没有正在执行的事件线程有时候 DELETE 卡在锁等待上事件状态看起来是正常的但实际 SQL 一直在等锁这种情况也会让人觉得“没执行”。还有一种情况特别迷建事件的用户带上ON COMPLETION PRESERVE没有如果漏了事件在第一次执行完就自动删除了看起来就是“跑了一次之后再也没跑过”。排查的时候可以直接再看一眼事件还在不在不在的话就是这个原因。4.2 大表删除太慢把业务都锁住了怎么办如果表本身有几千万行即便有索引一次性DELETE几十万甚至几百万行也可能引发一个问题DELETE 产生的行锁和间隙锁范围太大导致业务写入被阻塞同时 undo 日志膨胀主从延迟也可能被拉大。我处理这种大表清理喜欢用一个“分批删除”的思路把一大坨 DELETE 拆成很多个小任务每批只删几百或几千行一批删完稍微歇一下再删下一批。放到事件调度器里就可以设置一个每 5 分钟执行一次的事件DELIMITER $$ CREATE EVENT IF NOT EXISTS clear_user_log_batch ON SCHEDULE EVERY 5 MINUTE STARTS CURRENT_TIMESTAMP ON COMPLETION PRESERVE DO BEGIN DELETE FROM biz_db.user_log WHERE created_at NOW() - INTERVAL 30 DAY LIMIT 1000; END$$ DELIMITER ;这里用LIMIT 1000限制每次最多删 1000 行每 5 分钟跑一次白天即使任务在跑也不会长时间占用锁资源。有些朋友会担心这样删得太慢其实按这个速度一小时能删 1.2 万批乘以 1000 行理论上一天能处理千万级别的老数据对绝大多数应用已经够用。而且这个方案的优点是可控哪一批跑失败了下一次调度会自动继续不需要人工介入。不过需要注意一点分批删除的事件里如果 WHERE 条件的时间字段没有索引LIMIT 1000也救不了你因为数据库为了找这 1000 行可能已经把几百万行翻了一遍。索引这件事再怎么强调都不为过。4.3 时区问题事件执行时间和你以为的不一样MySQL 的事件调度器在计算触发时间的时候依赖的是系统时区设置。默认情况下NOW()返回的是数据库服务器的本地时间事件调度器判断“到没到点”用的也是这个时间。如果数据库服务器的时区是 UTC而你人在东八区你写STARTS 2024-01-01 03:00:00实际上是在 UTC 时间的凌晨 3 点执行也就是北京时间上午 11 点正好是业务高峰期。这是一类非常容易踩的坑。解决问题的方法有两个一是统一协调数据库服务器和应用服务器的时区把系统时区和 MySQL 的time_zone参数都调到一致比如国内业务就是东八区二是在写STARTS时间的时候明确使用带时区的写法避免依赖服务器默认时区。如果想验证当前数据库时区可以执行SELECT global.time_zone, session.time_zone, NOW();如果查询结果和你的预期时间相差了几个小时那大概率就是时区没有调对齐。另一个相关的技巧是在事件定义里直接用相对时间CURRENT_TIMESTAMP INTERVAL 1 DAY这样可以一定程度绕开“绝对时间点”带来的困惑因为无论时区是什么它都是按当前时间往后推算的。4.4 清理任务报错怎么看日志和定位原因时间到了事件也触发了但执行报错这种问题相对好排查一些。MySQL 会把事件执行的错误记到错误日志里先找到日志文件的位置SHOW VARIABLES LIKE log_error;然后打开日志搜索事件名称或者Event Scheduler关键字通常能找到类似这样的错误信息Error in event ...或者Execution of event ... failed。常见的报错原因包括表不存在库名没写对、权限不足cleaner 账号没有对应表权限、SQL 语法在事件里不合法、磁盘空间不足导致无法写入。还有一个比较隐蔽的问题事件里执行的 SQL 如果涉及事务比如删除的同时往归档表插入数据那COMMIT和ROLLBACK的处理就要考虑清楚。事件调度器本身不会主动帮你提交事务如果 SQL 写得不完整可能会留下未提交事务长期占用连接资源。我的建议是单条 DELETE 结束就让它自动提交涉及多条语句时在事件体里明确写好提交逻辑。另外如果事件执行失败了几次MySQL 默认不会停止这个事件除非你配置了具体的错误处理逻辑。它会在下一个调度周期继续尝试。所以失败不可怕关键是尽早从日志里发现并修复问题避免任务“看似在跑、实则报错”。5. 进阶玩法让定时清理任务更稳、更顺、更能维护5.1 用分区表做秒级清理删除分区比 DELETE 快得多对于数据量特别大、清理需求非常规律的表事件调度器配合 DELETE 即使分批做依然要消耗不少数据库资源。这里给一个进阶思路干脆把表设计成按时间分区的分区表然后定时任务只需要把过期分区直接DROP PARTITION掉这个操作的底层是删除数据文件和索引文件速度远快于逐行 DELETE。MySQL 5.7 支持 RANGE 分区可以直接按时间字段分区。比如日志表按月份分区每个月一个分区CREATE TABLE access_log ( id INT NOT NULL, log_time DATETIME NOT NULL, content TEXT, PRIMARY KEY (id, log_time) ) PARTITION BY RANGE (TO_DAYS(log_time)) ( 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)) );这样清理三月份之前的旧数据只需要执行ALTER TABLE access_log DROP PARTITION p202401;对这个分区表再建一个事件每个月跑一次先把下个月的新分区加进去再把三个多月前的旧分区删掉整个清理过程对线上的影响非常小。实现这个思路需要提前规划好分区策略对已上线的普通表做分区转换会比较麻烦但如果是新建表或者可以接受一段时间停写维护的表分区表才是清理任务的最优解。5.2 清理而不丢失顺手把数据归档到历史表有些场景下数据不是“没用”只是不常查。比如订单表、流水表业务方希望主表保持轻量但偶尔要查很老的单子。这种时候清理策略可以升级为“清理归档”事件调度器先把过期数据插入到归档表再从主表删除。示例 SQL 可以封装在一个存储过程中让事件调用它。过程的核心逻辑分两步第一步INSERT INTO order_archive SELECT * FROM orders WHERE create_time ...第二步DELETE FROM orders WHERE create_time ... LIMIT 5000。两大步骤通过事务包住归档和删除要么都成功要么都失败防止数据丢了一半。这种方案的注意点是归档表的表结构尽量和主表保持一致并且也要给归档表的时间字段加索引否则以后查历史数据会像大海捞针。归档表本身也会膨胀所以在设计归档方案的时候还要考虑归档表自身的生命周期否则只是把问题从主表挪到了归档表。5.3 把清理任务做成可配置、可观测的运维项实际运维中最怕的不是任务跑挂了而是任务跑挂了没人知道。我自己的维护习惯是把事件调度器的检查纳入日常巡检清单至少每周看一眼information_schema.EVENTS里的LAST_EXECUTED和STATUS。查看核心指标有一条很实用的 SQLSELECT EVENT_NAME, STATUS, LAST_EXECUTED, TIMESTAMPDIFF(HOUR, LAST_EXECUTED, NOW()) AS hours_since_last_run FROM information_schema.EVENTS WHERE EVENT_SCHEMA biz_db;hours_since_last_run如果大到超过了执行周期就说明事件有问题该查日志了。参数化方面如果有人经常改保留周期建议把事件里的时间间隔通过一个配置表维护或者定义一个全局变量来存保留天数然后事件处理逻辑里直接引用它。这样业务方说要改“保留 60 天”你就改一个数字不用重新去改事件的完整定义。不过全局变量方式不能跨会话共享状态用的时候注意读取时机和更新方式。还有一个进阶技巧是给事件加GET_LOCK防重入。设想一下如果某个极端情况下上一个周期的事件还没执行完下一个周期又到了两个 DELETE 同时跑容易把锁竞争和主从延迟放大。在事件体里先获取一个命名锁获取不到就直接退出能有效避免任务堆积。实现方式是在事件体中嵌入SELECT GET_LOCK(clean_user_log_lock, 0)做判断DO BEGIN IF GET_LOCK(clean_user_log_lock, 0) 1 THEN DELETE FROM biz_db.user_log WHERE created_at NOW() - INTERVAL 30 DAY LIMIT 1000; DO RELEASE_LOCK(clean_user_log_lock); END IF; END这个写法对短周期、高频事件特别有用。低频的每天跑一次反而没必要因为正常单次执行几秒就结束了冲突概率很低。核心原则是调度频率越密越要考虑并发冲突场景。再分享一个我自己的习惯给事件命名的时候带一个固定的前缀比如cleanup_这样查information_schema.EVENTS的时候直接按前缀过滤一眼就能看出系统里有哪些清理类任务。长年累月下来实例里的事件会越来越多没有一个清晰的命名规范后期维护真的会疯掉。最后说点个人体会做 MySQL 维护这几年我越来越觉得定时清数据这件事看起来简单但真正做好需要想清楚的东西很多。最开始我也图省事建一个每天 DELETE 一次的事件就完事结果凌晨的 DELETE 把主从延迟拉高业务第二天早上来投诉。后来改成拆批、加锁、做巡检才慢慢稳定下来。踩过几次坑之后我的体会是凡是和删除沾边的操作保守永远比激进好。事件调度器再方便也只是执行工具真正决定数据安全的是背后的保留策略和兜底方案。建议大家在确认清理策略之前先和业务方核对清楚“这些数据还需要留多久”再加好索引、设好时区、观察好执行记录最后才让定时任务上岗。这套流程走下来MySQL 定时清理表记录这件事基本就不会给你添什么麻烦了。
