前言
数据库采用 MySQL 8，并使用了按时间划分的分区表。为何如此设计？因为数据量极为庞大——每分钟新增上百条记录，日积月累之下，数据规模十分可观，查询性能自然随之下降。即便查询条件已经包含了分区字段，依然难以抵挡海量数据带来的压力，有时甚至需要等待几十秒才能返回结果，确实令人难以忍受。
所使用的分区表脚本如下所示：
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 账号运行，这样就不会受管理员密码更改的影响。
