# 分区表级冷热分离 #### 操作场景 在大数据处理场景中，随着数据量的不断增长，高效地管理冷热数据已成为一项重要挑战。若未对数据进行有效的冷热分离，不仅会增加存储成本，还会导致查询性能下降。您可以根据实际情况选择通过Shell脚本或存储过程进行冷热分离。 如果您当前的内核版本低于2.0.72.251200，不支持使用存储过程进行冷热分离，推荐您使用Shell脚本进行冷热分离。 如果内核版本号大于等于2.0.72.251200，推荐您使用存储过程进行冷热分离。 - 无分区 如果数据表没有分区，您可以使用TaurusDB控制台或使用SQL设置冷表，具体操作请参考[使用TaurusDB冷热分离](https://support.huaweicloud.com/usermanual-taurusdb/taurusdb_03_0196.html)。 - 有分区 - [通过Shell脚本对分区表定时进行冷数据归档] 以分区为对象，指导您在华为云弹性云服务器ECS上通过Shell脚本定时进行冷数据归档。建议使用[INTERVAL RANGE](https://support.huaweicloud.com/kerneldesc-taurusdb/taurusdb_20_0051.html)分区功能自动拓展分区，结合自动设置冷表，将低频使用的分区的数据归档到OBS上。 - [保留最新N个分区，通过存储过程对其余分区进行冷数据归档] 保留分区表中最新的N个分区不进行归档，以库中其余表为对象，指导您通过DAS连接TaurusDB实例，利用存储过程对各表依次进行冷数据归档。建议使用[INTERVAL RANGE](https://support.huaweicloud.com/kerneldesc-taurusdb/taurusdb_20_0051.html)分区功能自动拓展分区，结合自动设置冷表，将低频使用的分区的数据归档到OBS上。 本章节将详细介绍在分区场景下实施冷热分离的最佳实践。 #### 约束限制 - 使用存储过程对整个库进行冷热分离，要求内核版本号不低于2.0.72.251200。内核版本的查询方法请参见[如何查看云数据库 TaurusDB实例的版本号](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0141.html)。 - 归档的表要满足约束限制，具体请参考[归档冷表](https://support.huaweicloud.com/usermanual-taurusdb/taurusdb_03_0028.html)。 - 以下代码示例仅供参考，请根据业务场景进行测试和验证。 #### 通过Shell脚本对分区表定时进行冷数据归档 1. 创建ECS服务器。 具体操作请参见[创建弹性云服务器](https://support.huaweicloud.com/usermanual-ecs/ecs_03_0112.html)。 ![](https://support.huaweicloud.com/bestpractice-taurusdb/public_sys-resources/note_3.0-zh-cn.png) - 确保和TaurusDB实例配置成相同Region、相同可用区、相同VPC、相同安全组。 - 不用购买数据盘。 2. 登录ECS并下载安装MySQL客户端。 下载安装MySQL客户端的操作请参考[安装MySQL客户端](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0011.html)。 3. 连接TaurusDB实例，查看表结构以及对应归档状态。 下面以sales表为示例： 如下图所示，查看到表sales当前未归档为冷数据。 ![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002661357563.png "点击放大") 4. 通过Shell脚本自动设置冷表。 在ECS上创建如下脚本，设定从当前月份开始，每月1号01:00对表sales的分区进行归档。归档操作将针对除首个和最后一个分区外的所有分区进行；并且每间隔一个月会检查表是否创建了新的分区，并对新分区（除最后一个分区外）进行归档。 以下脚本以归档sales表为示例： ``` #!/usr/bin/sh passwd=****** user="root" ip=*.*.*.* conn="mysql -u$user -h$ip -p$passwd" database=test table=sales start_time=$(date "+%Y-%m-01 01:00:00") last_time=$start_time partition_order=2 while [ true ] do res=$($conn -se"SELECT TIMEDIFF(current_timestamp(),'$last_time') > 0;") if [ $res -gt 0 ]; then partition_nums=$($conn -se"select count(1) from information_schema.partitions where table_schema=\"$database\" and table_name=\"$table\";") if [ $partition_order -ge $partition_nums ]; then last_time=$($conn -se"SELECT DATE_ADD('$last_time',INTERVAL 1 MONTH);") continue fi partition_name=$($conn -se"select PARTITION_NAME from information_schema.partitions where table_schema=\"$database\" and table_name=\"$table\" and PARTITION_ORDINAL_POSITION = $partition_order;") $conn -e"CALL dbms_schs.make_io_transfer(\"start\", \"${database}\", \"${table}\", \"${partition_name}\", \"\", \"obs\");" if [ $? -ne 0 ]; then echo "archive failed" fi partition_order=$(($partition_order+1)) else sleep 1d continue fi done ``` 5. 连接TaurusDB实例，执行存储过程，查看对应表的归档状态。 使用存储过程sys.schs_show_all要求TaurusDB数据库内核版本大于等于2.0.60.241200。如不满足，请先[升级内核小版本](https://support.huaweicloud.com/usermanual-taurusdb/taurusdb_05_2265.html)。内核版本的查询方法请参见[如何查看云数据库 TaurusDB实例的版本号](https://support.huaweicloud.com/taurusdb_faq/taurusdb_faq_0141.html)。 sys.schs_show_all(*database* , *table* , *partition*)是TaurusDB内置的存储过程，三个参数分别表示库名、表名和分区名。 - 如果不指定表名和分区名，则表示查询该库下的所有归档表。 - 如果不指定分区名，则表示查询特定表的所有归档分区。 例如： - 查询某个实例上所有冷表 ``` CALL sys.schs_show_all( "", "", ""); ``` - 查询库名为test的所有冷分区或者冷表 ``` CALL sys.schs_show_all( "test", "", ""); ``` - 查询库名为test，表名为table1的冷分区或者冷表情况 ``` CALL sys.schs_show_all( "test", "table1", ""); ``` 下面以sales表为示例： ``` call sys.schs_show_all('test', 'sales', ''); ``` 当status列显示为FINISH时，表示除首个以及最后一个外的2个分区都归档成功。 ![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002631118262.png "点击放大") 6. 确认磁盘使用量的指标值降低，表示冷表的存储空间已释放（监控采集可能存在延迟）。 ![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002630958354.png "点击放大") #### 保留最新N个分区，通过存储过程对其余分区进行冷数据归档 1. [登录TaurusDB管理控制台](https://console.huaweicloud.com/gaussdbformysql/#/management/list)。 2. 单击管理控制台左上角的![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002671347251.png)，选择区域。 3. 在"实例管理"页面，选择目标实例，单击操作列的"登录"，进入数据管理服务实例登录界面。 您也可以在"实例管理"页面，单击目标实例名称，进入"实例概览"页面。在页面右上角，单击"登录"，进入数据管理服务实例登录界面。 4. 正确输入数据库用户名和密码，单击"测试连接"。 5. 测试连接通过后，单击"登录"，即可进入您的数据库并进行管理。 6. 单击"SQL操作 \> SQL窗口"，执行如下命令，创建存储过程。 确认存储过程中指定的数据库存在，如不存在，请首先[创建数据库](https://support.huaweicloud.com/usermanual-taurusdb/taurusdb_03_0121.html)并导入数据。 ``` DELIMITER // CREATE PROCEDURE archive_table_skip_first_and_retain_n( -- 数据库名称 IN dbName VARCHAR(64), -- 表名称 IN tableName VARCHAR(64), --如果tableName表是分区表，retainPartitionNum 指定保留最新的分区数量 IN retainPartitionNum INT ) BEGIN DECLARE partitionName VARCHAR(64); DECLARE done INT DEFAULT 0; DECLARE totalPartitionCount INT DEFAULT 0; DECLARE currentIndex INT DEFAULT 0; -- 游标声明 DECLARE cur_partitions CURSOR FOR SELECT PARTITION_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = dbName AND TABLE_NAME = tableName AND PARTITION_NAME IS NOT NULL ORDER BY PARTITION_ORDINAL_POSITION; -- 游标结束处理程序 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- ===================== 全局统一处理：库名、表名大小写条件 ===================== SET @schema_cond = ''; SET @table_cond = ''; -- 库名大小写处理 IF dbName IS NOT NULL AND dbName <> '' THEN IF @@global.lower_case_table_names = 1 THEN SET @schema_cond = CONCAT(' AND LOWER(a.schema_name) = \'', LOWER(dbName), '\''); ELSE SET @schema_cond = CONCAT(' AND a.schema_name = \'', dbName, '\''); END IF; END IF; -- 表名大小写处理 IF tableName IS NOT NULL AND tableName <> '' THEN IF @@global.lower_case_table_names = 1 THEN SET @table_cond = CONCAT(' AND LOWER(a.table_name) = \'', LOWER(tableName), '\''); ELSE SET @table_cond = CONCAT(' AND a.table_name = \'', tableName, '\''); END IF; END IF; -- ===================== 获取总分区数 ===================== SELECT COUNT(DISTINCT PARTITION_NAME) INTO totalPartitionCount FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = dbName AND TABLE_NAME = tableName AND PARTITION_NAME IS NOT NULL; -- ===================== 无分区表：先判断是否已归档，已归档则跳过 ===================== IF totalPartitionCount = 0 THEN -- 检查无分区表是否已经归档（partition_name = ''） SET @check_sql = CONCAT( 'SELECT COUNT(*) INTO @is_archived FROM mysql.schs_io_transfer a ', 'INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES c ON a.space_id = c.SPACE AND c.NAME NOT LIKE \'__recyclebin__%\' ', 'WHERE a.space_id != 4294967295 ', 'AND a.target_type = \'OBS\' ', 'AND a.transfer_status = \'FINISH\' ', 'AND a.partition_name = \'\' ', @schema_cond, @table_cond ); PREPARE check_stmt FROM @check_sql; EXECUTE check_stmt; DEALLOCATE PREPARE check_stmt; -- 已归档：直接跳过，不执行 IF @is_archived > 0 THEN SET done = 1; ELSE -- 未归档：执行归档 SET @sql = CONCAT('call dbms_schs.make_io_transfer(\'start\',\'', dbName,'\',\'',tableName,'\',\'\',\'\',\'obs\')'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET done = 1; END IF; END IF; -- ===================== 分区表处理 ===================== IF done != 1 THEN OPEN cur_partitions; partition_loop: LOOP FETCH cur_partitions INTO partitionName; IF done = 1 THEN LEAVE partition_loop; END IF; SET currentIndex = currentIndex + 1; -- 1. 跳过第一个分区 IF currentIndex = 1 THEN ITERATE partition_loop; END IF; -- 2. 保留最新N个分区 IF currentIndex > (totalPartitionCount - retainPartitionNum) THEN ITERATE partition_loop; END IF; -- 3. 判断是否已归档 SET @check_sql = CONCAT( 'SELECT COUNT(*) INTO @is_archived FROM mysql.schs_io_transfer a ', 'INNER JOIN INFORMATION_SCHEMA.PARTITIONS b ON a.schema_name = b.TABLE_SCHEMA AND a.table_name = b.TABLE_NAME ', 'AND (a.partition_name = b.PARTITION_NAME OR (a.partition_name = \'\' AND b.PARTITION_NAME IS NULL)) ', 'INNER JOIN INFORMATION_SCHEMA.INNODB_TABLESPACES c ON a.space_id = c.SPACE AND c.NAME NOT LIKE \'__recyclebin__%\' ', 'WHERE a.space_id != 4294967295 ', 'AND a.target_type = \'OBS\' ', 'AND a.transfer_status = \'FINISH\' ', 'AND a.partition_name = \'', partitionName, '\' ', @schema_cond, @table_cond ); PREPARE check_stmt FROM @check_sql; EXECUTE check_stmt; DEALLOCATE PREPARE check_stmt; -- 已归档则跳过 IF @is_archived > 0 THEN ITERATE partition_loop; END IF; -- 执行归档 SET @sql = CONCAT('call dbms_schs.make_io_transfer(\'start\',\'', dbName,'\',\'',tableName,'\',\'',partitionName,'\',\'\',\'obs\')'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP partition_loop; CLOSE cur_partitions; END IF; -- 清理会话变量 SET @schema_cond = NULL; SET @table_cond = NULL; SET @check_sql = NULL; SET @is_archived = NULL; END // DELIMITER ; ``` 7. （可选）查看当前test归档情况。 下面以sales表为示例： ![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002661347685.png "点击放大") 当前已归档情况： ![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002630948494.png "点击放大") 8. 创建事件定时器，定时调用存储过程。 在DAS上创建如下脚本，设定从4月1日开始，每月1号01:00对表sales的分区进行归档。归档操作将针对除首个和最新的2个分区外的所有分区进行，并且每间隔一个月会检查表是否创建了新的分区，对最新的2个分区外的旧分区进行归档。 ``` CREATE EVENT IF NOT EXISTS event_archive_sales_monthly ON SCHEDULE EVERY 1 MONTH STARTS '2026-04-01 01:00:00' DO CALL archive_table_skip_first_and_retain_n('test', 'sales', 2); ``` 9. （可选）查看对应表的归档状态。 插入数据，结合[INTERVAL RANGE](https://support.huaweicloud.com/kerneldesc-taurusdb/taurusdb_20_0051.html)自动创建分区_p20220501000000,_p20220601000000： ![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002631108406.png "点击放大") 新分区_p20220501000000,_p20220601000000创建后，_p20220301000000,_p20220401000000被自动归档： ![](https://support.huaweicloud.com/bestpractice-taurusdb/figure/zh-cn_image_0000002661467633.png "点击放大")