背景

在開發(fā)中，數(shù)據(jù)的變更與維護(hù)工作一般較頻繁。當(dāng)我們執(zhí)行數(shù)據(jù)庫(kù)的DML操作時(shí)，必須謹(jǐn)慎考慮變更對(duì)數(shù)據(jù)可能產(chǎn)生的后果，以及變更是否能夠順利執(zhí)行。若出現(xiàn)意外數(shù)據(jù)丟失、操作失誤或語(yǔ)法錯(cuò)誤等情況，我們必須迅速將數(shù)據(jù)庫(kù)恢復(fù)到變更之前的狀態(tài)，以確保數(shù)據(jù)的一致性和完整性。然而，回滾操作通常需要開發(fā)人員手動(dòng)編寫回滾SQL腳本，這不僅繁瑣復(fù)雜，而且極易出錯(cuò)。

為了解決這個(gè)問(wèn)題，ODC（OceanBase Developer Center）V4.2.0支持在執(zhí)行數(shù)據(jù)庫(kù)變更任務(wù)時(shí)自動(dòng)生成備份回滾 SQL 以提高開發(fā)效率、減少錯(cuò)誤率，保證數(shù)據(jù)的一致性和完整性。當(dāng)單條誤操作 SQL 受影響的數(shù)據(jù)量在10萬(wàn)以內(nèi)時(shí)，您可使用該方法進(jìn)行數(shù)據(jù)恢復(fù)。

功能體驗(yàn)

點(diǎn)擊「工單」-> 「新建工單」-> 「數(shù)據(jù)庫(kù)變更」，在 ODC 的「新建數(shù)據(jù)庫(kù)變更」頁(yè)面中，勾選「生成備份回滾方案」并填寫工單詳情。

• 點(diǎn)擊「新建」后待工單審批通過(guò)。
• ODC 會(huì)根據(jù)您填寫的「SQL內(nèi)容」生成對(duì)應(yīng)的「?jìng)浞莼貪L方案」，您可以在工單的「任務(wù)詳情」中的「任務(wù)信息」頁(yè)面點(diǎn)擊「下載備份回滾方案」以下載 ODC 生成的「?jìng)浞莼貪L方案」文件：

也可以在工單「任務(wù)詳情」中的「任務(wù)流程」中點(diǎn)擊「下載備份回滾方案」以下載該文件。

• 如需回滾該工單您可以在此工單的「任務(wù)詳情」頁(yè)面的右下腳點(diǎn)擊「回滾」重新發(fā)起數(shù)據(jù)庫(kù)變更工單以申請(qǐng)回滾操作。發(fā)起回滾工單后您可以在此工單的「任務(wù)詳情」中的「回滾工單」頁(yè)面查看此工單關(guān)聯(lián)的回滾工單。

例如，針對(duì) 「SQL內(nèi)容」為以下的數(shù)據(jù)庫(kù)變更任務(wù)：

update t2 set c2=11 where c1=1;
update tab2 set c2=11 where id=1;

生成的「?jìng)浞莼貪L方案」示例如下：

