列存快照-创建、修改、查询

本文介绍如何创建、修改、查询列存快照。

版本限制

实例版本需要在5.4.20及以上，且引擎版本为MySQL 5.7或MySQL 8.0。

说明

• 实例版本的规则说明，请参见版本说明。

• 实例版本的查看方式，请参见查看实例版本。

准备工作

在使用列存快照功能前，需要先调整数据库的一些参数配置。连接数据库后，执行如下步骤：

说明

PolarDB-X提供了多种连接数据库的方式。具体信息，请参见连接数据库。

• 执行如下代码，为所有写入事务开启严格的外部一致性保证：

SET GLOBAL ENABLE_EXTERNAL_CONSISTENCY_FOR_WRITE_TRX = TRUE;

说明

• 需要使用高权限账号执行。
• 返回结果中如果有warnings，则代表执行没成功，请检查您是否使用的是高权限账号。
• 开启事务的严格外部一致性保证后，所有事务都会严格执行两阶段提交。

• 关闭列存索引自适应心跳（可选）：

CALL polardbx.columnar_set_config('columnar_heartbeat_interval_ms_self_adaption', false);

说明

建议您关闭列存索引自适应心跳，以避免产生过多的元数据。

创建列存快照

列存快照是基于列存索引实现的，因此，您可以通过创建列存索引的语法CREATE CLUSTERED COLUMNAR INDEX，在其中特别指定columnar_options中的type参数为snapshot创建对应的列存快照。更多信息，请参见创建和使用CCI。

CREATE
CLUSTERED COLUMNAR INDEX snapshot_name
ON tbl_name (index_sort_key_name,...)
[partition_options]
columnar_options='{
"k1":"v1",
"k2":"v2",
}'

columnar_options:
"type":"snapshot"
"snapshot_retention_days":"7"
"auto_gen_columnar_snapshot_interval":"30"

参数说明：

示例

创建列存快照有以下4种方式：

• 在创建表的同时创建列存索引与列存快照：

CREATE TABLE t_order (
`id` BIGINT(11) NOT NULL AUTO_INCREMENT,
`order_id` VARCHAR(20) DEFAULT NULL,
`buyer_id` VARCHAR(20) DEFAULT NULL,
`seller_id` VARCHAR(20) DEFAULT NULL,
`order_snapshot` LONGTEXT DEFAULT NULL,
`order_detail` LONGTEXT DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `l_i_order` (`order_id`),
CLUSTERED COLUMNAR INDEX `seller_snapshot` (`seller_id`)
PARTITION BY HASH(`order_id`)
PARTITIONS 16
COLUMNAR_OPTIONS='{
"type":"snapshot",
"snapshot_retention_days":"7",
"auto_gen_columnar_snapshot_interval":"30"
}'
) ENGINE=INNODB DEFAULT CHARSET=UTF8 PARTITION BY HASH(`order_id`) PARTITIONS 16;

• 使用CREATE CLUSTERED COLUMNAR INDEX语句为表创建列存索引与列存快照：

CREATE CLUSTERED COLUMNAR INDEX `seller_snapshot` ON t_order (`seller_id`)
PARTITION BY HASH(`order_id`)
PARTITIONS 16
COLUMNAR_OPTIONS='{
"type":"snapshot",
"snapshot_retention_days":"7",
"auto_gen_columnar_snapshot_interval":"30"
}';

使用ALTER语句，为表创建列存索引与列存快照：
ALTER TABLE t_order ADD CLUSTERED COLUMNAR INDEX `seller_snapshot` (`seller_id`)
partition by hash(`order_id`)
partitions 16
columnar_options='{
"type":"snapshot",
"snapshot_retention_days":"10",
"auto_gen_columnar_snapshot_interval":"30"
}';

• 为已有列存索引的表创建列存快照：

CALL polardbx.columnar_set_config(<cci_id>, 'type', 'snapshot');

说明

其中cci_id为列存索引的ID。如何获取cci_id，请参见SHOW COLUMNAR STATUS。

调整列存快照的属性

