mysql分区表自动归档的实施步骤

前言

有个项目，数据库是mysql8，使用了分区表，按时间分区。之所以使用分区表，当然是数据量大，每分钟上百条记录，时间一长，数据量就很可观了，造成查询十分耗时，尽管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;

二、归档方式

归档可以分为手动归档和自动归档两种方式。第一次归档的话，可能已经积压了多年的数据，采用手动归档，先迁为快；之后再自动归档。无论是手动还是自动，都有3个关键步骤：

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年上半年的数据（<20260701）。

四、自动归档

自动归档原理与手动类似，只不过将命令集成到一个存储过程，然后用批处理定期运行而已。

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账号运行，不受系统管理员密码更改影响。

总结