AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB分布式版 用户指南 企业版（分布式） 冷数据归档（TTL） 最佳实践

最佳实践

更新时间：
产品详情
我的收藏

本文为您介绍冷数据归档如何选择适合的归档方案以及案例分享。

按行归档对比按分区归档

TTL表支持以下两种的归档方案：

  • 按行归档：TTL表使用DELETE的DML语句清理过期数据，并将所有数据以列存索引的形式都归档到OSS。

  • 按分区归档：TTL表使用DROP (SUB)PARTITION的DDL语句清理过期数据，并将所有数据以列存索引的形式都归档到OSS。

上述两种方案的核心区别是主要在于冷数据的清理方式不同，两者各有优劣，可以满足不同的业务场景。

对比项

按行归档

按分区归档

使用约束

  • TTL列支持的类型：

    • 时间类型：DATE、DATETIME、TIMESTAMP。

    • 整数类型：INT、BIGINT。

  • 需要为TTL列创建一个本地索引。

  • 原在线表的分区方案和索引无须任何修改。

  • TTL列支持的类型：

    • 时间类型：DATE、DATETIME、TIMESTAMP。

    • 整数类型：INT、BIGINT。

  • 原在线表需要按TTL列进行RANGE分区（一级分区或二级分区）。

  • 原在线表不能使用任何全局索引。

原理简述

  • 在线表所有数据（包括冷数据与热数据）将被提前归档并上传到OSS。

  • 定期使用DELETE等DML语句对在线表进行扫描，并根据TTL列及过期时间对在线表进行清理。

  • 在线表所有数据（包括冷数据与热数据）将被提前归档并上传到OSS。

  • 定期使用DROP (SUB)PARTITION等DDL语句对在线表过期数据的清理。

优点

  • 使用删分区的DDL清理冷数据，冷数据清理速度较快。

  • 清理过程对CN和DN的CPU/IOPS资源占用较低。

  • 清理后在线表不会产生碎片（无须手动OPTIMIZE TABLE清理碎片），磁盘空间会立即释放。

  • 数据清理过程中不会锁表。

缺点

  • 清理速度相对较慢（约为每小时1000W～2000W行），且会产生Binlog。

  • 清理过程需要占用CN和DN一定量的CPU和IOPS资源（约10%~20%)。

  • 清理后在线表有可能会产生一定量的碎片，需要定期手动或自动执行OPTIMIZE TABLE消除表碎片，磁盘空间才能及时释放。

  • 使用条件约束多，业务表有适配成本：

  • 清理操作无法支持附加用户指定的过滤条件。

说明

综上所述，按行归档与按分区归档各自的主要特点如下：

  • 按行归档：对表无约束，能适用更多业务的归档场景，使用方案，但清理慢。

  • 按行归档：要求在线表使用RANGE分区，不能使用GSI/UGSI，对表约束多，但清理快。

如何选择归档方案

推荐思路

您可以参考如下流程图判断在线表适用按行归档还是按分区归档。

image
说明

综上所述，选择归档方案的原则是业务表能按时间分区就优先使用按分区归档，否则按行归档。

如果您的实例之前已经基于TTL 1.0 的Local Partition完成归档清理并想继续使用，请参见冷数据归档语法。

TTL 1.0冷数据归档是PolarDB-X直接基于MySQL原生分区表实现的冷数据的归档及清理的功能，该方案需要修改表结构（比如，主键及唯一建都要加上时间列），约束比较多，归档过程中会容易锁全表（MySQL的加减分区会锁全表）并有一定概率影响业务，综上TTL 1.0存在一些问题，所以新实例不再推荐使用TTL 1.0。

