MySQL 5.6按月分区审计表定期截断旧分区的定时事件实现方法
基于MySQL定时事件的分区清理实现方案
前置关键提醒
你当前的Range分区规则存在根本性缺陷,无法支撑“留存近15天交易数据”的业务要求:
现有分区逻辑是按month(creationDate)的数字值划分边界,所有年份的1月数据都会存入p0分区、2月数据存入p1分区,完全不区分年份维度。如果直接基于这个分区做删除操作,会把往年同月份的有效历史数据一并清空,完全达不到留存要求,必须先调整分区规则再落地定时任务。
完整实现步骤
1. 基础环境配置检查
- 确认MySQL事件调度器处于开启状态:执行
SHOW VARIABLES LIKE 'event_scheduler';,如果返回值为OFF,可通过以下方式开启:- 临时生效(重启后失效):执行
SET GLOBAL event_scheduler = ON; - 永久生效:在MySQL配置文件my.cnf的[mysqld]段下添加
event_scheduler=ON,重启MySQL服务后生效
- 临时生效(重启后失效):执行
- 确认操作账号拥有对应权限:需要目标表的
ALTER权限、全局EVENT权限,无权限的话需要提前联系库管账号授权。
2. 修正表分区规则
将原有按月份数字的分区逻辑,调整为按实际时间边界的Range分区,让每个分区对应固定时间范围的数据集,示例改造SQL(以库存审计表名为inventory_audit为例,根据实际表名替换):
ALTER TABLE inventory_audit PARTITION BY RANGE (TO_DAYS(creationDate)) ( -- 每个分区对应自然月,边界为当月最后一天的次日 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')), -- 预留未来3个月的空分区,避免数据落入兜底大分区 PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')), PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')), PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')), -- 兜底分区,避免无匹配分区导致写入报错 PARTITION pmax VALUES LESS THAN MAXVALUE );
注意:如果表数据量较大,分区改造操作会触发全表数据重写,需要在业务低峰期执行,避免长时间锁表影响线上业务。
3. 创建每月15号执行的定时事件
事件逻辑包含两部分:① 遍历识别所有数据时间早于“当前日期往前15天”的分区,执行截断操作;② 拆分兜底的pmax分区,为未来3个月的时间范围创建空的新分区,避免大量数据落入无边界的兜底分区。
DELIMITER $$ CREATE EVENT IF NOT EXISTS evt_clean_inventory_audit_partition ON SCHEDULE EVERY 1 MONTH -- 首次触发时间为下一个15号0点,后续每月15号自动执行 STARTS DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-15'), INTERVAL 0 HOUR) ON COMPLETION PRESERVE ENABLE COMMENT '每月15号清理库存审计表过期分区,保障近15天数据留存' DO BEGIN -- 定义过期阈值:当前日期往前推15天 DECLARE expire_date DATE DEFAULT DATE_SUB(CURDATE(), INTERVAL 15 DAY); DECLARE op_done INT DEFAULT 0; -- 游标:获取所有时间边界早于过期阈值的非兜底分区 DECLARE cur_expire_partition CURSOR FOR SELECT partition_name FROM information_schema.partitions WHERE table_schema = DATABASE() AND table_name = 'inventory_audit' AND partition_description < TO_DAYS(expire_date) AND partition_name != 'pmax'; -- 游标遍历结束的退出标记 DECLARE CONTINUE HANDLER FOR NOT FOUND SET op_done = 1; -- 第一步:逐一遍历过期分区,执行截断操作 OPEN cur_expire_partition; expire_loop: LOOP FETCH cur_expire_partition INTO @target_partition; IF op_done = 1 THEN LEAVE expire_loop; END IF; -- 动态拼接分区截断SQL并执行 SET @truncate_sql = CONCAT('ALTER TABLE inventory_audit TRUNCATE PARTITION ', @target_partition); PREPARE exec_stmt FROM @truncate_sql; EXECUTE exec_stmt; DEALLOCATE PREPARE exec_stmt; END LOOP; CLOSE cur_expire_partition; -- 第二步:拆分pmax兜底分区,提前创建未来3个月的空分区 SET @next_boundary = DATE_ADD(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 3 MONTH); SET @reorganize_sql = CONCAT( 'ALTER TABLE inventory_audit REORGANIZE PARTITION pmax INTO (', 'PARTITION p', DATE_FORMAT(@next_boundary, '%Y%m'), ' VALUES LESS THAN (TO_DAYS(', QUOTE(@next_boundary), ')),', 'PARTITION pmax VALUES LESS THAN MAXVALUE', ')' ); PREPARE exec_stmt FROM @reorganize_sql; EXECUTE exec_stmt; DEALLOCATE PREPARE exec_stmt; END$$ DELIMITER ;
4. 上线前验证要点
- 先单独执行游标查询SQL,核对识别出的过期分区,确认所有被标记为过期的分区内,不存在近15天的有效数据,避免误删。
- 第一次上线时不要直接启用自动执行,先手动在测试环境跑通全流程逻辑,确认截断的数据范围和新分区创建逻辑符合预期,再正式启用事件。
- 数据量较大的业务场景,建议将分区粒度从按月调整为按天,避免整月分区内混杂近15天有效数据导致的误删问题。
内容的提问来源于stack exchange,提问作者bharatharatna puli
相关产品推荐
相关产品推荐

