TaurusDB冷热分离最佳实践

操作场景

本实践针对以下两种场景，提供对应实践教程：

对分区表进行冷热分离场景：通过Shell脚本对分区表定时进行冷数据归档
针对分区表的场景，以分区为对象，指导您在华为云弹性云服务器ECS上通过Shell脚本定时进行冷数据归档。建议使用INTERVAL RANGE分区功能自动拓展分区，结合自动设置冷表，将低频使用的分区的数据归档到OBS上。

没有分区的表可以使用TaurusDB控制台或使用SQL设置冷表，具体操作请参考使用TaurusDB冷热分离。
对整个库进行冷热分离场景：通过存储过程对各个表依次进行冷数据归档

针对整个库归档的场景，将以库中所有表为对象（分区表即为分区），指导您通过DAS连接TaurusDB实例，利用存储过程对各表依次进行冷数据归档。为避免归档时间过长，建议整库不要超过50张表。

此方法要求内核版本号不低于2.0.72.251200。内核版本的查询方法请参见如何查看云数据库 TaurusDB实例的版本号。

使用须知

归档的表要满足约束限制，具体请参考归档冷表。
以下代码示例仅供参考，请根据业务场景进行测试和验证。

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

操作流程

图1 分区级冷热分离流程图

操作步骤

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

通过存储过程对各个表依次进行冷数据归档

操作流程

图2 库表级冷热分离流程图

操作步骤

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

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

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

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

DELIMITER //
CREATE PROCEDURE archive_table_non_first_partition(
    IN dbName VARCHAR(64)  -- 输入参数:指定数据库名称
)
BEGIN
    -- ===================== 所有DECLARE必须放在最开头 =====================
    -- 1. 声明变量
    DECLARE tableName VARCHAR(64);       -- 存储表名
    DECLARE partitionName VARCHAR(64);   -- 存储分区名
    DECLARE done INT DEFAULT 0;           -- 游标循环结束标记
    DECLARE partitionCount INT DEFAULT 0;-- 单个表的分区总数
    DECLARE partitionIndex INT DEFAULT 0;-- 分区遍历索引(用于跳过首个分区)
    -- 2. 声明所有游标(提前集中声明,避免语法错误)
    -- 游标1:获取指定数据库下的所有分区表
    DECLARE cur_partition_tables CURSOR FOR
        SELECT DISTINCT t.TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES t
        INNER JOIN INFORMATION_SCHEMA.PARTITIONS p
            ON t.TABLE_SCHEMA = p.TABLE_SCHEMA
            AND t.TABLE_NAME = p.TABLE_NAME
        WHERE t.TABLE_SCHEMA = dbName
          AND t.TABLE_TYPE = 'BASE TABLE'
          AND p.PARTITION_NAME IS NOT NULL  -- 筛选分区表
        ORDER BY t.TABLE_NAME;
    -- 游标2:获取单个分区表的所有分区(按创建顺序排序)
    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;
    -- 游标3:获取指定数据库下的所有无分区表
    DECLARE cur_non_partition_tables CURSOR FOR
        SELECT t.TABLE_NAME
        FROM information_schema.TABLES t
        WHERE t.TABLE_SCHEMA = dbName
          AND t.TABLE_TYPE = 'BASE TABLE'
          AND NOT EXISTS (  -- 排除分区表
              SELECT 1
              FROM information_schema.PARTITIONS p
              WHERE p.TABLE_SCHEMA = t.TABLE_SCHEMA
                AND p.TABLE_NAME = t.TABLE_NAME
                AND p.PARTITION_NAME IS NOT NULL
          );
    -- 3. 声明游标结束处理程序(仅需声明一次)
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- ===================== 第一部分：处理分区表(非首个分区) =====================
    -- 步骤1:遍历所有分区表
    OPEN cur_partition_tables;
    partition_table_loop: LOOP
        FETCH cur_partition_tables INTO tableName;
        IF done = 1 THEN
            LEAVE partition_table_loop;
        END IF;
        -- 重置变量,统计当前表的分区总数
        SET done = 0;
        SET partitionIndex = 0;
        SELECT COUNT(DISTINCT PARTITION_NAME) INTO partitionCount
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_SCHEMA = dbName
          AND TABLE_NAME = tableName
          AND PARTITION_NAME IS NOT NULL;
        -- 跳过只有1个分区的表(无非首个分区可处理)
        IF partitionCount <= 1 THEN
            ITERATE partition_table_loop;
        END IF;
        -- 步骤2:遍历当前分区表的所有分区
        OPEN cur_partitions;
        partition_loop: LOOP
            FETCH cur_partitions INTO partitionName;
            IF done = 1 THEN
                LEAVE partition_loop;
            END IF;
            -- 跳过首个分区
            SET partitionIndex = partitionIndex + 1;
            IF partitionIndex = 1 THEN
                ITERATE partition_loop;
            END IF;
            -- 拼接并执行分区级归档SQL
            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 LOOP partition_table_loop;
    CLOSE cur_partition_tables;
    -- ===================== 第二部分：处理无分区表 =====================
    -- 重置结束标记(避免继承上一轮游标状态)
    SET done = 0;
    -- 步骤1:遍历所有无分区表
    OPEN cur_non_partition_tables;
    non_partition_table_loop: LOOP
        FETCH cur_non_partition_tables INTO tableName;
        IF done = 1 THEN
            LEAVE non_partition_table_loop;
        END IF;
        -- 拼接并执行无分区表归档SQL(修复参数顺序,和分区表保持一致)
        SET @sql = CONCAT(
            'call dbms_schs.make_io_transfer(\'start\', \'',
            dbName, '\', \'', tableName, '\', \'\', \'\', \'obs\')'
        );
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP non_partition_table_loop;
    CLOSE cur_non_partition_tables;
END //
DELIMITER ;

（可选）执行SQL语句，确认当前库下的表类型。

以test库为例，test库下有分区表t1_p，t2_p, order_info，此外还有三个非分区表t1，t2，t3。
```
SELECT TABLE_SCHEMA,TABLE_NAME,PARTITION_NAME from information_schema.PARTITIONS where TABLE_SCHEMA = 'test';
```
执行存储过程，归档指定库下的所有表。直到所有表归档成功后才会返回结果，整个过程可能耗时较长。
```
call archive_table_non_first_partition('test');
```
执行存储过程，查看冷数据归档信息。

sys.schs_show_all(database, table, partion)是TaurusDB内置的存储过程，三个参数分别表示库名、表名和分区名。
- 如果不指定表名和分区名，则表示查询该库下的所有归档表。
- 如果不指定分区名，则表示查询特定表的所有归档分区。
例如：
- 查询某个实例上所有冷表
```
CALL sys.schs_show_all( "", "", "");
```
- 查询库名为test的所有冷分区或者冷表
```
CALL sys.schs_show_all( "test", "", "");
```
- 查询库名为test，表名为table1的冷分区或者冷表情况
```
CALL sys.schs_show_all( "test", "table1", "");
```
因为TaurusDB冷热分离特性不支持归档分区表的首个分区，因此，表t1_p的首个分区p0，表t2_p的首个（即唯一）分区p0和表order_info的首个分区p_Beijing未进行归档。

下面以test库为示例：
```
call sys.schs_show_all('test', '', '');
```
当status列显示为FINISH时，表示分区表归档成功。
确认磁盘使用量的指标值降低，表示冷表的存储空间已释放（监控采集可能存在延迟）。