按行归档如何创建归档表

  1. 按行归档时的推荐TTL定义：

    • TTL_EXPR：TTL列名、过期时间间隔、过期时间间隔单位、TTL列的时区（TTL列是TIMESTAMP类型必须指定时区），选取建议如下：

      预期过期时间间隔

      推荐过期时间间隔的单位

      示例

      清理的时间颗粒度

      < 1个月

      DAY

      • 保留最近7天：TTL_EXPR = `time_col` EXPIRE AFTER 7 DAY TIMEZONE '+08:00'。

      • 保留最近30天：TTL_EXPR = `ttl_col` EXPIRE AFTER 30 DAY TIMEZONE '+08:00'。

      按天清理，只有当整一天的数据都过期才开始清理这一天的数据。例如：保留最近7天数据，假设当前时间是2025-06-10，那么清理范围为time_col < '2025-06-03'。

      <=36个月

      MONTH

      • 保留最近12个月：TTL_EXPR = `time_col` EXPIRE AFTER 12 MONTH TIMEZONE '+08:00'

      • 保留最近3年：TTL_EXPR = `time_col` EXPIRE AFTER 36 MONTH TIMEZONE '+08:00'

      按月清理：只有当整一个月的数据都过期才开始清理这个月的数据。例如：保留最近一个月数据，假设当前时间是2025-06-10，那么清理范围为time_col < '2025-05-01'。

      >36个月

      YEAR

      保留最近4年：TTL_EXPR = `time_col` EXPIRE AFTER 4 YEAR TIMEZONE '+08:00'

      按年清理：只有当整一年的数据都过期才开始清理这一年的数据。例如：保留最近一年数据，假设当前时间是 2025-06-10，那么清理范围为time_col < '2024-01-01'。

    • TTL_JOB：设定清理任务调度频率及其可运维时间窗口，建议根据业务实际低谷期选择。

    • TTL_CLEANUP：是否允许TTL任务清理数据，若TTL表有归档需求，建议等归档表创建完成后再打开，以避免还没归档就开始清理数据。

    • TTL_PART_INTERVAL：定义归档表的相邻两个分区之间时间间隔，若TTL表有归档需求，建议设置该值，推荐实践如下：

      预期热数据保留时间长度

      推荐分区间隔时间单位

      示例

      <= 1个月

      DAY

      • 1天一个分区：TTL_PART_INTERVAL = INTERVAL(1, DAY)。

      • 7天一个分区：TTL_PART_INTERVAL = INTERVAL(7, DAY)。

      >=1个月

      <=36个月

      MONTH

      一个月一个分区：TTL_PART_INTERVAL = INTERVAL(1, MONTH)。

      >=3年

      YEAR

      一年一个分区：TTL_PART_INTERVAL = INTERVAL(1, YEAR)。

    • ARCHIVE_TABLE_PRE_ALLOCATE：定义归档表提前预建多少个分区，若TTL表无归档需求，则可不设置，若TTL表有归档需求，推荐设置如下：

      分区间隔

      推荐预建分区数目

      按天分区：TTL_PART_INTERVAL = INTERVAL(1, DAY)

      7~14（提前1周或2周预建分区），例如：ARCHIVE_TABLE_PRE_ALLOCATE = 7。

      按月分区：TTL_PART_INTERVAL = INTERVAL(1, MONTH)

      3（提前3个月预建分区），例如：ARCHIVE_TABLE_PRE_ALLOCATE = 3。

      按年分区：TTL_PART_INTERVAL = INTERVAL(1, YEAR)

      1~2（提前1~2年预建分区），例如：ARCHIVE_TABLE_PRE_ALLOCATE = 1。

    • ARCHIVE_TABLE_POST_ALLOCATE：定义归档表向过去时间补建多少个分区，仅对创建归档表有效，若TTL表无归档需求，则可不设置，若TTL表有归档需求，推荐设置如下：

      分区间隔

      推荐归档表补建分区数目

      按天分区：TTL_PART_INTERVAL = INTERVAL(1, DAY)

      60~180（归档表只补建最近60天或180的分区），例如：ARCHIVE_TABLE_POST_ALLOCATE = 180。

      按月分区：TTL_PART_INTERVAL = INTERVAL(1, MONTH)

      24~48（归档表只补建最近24～48个月的分区），例如：ARCHIVE_TABLE_POST_ALLOCATE = 48。

      按年分区：TTL_PART_INTERVAL = INTERVAL(1, YEAR)

      4~8（归档表只补建最近4~8年的分区），例如：ARCHIVE_TABLE_POST_ALLOCATE = 8。

    • ARCHIVE_TYPE：定义归档策略，默认是按行归档（即'ROW')，显式设置即可。

  2. 为表添加TTL定义。

    TTL定义的属性确定后，您就可以创建TTL表，示例如下：

    ALTER TABLE　`my_ttl_tbl`
