MariaDB 10.3 系统版本表(System-Versioned Tables)，

和通数据库htsjk.Com2019-10-07 06:25 来源:未知阅读:7546 评论 320 热度2
标签：
MariaDB 10.3 系统版本表(System-Versioned Tables)，

第一部分 概述：

系统版本表是SQL：2011标准中首次引入的功能。 系统版本表存储所有更改的历史记录，而不仅仅是
当前时刻有效的数据。 这允许对任何时间点进行数据分析，审核变更并比较来自不同时间点的数据。
典型的应用场景：
1.法医鉴定数据(Forensic analysis)或者法律数据需要存储N多年
2.历史遗留数据回看或趋势分析
3.时间点数据--可以查看或者回复任意节点的数据。
此功能在Oracle数据库中称为闪回查询(Flashback Query)。
MariaDB 10.3.4版本首次实现此功能。

第二部分 系统版本的创建语法：

create table语法已扩展为允许创建系统版本的表。 要进行系统版本化表，根据sql:2011标准，
表必须有两个生成的列，一个period和一个特殊的表选项子句：

create table wuhan.t(
   x int,
   start_timestamp timestamp(6) generated always as row start,
   end_timestamp timestamp(6) generated always as row end,
   period for system_time(start_timestamp, end_timestamp)
) with system versioning;

在mariadb中语法可以简化为：
create table wuhan.t1 (
   x int
) with system versioning;
在mariadb中简化的语法中不会创建额外的列，并且不会混淆select * from t的输出，版本信息仍会存储在，
我们可以通过伪劣直接访问版本信息：
mariadb [wuhan]> insert into wuhan.t1(x)values(1);
query ok, 1 row affected (0.003 sec)

mariadb [wuhan]> select x,row_start, row_end from t1;
+------+----------------------------+----------------------------+
| x    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2018-08-05 00:37:47.250387 | 2038-01-19 11:14:07.999999 |
+------+----------------------------+----------------------------+
1 row in set (0.002 sec)

第三部分 普通表增减系统版本信息

mariadb [wuhan]> create table wuhan.t2(id int not null,keyname varchar(20));
query ok, 0 rows affected (0.006 sec)
添加版本信息：
mariadb [wuhan]> show create table wuhan.t2\g
*************************** 1. row ***************************
       table: t2
create table: create table `t2` (
  `id` int(11) not null,
  `keyname` varchar(20) collate utf8mb4_unicode_ci default null
) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci
1 row in set (0.003 sec)

mariadb [wuhan]> alter table wuhan.t2 add system versioning;
query ok, 0 rows affected (0.010 sec)              
records: 0  duplicates: 0  warnings: 0

mariadb [wuhan]> show create table wuhan.t2\g               
*************************** 1. row ***************************
       table: t2
create table: create table `t2` (
  `id` int(11) not null,
  `keyname` varchar(20) collate utf8mb4_unicode_ci default null
) engine=innodb default charset=utf8mb4 collate=utf8mb4_unicode_ci with system versioning
1 row in set (0.000 sec)
我们可以看到在表的定义的最后部分有了with system versioning关键字。
--删除版本信息:
mariadb [wuhan]> alter table wuhan.t2 drop system versioning;

--显式的增加版本信息的列：
alter table wuhan.t2 add column begintime timestamp(6) generated always as row start,
              add column endtime timestamp(6) generated always as row end,
              add period for system_time(begintime, endtime),
              add system versioning;
