创建表级冷热存储策略

本文将向您介绍如何使用 SQL 语句创建表级冷热存储策略（STORAGE_CACHE_POLICY）：

• 创建普通表的冷热存储策略。
• 创建索引表的冷热存储策略。

注意事项

在共享存储模式下，才可以在创建表/索引时指定冷热存储策略。

创建普通表的冷热存储策略

创建表时指定表级别冷热存储策略是在 CREATE TABLE 语句后增加表选项 STORAGE_CACHE_POLICY，语法格式如下：

CREATE TABLE table_name (table_definition_list)
    STORAGE_CACHE_POLICY (storage_cache_policy_option);

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "cold"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit
    | MIXED_RETENTION = intnum retention_time_unit
    | GRANULARITY = {"partition" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

相关参数说明

STORAGE_CACHE_POLICY 为可选项，如果不指定 STORAGE_CACHE_POLICY 选项，则建表时默认使用租户配置项 default_storage_cache_policy 的值。storage_cache_policy_option 中不同属性以 Key-Value 格式定义。各个属性的语意如下：

• GLOBAL = {"hot" | "auto" | "cold"}：用于指定全表数据的冷热存储策略，取值如下：

  • hot：表示指定表的所有数据都为热数据，在缓存盘空间足够的情况下，表下所有数据将会缓存到本地缓存盘。
  • auto：表示指定表的热数据为系统自动识别。
  • cold：表示指定表数据为冷数据，不会缓存到本地缓存盘，对于已经持久化到缓存的数据会主动触发缓存淘汰，后续访问的表数据可以存储在宏块内存缓存，不会持久化到宏块缓存和微块缓存。

• timeline_strategy_list：表示时间轴策略参数列表，各参数间使用英文逗号分隔。时间轴策略支持通过时间来判断分区/宏块数据冷热存储的机制，系统根据配置的策略，自动调整本地缓存盘上的数据。

  说明

  时间轴：按照 Range 分区定义的分区范围取值进行热数据缓存，当满足一定时间条件时，该分区的数据被判定为热数据。使用时间轴策略时，需要注意以下事项：

  • 仅支持 Range 分区表使用（Range 分区为一级或者二级都可以），因为需要时间来确定是否过期，但不能是双 Range 分区，会因为无法确定以哪个 Range 分区时间为准。
  • 使用时间轴时，分区表达中只能有列名，不支持表达式转换。例如 PARTITION BY RANGE COLUMNS(expr(col3))。
  • BOUNDARY_COLUMN 必须为分区键，如果分区键有多列，BOUNDARY_COLUMN 需为第一列，用来判断分区是否过期。

  • BOUNDARY_COLUMN = column_name：用于判定热数据的列。支持整型（BIGINT 或者 INT 类型，格式为 Unix 时间戳）和时间类型（TIMESTAMP、DATE、DATETIME 或者 YEAR 类型）。

    • 如果 BOUNDARY_COLUMN 类型为整型时，表的分区方式支持 Range/Range Columns 分区类型。
    • 如果 BOUNDARY_COLUMN 类型为时间类型时，表的分区方式只能使用 Range Columns 分区类型。

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}：用于指定参数 BOUNDARY_COLUMN 的时间单位。仅支持 BOUNDARY_COLUMN 为整型时设置此参数，整型数值用作时间戳时，需要给定时间戳单位，否则可能会导致错解析出来错误的时间。取值如下：

    • 当分区列为 INT 类型时，BOUNDARY_COLUMN_UNIT 取值只能为 s。

    • 当分区列为 BIGINT 类型时，BOUNDARY_COLUMN_UNIT 取值可以是 s 或者 ms。

      注意

      如果格式非 Unix 时间戳，将不能正确识别 INT 类型表示的时间。

  • HOT_RETENTION = intnum retention_time_unit：用于配置热数据的时间范围。

    • intnum：表示整数。

    • retention_time_unit：表示时间单位，取值如下：

      • YEAR：表示年。
      • MONTH：表示月。
      • WEEK：表示周。
      • DAY：表示天。
      • HOUR：表示小时。
      • MINUTE：表示分钟。

  注意

  参数 HOT_RETENTION 和 MIXED_RETENTION 在时间轴语法中至少要设置一个。

  • MIXED_RETENTION = intnum retention_time_unit：

    • 如果设置 MIXED_RETENTION，则表示距离 HOT_RETENTION + MIXED_RETENTION 的时间范围后的数据都是 cold 数据。
    • 如果不设置 MIXED_RETENTION，则到达 HOT_RETENTION 之后，就一直为 auto 数据。
    • 如果没有设置 HOT_RETENTION, 则表示 [0, MIXED_RETENTION] 的时间范围为 auto 数据，MIXED_RETENTION 之后的为 cold 数据。

  • GRANULARITY = {"partition" | "block"}：可选项，用于指定数据冷热存储的操作粒度，取值如下：

    • partition：表示缓存数据的策略按照分区粒度处理。

    • block：表示缓存数据的策略按照宏块粒度处理。

      说明

      在没有分区表的情况下，需要手动指定一段时间内数据为热点数据时，可以指定 block。详细使用宏块冷热策略的介绍，参见 创建宏块级冷热存储策略。