MODIFY TTL
SET
TTL_ENABLE='ON',
TTL_CLEANUP='OFF',
TTL_EXPR=`date_field` EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB=CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00'
TTL_PART_INTERVAL=INTERVAL(1,MONTH),
ARCHIVE_TYPE='ROW',
ARCHIVE_TABLE_PRE_ALLOCATE=3,
ARCHIVE_TABLE_POST_ALLOCATE=24;

  3. TTL列添加本地索引。

    按行归档策略，需要按TTL列进行时间范围的过滤清理数据，为提升性能，请务必要给TTL列提前添加本地索引，示例如下：

    CREATE INDEX `idx_time` ON `my_ttl_tbl`(ttl_ts_col);

  4. （可选）创建归档表。

    如果您对TTL表的过期数据有归档需求，可以为TTL表创建对应的归档表，示例如下：

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

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

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

  5. 启动清理任务：

    ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET 
TTL_ENABLE='ON',
TTL_CLEANUP='ON';

按分区归档如何创建归档表

  1. 按需设置按分区归档TTL定义。

    • TTL_EXPR：TTL列名、过期时间间隔、过期时间间隔单位、TTL列的时区（TTL列是TIMESTAMP类型必须指定时区）。

    • TTL_JOB：设定清理任务调度频率及其可运维时间窗口，建议根据业务实际低谷期选择。

    • TTL_CLEANUP：是否允许TTL任务清理数据，若TTL表有归档需求，建议等归档表创建完成后再打开，以避免还没归档就开始清理数据。

    • TTL_PART_INTERVAL：定义归档表的相邻两个分区之间时间间隔，若TTL表有归档需求，建议设置该值，推荐实践如下：

      预期热数据保留时间长度

      推荐分区间隔时间单位

      示例

      <= 1个月

      DAY

      • 1天一个分区：TTL_PART_INTERVAL = INTERVAL(1, DAY)。

      • 7天一个分区：TTL_PART_INTERVAL = INTERVAL(7, DAY)。

      >=1个月

      <=36个月

      MONTH

      一个月一个分区：TTL_PART_INTERVAL = INTERVAL(1, MONTH)。

      >=3年

      YEAR

      一年一个分区：TTL_PART_INTERVAL = INTERVAL(1, YEAR)。

    • ARCHIVE_TABLE_PRE_ALLOCATE：定义归档表提前预建多少个分区，若TTL表无归档需求，则可不设置，若TTL表有归档需求，推荐设置如下：

      分区间隔

      推荐预建分区数目

      按天分区：TTL_PART_INTERVAL = INTERVAL(1, DAY)

      7~14（提前1周或2周预建分区），例如：ARCHIVE_TABLE_PRE_ALLOCATE = 7。

      按月分区：TTL_PART_INTERVAL = INTERVAL(1, MONTH)

      3（提前3个月预建分区），例如：ARCHIVE_TABLE_PRE_ALLOCATE = 3。

      按年分区：TTL_PART_INTERVAL = INTERVAL(1, YEAR)

      1~2（提前1~2年预建分区），例如：ARCHIVE_TABLE_PRE_ALLOCATE = 1。

    • ARCHIVE_TABLE_POST_ALLOCATE：定义归档表向过去时间补建多少个分区，仅对创建归档表有效，若TTL表无归档需求，则可不设置，若TTL表有归档需求，推荐设置如下：

      分区间隔

      推荐归档表补建分区数目

      按天分区：TTL_PART_INTERVAL = INTERVAL(1, DAY)

      60~180（归档表只补建最近60天或180的分区），例如：ARCHIVE_TABLE_POST_ALLOCATE = 180。

      按月分区：TTL_PART_INTERVAL = INTERVAL(1, MONTH)

      24~48（归档表只补建最近24～48个月的分区），例如：ARCHIVE_TABLE_POST_ALLOCATE = 48。

      按年分区：TTL_PART_INTERVAL = INTERVAL(1, YEAR)

      4~8（归档表只补建最近4~8年的分区），例如：ARCHIVE_TABLE_POST_ALLOCATE = 8。

    • ARCHIVE_TYPE：定义归档策略，按一级分区归档应取值'PARTITION'，按二级分区归档应取值'SUBPARTITION'，必填属性。

  2. 创建TTL表，示例如下：

    CREATE TABLE my_ttl_tbl
(
  id BIGINT,
  ttl_ts_int_col BIGINT
)
PARTITION BY KEY(`id`)
PARTITIONS 8
SUBPARTITION BY RANGE COLUMNS(`ttl_ts_int_col`)
(
  SUBPARTITION sp20250401 VALUES LESS THAN (1743436800),
  SUBPARTITION sp20250501 VALUES LESS THAN (1746028800),
  SUBPARTITION sp20250601 VALUES LESS THAN (1748707200)
);

