截至系统时间查询

您可以使用此子句读取历史数据并通过减少事务冲突来提高性能。

用于AS OF SYSTEM TIME减少与长时间运行的查询的冲突

如果您有可以容忍稍微过时的读取的长时间运行的查询(例如执行全表扫描的分析查询),请考虑使用该AS OF SYSTEM TIME子句。使用它,您的查询将返回过去某个不同点出现的数据,并且不会与其他并发事务发生冲突,从而提高应用程序的性能。

但是,由于AS OF SYSTEM TIME返回历史数据,您的读取可能会过时。

概要

AS OF SYSTEM TIME子句在多个SQL上下文中受支持,包括但不限于:

  • SELECT从句中,在子句FROM后。
  • BACKUP...TO子句的参数之后。
  • RESTORE..FROM子句的参数之后。
  • BEGIN关键字之后。
  • SET TRANSACTION关键字之后。

示例

选择查询历史数据

这个例子代表了数据库的当前数据:

select empno,ename from emp 
where deptno=50;
  empno | ename
--------+---------
    101 | ALLEN
   7580 | WALLER

相反,我们可以检索2022年 08月3日12:45 UTC的值:

select empno,ename from emp 
 AS OF SYSTEM TIME '2022-08-03 12:45:00'
where deptno=50;
  empno | ename
--------+---------
   7580 | WALLER

使用不同的时间戳格式

常见形式

select * from test AS OF SYSTEM TIME '2022-08-01 08:00:00'

其他形式的查询

select * from test AS OF SYSTEM TIME 1451635200000000000
select * from test AS OF SYSTEM TIME '-4h'
select * from test AS OF SYSTEM TIME INTERVAL '-4h'

从多个表中选择

在单个FROM子句中选择多个表时,AS OF SYSTEM TIME子句必须出现在最后并应用于整个SELECT子句。

例如:

select * from a, b, c AS OF SYSTEM TIME '-4h';
select * from a JOIN b ON a.x = b.y AS OF SYSTEM TIME '-4h';
select * from (select * from a), (select * from b) AS OF SYSTEM TIME '-4h';

AS OF SYSTEM TIME在子查询中使用

为了便于从更简单的查询中组合更大的查询,Hubble允许AS OF SYSTEM TIME在以下条件下进行子查询:

  • 顶级查询还指定AS OF SYSTEM TIME
  • 所有AS OF SYSTEM TIME子句都指定相同的时间戳。

例如:

select * from (select * from t AS OF SYSTEM TIME '-6h') t
           JOIN u ON t.x = u.y
           AS OF SYSTEM TIME '-6h'  -- same timestamp as above - OK.
     WHERE t.x < 100;

AS OF SYSTEM TIME在交易中使用

您可以使用该SET语句使用过去指定时间的数据库内容执行事务

BEGIN;

2019-04-09 18:02:52截止到现在的数据

SET TRANSACTION AS OF SYSTEM TIME '2019-04-09 18:02:52.0+00:00';
select * from a;
select * from b;
COMMIT;

用于AS OF SYSTEM TIME恢复最近丢失的数据

建表并插入数据

create table b (id int PRIMARY KEY);
insert into b VALUES (10), (20);
SELECT now();
               now
---------------------------------
  2022-08-29 16:44:00.662129+08
drop table b;
select * from b AS OF SYSTEM TIME '2022-08-29 16:44:00.662129+08';
 id
------
  10
  20

如果执行以下语句就会显示表不存在

select * from b ;
ERROR: relation "b" does not exist
SQLSTATE: 42P01

一旦发生垃圾回收,AS OF SYSTEM TIME将无法再恢复丢失的数据。对于更长期的恢复解决方案,请考虑对集群进行完整备份或增量备份。