MySQL 動態分區管理實戰：儲存過程 + 事件排程自動化新增/刪除分區

MySQL 動態分區管理實戰

生產環境中，按時間分區的大表需要定期新增未來分區、歸檔/刪除歷史分區。純手工操作易遺漏、易出錯。本文展示一套「元資料表 + 儲存過程 + 事件排程」的自動化分區管理方案，實現分區生命週期全自動化。

一、架構設計

1. 元資料表 table_fen_qu：記錄每張分區表的分區名、對應時間、備份狀態，作為管理依據。
2. 儲存過程 general_procedure：核心邏輯，支援首次建立、增量新增、全量重建三種模式。
3. 事件排程 eachDayEvent：每日定時觸發，對多張業務表批次執行分區維護。

二、元資料表結構

CREATE TABLE table_fen_qu (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fenquname VARCHAR(64) NOT NULL COMMENT '分區名',
    tablename VARCHAR(64) NOT NULL COMMENT '業務表名',
    monittime DATETIME NOT NULL COMMENT '分區對應時間',
    backupflag TINYINT NOT NULL DEFAULT 0 COMMENT '0=未備份, 1=已備份',
    UNIQUE KEY uk_table_time (tablename, monittime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、核心儲存過程邏輯解析

general_procedure(tablename, intervalHour, newIntervalNum)

• tablename：目標分區表名
• intervalHour：分區時間間隔（小時）
• newIntervalNum：每次新增分區數量

模式 A：增量新增（表已有分區記錄）

1. 查詢 table_fen_qu 中該表最新分區時間 maxMonitTime。
2. 若 maxMonitTime - 72小時 > 當前時間，說明分區足夠，無需新增。
3. 否則從 maxMonitTime 起，每 intervalHour 小時新增一個分區，共 newIntervalNum 個。
   • 分區命名：pYYYYMMDDHHMMSS
   • 動態構建 ALTER TABLE ... ADD PARTITION，使用 PREPARE/EXECUTE 執行。
   • 同步寫入 table_fen_qu 記錄。

模式 B：全量重建（首次建立或異常修復）

1. 計算時間窗口：minMonitTime = 當前時間 - 60天，maxMonitTime = 當前時間 + 4天。
2. 分區數 = (max - min) / intervalHour，最少 12 個。
3. 先移除現有分區：ALTER TABLE ... REMOVE PARTITIONING。
4. 清空 table_fen_qu 該表記錄。
5. 使用 PARTITION BY RANGE COLUMNS(moint_time) 重建全部分區，單次 ALTER TABLE 完成（效能優於逐個 ADD）。
6. 同步寫入 table_fen_qu。

四、事件排程配置

-- 開啟事件排程器
SET GLOBAL event_scheduler = ON;

-- 建立每日執行事件
DROP EVENT IF EXISTS eachDayEvent;
DELIMITER ||
CREATE EVENT eachDayEvent
ON SCHEDULE EVERY 1 DAY STARTS '2024-01-01 00:00:00'
ON COMPLETION PRESERVE ENABLE
DO BEGIN
    -- 非能耗 5 分鐘表：6 小時/分區，預留 8 個（48 小時）
    CALL general_procedure('no_energy_five_minute_data', 6, 8);
    -- 原始數據表：同上
    CALL general_procedure('temp_data', 6, 8);
    -- 能耗 5 分鐘表：24 小時/分區，預留 4 個（4 天）
    CALL general_procedure('energy_five_minute_data', 24, 4);
    -- 能耗 5 分鐘匯總表：同上
    CALL general_procedure('energy_five_minute_data_summarize', 24, 4);
    -- 能耗小時匯總：168 小時/分區（週），預留 4 個
    CALL general_procedure('energy_hour_data_summarize', 168, 4);
    -- 能耗分類分項 5 分鐘：週分區
    CALL general_procedure('energy_item_five_minute_data', 168, 4);
    -- 能耗分類分項小時：季度分區（90 天）
    CALL general_procedure('energy_item_hour_data', 2160, 4);
    -- 能耗天匯總：半年分區
    CALL general_procedure('energy_day_data_summarize', 4380, 4);
    -- 清理已備份分區
    CALL del_fenqu();
END ||
DELIMITER ;

五、清理已備份分區儲存過程 del_fenqu

CREATE PROCEDURE del_fenqu()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE v_tablename, v_fenquname VARCHAR(64);
    DECLARE cur CURSOR FOR
        SELECT tablename, fenquname FROM table_fen_qu WHERE backupflag = 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_tablename, v_fenquname;
        IF done THEN LEAVE read_loop; END IF;

        SET @sql = CONCAT('ALTER TABLE ', v_tablename, ' DROP PARTITION ', v_fenquname);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        DELETE FROM table_fen_qu WHERE tablename = v_tablename AND fenquname = v_fenquname;
    END LOOP;
    CLOSE cur;
END;

六、關鍵技術細節與避坑

1. 動態 SQL + PREPARE：分區名、時間值需動態拼接，必須用 PREPARE/EXECUTE 執行。
2. RANGE COLUMNS：直接支援 DATETIME 欄位分區，無需 TO_DAYS() 轉換，避免函數運算導致剪枝失效。
3. 分區命名規範：p + 時間戳 確保唯一、可排序、易識別。
4. 備份標記機制：backupflag 由外部備份系統更新，分區管理僅清理 flag=1 的分區，實現「備份確認後再刪除」的安全閉環。
5. 首次部署順序：
   1. 建立 table_fen_qu 表
   2. 建立 general_procedure、del_fenqu
   3. 手動呼叫一次 general_procedure 初始化分區
   4. 建立並啟用 eachDayEvent
6. 監控告警：監控 event_scheduler 狀態、事件執行耗時、分區數量預警（接近 1024 上限）。

七、擴展建議

• 將 intervalHour、newIntervalNum、保留天數 等參數遷移至配置表，實現「零代碼變更調整策略」。
• 整合 pt-archiver 或自建歸檔任務，將冷分區資料遷移至廉價儲存（ClickHouse、S3/Parquet），而非直接刪除。
• 支援 LIST/HASH 分區類型的通用化管理框架。

這套方案在某能耗監測系統（百億級資料、數十張分區表）穩定運行 3 年+，實現了分區管理「零人工、零遺漏、零事故」。核心思想是：用元資料驅動 DDL，用事件排程替代排程腳本，用備份標記保障資料安全。