ALTER TABLE　`my_ttl_tbl`
MODIFY TTL
SET
TTL_ENABLE='ON',
TTL_CLEANUP='OFF',
TTL_EXPR=FROM_UNIXTIME(`ttl_ts_int_col`) EXPIRE AFTER 1 MONTH TIMEZONE '+08:00',
TTL_JOB=CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00'
TTL_PART_INTERVAL=INTERVAL(1,MONTH),
ARCHIVE_TYPE='SUBPARTITION',
ARCHIVE_TABLE_PRE_ALLOCATE=3,
ARCHIVE_TABLE_POST_ALLOCATE=24;

  3. （可选）创建归档表。

    如果您对TTL表的过期数据有归档需求，可以为TTL表创建对应的归档表，示例如下：

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

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

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

  4. 启动清理任务：

    ALTER TABLE `my_ttl_tbl`
MODIFY TTL
SET 
TTL_ENABLE='ON',
TTL_CLEANUP='ON';

冷数据归档案例分享

按行归档：S公司归档历史订单

S公司是一个国内知名品牌的物流公司，其经营的业务应用每天都需要新增及存储大量的订单信息。

业务需求

由于当前S公司的业务发展很快，导致数据库的数据规模增长迅速，其中订单信息的数据量每天都在快速增长，导致业务查询越来越慢。因此，应用需要PolarDB-X所提供的冷数据归档能力，以帮助业务能定期归档订单表的历史数据，从而避免业务因订单表历史数据过多影响稳定性。

order表是S公司核心的订单表：

