MySQL分区表自动归档详细步骤

前言

数据库采用MySQL 8，并使用了按时间划分的分区表。之所以这样做，是因为数据量庞大——每分钟产生上百条记录，随着时间的推移，数据量急剧增长，查询速度明显下降。即使SQL查询条件已经使用了分区字段，仍然无法避免数据堆积带来的性能问题，有时查询结果需要等待几十秒甚至更久，严重影响使用体验。

以下为分区表创建脚本示例：

DROP TABLE IF EXISTS `monkey_data`;CREATE TABLE `monkey_data` (  `ID` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'ID',  `BOARD_ID` int NOT NULL COMMENT '板卡ID',  `CODE` varchar(100) NOT NULL COMMENT '属性编号',  `VALUE` double COMMENT '值',  `CREATE_DATE` datetime COMMENT '读取时间',  PRIMARY KEY (`ID`,`CREATE_DATE`)) COMMENT='猴子猴孙采集记录'PARTITION BY RANGE COLUMNS(create_date) (    PARTITION p240101 VALUES LESS THAN ('2024-01-01'),    PARTITION p240701 VALUES LESS THAN ('2024-07-01'),    PARTITION p250101 VALUES LESS THAN ('2025-01-01'),    PARTITION p250701 VALUES LESS THAN ('2025-07-01'),    PARTITION p260101 VALUES LESS THAN ('2026-01-01'),    PARTITION p260701 VALUES LESS THAN ('2026-07-01'),    PARTITION p270101 VALUES LESS THAN ('2027-01-01'),    PARTITION p270701 VALUES LESS THAN ('2027-07-01'),    PARTITION p280101 VALUES LESS THAN ('2028-01-01'),    PARTITION p280701 VALUES LESS THAN ('2028-07-01'),    PARTITION p290101 VALUES LESS THAN ('2029-01-01'),    PARTITION p290701 VALUES LESS THAN ('2029-07-01'),    PARTITION p300101 VALUES LESS THAN ('2030-01-01'),    PARTITION p300701 VALUES LESS THAN ('2030-07-01'),    PARTITION p310101 VALUES LESS THAN ('2031-01-01'),    PARTITION p310701 VALUES LESS THAN ('2031-07-01'),    PARTITION p320101 VALUES LESS THAN ('2032-01-01'),    PARTITION p320701 VALUES LESS THAN ('2032-07-01'),    PARTITION p330101 VALUES LESS THAN ('2033-01-01'),    PARTITION p330701 VALUES LESS THAN ('2033-07-01'),    PARTITION p340101 VALUES LESS THAN ('2034-01-01'),    PARTITION p340701 VALUES LESS THAN ('2034-07-01'),    PARTITION p350101 VALUES LESS THAN ('2035-01-01'),    PARTITION p350701 VALUES LESS THAN ('2035-07-01'),    PARTITION p360101 VALUES LESS THAN ('2036-01-01'),    PARTITION p360701 VALUES LESS THAN ('2036-07-01'),    PARTITION p370101 VALUES LESS THAN ('2037-01-01'),    PARTITION p370701 VALUES LESS THAN ('2037-07-01'),    PARTITION p380101 VALUES LESS THAN ('2038-01-01'),    PARTITION p380701 VALUES LESS THAN ('2038-07-01'),    PARTITION p390101 VALUES LESS THAN ('2039-01-01'),    PARTITION p390701 VALUES LESS THAN ('2039-07-01'),    PARTITION p400101 VALUES LESS THAN ('2040-01-01'),    PARTITION p400701 VALUES LESS THAN ('2040-07-01'),    PARTITION p410101 VALUES LESS THAN ('2041-01-01'),    PARTITION p410701 VALUES LESS THAN ('2041-07-01'),    PARTITION p420101 VALUES LESS THAN ('2042-01-01'),    PARTITION p420701 VALUES LESS THAN ('2042-07-01'),    PARTITION p430101 VALUES LESS THAN ('2043-01-01'),    PARTITION p430701 VALUES LESS THAN ('2043-07-01'),        PARTITION p440101 VALUES LESS THAN MAXVALUE);