示例

• 手动方式指定热点数据。

  obclient> CREATE TABLE tbl1 (
      col1 INT, 
      col2 INT)
      STORAGE_CACHE_POLICY (GLOBAL = "hot");
  

• 创建表 tbl2_date，指定 DATE（DATE/DATETIME/TIMESTAMP/YEAR/DAY 等）时间属性的列，热数据维持 1 个月后，直接变为冷数据。

  obclient> CREATE TABLE tbl2_date (
      col1 BIGINT NOT NULL,
      col2 VARCHAR(50),
      col3 DATE NOT NULL)
      STORAGE_CACHE_POLICY (
          BOUNDARY_COLUMN = col3,
          HOT_RETENTION = 1 MONTH,
          MIXED_RETENTION = 0 HOUR)
      PARTITION BY RANGE COLUMNS(col3) (
          PARTITION M202001 VALUES LESS THAN('2020/02/01'),
          PARTITION M202002 VALUES LESS THAN('2020/03/01'),
          PARTITION M202003 VALUES LESS THAN('2020/04/01'),
          PARTITION MMAX VALUES LESS THAN MAXVALUE);
  

• 创建表 tbl2_int，指定 BIGINT（INT/BIGINT 等）整型的列，需要指定 BOUNDARY_COLUMN_UNIT，默认按照 UTC 时间戳处理。热数据维持 1 个月后，变为 auto 数据，一周以后再转为冷数据。

  obclient> CREATE TABLE tbl2_int (
      col1 BIGINT NOT NULL,
      col2 VARCHAR(50),
      col3 BIGINT NOT NULL)
      STORAGE_CACHE_POLICY (
          BOUNDARY_COLUMN = col3,
          BOUNDARY_COLUMN_UNIT = "ms",
          HOT_RETENTION = 1 MONTH,
          MIXED_RETENTION = 1 WEEK)
      PARTITION BY RANGE COLUMNS(col3) (
          PARTITION M202001 VALUES LESS THAN('2020/02/01'),
          PARTITION M202002 VALUES LESS THAN('2020/03/01'),
          PARTITION M202003 VALUES LESS THAN('2020/04/01'),
          PARTITION MMAX VALUES LESS THAN MAXVALUE);
  

创建索引表的冷热存储策略

可以使用以下方式指定索引冷热存储策略：

• 创建索引时，指定索引的表级冷热存储策略。
• 创建表时创建索引，指定索引的表级冷热存储策略。
• 给已有表增加索引时，指定索引的表级冷热存储策略。

更多有关创建索引的详细介绍，参见 创建索引。

创建索引时，指定索引的表级冷热存储策略

语法格式如下：