CREATE TABLE `order` (
  `id` bigint(20) NOT NULL,
  `cid` bigint(20) NOT NULL,
  `modify_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改日期',
  `field_1` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `field_2` varchar(16) CHARACTER SET utf8mb4 NOT NULL,
  `field_3` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '0',
  `field_4` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX idx_modify_time(`modify_time`)
)
PARTITION BY KEY(`cid`,`id`)
PARTITIONS 32;
说明

  • order表是按`cid`,`id`进行哈希分区，并且业务的分区方案及表定义不允许发生变化。

    • 总数据量：5亿+（保存了最近1～2年数据）。

    • 日平均增长量：150万+。

    • 平均行长：4 KB+。

    • 存储空间占用：2 TB+。

  • order表归档需求：

    • 业务表按modify_time过期。

    • 在线数据保留180天。

    • 归档表按天分区，预建30天分区，补建最近180天的分区。

    • 归档表历史数据可提供查询。

归档方案

因为order表是一个存量的业务表，业务已经选择符合业务场景的切分维度`cid`,`id`，且不能更改表结构，所以order表无法使用按分区归档及按分区清理数据，只能选择按行归档。

实施步骤

  1. 添加TTL定义，示例如下：

    ALTER TABLE　`order`
MODIFY TTL
SET
TTL_ENABLE='OFF', -- 归档完成前，先关闭TTL自动调度
TTL_CLEANUP='OFF', -- 归档完成前，先关闭数据清理
TTL_EXPR=`modify_time` EXPIRE AFTER 180 DAY TIMEZONE '+08:00', -- 过期时间是180天
TTL_JOB=CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00', -- 每天凌晨2点执行
TTL_PART_INTERVAL=INTERVAL(1,DAY), -- 归档表分区间隔是1天一个分区
ARCHIVE_TYPE='ROW', -- 使用按行归档策略
ARCHIVE_TABLE_PRE_ALLOCATE=30, -- 归档表预建30天分区
ARCHIVE_TABLE_POST_ALLOCATE=180; -- 归档表补建180天分区

  2. 为TTL表order创建归档表，示例如下：

    CREATE TABLE `order_arc` LIKE `order` ENGINE='Columnar' ARCHIVE_MODE='TTL';

  3. 打开清理任务调度开关，示例如下：

    ALTER TABLE　`order`
MODIFY TTL
SET
TTL_ENABLE='ON', -- 归档完成前，先关闭TTL自动调度
TTL_CLEANUP='ON'; -- 归档完成前，先关闭数据清理

最终效果

按行归档后，S公司的order表顺利完成归档，目前业务运行平常，order表的在线数据保持近2亿＋的热数据，TTL归档的总体情况如下：

  • 归档前总数据量：5亿＋，文件大小：2 TB+。

  • 归档后文件大小：280 GB+，数据库存储空间压缩近10倍。

  • 归档总用时：5小时左右。

按行清理速度：

  • 日均清理最高：7千万+。

  • 清理时间窗口：3小时。

  • DN资源占用：

    • CPU：15%+。

    • IOPS：20%+。　

最后，order表所有的归档数据都保存在名为order_arc的归档表，应用通过主实例或列存只读实例，都可以很方便地查询到已归档的历史数据。

按分区归档：B公司清理交易日志

B公司的业务应用提供是一个在线交易查询平台，其业务的特点是每时每刻都要订阅及存储来自其上游的海量交易日志，从而向它的用户提供查询最新的交易行情。

业务需求

因为B公司的业务所要存储的交易日志数据量非常大，而且日增量非常快，日增数据量：5亿＋。所以，业务核心的交易日志表trade_log采用的二级分区的设计，SQL如下：

CREATE TABLE `trade_log` (
  `trade_id` bigint NOT NULL AUTO_INCREMENT,
  `ts_int_col` bigint NOT NULL DEFAULT '0',
  `field_1` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `field_2` int NOT NULL DEFAULT '0',
  `field_3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`trade_id`),
  KEY `idx_ts` (`ts_int_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_0900_ai_ci
PARTITION BY KEY(`trade_id`)
PARTITIONS 128
SUBPARTITION BY RANGE(`ts_int_col`)
(
  SUBPARTITION `sp20250603` VALUES LESS THAN (1748908800000),
  SUBPARTITION `sp20250604` VALUES LESS THAN (1748995200000),
  SUBPARTITION `sp20250605` VALUES LESS THAN (1749081600000),
  SUBPARTITION `sp20250606` VALUES LESS THAN (1749168000000),
  SUBPARTITION `sp20250607` VALUES LESS THAN (1749254400000)
);
说明

  • trade_log表使用的分区方案为：

    • 一级分区：按trade_id哈希分区。

    • 二级分区：按ts_int_col时间Range分区，其中ts_int_col是毫秒时间戳整数。

  • 业务的需求是通过定期删除二级Range分区，实现清理的过期数据。

业务方类似上述的这种使用二级RANGE分区的需求清理过期数据的日志表有几十张，且每一张表RANGE分区列的类型、过期时间间隔以及分区间隔都可能不一样。例如：有些表它的分区列类型可能是DATETIME，按月分区，保留最近半年。有些表的分区列类型是 BIGINT（毫秒时间戳），按天分区，保留3天或7天等。

对于以上复杂的分区场景，业务方不希望再维护一个的后台任务为这些表自动删分区及加分区。因此，希望通过使用PolarDB-X提供的冷数据归档能力，使这些表按照时间RANGE分区定期清理过期数据，并自动补充新的RANGE分区。

综上所述，trade_log表归档需求如下：

  • 按ts_int_col列自动过期分区，ts_int_col列是一个毫秒时间戳整数。

  • 只需保留3天数据，超过3天的数据可以自动清理。

  • 按天分区（相邻两个分区时间间隔是1天）。

  • 预建2天分区。

  • 不需要归档数据。

归档方案

由于trade_log表的二级分区使用了按时间RANGE分区，所以需要使用按分区归档的功能方可满足业务的需求。

实施步骤

添加TTL定义，示例如下：

ALTER TABLE　`trade_log`
MODIFY TTL
SET
TTL_ENABLE='ON', -- 没有创建归档表需求，可直接TTL自动调度
TTL_CLEANUP='ON', -- 没有创建归档表需求，可直接打开数据清理
TTL_EXPR=FROM_UNIXTIME(`ts_int_col` / 1000) EXPIRE AFTER 3 DAY TIMEZONE '+08:00', -- 过期时间是3天
TTL_JOB=CRON '0 0 2 */1 * ? *' TIMEZONE '+08:00', -- 每天凌晨2点执行
TTL_PART_INTERVAL=INTERVAL(1,DAY), -- TTL表及归档表分区间隔是1天一个分区
ARCHIVE_TYPE='SUBPARTITION', -- 使用按二级分区归档策略
ARCHIVE_TABLE_PRE_ALLOCATE=2; -- 归档表预建2天分区
说明

由于业务方无归档需求，只添加TTL定义，自动加减分区。

最终效果

  • 业务方其它所有的需要清理数据的日志表（40+张表），均通过类似上述的操作实现RANGE分区的自动加减分区。

  • 业务表在执行自动加减分区的操作均是Online DDL操作，不锁表，业务不会受影响。

上一篇：常见问题下一篇：全球数据库（GDN）