分区表级冷热分离

操作场景

在大数据处理场景中，随着数据量的不断增长，高效地管理冷热数据已成为一项重要挑战。若未对数据进行有效的冷热分离，不仅会增加存储成本，还会导致查询性能下降。您可以根据实际情况选择通过Shell脚本或存储过程进行冷热分离。

如果您当前的内核版本低于2.0.72.251200的，不支持使用存储过程进行冷热分离，推荐您使用Shell脚本进行冷热分离。

如果内核版本号大于等于2.0.72.251200的，推荐您使用存储过程进行冷热分离。

无分区
 如果数据表没有分区，您可以使用TaurusDB控制台或使用SQL设置冷表，具体操作请参考使用TaurusDB冷热分离。
有分区
- 通过Shell脚本对分区表定时进行冷数据归档
  以分区为对象，指导您在华为云弹性云服务器ECS上通过Shell脚本定时进行冷数据归档。建议使用INTERVAL RANGE分区功能自动拓展分区，结合自动设置冷表，将低频使用的分区的数据归档到OBS上。
- 保留最新N个分区，通过存储过程对其余分区进行冷数据归档
  保留分区表中最新的N个分区不进行归档，以库中其余表为对象，指导您通过DAS连接TaurusDB实例，利用存储过程对各表依次进行冷数据归档。建议使用INTERVAL RANGE分区功能自动拓展分区，结合自动设置冷表，将低频使用的分区的数据归档到OBS上。
本章节将详细介绍在分区场景下实施冷热分离的最佳实践。

约束限制

使用存储过程对整个库进行冷热分离，要求内核版本号不低于2.0.72.251200。内核版本的查询方法请参见如何查看云数据库 TaurusDB实例的版本号。
归档的表要满足约束限制，具体请参考归档冷表。
以下代码示例仅供参考，请根据业务场景进行测试和验证。

通过Shell脚本对分区表定时进行冷数据归档

创建ECS服务器。
具体操作请参见创建弹性云服务器。
- 确保和TaurusDB实例配置成相同Region、相同可用区、相同VPC、相同安全组。
- 不用购买数据盘。
登录ECS并下载安装MySQL客户端。

下载安装MySQL客户端的操作请参考安装MySQL客户端。
连接TaurusDB实例，查看表结构以及对应归档状态。

下面以sales表为示例：

如下图所示，查看到表sales当前未归档为冷数据。

通过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

连接TaurusDB实例，执行存储过程，查看对应表的归档状态。

使用存储过程sys.schs_show_all要求TaurusDB数据库内核版本大于等于2.0.60.241200。如不满足，请先升级内核小版本。内核版本的查询方法请参见如何查看云数据库 TaurusDB实例的版本号。

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个分区都归档成功。
确认磁盘使用量的指标值降低，表示冷表的存储空间已释放（监控采集可能存在延迟）。

保留最新N个分区，通过存储过程对其余分区进行冷数据归档

登录TaurusDB管理控制台。
单击管理控制台左上角的，选择区域和项目。
在“实例管理”页面，选择目标实例，单击操作列的“登录”，进入数据管理服务实例登录界面。

您也可以在“实例管理”页面，单击目标实例名称，进入“实例概览”页面。在页面右上角，单击“登录”，进入数据管理服务实例登录界面。
正确输入数据库用户名和密码，单击“测试连接”。
测试连接通过后，单击“登录”，即可进入您的数据库并进行管理。

单击“SQL操作 > SQL窗口”，执行如下命令，创建存储过程。

确认存储过程中指定的数据库存在，如不存在，请首先创建数据库并导入数据。

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 ;

（可选）查看当前test归档情况。

下面以sales表为示例：

当前已归档情况：
创建事件定时器，定时调用存储过程。

在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);
```
（可选）查看对应表的归档状态。

插入数据，结合INTERVAL RANGE自动创建分区_p20220501000000,_p20220601000000：

新分区_p20220501000000,_p20220601000000创建后，_p20220301000000,_p20220401000000被自动归档：

父主题： TaurusDB冷热分离最佳实践

上一篇：TaurusDB冷热分离最佳实践

下一篇：库级冷热分离

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！

系统繁忙，请稍后重试