归档表语法说明_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

归档表是用于保存已归档数据的表。TTL表与归档表之间存在强绑定的一对一关系，即每个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及以上。
说明
- 实例版本的规则说明，请参见版本说明。
- 实例版本的查看方式，请参见查看实例版本。
TTL定义仅支持AUTO模式数据库的分区表（不包括使用Local Partition的分区表）。

名词解析

名词	说明
冷数据	在实例中某些数据库表几乎没有更新，且查询频率非常低的数据。
在线表	承载在线流量的业务数据表。
归档表	用于保存已归档数据的表，通常存放在高压缩、低成本的存储介质中，例如OSS。
TTL	全称为“生存时间”（Time To Live），指的是数据在数据库中所能存储的时间长度。在此时间段之后，数据将被自动清理。
TTL表	所有设置了TTL定义的在线表。
TTL列	TTL定义中用于计算数据有效性的时间列。

创建归档表

在通过CREATE TABLE或ALTER TABLE语句创建TTL表后，若对TTL表的历史数据有归档需求，则需为当前TTL表创建相应的归档表。归档表会将TTL表的数据转存至高压缩且低成本的OSS对象存储中，从而有效降低存储成本。

语法定义

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

说明

ENGINE的值必须为'Columnar'，不允许其他值。
ARCHIVE_MODE的值必须为'TTL'，不允许其他值。
CREATE TABLE LIKE语句的源表必须是含有TTL定义的表。同时，必须指定ENGINE = 'Columnar'与ARCHIVE_MODE = 'TTL'。
在创建归档表之前，建议将TTL_ENABLE设置为'OFF'，关闭自动清理任务。详细操作说明，请参见TTL定义操作说明。

语法示例

例如，已经创建了一个名为my_ttl_tbl的TTL表。如果希望为该TTL表创建一个名为my_arc_tbl的归档表，可以使用以下SQL语句：

/*+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';

说明

创建归档表的CREATE TABLE LIKE语句，将触发TTL表的全表数据归档流程（该归档操作本质上是创建列存索引（CCI）并上传至OSS的过程），因此，执行时间通常较长。
上述示例SQL语句中的HINT/*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/，可以使该归档表的创建DDL操作在后台以异步方式执行。在整个归档表创建过程中，原TTL表的读写操作不会受到影响。
有关PolarDB-X的TTL表冷数据归档及清理原理，请参见原理概述。

归档表分区

分区方案

归档表默认会按TTL定义中TTL_EXPR的ttl_column进行Range分区。Range分区的间隔是TTL定义中TTL_EXPR的ttl_interval_definition。
例如，在某个TTL表中，TTL列为date_field。如果数据的存活时间间隔按月进行定义，保留最近3个月的数据。那么归档表的Range分区的间隔就是一个月，一个月对应一个Range分区。示例如下：
```
TTL_EXPR = `date_field` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00'  
```

当创建归档表时，自动生成的Range分区数量及其定义由两个参数共同决定。

ARCHIVE_TABLE_PRE_ALLOCATE：该参数是决定在创建归档表时，最多向未来时间预建多少个分区。
ARCHIVE_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)
)

调整分区参数

若您希望调整ARCHIVE_TABLE_PRE_ALLOCATE或ARCHIVE_TABLE_POST_ALLOCATE的值，以便为未来时间及过去时间创建更多的分区，您可以使用以下语法进行调整：

/**
 * 给未来时间预建 12 个分区，
 * 给过去时间补建 24 个分区，
 */
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET 
ARCHIVE_TABLE_PRE_ALLOCATE=12,
ARCHIVE_TABLE_POST_ALLOCATE=24;

查询归档表

查询归档表定义

创建归档表的过程实质上是一个创建列存索引的过程。当归档表创建完成后，原TTL表中将新增对应的列存索引（名称为arctmp_+归档表表名），这个列存索引就是归档表专用的列存索引。详细说明请参见原理概述。

因此，通过对TTL表执行SHOW CREATE TABLE指令，便可以查询到归档表的列存索引定义及其分区情况。

例如，TTL表my_ttl_tbl的归档表my_arc_tbl所对应的列存索引名为arctmp_my_arc_tbl。归档表my_arc_tbl实质上是一个显式指定列存索引arctmp_my_arc_tbl的查询视图，如下所示：

显示所有表。

show tables;

+------------------+
| Tables_in_ttldb |
+------------------+
| my_ttl_tbl       |
| my_arc_tbl       |
+------------------+
2 rows in set (0.00 sec)

对TTL表执行SHOW CREATE TABLE。

show create table my_ttl_tbl\G;

*************************** 1. row ***************************
       Table: my_ttl_tbl
Create Table: 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
1 row in set (0.00 sec)

对归档表执行SHOW CREATE TABLE。

show create table my_arc_tbl\G;

*************************** 1. row ***************************
                View: my_arc_tbl
         Create View: CREATE VIEW `my_arc_tbl` AS SELECT `my_ttl_tbl`.`id`, `my_ttl_tbl`.`date_field`
FROM `ttldb`.`my_ttl_tbl` FORCE INDEX(`ARCTMP_MY_ARC_TBL`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

查询归档表数据

基于归档表my_arc_tbl这个查询视图，您可以方便地查询TTL表中所有已完成归档的历史数据。例如：

select count(1) from my_arc_tbl;

说明

查询归档表数据时，需要将远端的OSS文件拉取至本地磁盘。这是一项相对耗费I/O资源的操作，在未命中本地OSS缓存的情况下，查询时间可能会较慢。因此，建议业务低频次执行。

删除归档表

TTL表与归档表之间存在强绑定的一对一关系（每个TTL表最多只能创建一个归档表）。因此，删除操作需注意：

若TTL表被删除，其对应的归档表也会被同步删除。
若归档表被删除，其对应的TTL表不会被删除。但是，TTL表的清理任务将变为单纯的数据清理，被清理的数据不会再被归档。

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

例如，TTL表my_ttl_tbl所绑定的归档表为my_arc_tbl，直接删除my_arc_tbl会触发如下报错：

显示所有表。

show tables;

+------------------+
| Tables_in_ttldb |
+------------------+
| my_ttl_tbl       |
| my_arc_tbl       |
+------------------+
2 rows in set (0.00 sec)

对归档表执行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

若您确认需要单独删除归档表，可使用以下DROP TABLE语法来完成此操作。例如，您想要删除名为my_arc_tbl的归档表：

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

说明

DROP TABLE前面所附的HINT/*+TDDL:CMD_EXTRA(TTL_FORBID_DROP_TTL_TBL_WITH_ARC_CCI=false)*/是用于解除上述防止误删除限制的。

DDL相关限制

TTL表与归档表之间存在强绑定的一对一关系（每个TTL表最多只能创建一个归档表）。因此，当TTL表创建归档表后，将会有一些DDL操作的限制，具体如下所示：

含有归档表的TTL表，默认不允许执行普通的CREATE TABLE LIKE语句（即不包含参数ENGINE = 'Columnar' ARCHIVE_MODE = '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;
```
含有归档表的TTL表，默认不允许对TTL列进行列名或列类型等变更，例如MODIFY COLUMNS、CHANGE COLUMNS或RENAME COLUMNS等DDL操作。这是由于TTL列的变更可能会触发归档表的重分区，因此暂时不支持对TTL列进行相关操作。