/* 
[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 語(yǔ)句（僅針對(duì) UPDATE/DELETE 語(yǔ)句）。
• 查詢?cè)甲兏鼣?shù)據(jù)的 SQL 語(yǔ)句。
• 生成的回滾 SQL。
• 若針對(duì)一條 UPDATE/DELETE 變更 SQL 無(wú)法生成回滾 SQL，則其原因會(huì)顯示在 [ERROR MESSAGE]中。

ODC 如何自動(dòng)生成一條回滾 SQL

下面是 ODC 根據(jù)一條 SQL 生成對(duì)應(yīng)的回滾 SQL 的流程圖：

• SQL 解析：通過(guò) SQL 解析器遍歷 SQL 語(yǔ)法樹從而將一條 SQL 解析為 SQL 對(duì)象。
• SQL 校驗(yàn)：基于 SQL 對(duì)象校驗(yàn)是符合能夠生成對(duì)應(yīng)的回滾 SQL 的條件，包括：
  1. 判斷是否是 UPDATE/DELETE SQL，ODC 僅針對(duì) UPDATE/DELETE 語(yǔ)句生成對(duì)應(yīng)的回滾 SQL；
  2. 若是 OB MYSQL 模式，判斷變更表是否具有主鍵或唯一建，對(duì)于沒有主鍵或唯一鍵的表執(zhí)行的變更 SQL 不支持生成回滾 SQL。
• 基于 SQL 對(duì)象生成查詢?cè)甲兏鼣?shù)據(jù)的 SQL。
• 查詢?cè)甲兏鼣?shù)據(jù)。
• 基于 SQL 對(duì)象和原始變更數(shù)據(jù)生成對(duì)應(yīng)的回滾 SQL：
  • OB MYSQL 模式針對(duì) UPDATE 語(yǔ)句生成 REPLACE INTO 的回滾 SQL；
  • OB ORACLE 模式針對(duì) UPDATE 語(yǔ)句生成 DELETE 語(yǔ)句和 INSERT INTO 語(yǔ)句；
  • 針對(duì) DELETE 語(yǔ)句生成 INSERT INTO 語(yǔ)句。

接下來(lái)詳解介紹每一個(gè)流程的具體實(shí)現(xiàn)。

SQL 解析

SQL 解析可以理解為對(duì) SQL 進(jìn)行建模，將原始 SQL 語(yǔ)句轉(zhuǎn)化為一個(gè) SQL 對(duì)象。我們根據(jù) SQL 的詞法和語(yǔ)法文件可以生成 SQL 的詞法分析器和語(yǔ)法分析器。詞法分析階段將 SQL 語(yǔ)句拆解成 Token 序列，并識(shí)別出關(guān)鍵詞、標(biāo)識(shí)、常量等，而語(yǔ)法分析階段基于詞法分析的結(jié)果，構(gòu)造出一棵抽象語(yǔ)法數(shù)。例如一條 SQL：

UPDATE tab1, tab2 SET tab1.c2 = 100, tab2.c2=200 WHERE tab1.c1 = tab2.c1;

生成對(duì)應(yīng)的抽象語(yǔ)法樹如下圖：

通過(guò)遍歷語(yǔ)法樹將其轉(zhuǎn)化為一個(gè) SQL 對(duì)象：

可以看到，這個(gè) SQL 對(duì)象實(shí)際上是將 SQL 語(yǔ)句結(jié)構(gòu)化了，對(duì) SQL 的各個(gè)部分都定義了相應(yīng)的抽象，比如針對(duì) where 的查詢條件我們定義成了 CompoundExpression對(duì)象。通過(guò)將 SQL 語(yǔ)句解析成對(duì)象，我們就可以圍繞這個(gè)對(duì)象編寫生成對(duì)應(yīng)回滾 SQL 的處理邏輯。

SQL 校驗(yàn)

SQL 校驗(yàn)階段就是基于解析出來(lái)的 SQL statement 對(duì)象去做一些校驗(yàn)的邏輯：

• 判斷該 SQL 的類型是否是 UPDATE/DELETE 類型。
• 針對(duì) OB MYSQL 模式，我們需要做格外的校驗(yàn)變更表是否具有主鍵或者唯一鍵的邏輯判斷。之所以有這個(gè)校驗(yàn)邏輯是由于首先后續(xù)的批量查詢策略邏輯依賴主鍵/唯一鍵，其次針對(duì) UPDATE 語(yǔ)句生成的回滾 SQL 是 REPLACE INTO，該語(yǔ)句也依賴主鍵/唯一鍵。通過(guò) SQL 對(duì)象我們可以獲取到 update 語(yǔ)句的 table_references 對(duì)象，我們就基于這個(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;

而針對(duì) OB ORACLE 模式，我們直接采用 ROWID 作為每一張變更表的唯一建，因此不需要有校驗(yàn)變更表是否具有主鍵或者唯一鍵的邏輯。

• 校驗(yàn)該變更 SQL 是否符合能夠生成回滾 SQL 的語(yǔ)句。比如如下 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 無(wú)法直接確認(rèn)變更的字段屬于哪個(gè)表，因此該 SQL 不符合能夠生成回滾 SQL 的語(yǔ)句。這種情況只需稍做修改，指明列所屬的表就可以支持生成回滾 SQL：

UPDATE t1, t2 set t1.col1=2222, t2.col2=333 WHERE t1.c1=t2.c1; 

查詢?cè)甲兏鼣?shù)據(jù)

如何基于一條 SQL 生成對(duì)應(yīng)的查詢?cè)紨?shù)據(jù)的 SQL 呢？其實(shí)核心邏輯就是基于解析出的 SQL 對(duì)象和 SQL 語(yǔ)句格式去獲取我們需要的信息進(jìn)而拼接出查詢 SQL。拿 UPDATE 語(yǔ)句為例，以下是 OB MYSQL 的 UPDATE 語(yǔ)句的格式：

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]...]

簡(jiǎn)單示例

比如基于以下 SQL：

UPDATE tab1, tab2 SET tab1.c2 = 100, tab2.c2=200 WHERE tab1.c1 = tab2.c1;

通過(guò) SQL 解析后基于 SQL 對(duì)象我們可以獲取該 SQL Statement 的 table_references、update_asgn_list 和 where_condition，通過(guò) update_asgn_list 我們可以知道該 SQL 涉及的變更表有2個(gè)：tab1 和 tab2，進(jìn)而我們就可以針對(duì)每個(gè)變更表生成的查詢 SQL ：

SELECT * FROM tab1 WHERE tab1.c1 = tab2.c1;
 
SELECT * FROM tab2 WHERE tab1.c1 = tab2.c1;

當(dāng)然以上的示例 SQL 是一個(gè)最簡(jiǎn)單的例子，復(fù)雜一點(diǎn)的場(chǎng)景比如涉及 多表 join 和 子查詢 的場(chǎng)景又該怎么生成對(duì)應(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;

針對(duì)這條 SQL 我們通過(guò)解析 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 來(lái)獲取原始數(shù)據(jù)了。在查詢數(shù)據(jù)的時(shí)候可能會(huì)遇上這個(gè)問(wèn)題：一次查詢出的數(shù)據(jù)過(guò)大，導(dǎo)致內(nèi)存溢出怎么辦？針對(duì)這個(gè)問(wèn)題傳統(tǒng)的處理方式也有很多，比如通過(guò) limit 分頁(yè)查詢，但是這種方式在我們的場(chǎng)景下不適用，比如如果原始 SQL 就帶了 limit 那么這種方式就失效了。ODC 采用根據(jù)表的主鍵或唯一鍵（優(yōu)先采用主鍵）拼接批量查詢 SQL 的方式來(lái)解決這個(gè)問(wèn)題。

OB MYSQL 模式

比如我們通過(guò)查詢 SQL 可以獲取到表 tab1 的主鍵列為 c1，如果原始的查詢 SQL 如下：

SELECT tab1.* FROM tab1 WHERE c3 > 1；

我們先查詢出所有變更行數(shù)據(jù)的主鍵值：

SELECT tab1.c1 FROM tab1 WHERE c3 > 1；

通過(guò)以上 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);

這樣以來(lái)就可以做到批量查詢?cè)甲兏鼣?shù)據(jù)了。由于增加的 where 條件也是走索引的，因此也不存在性能瓶頸問(wèn)題。

OB ORACLE 模式

針對(duì) ORACLE 模式我們直接利用 ROWID 來(lái)拼接批量查詢 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

通過(guò)以上處理邏輯，我們已經(jīng)獲得了原始變更數(shù)據(jù)，那么接下來(lái)就是最后一步基于變更行數(shù)據(jù)生成回滾 SQL 了。

UPDATE 語(yǔ)句

OB MYSQL 模式

針對(duì) OB MYSQL 模式，UPDATE 語(yǔ)句生成的對(duì)應(yīng)回滾 SQL 為 REPLACE INTO 語(yǔ)句，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 模式

針對(duì) OB ORACLE 模式，會(huì)先生成 DELTE 語(yǔ)句來(lái)刪除變更數(shù)據(jù)，然后生成 INSERT INTO 語(yǔ)句插入原始的變更前的數(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 語(yǔ)句

針對(duì) DELETE 語(yǔ)句，生成的回滾 SQL 為 INSERT INTO 語(yǔ)句。例如針對(duì)以下變更 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é)語(yǔ)

以上就是OceanBase自動(dòng)生成回滾SQL的全過(guò)程(數(shù)據(jù)庫(kù)變更時(shí))的詳細(xì)內(nèi)容，更多關(guān)于OceanBase生成回滾SQL的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論