MySQL 动态分区管理：自动化与优化实践

以下是 MySQL 动态分区管理 的自动化与优化实践总结（基于MySQL 5.7/8.0+ 生产环境常见方案，重点覆盖 RANGE 分区 + 时间维度，2025 年主流做法）。

核心结论先放前面

维度	推荐方案（2025主流）	优点	缺点 / 注意事项	适用场景
自动化创建未来分区	Event Scheduler + Stored Procedure	原生、无额外组件、易审计	ALTER TABLE 元数据锁较重，高峰期慎用	中大型表，日/周/月分区
自动化删除旧分区	DROP PARTITION（最快）	秒级完成，不产生 binlog	必须是 RANGE/LIST，不能误删	日志、订单、监控、埋点数据
自动化重组分区	REORGANIZE PARTITION（MAXVALUE）	安全过渡，避免插入失败	锁表时间稍长	需要严格边界控制的业务
极致低影响方案	pt-online-schema-change / gh-ost	几乎无锁，适合超大表	引入外部工具，复杂性↑	单表 > 500GB+，不能停写
云原生增强	PolarDB/Aurora/MySQL HeatWave interval	内置自动间隔分区	需迁移到特定云产品	新项目或愿意上云

1. 典型自动化架构（最常用组合拳）

Event Scheduler
   ↓ 每天/每小时执行一次
Stored Procedure: maintain_partitions(db, tbl, interval_unit, keep_months, future_months)
   ├── 检查当前最大分区边界
   ├── 如果快到期 → REORGANIZE / ADD 新分区（提前创建未来 N 个）
   ├── 如果超过保留期 → DROP 最老分区
   └── 记录操作日志（可选插入分区管理日志表）

2. 核心存储过程示例（RANGE + TO_DAYS，按天分区）

-- 前置：开启事件调度器（全局只需执行一次）
SET GLOBAL event_scheduler = ON;

-- 辅助配置表（可选，但强烈推荐，便于管理多张表）
CREATE TABLE partition_manager_config (
    db_name       VARCHAR(64),
    table_name    VARCHAR(64),
    partition_col VARCHAR(64),          -- 如 'create_time'
    unit          ENUM('DAY','WEEK','MONTH'),  -- 分区粒度
    keep_units    INT,                  -- 保留多少个单位（例：保留180天）
    future_units  INT,                  -- 提前创建多少个未来单位
    last_run      DATETIME,
    PRIMARY KEY(db_name, table_name)
);

-- 核心维护过程（按天示例，可改成 MONTH 用 PERIOD_ADD / PERIOD_DIFF）
DELIMITER $$

