mysql分區(qū)表自動歸檔的具體步驟
更新時間：2026年07月10日 08:42:20 作者：左直拳
mysql數據自動歸檔是通過將不常用的歷史數據遷移或刪除以減輕數據庫壓力、提高查詢效率,這篇文章主要介紹了mysql分區(qū)表自動歸檔的具體步驟,需要的朋友可以參考下
前言
有個項目，數據庫是mysql8，使用了分區(qū)表，按時間分區(qū)。之所以使用分區(qū)表，當然是數據量大，每分鐘上百條記錄，時間一長，數據量就很可觀了，造成查詢十分耗時，盡管SQL的過濾條件使用了分區(qū)字段，但架不住數據量大，常常需要等待幾十秒才出來結果。分區(qū)表建立腳本如下：
待遷移的分區(qū)表：
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
);
很自然就想到，應該定期將一年前，或者半年前的數據遷走。思路是在數據庫中寫一個存儲過程，使用批處理命令調用它，然后定期執(zhí)行該批處理命令。具體步驟如下：
一、準備數據歸檔
數據直接刪除是不可想象的。我們要做的是，把時間比較早期的數據遷移走，遷移到專用的歷史庫。
1、首先創(chuàng)建一個歷史庫，承接源庫中轉移出來的數據。
create database monkey2022_history;
二、歸檔方式
歸檔可以分為手動歸檔和自動歸檔兩種方式。第一次歸檔的話，可能已經積壓了多年的數據，采用手動歸檔，先遷為快；之后再自動歸檔。無論是手動還是自動，都有3個關鍵步驟：
1）在歷史庫創(chuàng)建一張結構和源表一模一樣的普通表
2）使用了 EXCHANGE PARTITION（分區(qū)交換） 技術將數據歸檔到歷史表
3）刪除源表中已被清空的歷史分區(qū)
三、手動歸檔
1、在歷史庫中創(chuàng)建與待轉移表結構相同，但并非分區(qū)的表
在歷史庫中，為每年建一個表。比如今年是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、創(chuàng)建臨時中轉表
在源庫中創(chuàng)建一個臨時中轉表，將舊分區(qū)數據“秒級”挪出來。
CREATE TABLE monkey2022.monkey_data_tmp LIKE monkey2022.monkey_data; ALTER TABLE monkey2022.monkey_data_tmp REMOVE PARTITIONING;
3、搬遷
– 將 p240101 分區(qū)數據交換到中轉表（此步瞬間完成,原分區(qū)已無數據，是為分區(qū)交換技術）
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、刪除源表的相應分區(qū)
ALTER TABLE monkey2022.monkey_data DROP PARTITION p250701;
5、查看分區(qū)情況
SELECT
PARTITION_NAME AS '分區(qū)名',
PARTITION_EXPRESSION AS '分區(qū)表達式',
TABLE_ROWS AS '行數',
DATA_LENGTH / 1024 / 1024 AS '數據大小(MB)',
TABLE_SCHEMA AS '數據庫名'
FROM
information_schema.PARTITIONS
WHERE
TABLE_NAME = 'monkey_data'
AND TABLE_SCHEMA = 'monkey2022';
注意分區(qū)的日期是截止日期。比如p260701分區(qū)，存放的是2026年上半年的數據（<20260701）。
四、自動歸檔
自動歸檔原理與手動類似，只不過將命令集成到一個存儲過程，然后用批處理定期運行而已。
1、開啟數據庫定期操作
SET GLOBAL event_scheduler = ON;
2、定義存儲過程
核心邏輯拆解
第一步：精準定位“最老且超過 6 個月”的分區(qū)
第二步：動態(tài)構建“四步走”的拼裝 SQL
（1）建影子表：在歷史庫創(chuàng)建一張結構和主表一模一樣的普通表，表名叫 monkey_data_分區(qū)名。
（2）抹去分區(qū)屬性
（3）秒級交換：MySQL 將主表中這個分區(qū)的文件指針，跟影子表的文件指針瞬間做個對調。
（4）徹底刪掉該分區(qū)，立刻釋放磁盤空間
第三步：Debug 安全開關
存儲過程前面是拼出執(zhí)行的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、執(zhí)行
//CALL sp_archive_board_data_debug(1);//只輸出語句不執(zhí)行，便于調試 CALL sp_archive_board_data_debug(0);//真正執(zhí)行
4、調用
1）一次性設置賬號密碼，信息加密保存，以后腳本調用時就不用再寫端口了。
mysql_config_editor set --login-path=db_mgr --host=localhost --port=3306 --user=root --password
2）批處理文件
服務器操作系統為windows server。
@echo off
:: ============================================================
:: 配置區(qū)域：請根據實際安裝路徑修改 MYSQL_PATH
:: ============================================================
set "MYSQL_PATH=C:\Program Files\MySQL\MySQL Server 8.4\bin\mysql.exe"
set "LOG_FILE=D:\monkey2022\db-clean\archive_log.txt"
echo ------------------------------------------------------------ >> "%LOG_FILE%"
echo [%date% %time%] 啟動分區(qū)清理任務... >> "%LOG_FILE%"
:: 調用存儲過程 (0 為正式執(zhí)行模式)
"%MYSQL_PATH%" --login-path=db_mgr -e "CALL monkey2022.sp_archive_board_data_debug(0);" >> "%LOG_FILE%" 2>&1
:: 檢查執(zhí)行狀態(tài)
if %errorlevel% equ 0 (
echo [%date% %time%] 分區(qū)清理指令執(zhí)行成功。 >> "%LOG_FILE%"
) else (
echo [%date% %time%] 分區(qū)清理執(zhí)行出錯，請檢查上方日志。 >> "%LOG_FILE%"
)
echo ------------------------------------------------------------ >> "%LOG_FILE%"
3）將此批處理交由windows的任務計劃執(zhí)行
注意，如果windows的系統管理員的密碼更改，則依賴系統管理員的任務計劃需要重新輸入賬號密碼。所以該任務計劃最好由system賬號運行，不受系統管理員密碼更改影響。
總結
到此這篇關于mysql分區(qū)表自動歸檔的文章就介紹到這了,更多相關mysql分區(qū)表自動歸檔內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
后端開發(fā)中經常需要分頁展示，個時候就需要用到MySQL的LIMIT關鍵字。LIMIT在數據量大的時候極可能造成的一個問題就是深度分頁。本文就介紹一下解決方法，感興趣的可以了解一下2021-07-07
當在SQL、MySQL數據庫中一張表中插入一條記錄時，觸動觸發(fā)器，使同一數據庫的另一張表插入相同記錄。2010-09-09
本文介紹MySQL中通過UPDATE/INSERT結合SELECT語句,將字段值更新為當前最大值加1的方法,涵蓋嵌套子查詢與自定義變量方案,后者更高效適用于批量操作,感興趣的朋友跟隨小編一起看看吧2025-07-07
MySQL 5.6下table_open_cache參數優(yōu)化合理配置詳解
這篇文章主要介紹了MySQL 5.6下table_open_cache參數合理配置詳解,需要的朋友可以參考下2018-03-03
這篇文章主要介紹了如何解決limit 1000000加載慢的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2025-06-06
在本篇文章里小編給大家整理的是一篇關于mysql中寫判斷語句的方法總結內容，需要的朋友們可以學習參考下。2020-09-09
這篇文章主要介紹了MySQL的索引下推,索引下推是為了解決在過濾條件時,可能導致大量的數據行被檢索出來,但實際上只有很少的行滿足WHERE子句中的所有條件的情況,需要的朋友可以參考下2022-07-07
這篇文章主要介紹了Mysql根據時間查詢日期的優(yōu)化技巧，非常不錯，具有參考借鑒價值,需要的朋友可以參考下2018-03-03
這篇文章主要介紹了遠程連接mysql錯誤代碼1130的解決方法,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-12-12
MySQL group by和left join并用解決方式
這篇文章主要介紹了MySQL group by和left join并用解決方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-12-12