为表添加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表,且需包含 |
一个TTL表只允许创建一个归档表。
创建归档表过程中。不影响TTL表的读写。
创建归档表时会触发TTL表的全量数据归档操作(创建列存索引并上传至对象存储),该操作耗时较长,请耐心等待。您可以添加HINT
/*+TDDL:cmd_extra(ENABLE_ASYNC_DDL=true, PURE_ASYNC_DDL_MODE=true)*/
使该操作变为后台异步执行。创建归档表之前时,建议您设置
TTL_ENABLE = 'OFF'
,以关闭自动的数据清理,避免遗漏数据。
示例
为TTL表my_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_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)
)
示例
为未来时间预建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 COLUMNS
、CHANGE COLUMNS
、RENAME COLUMNS
等)。TTL表下有归档表时,不允许直接清除TTL定义,需先删除其下的归档表。