You need to enable JavaScript to run this app. MySQL 5.6按月分区审计表定期截断旧分区的定时事件实现方法
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.07 15:36:10