一个很自然的想法是：定期将一年前或半年前的数据迁移出去，不让旧数据长期占用在线资源。具体思路是在数据库中编写一个存储过程，通过批处理命令调用它，然后按计划定期执行。下面逐步拆解操作步骤。

一、准备数据归档

直接删除数据是不可行的。我们需要将较早的数据迁移到专门的历史库中，既保留历史记录，又减轻主库压力。

1、创建历史数据库

首先创建一个历史数据库，用于接收源库中转移出来的数据。

create database monkey2022_history;

二、归档方式

归档分为手动归档和自动归档两种方式。首次操作时，可能已经积累了数年的数据，此时手动归档效率最高，可以一次性完成迁移；之后使用自动归档定期执行。无论手动还是自动，核心步骤都相同，主要包括以下三步：

1）在历史库中创建一张与源表结构完全相同的普通表（非分区表）。

2）利用EXCHANGE PARTITION（分区交换）技术，将数据秒级迁移到历史表。

3）删除源表中已被清空的历史分区，释放磁盘空间。

三、手动归档

1、在历史库中创建与待转移表结构相同但非分区的表

在历史库中，按年份分别建表。假设当前年份为2026年，项目从2024年开始运行，那么需要将2024年、2025年的数据迁入历史库：

-- 在历史库中CREATE TABLE monkey2022_history.monkey_data_2024 LIKE monkey2022.monkey_data;ALTER TABLE monkey2022_history.monkey_data_2024 REMOVE PARTITIONING;CREATE TABLE monkey2022_history.monkey_data_2025 LIKE monkey2022.monkey_data;ALTER TABLE monkey2022_history.monkey_data_2025 REMOVE PARTITIONING;

2、创建临时中转表

在源库中创建一张临时中转表，用于快速将旧分区数据转移出来。

CREATE TABLE monkey2022.monkey_data_tmp LIKE monkey2022.monkey_data;ALTER TABLE monkey2022.monkey_data_tmp REMOVE PARTITIONING;

3、搬迁数据

将p240101分区数据交换到中转表（此操作瞬间完成，原分区数据立即清空，这正是分区交换技术的优势所在）。

ALTER TABLE monkey2022.monkey_data EXCHANGE PARTITION p250701 WITH TABLE monkey2022.monkey_data_tmp;INSERT INTO monkey2022_history.monkey_data_2025 SELECT * FROM monkey2022.monkey_data_tmp;truncate TABLE monkey2022.monkey_data_tmp;

4、删除源表的相应分区

ALTER TABLE monkey2022.monkey_data DROP PARTITION p250701;

5、查看分区情况

SELECT     PARTITION_NAME AS '分区名',    PARTITION_EXPRESSION AS '分区表达式',    TABLE_ROWS AS '行数',    DATA_LENGTH / 1024 / 1024 AS '数据大小(MB)',    TABLE_SCHEMA AS '数据库名'FROM     information_schema.PARTITIONSWHERE     TABLE_NAME = 'monkey_data'     AND TABLE_SCHEMA = 'monkey2022'; 

注意，分区的日期表示截止日期。例如，p260701分区存放的是2026年上半年的数据（即小于2026-07-01的数据）。

四、自动归档

自动归档的原理与手动归档相同，只是将命令集成到一个存储过程中，然后通过批处理定期运行。

1、开启数据库定期操作

SET GLOBAL event_scheduler = ON;

2、定义存储过程

核心逻辑拆解如下：

第一步：精准定位“最老且超过6个月”的分区。

第二步：动态构建“四步走”的拼装SQL。

（1）建影子表：在历史库中创建一张与主表结构完全相同的普通表，表名格式为monkey_data_分区名。

（2）移除分区属性。

（3）秒级交换：MySQL将主表中该分区的文件指针与影子表的文件指针瞬间对调，数据即完成迁移。

（4）彻底删除该分区，立即释放磁盘空间。

第三步：Debug安全开关。

存储过程前半部分用于拼装需要执行的SQL语句。如果参数v_debug不为0，系统仅输出SQL语句，便于检查正确性。毕竟数据库是项目中最宝贵的资源，程序丢失可以重写，但数据一旦丢失将无法恢复。

