企业官网建设流程全解析

你们有没有遇到过这种场景：一张日志表每天几万行往上涨，业务方说“超过 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 都有额外的事务提交开销，反而浪费。批次太大，又回到了大事务的老路。可以参考下面这张表选初始值：

3. 用事件调度器把清理固化成每日任务

存储过程写好了，接下来要让它每天自动跑。MySQL 自带的事件调度器就是干这个的，不需要额外装 crontab，也不需要外部脚本，直接挂在数据库内部，配合 binlog 还能在从库上保持同样的调度逻辑。

3.1 打开 event_scheduler

MySQL 8.0 里事件调度器默认可能是关闭的。在命令行先确认：

SHOW VARIABLES LIKE 'event_scheduler';

如果是 OFF，可以临时开启：

SET GLOBAL event_scheduler = ON;

但临时开启在 MySQL 重启后会恢复成配置值，所以要写进配置文件my.cnf的[mysqld]段：

event_scheduler=ON

然后重启 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\G

SELECT 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，往往比调批次参数有效得多。清理是一条长期路，不是一次性的冲刺，跑得稳比跑得快重要。