• 执行如下代码，可以调整列存快照的保留时间和自动生成快照点的时间间隔：

-- 修改保留时间为10天
CALL polardbx.columnar_set_config(<cci_id>, 'snapshot_retention_days', 10);-- 修改为每隔 10 分钟生成一个快照点
CALL polardbx.columnar_set_config(<cci_id>, 'auto_gen_columnar_snapshot_interval', 10);
-- 关闭自动生成快照点
CALL polardbx.columnar_set_config(<cci_id>, 'auto_gen_columnar_snapshot_interval', -1);

• 执行如下代码，为snapshot_retention_days和auto_gen_columnar_snapshot_interval 设置全局默认值：

CALL polardbx.columnar_set_config('auto_gen_columnar_snapshot_interval', 20);
CALL polardbx.columnar_set_config('snapshot_retention_days', 7);

说明

设置后，只对新建的列存快照生效，对已经创建的列存快照没有影响。

创建列存快照时，如果不显式设置snapshot_retention_days和auto_gen_columnar_snapshot_interval的值，系统会使用这两个参数的全局配置作为该列存快照的属性。

删除列存快照

• 删除列存索引

DROP INDEX `seller_snapshot` ON t_order;

• 删除表

DROP TABLE t_order;

查看列存快照定义

您可以通过如下代码，查看列存快照的定义：

SHOW FULL CREATE TABLE t_order;

注意事项

• 列存快照查询会基于最新的表结构返回结果，无论查询的快照点是否在更改表结构之前。例如：某表执行加列操作后，再查询加列操作之前的列存快照数据，那么结果中的表结构依然是加列操作之后的表结构。

• 目前仅支持数据库自动提交开启（autocommit=true）时，在事务内使用INSERT SELECT语句恢复数据。

• 使用INSERT SELECT语句时，不建议在SELECT子句中进行复杂查询，因为复杂查询可能会导致该语句执行效率很低。

示例

执行如下代码，创建示例数据库test_columnar_snapshot 并使用该数据库创建示例表tb1：

CREATE DATABASE test_columnar_snapshot MODE=AUTO;

USE test_columnar_snapshot;
CREATE TABLE tb1 (
id INT PRIMARY KEY,
a INT,
gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) PARTITION BY KEY(id);

执行如下代码，为示例表tb1创建列存快照（列存索引）cci：

CREATE CLUSTERED COLUMNAR INDEX cci ON tb1(a)
PARTITION BY KEY(id)
columnar_options='{
"type":"snapshot",
"snapshot_retention_days":"7",
"auto_gen_columnar_snapshot_interval":"30"
}';

执行如下代码，插入示例数据，并生成快照点：

INSERT INTO tb1 (id, a) VALUES (0, 0);
CALL polardbx.columnar_flush('test_columnar_snapshot', 'tb1', 'cci'); -- 生成列存索引级别的快照点并记录为 TSO1
INSERT INTO tb1 (id, a) VALUES (1, 1);
CALL polardbx.columnar_flush(); -- 生成实例级别的快照点并记录为 TSO2
INSERT INTO tb1 (id, a) VALUES (2, 2);

基于快照点查询列存快照：

SELECT * FROM tb1 AS OF TSO {TSO1} FORCE INDEX(cci) ORDER BY id; -- 预期出现 (0, 0)
SELECT * FROM tb1 AS OF TSO {TSO2} FORCE INDEX(cci) ORDER BY id; -- 预期出现 (0, 0), (1, 1)
SELECT * FROM tb1 FORCE INDEX(cci) ORDER BY id; -- 预期出现 (0, 0), (1, 1), (2, 2)

使用INSERT SELECT恢复数据：

CREATE TABLE tb1_tmp (
id INT PRIMARY KEY,
a INT,
gmt_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) PARTITION BY KEY(id);
-- 使用列存快照恢复数据
INSERT INTO tb1_tmp SELECT * FROM tb1 AS OF TSO {TSO1} FORCE INDEX(cci);
SELECT * FROM tb1_tmp ORDER BY id; -- 预期出现 (0, 0)