CREATE INDEX index_name ON table_name (column_name)
    STORAGE_CACHE_POLICY (storage_cache_policy_option);

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "cold" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit
    | MIXED_RETENTION = intnum retention_time_unit
    | GRANULARITY = {"partition" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

创建表时创建索引，指定索引的表级冷热存储策略

语法格式如下：

CREATE TABLE table_name (
    column_name column_definition,
    [column_name column_definition,...],
    INDEX index_name(column_name) STORAGE_CACHE_POLICY (storage_cache_policy_option)
    );

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "cold" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit
    | MIXED_RETENTION = intnum retention_time_unit
    | GRANULARITY = {"partition" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

给已有表增加索引时，指定索引的表级冷热存储策略

语法格式如下：

ALTER TABLE table_name ADD INDEX index_name(column_name)
    STORAGE_CACHE_POLICY (storage_cache_policy_option);

storage_cache_policy_option:
    GLOBAL = {"hot" | "auto" | "cold" | "none"}
    | timeline_strategy_list

timeline_strategy_list:
    BOUNDARY_COLUMN = column_name
    | BOUNDARY_COLUMN_UNIT = {"s" | "ms"}
    | HOT_RETENTION = intnum retention_time_unit
    | MIXED_RETENTION = intnum retention_time_unit
    | GRANULARITY = {"partition" | "block"}

retention_time_unit:
    YEAR
    | MONTH
    | WEEK
    | DAY
    | HOUR
    | MINUTE

相关参数说明

支持对索引表配置冷热存储策略：

• 对于局部索引，可以指定表级 STORAGE_CACHE_POLICY。如果未指定表级 STORAGE_CACHE_POLICY，则默认为 none，表示使用数据表的 STORAGE_CACHE_POLICY 配置，局部索引分区的冷热存储策略则跟随数据表的分区规则。

• 对于全局索引，除了指定表级的 STORAGE_CACHE_POLICY，如果索引表有分区的话，还可以指定索引表分区级的 STORAGE_CACHE_POLICY。全局索引如果未指定表级 STORAGE_CACHE_POLICY，则默认为 none。

  说明

  在 V4.4.1 及其之后的版本中，全局索引如果未指定表级 STORAGE_CACHE_POLICY，如果主表为 Global 策略，就默认跟随主表的 STORAGE_CACHE_POLICY，如果主表为时间轴策略，则默认为 hot 策略。

设置表级别的 STORAGE_CACHE_POLICY 时，参数 storage_cache_policy_option 中不同属性以 Key-Value 格式定义。各个属性的语意如下：

• GLOBAL = {"hot" | "auto" | "cold" | "none"}：用于指定索引的数据的冷热存储策略，取值如下：

  • hot：表示指定索引为热存储，在缓存盘空间足够的情况下，索引表下所有数据将会缓存到本地缓存盘。
  • auto：表示指定索引表热数据为系统自动识别。
  • cold：表示指定索引表数据为冷数据，不会缓存到本地缓存盘，对于已经持久化到缓存的数据会主动触发缓存淘汰，后续访问的表数据可以存储在宏块内存缓存，不会持久化到宏块缓存和微块缓存。
  • none：默认值，表示该索引的策略跟随主表 STORAGE_CACHE_POLICY 取值。

• timeline_strategy_list：表示时间轴策略参数列表，各参数间使用英文逗号分隔。时间轴策略支持通过时间来判断分区/宏块数据冷热存储的机制，系统根据配置的策略，自动调整本地缓存盘上的数据。

  说明

  时间轴：按照 Range 分区定义的分区范围取值进行热数据缓存，当满足一定时间条件时，该分区的数据被判定为热数据。使用时间轴策略时，需要注意以下事项：

  • 仅支持 Range 分区表使用（Range 分区为一级或者二级都可以），因为需要时间来确定是否过期，但不能是双 Range 分区，会因为无法确定以哪个 Range 分区时间为准。
  • 使用时间轴时，分区表达中只能有列名，不支持表达式转换。例如 PARTITION BY RANGE COLUMNS(expr(col3))。
  • BOUNDARY_COLUMN 必须为分区键，如果分区键有多列，BOUNDARY_COLUMN 需为第一列，用来判断分区是否过期。

  • BOUNDARY_COLUMN = column_name：用于判定热数据的列。支持整型（BIGINT 或者 INT 类型，格式为 Unix 时间戳）和时间类型（TIMESTAMP、DATE、DATETIME 或者 YEAR 类型）。

    • 如果 BOUNDARY_COLUMN 类型为整型时，表的分区方式支持 Range/Range Columns 分区类型。
    • 如果 BOUNDARY_COLUMN 类型为时间类型时，表的分区方式只能使用 Range Columns 分区类型。

  • BOUNDARY_COLUMN_UNIT = {"s" | "ms"}：用于指定参数 BOUNDARY_COLUMN 的时间单位。仅支持 BOUNDARY_COLUMN 为整型时设置此参数，整型数值用作时间戳时，需要给定时间戳单位，否则可能会导致错解析出来错误的时间。取值如下：

    • 当分区列为 INT 类型时，BOUNDARY_COLUMN_UNIT 取值只能为 s。
    • 当分区列为 BIGINT 类型时，BOUNDARY_COLUMN_UNIT 取值可以是 s 或者 ms。

  • HOT_RETENTION = intnum retention_time_unit：用于配置热数据的时间范围。

    • intnum：表示整数。

    • retention_time_unit：表示时间单位，取值如下：

      • YEAR：表示年。
      • MONTH：表示月。
      • WEEK：表示周。
      • DAY：表示天。
      • HOUR：表示小时。
      • MINUTE：表示分钟。

  • MIXED_RETENTION = intnum retention_time_unit：

    • 如果设置 MIXED_RETENTION，则表示距离 HOT_RETENTION + MIXED_RETENTION 的时间范围后的数据都是 cold 数据。
    • 如果不设置 MIXED_RETENTION，则到达 HOT_RETENTION 之后，就一直为 auto 数据。
    • 如果没有设置 HOT_RETENTION, 则表示 [0, MIXED_RETENTION] 的时间范围为 auto 数据，MIXED_RETENTION 之后的为 cold 数据。

  • GRANULARITY = {"partition" | "block"}：可选项，用于指定数据冷热存储的操作粒度，取值如下：

    • partition：表示缓存数据的策略按照分区粒度处理。

    • block：表示缓存数据的策略按照宏块粒度处理。

      说明

      在没有分区表的情况下，需要手动指定一段时间内数据为热点数据时，可以指定 block。详细使用宏块冷热策略的介绍，参见 创建宏块级冷热存储策略。

示例

• 创建索引时，指定索引的表级 STORAGE_CACHE_POLICY：

  1. 创建表 tbl3。

     CREATE TABLE tbl3 (col1 INT, col2 INT, col3 INT);
     

  2. 创建索引 idx1_tbl3 并指定索引表的数据为热数据。

     CREATE INDEX idx1_tbl3 ON tbl3 (col1) LOCAL 
         STORAGE_CACHE_POLICY (GLOBAL = "hot");
     

• 创建表时创建索引，指定索引表的数据为热数据。

  CREATE TABLE tbl4 (col1 INT, col2 INT,
      INDEX idx2 ((col1 + 1)) STORAGE_CACHE_POLICY (GLOBAL = "hot"), UNIQUE KEY ((col1 + col2)));
  

• 给已有表增加索引时，指定索引表的数据为热数据。

  ALTER TABLE tbl4 ADD INDEX idx3(col1, col2) GLOBAL 
      STORAGE_CACHE_POLICY (GLOBAL = "hot");
  

相关文档

• 自定义冷热存储策略概述
• 创建分区级冷热存储策略
• 修改表级冷热存储策略
• 修改分区级冷热存储策略
• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• 自定义冷热存储策略运维指南