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