DELIMITER //CREATE PROCEDURE `sp_archive_board_data_debug`(IN v_debug TINYINT)BEGIN    SET @target_p = (        SELECT PARTITION_NAME         FROM information_schema.PARTITIONS         WHERE TABLE_SCHEMA = 'monkey2022'           AND TABLE_NAME = 'monkey_data'           AND PARTITION_NAME LIKE 'p%'          AND PARTITION_DESCRIPTION < CONCAT("'", DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 MONTH), '%Y-%m-%d'), "'")        ORDER BY PARTITION_NAME ASC        LIMIT 1    );    IF @target_p IS NOT NULL THEN        SET @sql_create = CONCAT('CREATE TABLE IF NOT EXISTS monkey2022_history.monkey_data_', @target_p, ' LIKE jbh2022.board_data;');        SET @sql_unpart = CONCAT('ALTER TABLE monkey2022_history.board_data_', @target_p, ' REMOVE PARTITIONING;');        SET @sql_exch   = CONCAT('ALTER TABLE monkey2022.monkey_data EXCHANGE PARTITION ', @target_p, ' WITH TABLE monkey2022_history.board_data_', @target_p, ';');        SET @sql_drop   = CONCAT('ALTER TABLE monkey2022.money_data DROP PARTITION ', @target_p, ';');        SELECT '--- SQL Preview ---' AS Info;        SELECT @sql_create AS 'Step 1';        SELECT @sql_unpart AS 'Step 2';        SELECT @sql_exch   AS 'Step 3';        SELECT @sql_drop   AS 'Step 4';        IF v_debug = 0 THEN            SELECT '>>> Executing...' AS Status;            PREPARE stmt1 FROM @sql_create; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;            PREPARE stmt2 FROM @sql_unpart; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;            PREPARE stmt3 FROM @sql_exch;   EXECUTE stmt3; DEALLOCATE PREPARE stmt3;            PREPARE stmt4 FROM @sql_drop;   EXECUTE stmt4; DEALLOCATE PREPARE stmt4;            SELECT 'Done.' AS Final_Status;        ELSE            SELECT '>>> Debug Mode: No changes made.' AS Status;        END IF;    ELSE        SELECT 'No partitions found for archiving.' AS Status;    END IF;END //DELIMITER ;

3、执行存储过程

 //CALL sp_archive_board_data_debug(1);//仅输出语句不执行，便于调试 CALL sp_archive_board_data_debug(0);//真正执行

4、调用方式

1）一次性设置账号密码，信息加密保存，后续脚本调用时无需再输入端口。

mysql_config_editor set --login-path=db_mgr --host=localhost --port=3306 --user=root --password

2）批处理文件

服务器操作系统为Windows Server。

@echo off:: ============================================================:: 配置区域：请根据实际安装路径修改 MYSQL_PATH:: ============================================================set "MYSQL_PATH=C:Program FilesMySQLMySQL Server 8.4binmysql.exe"set "LOG_FILE=D:monkey2022db-cleanarchive_log.txt"echo ------------------------------------------------------------ >> "%LOG_FILE%"echo [%date% %time%] 启动分区清理任务... >> "%LOG_FILE%":: 调用存储过程 (0 为正式执行模式)"%MYSQL_PATH%" --login-path=db_mgr -e "CALL monkey2022.sp_archive_board_data_debug(0);" >> "%LOG_FILE%" 2>&1:: 检查执行状态if %errorlevel% equ 0 (    echo [%date% %time%] 分区清理指令执行成功。 >> "%LOG_FILE%") else (    echo [%date% %time%] 分区清理执行出错，请检查上方日志。 >> "%LOG_FILE%")echo ------------------------------------------------------------ >> "%LOG_FILE%"

3）将该批处理文件添加到Windows的任务计划程序中，即可定期自动执行。

需要注意的是，如果Windows的系统管理员密码发生更改，依赖该管理员账号的任务计划需要重新输入账号密码。建议将任务计划设置为以SYSTEM账号运行，这样可避免因管理员密码更改而影响计划任务执行。

总结