CREATE PROCEDURE maintain_daily_partitions(
    IN p_db     VARCHAR(64),
    IN p_table  VARCHAR(64),
    IN p_col    VARCHAR(64),          -- 分区键列名
    IN p_keep_days   INT DEFAULT 180,
    IN p_future_days INT DEFAULT 7
)
BEGIN
    DECLARE v_sql         TEXT;
    DECLARE v_max_date    DATE;
    DECLARE v_next_date   DATE;
    DECLARE v_part_name   VARCHAR(50);
    DECLARE v_exists      INT DEFAULT 0;
    DECLARE v_today       DATE DEFAULT CURDATE();

    -- 1. 获取当前最大分区边界（排除 MAXVALUE）
    SELECT MAX(CAST(PARTITION_DESCRIPTION AS SIGNED))
    INTO v_max_date
    FROM information_schema.PARTITIONS
    WHERE TABLE_SCHEMA = p_db
      AND TABLE_NAME   = p_table
      AND PARTITION_DESCRIPTION != 'MAXVALUE';

    IF v_max_date IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '表未分区或无有效分区';
    END IF;

    -- 2. 创建未来分区（提前 N 天）
    SET v_next_date = DATE_ADD(v_max_date, INTERVAL 1 DAY);
    WHILE v_next_date <= DATE_ADD(v_today, INTERVAL p_future_days DAY) DO
        SET v_part_name = CONCAT('p_', DATE_FORMAT(v_next_date, '%Y%m%d'));

        SELECT COUNT(*) INTO v_exists
        FROM information_schema.PARTITIONS
        WHERE TABLE_SCHEMA = p_db
          AND TABLE_NAME   = p_table
          AND PARTITION_NAME = v_part_name;

        IF v_exists = 0 THEN
            SET v_sql = CONCAT(
                'ALTER TABLE `', p_db, '`.`', p_table, '` ',
                'ADD PARTITION (PARTITION ', v_part_name,
                ' VALUES LESS THAN (TO_DAYS(''', v_next_date + INTERVAL 1 DAY, ''')))'
            );
            PREPARE stmt FROM v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            -- 可记录日志
            INSERT INTO partition_op_log(db, tbl, action, partition_name, exec_time)
            VALUES (p_db, p_table, 'ADD', v_part_name, NOW());
        END IF;

        SET v_next_date = DATE_ADD(v_next_date, INTERVAL 1 DAY);
    END WHILE;

    -- 3. 删除过期分区（保留 p_keep_days 天）
    SET v_next_date = DATE_SUB(v_today, INTERVAL p_keep_days DAY);
    SELECT MIN(CAST(PARTITION_DESCRIPTION AS SIGNED))
    INTO v_max_date   -- 复用变量，实际是最小分区
    FROM information_schema.PARTITIONS
    WHERE TABLE_SCHEMA = p_db AND TABLE_NAME = p_table
      AND PARTITION_DESCRIPTION != 'MAXVALUE';

    WHILE v_max_date < TO_DAYS(v_next_date) DO
        SET v_part_name = (SELECT PARTITION_NAME
                           FROM information_schema.PARTITIONS
                           WHERE TABLE_SCHEMA = p_db AND TABLE_NAME = p_table
                             AND CAST(PARTITION_DESCRIPTION AS SIGNED) = v_max_date);

        IF v_part_name IS NOT NULL THEN
            SET v_sql = CONCAT('ALTER TABLE `', p_db, '`.`', p_table, '` DROP PARTITION ', v_part_name);
            PREPARE stmt FROM v_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            INSERT INTO partition_op_log(db, tbl, action, partition_name, exec_time)
            VALUES (p_db, p_table, 'DROP', v_part_name, NOW());
        END IF;

        -- 继续找下一个最小分区
        SELECT MIN(CAST(PARTITION_DESCRIPTION AS SIGNED))
        INTO v_max_date
        FROM information_schema.PARTITIONS
        WHERE TABLE_SCHEMA = p_db AND TABLE_NAME = p_table
          AND PARTITION_DESCRIPTION != 'MAXVALUE';
    END WHILE;

END$$

DELIMITER ;

3. 创建事件（每天凌晨2点执行）

CREATE EVENT e_daily_partition_maintain
    ON SCHEDULE EVERY 1 DAY
    STARTS '2026-01-28 02:00:00'
    DO
    BEGIN
        -- 调用示例（可多张表）
        CALL maintain_daily_partitions('test_db', 'event_log', 'event_time', 365, 14);
        CALL maintain_daily_partitions('test_db', 'order_history', 'order_date', 180, 7);
    END;

4. 2025年生产环境优化要点（避免踩坑）

• 元数据锁（MDL）：高峰期执行 ALTER TABLE 会阻塞DML → 放低峰期 + 监控 Threads_running 和 Innodb_row_lock_waits
• MAXVALUE 分区：几乎所有方案都保留一个 p_max VALUES LESS THAN MAXVALUE，新数据先落这里，然后 REORGANIZE 拆分
• 监控告警：Prometheus + Grafana 监控分区数量、最大分区描述值与当前日期差距（< 3天告警）
• 批量操作：未来分区一次加 7~30 个，避免频繁 ALTER
• 日志表：建议建 partition_op_log 记录每次操作，便于追溯
• 回滚方案：分区误删是灾难 → 开启 binlog + 定期 mysqldump 分区结构
• 8.0+ 增强：innodb_parallel_read_threads、partition level MDL（部分云产品已支持）

5. 替代方案对比（当原生不够用时）

方案	锁粒度	复杂度	推荐场景
原生 Event + Procedure	中～高	低	< 300GB 表，业务可接受低峰维护
pt-online-schema-change	低	中	大表在线加/重组分区
gh-ost	极低	中高	极致大表（TB级）在线变更
PolarDB Interval Partition	内置自动	低	已使用阿里云 PolarDB

你的表是按天/月分区？数据量大概多少？保留周期多长？
有没有高峰期不能执行 ALTER 的限制？
告诉我更多细节，我可以帮你定制更精确的过程或给出低影响的变通方案。