利用历史列存快照查询和恢复历史数据

版本限制

注意事项

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

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

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

根据快照点查询快照

快照查询通过在SELECT语句中的表后追加AS OF TSO <TSO>来触发，要查询列存快照数据，还需追加FORCE INDEX，并指定列存快照名称，其语法如下：

SELECT * FROM <table_name> AS OF TSO <TSO> FORCE INDEX(<snapshot_name>);

示例：

-- FORCE INDEX 语法需放在 AS OF TSO 之后
SELECT * FROM tb1 AS OF TSO 7206138458723582016 FORCE INDEX(cci);

您也可以通过列存快照恢复历史数据，语法如下：

INSERT INTO <target_table> SELECT * FROM <source_table> AS OF TSO <TSO> FORCE INDEX(<snapshot_name>);

示例：

INSERT INTO tmp SELECT * FROM tb1 AS OF TSO 7206138458723582016 FORCE INDEX(cci);

示例

1. 执行如下代码，创建示例数据库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);

2. 执行如下代码，为示例表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"
   }';

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

   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);

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

   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)

5. 使用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)