CCI与冷数据归档表共用_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

在实际业务中，您可能会对已有列存索引（CCI）的表会有冷数据归档（TTL）的需求，为此PolarDB-X为您提供了CCI和归档表在同一张表共用的功能。

版本限制

仅AUTO模式数据库支持创建和使用CCI。
实例版本需为polardb-2.5.0_5.4.20-20250328及以上。
说明
- 实例版本的规则说明，请参见版本说明。
- 实例版本的查看方式，请参见查看实例版本。

前提条件

因为归档表是用于保存已归档数据的表，基于CCI实现的，是特殊类型的CCI，默认单张表内只允许创建一个CCI，所以PolarDB-X提供了MAX_CCI_COUNT参数控制单张表添加CCI的数量，示例如下：

SET GLOBAL MAX_CCI_COUNT = 2;

说明

允许单张表同时存在的CCI数量上限为2个，即可以为单张表创建2个CCI。
当表中存在多个普通CCI时，默认会固定使用一个CCI（最先创建的），可通过FORCE INDEX指定查询的具体CCI。
MAX_CCI_COUNT为0时，表示单张表不能创建CCI。

示例

设置MAX_CCI_COUNT的值为2，允许为单张表创建2个CCI。
```
SET GLOBAL MAX_CCI_COUNT = 2;
```

您可以通过以下两种方式创建同时包含TTL归档表和CCI的示例表。

先创建普通CCI表，再添加TTL归档表。

-- 创建表
CREATE TABLE ttl_tbl (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) NOT NULL,
  `b` timestamp(3),
  PRIMARY KEY(`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by KEY(`c`) partitions 4;
-- 创建CCI
CREATE CLUSTERED COLUMNAR INDEX `cci_c` ON ttl_tbl (`c`) partition by KEY(`c`) partitions 4;

-- 变更表为TTL表
ALTER TABLE ttl_tbl
MODIFY TTL
SET
TTL_EXPR = `b` EXPIRE AFTER 2 DAY TIMEZONE '+08:00'
TTL_CLEANUP='ON';
-- 为ttl_tbl创建归档表
CREATE TABLE ttl_tbl_arc LIKE ttl_tbl engine=Columnar archive_mode='ttl';

先创建TTL归档表，再添加CCI：

-- 创建TTL表
CREATE TABLE `ttl_tbl` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) NOT NULL,
  `b` timestamp(3),
  PRIMARY KEY(a)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 TTL = TTL_DEFINITION( TTL_ENABLE = 'OFF', TTL_CLEANUP='ON', TTL_EXPR = `b` EXPIRE AFTER 2 DAY TIMEZONE '+08:00', TTL_JOB = CRON '0 0 1 * * ? *' TIMEZONE '+08:00', ARCHIVE_TYPE = '', ARCHIVE_TABLE_NAME = '', ARCHIVE_TABLE_PRE_ALLOCATE = 2, ARCHIVE_TABLE_POST_ALLOCATE = 2 )
PARTITION BY KEY(`a`)
PARTITIONS 2;
-- 为ttl_tbl创建归档表
CREATE TABLE ttl_tbl_arc LIKE ttl_tbl engine=Columnar archive_mode='ttl';
-- 创建普通CCI
CREATE COLUMNAR CLUSTERED INDEX cci_c ON ttl_tbl(c) PARTITION BY KEY(c) PARTITIONS 4;

查看表结构。

SHOW CREATE TABLE ttl_tbl;

结果如下：

CREATE TABLE `ttl_tbl` (
	`a` int(11) NOT NULL AUTO_INCREMENT,
	`c` int(11) NOT NULL,
	`b` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
	PRIMARY KEY (`a`),
	CLUSTERED COLUMNAR INDEX `arctmp_ttl_tbl_arc` (`b`)
		PARTITION BY RANGE(UNIX_TIMESTAMP(`b`))
		(PARTITION p20240625 VALUES LESS THAN (1719244800) ENGINE = Columnar,
		 PARTITION p20240626 VALUES LESS THAN (1719331200) ENGINE = Columnar,
		 PARTITION p20240627 VALUES LESS THAN (1719417600) ENGINE = Columnar,
		 PARTITION p20240628 VALUES LESS THAN (1719504000) ENGINE = Columnar,
		 PARTITION p20240629 VALUES LESS THAN (1719590400) ENGINE = Columnar,
		 PARTITION p20240630 VALUES LESS THAN (1719676800) ENGINE = Columnar,
		 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = Columnar),
	CLUSTERED COLUMNAR INDEX `cci_c` (`c`)
		PARTITION BY KEY(`c`)
		PARTITIONS 4
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 TTL = TTL_DEFINITION ( TTL_ENABLE = 'OFF', TTL_EXPR = `b` EXPIRE AFTER 2 DAY TIMEZONE '+08:00', TTL_JOB = CRON '0 0 1 * * ? *', TTL_CLEANUP = 'ON', TTL_PART_INTERVAL = INTERVAL(1, DAY), ARCHIVE_TYPE = 'ROW', ARCHIVE_TABLE_NAME = 'ttl_t1_with_cci_arc', ARCHIVE_TABLE_PRE_ALLOCATE = 2, ARCHIVE_TABLE_POST_ALLOCATE = 2 )
PARTITION BY KEY(`a`)
PARTITIONS 2

插入测试数据。

INSERT INTO ttl_tbl (c, b) VALUES
(1, '2024-06-25 08:00:00'), -- 已过期3天
(2, '2024-06-26 09:00:00'), -- 已过期2天（边界）
(3, '2024-06-27 10:00:00'), -- 未过期（1天）
(4, '2024-06-28 09:00:00'); -- 未过期（当前时间）

手动清理过期数据。

ALTER TABLE ttl_tbl CLEANUP EXPIRED DATA;

查看在线表数据。

SELECT * FROM ttl_tbl FORCE INDEX(PRIMARY);

结果如下：

+---+---+-------------------------+
| a | c | b                       |
+---+---+-------------------------+
| 4 | 4 | 2024-06-28 09:00:00.000 |
+---+---+-------------------------+

说明

过期数据已被清理，默认删除3个时间单位的数据。

查看普通CCI索引数据。

SELECT * FROM ttl_tbl FORCE INDEX(cci_c);

结果如下：

+---+---+-------------------------+
| a | c | b                       |
+---+---+---+---------------------+
| 4 | 4 | 2024-06-28 09:00:00.000 |
+---+---+-------------------------+

说明

列存索引cci_c中过期数据已被物理删除。

查看归档CCI数据。

SELECT * FROM ttl_tbl_arc;

结果如下：

+---+---+-------------------------+
| a | c | b                       |
+---+---+-------------------------+
| 1 | 1 | 2024-06-25 08:00:00.000 |
| 2 | 2 | 2024-06-26 09:00:00.000 |
| 3 | 3 | 2024-06-27 10:00:00.000 |
| 4 | 4 | 2024-06-28 09:00:00.000 |
+---+---+-------------------------+

说明

包含已归档数据和提前归档的在线数据。