创建归档表

为表添加TTL定义后,如需高压缩、低成本的存储TTL的所有数据(基于列存索引,存储在远端对象存储(OSS)),您可以为TTL表创建对应的归档表。

版本限制

按行归档的版本要求

  • 引擎版本为MySQL 5.7时,实例版本必须是polardb-2.4.0_5.4.19-20240927_xcluster5.4.19-20240920及以上。

  • 引擎版本为MySQL 8.0时,实例版本必须是polardb-2.4.0_5.4.19-20240927_xcluster8.4.19-20240924及以上。

按分区归档的版本要求

  • 引擎版本为MySQL 5.7时,实例版本必须是polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221及以上。

  • 引擎版本为MySQL 8.0时,实例版本必须是polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304及以上。

说明

创建归档表

语法

CREATE TABLE #archiveTableName 
LIKE #ttlTableName
ENGINE = 'Columnar' ARCHIVE_MODE = 'TTL';

参数说明:

名称

说明

ENGINE

固定值为Columnar。

ARCHIVE_MODE

固定值为TTL。

CRETE LIKE

使用该语法创建归档表时,源表必须是TTL表,且需包含ENGINE = 'Columnar' ARCHIVE_MODE = 'TTL'

重要
  • 一个TTL表只允许创建一个归档表。

  • 创建归档表过程中。不影响TTL表的读写。

  • 创建归档表时会触发TTL表的全量数据归档操作(创建列存索引并上传至对象存储),该操作耗时较长,请耐心等待。您可以添加HINT /*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/使该操作变为后台异步执行。

  • 创建归档表之前时,建议您设置TTL_ENABLE = 'OFF',以关闭自动的数据清理,避免遗漏数据。

示例

TTLmy_ttl_tbl创建归档表:

/*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/
CREATE TABLE `my_arc_tbl`
LIKE `my_ttl_tbl`
ENGINE = 'Columnar' ARCHIVE_MODE = 'TTL';

归档表分区调整

按行归档按分区归档的主要区别是清理方式不同,但归档逻辑时一致的(TTL表所有数据通过列存索引,提前将所有数据实时同步到OSS完成归档)。

归档表的分区方案

归档表默认按照TTL定义中指定的时间列进行RANGE分区,间隔是TTL定义中时间单位。例如:TTL定义为TTL_EXPR = `date_field` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00' ,对应归档表的Range分区的间隔就是一个月。

ARCHIVE_TABLE_PRE_ALLOCATEARCHIVE_TABLE_POST_ALLOCATE分别控制预建分区个数和补建分区个数。例如:假设当前时间是'2024-10-02' ,ARCHIVE_TABLE_PRE_ALLOCATE值为4(向未来预建4个分区), ARCHIVE_TABLE_POST_ALLOCATE值为3(补建最多3个分区),最终归档表分区如下:

PARTITION BY RANGE COLUMNS(`date_field`) (
  PARTITION `pstart` VALUES LESS THAN ('1970-01-02 00:00:00'),
  PARTITION `p202407` VALUES LESS THAN ('2024-08-01 00:00:00'),
  PARTITION `p202408` VALUES LESS THAN ('2024-09-01 00:00:00'),
  PARTITION `p202409` VALUES LESS THAN ('2024-10-01 00:00:00'),
  PARTITION `p202410` VALUES LESS THAN ('2024-11-01 00:00:00'), -- 当前时间 2024-10-02 所在的分区
  PARTITION `p202411` VALUES LESS THAN ('2024-12-01 00:00:00'),
  PARTITION `p202412` VALUES LESS THAN ('2025-01-01 00:00:00'),
  PARTITION `p202501` VALUES LESS THAN ('2025-02-01 00:00:00'),
  PARTITION `pm` VALUES LESS THAN (MAXVALUE)
)

示例

为未来时间预建12个分区,为过去时间补建24个分区:

ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET 
ARCHIVE_TABLE_PRE_ALLOCATE=12,
ARCHIVE_TABLE_POST_ALLOCATE=24;

查询归档表

查看归档表的定义

归档表是基于列存索引的,创建归档的过程中系统会为对应TTL表创建列存索引(列存索引名称格式:arctmp_ + 归档表表名,该列存索引为归档表专用),可以使用SHOW CREATE TABLE语句查看其归档表列存索引的定义和分区。

  • 查看TTL表结构,arctmp_my_arc_tbl为归档表专用列存索引:

    SHOW CREATE TABLE my_ttl_tbl;

    返回结果:

    CREATE TABLE `my_ttl_tbl` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `date_field` datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            CLUSTERED COLUMNAR INDEX `arctmp_my_arc_tbl` (`date_field`)
                    PARTITION BY RANGE COLUMNS(`date_field`)
                    (PARTITION `pstart` VALUES LESS THAN ('1970-01-02 00:00:00') ENGINE = Columnar,
                     PARTITION `p201903` VALUES LESS THAN ('2019-04-01 00:00:00') ENGINE = Columnar,
                     ...
                     PARTITION `p202506` VALUES LESS THAN ('2025-07-01 00:00:00') ENGINE = Columnar,
                     PARTITION `p202507` VALUES LESS THAN ('2025-08-01 00:00:00') ENGINE = Columnar)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 TTL = TTL_DEFINITION( TTL_ENABLE = 'OFF', TTL_EXPR = `date_field` EXPIRE AFTER 2 MONTH TIMEZONE '+08:00', TTL_JOB = CRON '0 0 1 * * ? *' TIMEZONE '+08:00', ARCHIVE_TYPE = 'COLUMNAR', ARCHIVE_TABLE_NAME = 'my_arc_tbl', ARCHIVE_TABLE_PRE_ALLOCATE = 12, ARCHIVE_TABLE_POST_ALLOCATE = 64 )
    PARTITION BY KEY(`id`)
    PARTITIONS 8
  • 查看归档表结构,my_arc_tbl是显式指定列存索引arctmp_my_arc_tbl的查询视图:

    SHOW CREATE TABLE MY_ARC_TBL;

    返回结果:

    CREATE VIEW `my_arc_tbl` AS SELECT `my_ttl_tbl`.`id`, `my_ttl_tbl`.`date_field`
    FROM `ttldb7`.`my_ttl_tbl` FORCE INDEX(`ARCTMP_MY_ARC_TBL`)
    character_set_client: utf8
    collation_connection: utf8_general_ci

查询归档表的数据

基于my_arc_tbl查询视图,可以查询TTL表所有已经完成归档的历史数据:

SELECT COUNT(1) FROM my_arc_tbl;
说明

建议较低频次查询归档表,因为查询归档表时,如果没有命中本地OSS缓存,则需要拉取远端OSS文件到本地磁盘,会占用一定的IO资源,查询速度会相对慢一些。

删除归档表

TTL表与归档表是强绑定的一对一主从关系(一个TTL表最多只允许创建一个归档表):

  • 如果TTL表被删除,它对应的归档表也会被同步删除。

  • 如果归档表被删除,它对应的TTL表不会被删除,但TTL的清理任务将自动变成单纯的数据清理,被清理的数据不会再被归档。

  • 如果归档表没被删除,TTL表不允许删除TTL定义。

默认情况下, 为防误删已归档的历史数据,归档表的DROP TABLE操作会被限制。

DROP TABLE my_arc_tbl;

返回结果:

ERROR 4601 (HY000): [189b8f1eb7cxxxxx][10.57.xxx.xxx:xxxx][ttldb]ERR-CODE: [TDDL-4601][ERR_EXECUTOR] Forbid to drop the ttl-defined table `ttldb`.`my_arc_tbl` with archive cci, please use the hint /*TDDL:cmd_extra(TTL_FORBID_DROP_TTL_TBL_WITH_ARC_CCI=false)*/ to drop this table