MariaDB [wuhan]> show create table wuhan.t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `keyname` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `begintime` timestamp(6) GENERATED ALWAYS AS ROW START,
  `endtime` timestamp(6) GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (`begintime`, `endtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci WITH SYSTEM VERSIONING
1 row in set (0.000 sec)


第四部分 查询历史数据的语法：
为了查询隶属数据直接使用关键字 for system_time跟在表名之后(若表有别名放在别名之后).
SQL:2011标准提供了三种语法扩展：
1. AS OF 用于查询过去特定的时间点的数据：
SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06'
2.BETWEEN start AND end 用于查询行数据起始节点时间明确指定的表：
SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW()
3.FROM start TO end 适用于查询时间点可视的是时间点范围，可以指定起始时间。
SELECT * FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00'

MariaDB实现了非标准的语法，用于查询所有数据，包含历史的和当前的：
SELECT * FROM t FOR SYSTEM_TIME ALL;

若不适用for system_time则查询当前的数据，等同于select * from t for  SYSTEM_TIME AS OF CURRENT_TIMESTAMP;

第五部分 视图和子查询：
当在视图和子查询中使用系统版本表时可以直接使用for system_time。
实例：
REATE VIEW v1 AS SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06'
或者：
CREATE VIEW v1 AS SELECT * FROM t;
SELECT * FROM v1 FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

第六部分 精确事务历史：
当插入或删除的时间点并不一定意味着同一时刻可以看到变更。对于事务表可能在长事务中需要数个小时。
对一些应用程序，比如分析数年前的数据这种区别并不明显。
但是对于有些场景比如法医分析，这种精确的事务就至关重要。

MariaDB仅在InnoDB存储引擎中支持事务精确的历史信息。
这允许精确的数据查看，就像在指定时间点执行SELECT的新连接所看到的那样 - 在该点之前插入的行，
但在之后提交将不会显示。

为使用事务精确的历史，InnoDB需要记忆的不是时间戳而是每行的事务标识符，通过创建生成列
bigint unsigned 而非timestamp(6).这些列必须显式指定，但是可以使用invisible和select * from t混淆。

当使用事务精确历史时，定义和查询如下：
CREATE TABLE wuhan.t(
   x INT,
   start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,
   end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,
   PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)
) WITH SYSTEM VERSIONING;
SELECT * FROM t FOR SYSTEM_TIME AS OF TRANSACTION 12345;

第七部分 当前数据和历史分离存储
当历史数据和当前数据一起存储势必会增加表的大小，因此当查询将会耗费
更多的时间(表扫描和索引扫描)，因为要跳过历史数据。事实上更多的查询
是查询当前的数据，我们可以很好的利用system_time 对表进行分区，由于
表分区裁剪优化，将当前数据和历史进行分离存储，以减少版本控制的开销。

分区方案1：将当前数据和历史数据分开：
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME (
    PARTITION p_hist HISTORY,
    PARTITION p_cur CURRENT
  );
分区方案2：支持自动分区滚动，按照表行数
CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME LIMIT 100000 (
    PARTITION p0 HISTORY,
    PARTITION p1 HISTORY,
    PARTITION pcur CURRENT
  );
 分区方案3：支持自动分区滚动，按照时间
 CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME INTERVAL 1 month (
    PARTITION p0 HISTORY,
    PARTITION p1 HISTORY,
    PARTITION p2 HISTORY,
    PARTITION pcur CURRENT
  );
 分区方案4:混合使用system_time和子分区
 CREATE TABLE t (x INT) WITH SYSTEM VERSIONING
  PARTITION BY SYSTEM_TIME
    SUBPARTITION BY KEY (x)
    SUBPARTITIONS 4 (
    PARTITION ph HISTORY,
    PARTITION pc CURRENT
  );
  
 第八部分 删除旧的历史数据：
  由于系统版本信息保留了历史所有的数据，随着时间斗转星移，表可能会很大。
 有很多办法缩小空间并移除旧的历史数据。
 方法1：删除system version信息再添加，这种办法非常耗时需要重建表2次。
ALTER TABLE t DROP SYSTEM VERSIONING;
ALTER TABLE t ADD SYSTEM VERSIONING;
方法2：使用分区，删除历史中的部分数据：（此方法不能删除当前分区和仅有的历史的历史分区）
ALTER TABLE t DROP PARTITION p0;
方法3：使用delete变体裁剪历史数据：
DELETE HISTORY FROM t;
DELETE HISTORY FROM t BEFORE SYSTEM_TIME '2016-10-09 08:07:06';
DELETE HISTORY FROM t BEFORE SYSTEM_TIME TRANSACTION xxx;
为了保证数据的完整性需要delete操作需要delete history权限。

第九部分 系统版本表中排除列
若对某列的信息不敏感或者不感兴趣，则可以将将此列从系统版本表中排除。这是MariaDB做的扩展功能。
示例：
CREATE TABLE t (
   x INT,
   y INT WITHOUT SYSTEM VERSIONING
) WITH SYSTEM VERSIONING;
等同于：
CREATE TABLE t (
   x INT WITH SYSTEM VERSIONING,
   y INT
);

第十部分：系统版本表相关的参数
MariaDB [(none)]> select @@version;
+--------------------+
| @@version          |
+--------------------+
| 10.3.8-MariaDB-log |
+--------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show variables like 'system_version%'; 
+---------------------------------+---------+
| Variable_name                   | Value   |
+---------------------------------+---------+
| system_versioning_alter_history | ERROR   |
| system_versioning_asof          | DEFAULT |
+---------------------------------+---------+
2 rows in set (0.001 sec)
 system_versioning_alter_history
 有效范围值：error和keep
 SQL:2011标准不允许在系统版本表上使用alter table操作。
 当设置为error时，试图修改系统版本表的时候就会报错；
 当设置为keep时候alter table则会被允许，但是历史数据将变得不准确
，查询历史数据将会显示新的表结构。这个模式仍然有用，比如向表中加新的列。

system_versioning_asof
若设置为特定的时间戳则一个隐式的for system_time as of 语句将适用于所有的查询，
此种情形很适用于想要在特定的时间点对历史记录进行多次查询。
若设置为default则按照默认的方式恢复到任何时间节点。
上述2个参数均可动态修改。

第十一部分：系统版本表的限制
1.系统版本表不适用于生成的虚拟和持久列(generated (virtual and persistent) columns)
2.mysqldump备份工具不能备份历史数据，恢复的时候也不能恢复历史数据，建议使用xtrabackup备份数据。
3.物理删除的方式如drop database ，drop table，truncate table的方式数据则无法恢复。

第十二部分：使用建议
建议此功能结合MySQL的Replication在从库上使用，可以将历史记录都保存下来，主库的误操作可以
在从库查询。


资料参考：
https://mariadb.com/kb/en/library/system-versioned-tables/
https://en.wikipedia.org/wiki/SQL:2011