本文通过介绍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及以上。
按分区归档的版本要求
引擎版本为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及以上。
添加TTL定义
在PolarDB-X中,您可以通过ALTER
语句为表指定TTL定义,以告诉数据库这个表的数据过期规则。被添加TTL定义的表,下文中统一称为TTL表。
添加TTL定义,仅会修改在线表的TTL元数据,不会引起任何的数据清理或分区变更,不会对在线表的在线业务产生任何影响。
TTL定义仅支持AUTO模式数据库的分区表(不包括使用
Local Partition
的分区表)。
语法
ALTER TABLE table_name
MODIFY TTL
SET
TTL_EXPR = xxx
[,TTL_JOB = xxx]
[,TTL_PART_INTERVAL = xxx]
[,TTL_FILTER = xxx]
[,TTL_ENABLE = xxx]
[,TTL_CLEANUP = xxx]
[,ARCHIVE_TYPE = xxx]
[,ARCHIVE_TABLE_PRE_ALLOCATE = xxx]
[,ARCHIVE_TABLE_POST_ALLOCATE = xxx]
;
各参数详细说明,请参见TTL定义说明。
示例
创建示例分区表my_ttl_tbl
:
CREATE TABLE `my_ttl_tbl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date_field` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`id`)
PARTITIONS 8;
添加TTL定义:
ALTER TABLE my_ttl_tbl
MODIFY TTL
SET
TTL_EXPR = `date_field` EXPIRE AFTER 2 MONTH TIMEZONE '+08:00';
TTL定义说明
PolarDB-X 2.0的TTL定义主要由四部分组成,分别是TTL_EXPR、TTL_JOB(可选)、TTL_PART_INTERVAL(可选)、TTL_FILTER(可选)。
TTL_EXPR
用于定义TTL的时间列、数据存活时间间隔以及数据的过期策略。
语法
TTL_EXPR = data_expiration_expr_definition data_expiration_expr_definition: data_expire_by_time_defition | data_expire_by_part_count_defition data_expire_by_time_defition: ttl_time_column_expr EXPIRE AFTER data_expire_expr_definition timezone_definition ttl_time_column_expr: ttl_time_col | FROM_UNIXTIME(ttl_ts_int_col) | FROM_UNIXTIME(ttl_milli_ts_int_col/1000) data_expire_expr_definition: EXPIRE AFTER ttl_expire_interval_definition ttl_expire_interval_definition: expire_after_interval_count interval_timeunit_expr interval_timeunit_expr: DAY | MONTH | YEAR timezone_definition: TIMEZONE time_zone_value time_zone_value: '[+-]HH:MM' expire_after_interval_count: int_number data_expire_by_part_count_defition: ttl_int_col data_expire_over_definition data_expire_over_definition: EXPIRE OVER expire_over_partition_count PARTITIONS expire_over_partition_count: int_number
data_expire_by_time_defition:按照固定的时间间隔自动过期,即TTL表的数据若存活超过指定的时间间隔,将被认为是过期数据,通常用于按时间归档,下文统一将这种过期策略称为EXPIRE AFTER过期策略。
ttl_time_col:表示TTL列,支持类型如下:
时间类型:DATE、DATETIME、TIMESTAMP。
整数类型:INT、BIGINT。
ttl_ts_int_col:特指具有以秒为单位的时间戳(例如:UNIX系统的时间戳)含义的整数类型的时间列,
FROM_UNIXTIME(ttl_ts_int_col)
表示将时间戳的整数转为实际时间,并判断是否过期。ttl_milli_ts_int_col:特指具有以毫秒为单位的时间戳含义(例如:JAVA时间戳)的整数类型的时间列,因此
FROM_UNIXTIME(ttl_ts_int_col/1000)
表示将毫秒时间戳的整数转为实际时间进行判断是否过期。ttl_expire_interval_definition:数据存活的时间周期,格式为
IntervalNumber IntervalTimeUnit
,当(now() - ttl_time_column_expr) > ttl_expire_interval_definition
时,满足条件的数据将会被自动清理。其中IntervalTimeUnit只支持DAY/MONTH/YEAR
三种时间单位。示例:TTL_EXPR = `ttl_col` EXPIRE AFTER 3/*intervalNumber*/ MONTH /*intervalTimeUnit*/ TIMEZONE '+08:00'
其中过期时间间隔数目
IntervalNumber
的值为3,过期时间间隔单位IntervalTimeUnit
的值为MONTH
。过期时间间隔单位
IntervalTimeUnit
是与清理时间区间的上界取值的截断及标准化处理有密切相关,详细信息,请参见TTL表冷数据清理算法。IntervalTimeUnit是
TTL_PART_INTERVAL
表达式的默认时间单位,如果TTL_PART_INTERVAL
未被显式设置,则系统会采用IntervalTimeUnit
为分区间隔单位。
timezone_definition:定义TTL列的时区,TTL表定时任务需要根据时区来计算是否过期,TTL列为TIMESTAMP类型时必须指定时区,仅支持
'[+-]HH:MM'
格式。
data_expire_by_part_count_defition:按照固定的RANGE分区数目自动过期,即TTL表最多保留
expire_over_partition_count
个分区,会删除最小的RANGE分区,通常用于按分区归档,下文统一将这种过期策略称为EXPIRE OVER过期策略。通常该策略还需要与TTL_PART_INTERVAL属性配合使用。ttl_int_col:特指整数类型的TTL列。
expire_over_partition_count:最多保留的固定分区数目。
示例
TTL列类型为DATETIME类型,TTL表保留最近3个月数据。
TTL_EXPR = `<ttl_col>` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00'
TTL列类型为TIMESTAMP类型,TTL列使用的时区是东十时区,TTL表保留最近30天数据。
TTL_EXPR = `<ttl_col>` EXPIRE AFTER 30 DAY TIMEZONE '+10:00'
TTL列类型为INT类型,TTL列是UNIX时间戳(以秒为单位)的整数,TTL列使用的时区是零点时区,TTL表保留最近7天数据。
TTL_EXPR = FROM_UNIXTIME(`<ttl_col>`) EXPIRE AFTER 7 DAY TIMEZONE '+00:00'
TTL列类型为INT类型,TTL列是毫秒时间戳的整数,TTL列使用的时区是东八时区,TTL表保留最近60天数据。
TTL_EXPR = FROM_UNIXTIME(`<ttl_col>`/1000) EXPIRE AFTER 60 DAY TIMEZONE '+08:00'
TTL列类型为BIGINT类型,TTL列是业务递增的流水单号,TTL表的RANGE分区最多保留16个分区。
TTL_EXPR = `<ttl_col>` EXPIRE OVER 16 PARTITIONS
data_expiration_expr_definition支持以下两种数据过期策略:
TTL_JOB
用于定义TTL定时任务提交的时间点和频率,该属性为可选,默认值为'0 0 1 */1 * ? *'
(每天凌晨一点调用一次)。
语法
TTL_JOB = quartz_cron_expr_definition[timezone_definition] quartz_cron_expr_definition : CRON cron_expr_value timezone_definition : TIMEZONE time_zone_value time_zone_value: '[+-]HH:MM'
参数说明
名称
说明
cron_expr_value
系统清理任务的时间点和频率,使用QUARTZ框架的CRON表达式表示,该表达式由以下七部分组成:
秒(0-59)
分钟(0-59)
小时(0-23,用
*
表示每小时)日期(1-31,表示月份中的某一天,用
*
表示每天)月份(1-12或JAN、FEB、MAR、APR、...、DEC,用
*
表示每月)星期中的日期(1-7或SUN、MON、TUE、WED、THU、FRI、SAT,1代表星期日,?表示占位符不设置)
年份(用
*
表示每一年)
timezone_definition(可选)
timezone_definition
中的time_zone_value
固定为'+08:00'
,即默认后台TTL表定时任务的调度均衡按东八区时间进行计算,暂不支持修改为其它时区。重要因为定时清理任务运行占用一定的资源,所以PolarDB-X 2.0不支持将定时任务的周期设置为分钟级或秒级,也不建议设置为小时级别,以防止清理任务运行过于频繁影响业务稳定性。
示例
TTL后台的清理任务每天凌晨一点调用一次:
TTL_JOB = CRON '0 0 1 */1 * ? *' TIMEZONE '+08:00'
注意事项
TTL_JOB
定义的是TTL表定时任务被定时自动提交的时间点及频率,但TTL表定时任务的实际运行还要受到可运维窗口(默认窗口为02:00~05:00)的限制。本轮可运维窗口未执行完成的TTL表定时任务会自动暂停,并且在下一轮可运维窗口继续执行。例如:
TTL_JOB = CRON '0 0 1 */1 * ? *' TIMEZONE '+08:00'
,若可运维时间窗口是02:00~05:00,TTL表定时任务会在每天的01:00发起任务并提交,但任务实际会等到02:00才开始正式运行,并且若在05:00TTL表定时任务还没执行完成,TTL表定时任务将自动进入暂停状态,并等到第二天02:00到继续运行。TTL表定时任务尽量在可运维窗口时间内执行完毕,否则可能会导致残留的数据积累的越来越多。
TTL_PART_INTERVAL
用于指定TTL表及其归档表的相邻两个分区之间的时间间隔或数值间隔。该属性生效的三个主要场景:
TTL表在采用按分区归档时,定时任务给TTL表自动预建新的RANGE分区,新建分区之间时间间隔即是
TTL_PART_INTERVAL
的间隔;TTL表在创建归档表以保存归档数据时,数据库会为归档表自动构建RANGE分区定义,这些RANGE分区的时间间隔就是
TTL_PART_INTERVAL
的间隔。TTL表在创建归档表后,定时任务给归档表自动预建新的RANGE分区,新建分区之间时间间隔即是
TTL_PART_INTERVAL
的间隔。
该属性通常与TTL_EXPR配合一起使用。
语法
TTL_PART_INTERVAL = INTERVAL(int_value, interval_time_unit) interval_time_unit : DAY | MONTH | YEAR | NUMBER
说明其中NUMBER(整数)为间隔单位时,TTL列需为整数类型且使用EXPIRE OVER的过期策略,且取值必须是正整数。
关于TTL_PART_INTERVAL的默认值,按分区归档时,如果仅指定TTL_EXPR的过期时间间隔(例如:
`ttl_col` EXPIRE AFTER 3 MONTH TIMEZONE '+08:00'
),没有显式指定TTL_PART_INTERVAL,其默认值按照如下方式定义:TTL_PART_INTERVAL的时间间隔单位与TTL_EXPR的过期时间间隔单位相同。
TTL_PART_INTERVAL的间隔数默认为1。
示例
按年分区(一年一个分区)。
TTL_PART_INTERVAL = INTERVAL(1, YEAR)
按月分区(一个月一个分区)。
TTL_PART_INTERVAL = INTERVAL(1, MONTH)
按周分区(7天一个分区)。
TTL_PART_INTERVAL = INTERVAL(7, DAY)
按天分区(一天一个分区)。
TTL_PART_INTERVAL = INTERVAL(1, DAY)
按整数范围分区(按间隔100000为一个分区)
TTL_PART_INTERVAL = INTERVAL(100000, NUMBER)
TTL_FILTER
对于使用了按行归档的TTL表,该属性可供您指定额外的业务过滤条件,让TTL任务仅清理满足业务特殊查询条件的过期数据。
语法
TTL_FILTER = COND_EXPR (query_cond_expr)
query_cond_expr指的是普通DELETE语句中的WHERE子句的条件表达式,默认值为
''
,与TTL按行清理的时间条件是AND
关系。例如:当前正在清理的时间范围是`ttl_col` < '2025-05-01'
,query_cond_expr是`status`=1
,即实际的清理过滤条件是(( `ttl_col` < '2025-05-01' ) AND ( `status` = 1))
。因为query_cond_expr
的条件表达式不能使用TTL列的索引,所以建议您尽量简化query_cond_expr条件表达式,复杂的条件表达式可能会影响按行清理速度。重要清理任务默认会将TTL列为NULL值的数据行当做最小值并清理,您可以设置
TTL_FILTER = COND_EXPR( <ttl_col_name> IS NOT NULL ))
后,不再自动清理TTL列为NULL值的数据行,该设置仅支持实例版本polardb-2.5.0_5.4.20-20250328_xcluster5.4.20-20250221
(MySQL 5.7)及以上或polardb-2.5.0_5.4.20-20250328_xcluster8.4.20-20250304
(MySQL 8.0)及以上。示例
数据清理规则只想应用于被标记为完成状态(status=1)的过期数据:
TTL_FILTER = COND_EXPR(`status` = 1)
其他定义说明
名称 | 说明 | 示例 |
TTL_ENABLE | TTL表的定时任务开关。取值范围如下:
|
|
TTL_CLEANUP | 清理TTL表的过期数据的开关。取值范围如下:
说明
|
|
ARCHIVE_TYPE(必填) | TTL表的归档类型,可取值如下:
|
|
ARCHIVE_TABLE_PRE_ALLOCATE | TTL表定时任务提前给TTL表或归档表的预建 |
|
ARCHIVE_TABLE_POST_ALLOCATE | TTL表在创建新的归档表时,TTL给归档表的过去时间自动补建的 |
|
TTL定义示例
PolarDB-X的TTL目前支持以下两种的归档方式:
按行归档:TTL表使用DELETE的DML语句清理过期数据, 且所有被清理的过期数据走列存提前归档;
按分区归档:TTL表使用
DROP (SUB)PARTITION
的DDL语句清理过期数据,且所有被清理的过期分区的数据走列存提前归档。在按分区归档场景下,按数据过期策略,又可细分为2种:EXPIRE AFTER策略:数据按固定的时间间隔过期,即数据存活超过指定的时间间隔后,将被视为过期数据;
EXPIRE OVER策略:数据按固定的分区数目过期,即RANGE分区数目超过指定的数量后,默认将最小的分区视为过期数据,该策略通常适用于TTL列是整数类型的数据归档。
以下是两种归档方式在各种场景的TTL定义的使用示例。
按行归档
TTL表首先将过期数据通过列存索引提前上传或同步到OSS,完成归档;再使用DELETE的DML语句清理过期数据。
由于按行归档时清理使用的DELETE语句,清理逻辑因TTL列的不同数据类型(主要是时间类型及整数类型)而有所不同,下边分别以TTL列是时间类型及TTL列是整数类型举例说明。
按分区归档
TTL表首先将过期数据通过列存索引提前上传或同步到OSS,完成归档(如果没有配置归档表则没有这一步);再使用DROP (SUB)PARTITION
的DDL语句清理过期数据。
由于按分区归档使用的DROP (SUB)PARTITION
的DDL语句清理过期的RANGE分区,所以下边分别以按一级分区归档及按二级分区归档举例说明。
按一级分区归档
数据按时间间隔自动过期示例
数据按分区数目自动过期示例
按二级分区归档
数据按时间间隔自动过期示例
数据按分区数目自动过期示例
调整TTL定义示例
TTL定义支持您通过SQL进行调整,但只会在N+1轮及以后生效。
调整任务开关(TTL_ENABLE)
默认情况下TTL_ENABLE是OFF(关闭)状态,如需开启该表的TTL任务的自动调度,可使用如下SQL:
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_ENABLE = 'ON';
其中表my_ttl_tbl
必须添加了TTL定义。
调整数据清理开关(TTL_CLEANUP)
TTL_CLEANUP在默认情况下是OFF(关闭)状态。当TTL_CLEANUP = 'OFF'
时,每次TTL任务将不会清理任何数据或删除任何过期的分区,但会自动为TTL表或归档表补充新的分区。因此,如果需要创建归档表以保存归档的历史数据,强烈建议只有当归档表创建完成后,再显式打开TTL表的这个数据清理开关,以避免在还没归档完成时,出现数据清理现象。
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_CLEANUP = 'ON';
调整数据过期定义(TTL_EXPR)
假设表my_ttl_tbl
的TTL_EXPR定义是:
TTL_EXPR = `date_field` EXPIRE AFTER 2 YEAR TIMEZONE '+08:00';
将TTL的过期时期间间隔改为保留最近1年,通过重新设置TTL_EXPR的定义来修改:
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_EXPR = `date_field` EXPIRE AFTER 12 MONTH TIMEZONE '+08:00';
调整调度任务频率(TTL_JOB)
调整清理任务的调度频率为每天03:00:
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_JOB = CRON '0 0 3 */1 * ? *' TIMEZONE '+08:00';
TTL任务的实际运行还会受运维时间窗口的时间限制,即所有自动触发的后台任务只能在每天指定运维时间区间内才能运行,默认的运维时间窗口是02:00~05:00(东八区时间)。
调整分区间隔(TTL_PART_INTERVAL)
调整分区间隔为一月一个分区(INTERVAL(1,MONTH
)):
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_PART_INTERVAL = INTERVAL(1,MONTH);
在按分区归档场景,TTL_PART_INTERVAL同时会对TTL表及其归档表生效。这是因为TTL表及其归档表会同步地自动增加分区,两者分区会一直保持对齐。比如,TTL任务准备给TTL表预建
'2025-06-01'
的新分区,那么若TTL表存在对应归档表的话,它的归档表也会同时地增加'2025-06-01'
的新分区。在按行归档场景,TTL_PART_INTERVAL只对归档表生效。这是因为归档表是强制按TTL列进行范围分区,而原来的TTL表的分区方案保持不变。
调整附加过滤条件(TTL_FILTER)
按行归档可以动态调整TTL表在数据清理时所用的过滤条件。例如:仅清理订单状态为已完成的数据(`order_status`=1
):
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_FILTER=COND_EXPR(`order_status`=1);
过滤条件列必须存在,否则清理任务开始后会报错。
过滤条件可以使用索引优化查询性能,建议使用索引列当作过滤条件。
不建议使用复杂的过滤条件(例如:子查询、大批量IN查询),以免影响清理性能。
过滤条件表达式和TTL列过期时间条件(
ttl_col < 'yyyy-MM-dd'
)是强制AND关系,即TTL_FILTER只能对过期数据进行筛选。
清空所有TTL_FILTER所定义的附加过滤条件:
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
TTL_FILTER=COND_EXPR(TRUE);
调整归档类型(ARCHIVE_TYPE)
您可以通过如下SQL动态调整TTL表的归档策略(即切换按分区归档或按行归档):
-- 修改为按行归档
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
ARCHIVE_TYPE = 'ROW';
按行归档转为按分区归档之前,需确保TTL表是按TTL列进行分区的,否则转换失败。
调整预建分区数(ARCHIVE_TABLE_PRE_ALLOCATE)
调整TTL表预建分区数目为3个:
ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET
ARCHIVE_TABLE_PRE_ALLOCATE = 3;
清除TTL定义
ALTER TABLE `my_ttl_tbl` REMOVE TTL;
TTL表下有归档表时,不允许直接清除TTL定义,需先删除其下的归档表。