OceanBase自動(dòng)生成回滾SQL的全過程(數(shù)據(jù)庫變更時(shí))
背景
在開發(fā)中,數(shù)據(jù)的變更與維護(hù)工作一般較頻繁。當(dāng)我們執(zhí)行數(shù)據(jù)庫的DML操作時(shí),必須謹(jǐn)慎考慮變更對數(shù)據(jù)可能產(chǎn)生的后果,以及變更是否能夠順利執(zhí)行。若出現(xiàn)意外數(shù)據(jù)丟失、操作失誤或語法錯(cuò)誤等情況,我們必須迅速將數(shù)據(jù)庫恢復(fù)到變更之前的狀態(tài),以確保數(shù)據(jù)的一致性和完整性。然而,回滾操作通常需要開發(fā)人員手動(dòng)編寫回滾SQL腳本,這不僅繁瑣復(fù)雜,而且極易出錯(cuò)。
為了解決這個(gè)問題,ODC(OceanBase Developer Center)V4.2.0支持在執(zhí)行數(shù)據(jù)庫變更任務(wù)時(shí)自動(dòng)生成備份回滾 SQL 以提高開發(fā)效率、減少錯(cuò)誤率,保證數(shù)據(jù)的一致性和完整性。當(dāng)單條誤操作 SQL 受影響的數(shù)據(jù)量在10萬以內(nèi)時(shí),您可使用該方法進(jìn)行數(shù)據(jù)恢復(fù)。
功能體驗(yàn)
點(diǎn)擊「工單」-> 「新建工單」-> 「數(shù)據(jù)庫變更」,在 ODC 的「新建數(shù)據(jù)庫變更」頁面中,勾選「生成備份回滾方案」并填寫工單詳情。
- 點(diǎn)擊「新建」后待工單審批通過。
- ODC 會(huì)根據(jù)您填寫的「SQL內(nèi)容」生成對應(yīng)的「備份回滾方案」,您可以在工單的「任務(wù)詳情」中的「任務(wù)信息」頁面點(diǎn)擊「下載備份回滾方案」以下載 ODC 生成的「備份回滾方案」文件:
也可以在工單「任務(wù)詳情」中的「任務(wù)流程」中點(diǎn)擊「下載備份回滾方案」以下載該文件。
- 如需回滾該工單您可以在此工單的「任務(wù)詳情」頁面的右下腳點(diǎn)擊「回滾」重新發(fā)起數(shù)據(jù)庫變更工單以申請回滾操作。發(fā)起回滾工單后您可以在此工單的「任務(wù)詳情」中的「回滾工單」頁面查看此工單關(guān)聯(lián)的回滾工單。
例如,針對 「SQL內(nèi)容」為以下的數(shù)據(jù)庫變更任務(wù):
update t2 set c2=11 where c1=1; update tab2 set c2=11 where id=1;
生成的「備份回滾方案」示例如下:
/* [SQL]: update t2 set c2=11 where c1=1 [QUERY SQL]: SELECT t2.* FROM t2 WHERE c1=1; */ REPLACE INTO `jingtian_test`.`t2` VALUES (1,11,1); /* [SQL]: update tab2 set c2=11 where id=1 [ERROR MESSAGE]: It is not supported to generate rollback plan for tables without primary key or unique key */
包含以下幾部分內(nèi)容:
- 原始的變更 SQL 語句(僅針對 UPDATE/DELETE 語句)。
- 查詢原始變更數(shù)據(jù)的 SQL 語句。
- 生成的回滾 SQL。
- 若針對一條 UPDATE/DELETE 變更 SQL 無法生成回滾 SQL,則其原因會(huì)顯示在
[ERROR MESSAGE]
中。
ODC 如何自動(dòng)生成一條回滾 SQL
下面是 ODC 根據(jù)一條 SQL 生成對應(yīng)的回滾 SQL 的流程圖:
- SQL 解析:通過 SQL 解析器遍歷 SQL 語法樹從而將一條 SQL 解析為 SQL 對象。
- SQL 校驗(yàn):基于 SQL 對象校驗(yàn)是符合能夠生成對應(yīng)的回滾 SQL 的條件,包括:
- 判斷是否是 UPDATE/DELETE SQL,ODC 僅針對 UPDATE/DELETE 語句生成對應(yīng)的回滾 SQL;
- 若是 OB MYSQL 模式,判斷變更表是否具有主鍵或唯一建,對于沒有主鍵或唯一鍵的表執(zhí)行的變更 SQL 不支持生成回滾 SQL。
- 基于 SQL 對象生成查詢原始變更數(shù)據(jù)的 SQL。
- 查詢原始變更數(shù)據(jù)。
- 基于 SQL 對象和原始變更數(shù)據(jù)生成對應(yīng)的回滾 SQL:
- OB MYSQL 模式針對 UPDATE 語句生成 REPLACE INTO 的回滾 SQL;
- OB ORACLE 模式針對 UPDATE 語句生成 DELETE 語句和 INSERT INTO 語句;
- 針對 DELETE 語句生成 INSERT INTO 語句。
接下來詳解介紹每一個(gè)流程的具體實(shí)現(xiàn)。
SQL 解析
SQL 解析可以理解為對 SQL 進(jìn)行建模,將原始 SQL 語句轉(zhuǎn)化為一個(gè) SQL 對象。我們根據(jù) SQL 的詞法和語法文件可以生成 SQL 的詞法分析器和語法分析器。詞法分析階段將 SQL 語句拆解成 Token 序列,并識別出關(guān)鍵詞、標(biāo)識、常量等,而語法分析階段基于詞法分析的結(jié)果,構(gòu)造出一棵抽象語法數(shù)。例如一條 SQL:
UPDATE tab1, tab2 SET tab1.c2 = 100, tab2.c2=200 WHERE tab1.c1 = tab2.c1;
生成對應(yīng)的抽象語法樹如下圖:
通過遍歷語法樹將其轉(zhuǎn)化為一個(gè) SQL 對象:
可以看到,這個(gè) SQL 對象實(shí)際上是將 SQL 語句結(jié)構(gòu)化了,對 SQL 的各個(gè)部分都定義了相應(yīng)的抽象,比如針對 where 的查詢條件我們定義成了 CompoundExpression
對象。通過將 SQL 語句解析成對象,我們就可以圍繞這個(gè)對象編寫生成對應(yīng)回滾 SQL 的處理邏輯。
SQL 校驗(yàn)
SQL 校驗(yàn)階段就是基于解析出來的 SQL statement 對象去做一些校驗(yàn)的邏輯:
- 判斷該 SQL 的類型是否是 UPDATE/DELETE 類型。
- 針對 OB MYSQL 模式,我們需要做格外的校驗(yàn)變更表是否具有主鍵或者唯一鍵的邏輯判斷。之所以有這個(gè)校驗(yàn)邏輯是由于首先后續(xù)的批量查詢策略邏輯依賴主鍵/唯一鍵,其次針對 UPDATE 語句生成的回滾 SQL 是 REPLACE INTO,該語句也依賴主鍵/唯一鍵。通過 SQL 對象我們可以獲取到 update 語句的 table_references 對象,我們就基于這個(gè)變更的 table 名用以下去查詢校驗(yàn)該 table 是否具有主鍵或者唯一鍵:
-- 查詢主鍵 SHOW INDEX FROM table_name WHERE Non_unique = 0 and Key_name='PRIMARY'; -- 查詢唯一建 SHOW INDEX FROM table_name WHERE Non_unique = 0;
而針對 OB ORACLE 模式,我們直接采用 ROWID 作為每一張變更表的唯一建,因此不需要有校驗(yàn)變更表是否具有主鍵或者唯一鍵的邏輯。
- 校驗(yàn)該變更 SQL 是否符合能夠生成回滾 SQL 的語句。比如如下 SQL:
UPDATE t1, t2 set col1=2222, col2=333 WHERE t1.c1=t2.c1;
由于 table_references 中涉及多個(gè)表,但是 set column_name=... 時(shí)沒有指明該column_name 的表名,導(dǎo)致我們僅根據(jù)這一條 SQL 無法直接確認(rèn)變更的字段屬于哪個(gè)表,因此該 SQL 不符合能夠生成回滾 SQL 的語句。這種情況只需稍做修改,指明列所屬的表就可以支持生成回滾 SQL:
UPDATE t1, t2 set t1.col1=2222, t2.col2=333 WHERE t1.c1=t2.c1;
查詢原始變更數(shù)據(jù)
如何基于一條 SQL 生成對應(yīng)的查詢原始數(shù)據(jù)的 SQL 呢?其實(shí)核心邏輯就是基于解析出的 SQL 對象和 SQL 語句格式去獲取我們需要的信息進(jìn)而拼接出查詢 SQL。拿 UPDATE 語句為例,以下是 OB MYSQL 的 UPDATE 語句的格式:
UPDATE [IGNORE] table_references SET update_asgn_list [WHERE where_condition] [ORDER BY order_list] [LIMIT row_count]; table_references: tbl_name [PARTITION (partition_name,...)] [, ...] update_asgn_list: column_name = expr [, ...] order_list: column_name [ASC|DESC] [, column_name [ASC|DESC]...]
簡單示例
比如基于以下 SQL:
UPDATE tab1, tab2 SET tab1.c2 = 100, tab2.c2=200 WHERE tab1.c1 = tab2.c1;
通過 SQL 解析后基于 SQL 對象我們可以獲取該 SQL Statement 的 table_references、update_asgn_list 和 where_condition,通過 update_asgn_list 我們可以知道該 SQL 涉及的變更表有2個(gè):tab1 和 tab2,進(jìn)而我們就可以針對每個(gè)變更表生成的查詢 SQL :
SELECT * FROM tab1 WHERE tab1.c1 = tab2.c1; SELECT * FROM tab2 WHERE tab1.c1 = tab2.c1;
當(dāng)然以上的示例 SQL 是一個(gè)最簡單的例子,復(fù)雜一點(diǎn)的場景比如涉及 多表 join 和 子查詢 的場景又該怎么生成對應(yīng)的查詢 SQL 呢?其實(shí)不管 SQL 多復(fù)雜我們的核心都是基于 SQL Statement 去拼接出查詢 SQL。
考慮如下涉及多表 join 的 SQL:
UPDATE tab1 v1 inner join tab2 v2 inner join tab3 v3 on v1.c1 = v2.c1 and v1.c1=v3.c1 SET v1.c2=200, v2.c2=200;
針對這條 SQL 我們通過解析 table_references、 update_asgn_list 從而拼接的查詢 SQL 如下:
SELECT v1.* FROM tab1 v1 inner join tab2 v2 inner join tab3 v3 on v1.c1 = v2.c1 and v1.c1=v3.c1; SELECT v2.* FROM tab1 v1 inner join tab2 v2 inner join tab3 v3 on v1.c1 = v2.c1 and v1.c1=v3.c1;
批量查詢策略
獲取到基于原始 SQL 生成的查詢 SQL 后,我們就可以執(zhí)行查詢 SQL 來獲取原始數(shù)據(jù)了。在查詢數(shù)據(jù)的時(shí)候可能會(huì)遇上這個(gè)問題:一次查詢出的數(shù)據(jù)過大,導(dǎo)致內(nèi)存溢出怎么辦?針對這個(gè)問題傳統(tǒng)的處理方式也有很多,比如通過 limit 分頁查詢,但是這種方式在我們的場景下不適用,比如如果原始 SQL 就帶了 limit 那么這種方式就失效了。ODC 采用根據(jù)表的主鍵或唯一鍵(優(yōu)先采用主鍵)拼接批量查詢 SQL 的方式來解決這個(gè)問題。
OB MYSQL 模式
比如我們通過查詢 SQL 可以獲取到表 tab1 的主鍵列為 c1,如果原始的查詢 SQL 如下:
SELECT tab1.* FROM tab1 WHERE c3 > 1;
我們先查詢出所有變更行數(shù)據(jù)的主鍵值:
SELECT tab1.c1 FROM tab1 WHERE c3 > 1;
通過以上 SQL 我們就可以查詢出變更的總行數(shù)以及每一行的主鍵值,若總變更行數(shù)為 1500 且設(shè)置的批量查詢的大小為 1000,也就是每次查詢最多獲取 1000 行的數(shù)據(jù),那么基于以上查詢 SQL 得到的批量查詢 SQL 為:
SELECT tab1.* FROM tab1 WHERE c3 > 1 AND c1 IN (1,2,3, ..., 1000); SELECT tab1.* FROM tab1 WHERE c3 > 1 AND c1 IN (1001,1002,1003, ..., 1500);
這樣以來就可以做到批量查詢原始變更數(shù)據(jù)了。由于增加的 where 條件也是走索引的,因此也不存在性能瓶頸問題。
OB ORACLE 模式
針對 ORACLE 模式我們直接利用 ROWID 來拼接批量查詢 SQL。比如原始 SQL;
SELECT tab1.* FROM tab1 WHERE c3 > 1;
我們先查詢出所有變更行數(shù)據(jù)的 ROWID 值:
SELECT ROWID FROM tab1 WHERE c3 > 1;
那么基于以上查詢 SQL 得到的批量查詢 SQL 為:
SELECT tab1.* FROM tab1 WHERE c3 > 1 AND ROWID IN (...);
生成回滾 SQL
通過以上處理邏輯,我們已經(jīng)獲得了原始變更數(shù)據(jù),那么接下來就是最后一步基于變更行數(shù)據(jù)生成回滾 SQL 了。
UPDATE 語句
OB MYSQL 模式
針對 OB MYSQL 模式,UPDATE 語句生成的對應(yīng)回滾 SQL 為 REPLACE INTO 語句,REPLACE INTO 用于實(shí)時(shí)覆蓋寫入數(shù)據(jù)。寫入數(shù)據(jù)時(shí),會(huì)先根據(jù)主鍵或唯一鍵判斷待寫入的數(shù)據(jù)是否已經(jīng)存在于表中,并根據(jù)判斷結(jié)果選擇不同的方式寫入數(shù)據(jù):
- 如果待寫入數(shù)據(jù)已經(jīng)存在,則先刪除該行數(shù)據(jù),然后插入新的數(shù)據(jù)。
- 如果待寫入數(shù)據(jù)不存在,則直接插入新數(shù)據(jù)。
這里要求變更表必須有主鍵或者是唯一索引,否則 replace into 會(huì)直接插入數(shù)據(jù)(等效于 INSERT INTO),這可能會(huì)導(dǎo)致表中出現(xiàn)重復(fù)的數(shù)據(jù)。
最終 ODC 生成的完整的備份回滾 SQL 文件如下所示:
/* [SQL]: update tab set col2=2 where col=1; [QUERY SQL]: SELECT tab.* FROM tab WHERE col=1; */ REPLACE INTO `schema_name`.`tab` VALUES (1,1,1);
OB ORACLE 模式
針對 OB ORACLE 模式,會(huì)先生成 DELTE 語句來刪除變更數(shù)據(jù),然后生成 INSERT INTO 語句插入原始的變更前的數(shù)據(jù),從而避免插入數(shù)據(jù)時(shí)存在主鍵或者唯一鍵的沖突。
/* [SQL]: UPDATE TAB1 set c2=2 where c1=1 [QUERY SQL]: SELECT TAB1.* FROM TAB1 WHERE c1=1; */ DELETE FROM TAB1 WHERE c1=1; INSERT INTO "TEST"."TAB1" VALUES (1,1);
DELETE 語句
針對 DELETE 語句,生成的回滾 SQL 為 INSERT INTO 語句。例如針對以下變更 SQL:
delete from tab where col=1;
最終生成的備份回滾文件內(nèi)容如下:
/* [SQL]: delete from tab where col=1; [QUERY SQL]: SELECT tab.* FROM tab WHERE col=1; */ INSERT INTO `schema_`.`tab` VALUES (1,1,1);
結(jié)語
以上就是OceanBase自動(dòng)生成回滾SQL的全過程(數(shù)據(jù)庫變更時(shí))的詳細(xì)內(nèi)容,更多關(guān)于OceanBase生成回滾SQL的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
面向云服務(wù)的GaussDB全密態(tài)數(shù)據(jù)庫現(xiàn)狀及問題小結(jié)
全密態(tài)數(shù)據(jù)庫,顧名思義與大家所理解的流數(shù)據(jù)庫、圖數(shù)據(jù)庫一樣,就是專門處理密文數(shù)據(jù)的數(shù)據(jù)庫系統(tǒng),這篇文章主要介紹了面向云服務(wù)的GaussDB全密態(tài)數(shù)據(jù)庫,未來GaussDB會(huì)將該能力逐步開源到openGauss,與社區(qū)共同推進(jìn)和完善全密態(tài)數(shù)據(jù)庫解決方案,一起打造數(shù)據(jù)庫安全生態(tài)2024-02-02數(shù)據(jù)庫設(shè)計(jì)的完整性約束表現(xiàn)在哪些方面
數(shù)據(jù)完整性是指數(shù)據(jù)的正確性、完備性和一致性,是衡量數(shù)據(jù)庫質(zhì)量好壞的規(guī)范。數(shù)據(jù)庫完整性由各式各樣的完整性約束來確保,因而可以說數(shù)據(jù)庫完整性規(guī)劃即是數(shù)據(jù)庫完整性約束的規(guī)劃。那么,數(shù)據(jù)庫設(shè)計(jì)的完整性約束表現(xiàn)哪些方面?2015-10-10如何判斷a、b、c三個(gè)字段同時(shí)為0則不顯示這條數(shù)據(jù)
有時(shí)候我們需要判斷當(dāng)a、b、c三個(gè)字段同時(shí)為0則不顯示,下面這個(gè)方法不錯(cuò),需要的朋友可以參考下2013-08-08SunlightDB 2017新型區(qū)塊鏈數(shù)據(jù)庫
這篇文章主要為大家詳細(xì)介紹了SunlightDB 2017新型區(qū)塊鏈數(shù)據(jù)庫的相關(guān)資料,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2018-01-01用戶管理的備份(一致性備份、非一致性備份、脫機(jī)備份、聯(lián)機(jī)備份)
用戶管理的備份(一致性備份、非一致性備份、脫機(jī)備份、聯(lián)機(jī)備份)說明文檔。2009-05-05