系統(tǒng)版本表是SQL:2011標(biāo)準(zhǔn)中首次引入的功能。系統(tǒng)版本表存儲所有更改的歷史數(shù)據(jù)，而不僅僅是當(dāng)前時刻有效的數(shù)據(jù)。舉個例子，同一行數(shù)據(jù)一秒內(nèi)被更改了10次，那么就會保存10份不同時間的版本數(shù)據(jù)。就像《源代碼》電影里的平行世界理論一樣，你可以退回任意時間里。從而有效保障你的數(shù)據(jù)是安全的，DBA手抖或程序BUG引起的數(shù)據(jù)丟失，在MariaDB10.3里已成為過去。

成都創(chuàng)新互聯(lián)公司服務(wù)項目包括沈丘網(wǎng)站建設(shè)、沈丘網(wǎng)站制作、沈丘網(wǎng)頁制作以及沈丘網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，沈丘網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到沈丘省份的部分城市，未來相信會繼續(xù)擴大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

一、創(chuàng)建系統(tǒng)版本表

例子：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `ts` timestamp(6) GENERATED ALWAYS AS ROW START,
  `te` timestamp(6) GENERATED ALWAYS AS ROW END,
  PRIMARY KEY (`id`,`te`),
  PERIOD FOR SYSTEM_TIME (`ts`, `te`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;

注意看紅色字體，這就是新增加的語法，字段ts和te是數(shù)據(jù)變化的起止時間和結(jié)束時間。

另外用ALTER TABLE更改表結(jié)構(gòu)，語法如下：

ALTER TABLE t1 ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
              ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
              ADD PERIOD FOR SYSTEM_TIME(ts, te),
              ADD SYSTEM VERSIONING;

二、查詢歷史數(shù)據(jù)

這里我們做一個實驗，首先要插入1條數(shù)據(jù)，如下圖所示：

接著把姓名為“張三”，改成“李四”（誤更改數(shù)據(jù)）

現(xiàn)在數(shù)據(jù)已經(jīng)成功變更，那么我想查看歷史數(shù)據(jù)怎么辦呢？非常簡單，一條命令搞定。

語法一：查詢一小時內(nèi)的歷史數(shù)據(jù)。
SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 HOUR) AND NOW();
HOUR:小時
MINUTE:分鐘
DAY:天
MONTH:月
YEAR:年

語法二：查詢一段時間內(nèi)的歷史數(shù)據(jù)
SELECT * FROM t1 FOR SYSTEM_TIME FROM '2018-05-15 00:00:00' TO '2018-05-15 14:00:00';

語法三：查詢所有歷史數(shù)據(jù)
SELECT * FROM t1 FOR SYSTEM_TIME ALL;

三、恢復(fù)歷史數(shù)據(jù)

現(xiàn)在我們已經(jīng)找到了歷史數(shù)據(jù)“張三”，只需把它導(dǎo)出來做恢復(fù)即可。

SELECT id,name FROM t1 FOR SYSTEM_TIME ALL where id = 1 AND name = 
'張三' into outfile '/tmp/t1.sql' \
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

FIELDS TERMINATED BY ',' --- 字段的分隔符
OPTIONALLY ENCLOSED BY '"' --- 字符串帶雙引號

導(dǎo)入恢復(fù)

load data infile '/tmp/t1.sql' replace into table t1 \
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' \
(id,name);

非常簡單的恢復(fù)完數(shù)據(jù)，此方法比之前用MySQLbinlog或自研腳本等工具做閃回效率高得多。

四、單獨存儲歷史數(shù)據(jù)

當(dāng)歷史數(shù)據(jù)與當(dāng)前數(shù)據(jù)一起存儲時，勢必會增加表的大小，且當(dāng)前的數(shù)據(jù)查詢：表掃描和索引搜索，將會花費更多的時間，因為需要跳過歷史數(shù)據(jù)。那么我們可以將通過表分區(qū)將其分開、單獨存儲，以減少版本控制的開銷。
接上面的例子，執(zhí)行下面的語句：

alter table t1 
  PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH (
    PARTITION p0 HISTORY,
    PARTITION p1 HISTORY,
    PARTITION p2 HISTORY,
    PARTITION p3 HISTORY,
    PARTITION p4 HISTORY,
    PARTITION p5 HISTORY,
    PARTITION p6 HISTORY,
    PARTITION pcur CURRENT
  );

意思為：按照月份分割歷史數(shù)據(jù)，今天至一個月后（2018年6月15日）的歷史數(shù)據(jù)放入p0分區(qū)，次月的歷史數(shù)據(jù)放入p1分區(qū)，依次類推至（2018年12月15日）存p6分區(qū)。當(dāng)前數(shù)據(jù)存儲在pcur分區(qū)里。

可以通過數(shù)據(jù)字典表，來查看每個分區(qū)表的數(shù)據(jù)輪詢時間狀態(tài)信息。

SELECT PARTITION_DESCRIPTION,TABLE_ROWS FROM 
`information_schema`.`PARTITIONS` WHERE table_schema='hcy' AND 
table_name='t1';

五、刪除舊的歷史數(shù)據(jù)

系統(tǒng)版本表存儲了所有的歷史數(shù)據(jù)，隨著時間的推移，歷史版本數(shù)據(jù)會變得越來越大，那么我們就可以將其最老的歷史數(shù)據(jù)刪除。
例：將p0分區(qū)刪除
ALTER TABLE t1 DROP PARTITION p0;

六、正確使用姿勢

通過上述介紹，我們了解了系統(tǒng)版本表的原理。在高并發(fā)寫入場景下，勢必會帶來性能上的損失，所以要用正確的姿勢開啟該功能。

例：主庫是MySQL 5.6或者MariaDB 10.0/1/2版本，搭建一個新從庫MariaDB 10.3，在該從庫上轉(zhuǎn)換為系統(tǒng)版本控制表。這樣主庫上誤刪或誤篡改數(shù)據(jù)，可以在從庫上通過版本控制找回。

注：主庫是低版本，從庫是高版本，是可以向前兼容binlog格式的。

七、注意事項

1、參數(shù)system_versioning_alter_history要設(shè)置為KEEP（在my.cnf配置文件里寫死），否則默認不能執(zhí)行DDL修改表結(jié)構(gòu)操作。
set global system_versioning_alter_history = 'KEEP';

注：增加字段時，要加上after關(guān)鍵字，否則會在te字段后面，造成同步失敗。例：
alter table t1 add column address varchar(500) after name;

2、mysqldump工具不會導(dǎo)出歷史數(shù)據(jù)，所以在做備份時，可以通過Percona XtraBackup熱備份工具來備份物理文件。

3、搭建從庫時，如果你用mysqldump工具，要先導(dǎo)出表結(jié)構(gòu)文件，再導(dǎo)出數(shù)據(jù)。
1）只導(dǎo)出表結(jié)構(gòu)：
# mysqldump -S /tmp/mysql3306.sock -uroot -p123456 --single-transaction --compact -c -d -q -B test > ./test_schema.sql

導(dǎo)入完表結(jié)構(gòu)后，批量執(zhí)行DDL轉(zhuǎn)換系統(tǒng)版本表，腳本如下（附件里點擊下載）：

# cat convert.php 

注：先安裝php-mysql驅(qū)動
#yum install php php-mysql -y
#php convert.php

2)只導(dǎo)出數(shù)據(jù)：

# mysqldump -S /tmp/mysql3306.sock -uroot -p123456 --single-transaction 
--master-data=2 --compact -c -q -t -B test > test_data.sql

參考文檔：
https://mariadb.com/kb/en/library/system-versioned-tables/