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