可以使用特定语句删除归档表。

/*+TDDL:CMD_EXTRA(TTL_FORBID_DROP_TTL_TBL_WITH_ARC_CCI=false)*/
DROP TABLE my_arc_tbl;
说明

/*+TDDL:CMD_EXTRA(TTL_FORBID_DROP_TTL_TBL_WITH_ARC_CCI=false)*/表示解除该归档的删除限制。

DDL相关限制

TTL表创建归档表后,有如下DDL限制:

  • 对含有归档表的TTL表执行普通CREATE TABLE LIKE复制表时,需要前缀HINT /*+TDDL:CMD_EXTRA(ALLOW_CREATE_TABLE_LIKE_IGNORE_ARCHIVE_CCI=true)*/,以自动去掉归档表的相关定义。例如:

    /*+TDDL:CMD_EXTRA(ALLOW_CREATE_TABLE_LIKE_IGNORE_ARCHIVE_CCI=true)*/
    CREATE TABLE my_tbl2 like my_ttl_tbl;
    说明

    新表my_tbl2会同样带有原表的TTL定义,但新表的调整任务开关(TTL_ENABLE)会改为关闭状态。

  • 因为TTL列变更可能会触发归档表重分区,所以默认不支持对含有归档表的TTL表的TTL列进行列变更DDL操作(MODIFY COLUMNSCHANGE COLUMNSRENAME COLUMNS等)。

  • TTL表下有归档表时,不允许直接清除TTL定义,需先删除其下的归档表。