MySQL 存儲過程實現 MyISAM 與 InnoDB 表定期優化清理

背景與需求

在數據中心環境中，大量採用 MyISAM 引擎的日誌表需要定期維護。為了不影響業務高峰期，清理任務排程在每個週末執行，單次處理約 10 GB 數據，預期在 5 到 10 分鐘內完成。本文不贅述 Event Scheduler 的配置細節，直接提供核心的存儲過程實現代碼。

存儲過程核心代碼

DROP PROCEDURE IF EXISTS proc_optable;

CREATE PROCEDURE proc_optable()
BEGIN
    DECLARE dono INT DEFAULT 0;
    DECLARE o VARCHAR(2000);
    DECLARE optable CURSOR FOR
        SELECT CONCAT('OPTIMIZE TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;')
        FROM information_schema.`TABLES`
        WHERE TABLE_TYPE = 'BASE TABLE'
          AND ENGINE IN ('MyISAM', 'InnoDB', 'BDB');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET dono = 1;

    SET dono = 0;
    OPEN optable;

    REPEAT
        FETCH optable INTO o;
        SET @o = o;
        PREPARE MSQL FROM @o;
        EXECUTE MSQL;
    UNTIL dono END REPEAT;

    CLOSE optable;
END;

關鍵點解析

• 動態 SQL 構建：利用 information_schema.TABLES 篩選目標引擎類型，動態拼接 OPTIMIZE TABLE 語句。
• 游標遍歷：配合 CONTINUE HANDLER 捕獲 NOT FOUND 狀態，實現對所有符合條件表的批量處理。
• 預處理語句：使用 PREPARE 和 EXECUTE 執行動態生成的 SQL，兼容 MySQL 5.1.6 及以上版本。

部署建議

將此存儲過程部署後，配合 CREATE EVENT 定義週末執行計劃，即可實現無人值守的表碎片整理與空間回收，保障數據庫長期穩定運行。