915
HybridDB · 最佳實踐 · HybridDB 數據合並的方法與原理
引言
剛開始使用HybridDB的用戶，有個問的比較多的問題：如何快速做數據“合並”（Merge）？所謂“合並”，就是把數據新版本更新到HybridDB中。如果數據已經存在，則將它們替換為新版本；如果不存在，將它們插入數據庫中。一般是離線的做這種數據合並，例如每天一次批量把數據更新到HybridDB中。也有客戶需要實時的更新，即做到分鍾級甚至秒級延遲。這裏我們介紹一下HybridDB中數據合並的方法和背後原理。
簡單更新過程
無論怎麼做數據合並，都是對數據的修改，即Update、Delete、Insert、Copy等操作。我們先要了解一下HybridDB中的數據更新過程。我們以用戶發起一次Update操作為例（對列存表單行記錄的更新），整個流程如下圖所示。
其中的步驟說明如下：
-
用戶把Update的SQL請求發送到主節點；
-
主節點發起分布式事務，並對被Update的表加鎖（HybridDB不允許並行的Update同一張表），然後把更新請求分發到對應的子節點。
-
子節點通過索引掃描，定位到要更新的數據，並更新數據。對於列存表，更新邏輯其實就是刪除舊的數據行，並在表的尾端寫入新的數據行。（列存表）被更新的數據頁麵會寫入內存緩存區，對應的表文件長度的變化（因為尾端寫入了數據，所以數據表對應的文件長度增大了）會寫入日誌（xlog文件）。
-
在Update命令結束前，內存中的被更新的數據頁麵和xlog日誌，都要同步到Mirror節點。同步完成後，主節點結束分布式事務，返回用戶執行成功的消息。
可以看出，整個過程的鏈條很長，SQL語句解析、分布式事務、鎖，主節點子節點之間的連接建立、子節點與Mirror數據和日誌同步等操作，都會耗費CPU或IO資源，同時拖慢整個請求的響應時間。因此，對於HybridDB來說，應該盡量避免單行數據的更新，而是盡量批量的更新數據，也就是盡量做到：
-
盡量把更新放到一個SQL語句，減少語句解析、節點通信、數據同步等開銷；
-
盡量把更新放到一個事務，避免不必要的事務開銷。
簡而言之，就是數據的合並和更新，盡量以”成批“的形式進行。下麵我們看看，如何批量的做數據更新。
批量Update
假如我們要Update很多獨立數據行，怎麼才能用一個SQL來實現呢？
我們假設有張表target_table需要做更新（稱為目標表），這張表的定義如下。一般目標表都非常大，這裏我們往target_table裏麵插入1千萬數據。為了能快速更新，target_table上要有索引。這裏我們定義了primary key，會隱含的創建一個唯一值索引（unique index）。
create table target_table(c1 int, c2 int, primary key (c1));
insert into target_table select generate_series(1, 10000000);
為了做批量的Update，需要用到中間表（Stage Table），其實就是為了更新數據臨時創建的表。為了更新target_table的數據，可以先把新數據插入到中間表source_table中。然後，把新數據通過COPY命令、OSS外部表等方式導入到source_table。這裏為簡單起見，我們直接插入一些數據。
create table source_table(c1 int, c2 int);
insert into source_table select generate_series(1, 100), generate_series(1,100);
source_table數據準備好後，執行下麵的update set … from … where ..語句，即可實現批量的Update。注意，為了最大限度的使用到索引，在執行Update前，要使用set opitimzer=on啟用ORCA優化器（如果不啟用ORCA優化器，則需要執行set enable_nestloop = on才能使用到索引）。
set optimizer=on;
update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;
這種Update的執行計劃如下：
=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Update (cost=0.00..586.10 rows=25 width=1)
-> Result (cost=0.00..581.02 rows=50 width=26)
-> Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..581.02 rows=50 width=22)
Hash Key: public.target_table.c1
-> Assert (cost=0.00..581.01 rows=50 width=22)
Assert Cond: NOT public.target_table.c1 IS NULL
-> Split (cost=0.00..581.01 rows=50 width=22)
-> Nested Loop (cost=0.00..581.01 rows=25 width=18)
Join Filter: true
-> Table Scan on source_table (cost=0.00..431.00 rows=25 width=8)
-> Index Scan using target_table_pkey on target_table (cost=0.00..150.01 rows=1 width=14)
Index Cond: public.target_table.c1 = source_table.c1
可以看到，HybridDB“聰明”的選擇了索引。但是，如果往source_table裏麵加入更多數據，優化器會認為使用Nest Loop關聯方法+索引掃描，不如不使用索引高效，而是會選取Hash關聯方法+表掃描方式執行。例如：
postgres=> insert into source_table select generate_series(1, 1000), generate_series(1,1000);
INSERT 0 1000
postgres=> analyze source_table;
ANALYZE
postgres=> explain update target_table set c2 = source_table.c2 from source_table where target_table.c1= source_table.c1;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Update (cost=0.00..1485.82 rows=275 width=1)
-> Result (cost=0.00..1429.96 rows=550 width=26)
-> Assert (cost=0.00..1429.94 rows=550 width=22)
Assert Cond: NOT public.target_table.c1 IS NULL
-> Split (cost=0.00..1429.93 rows=550 width=22)
-> Hash Join (cost=0.00..1429.92 rows=275 width=18)
Hash Cond: public.target_table.c1 = source_table.c1
-> Table Scan on target_table (cost=0.00..477.76 rows=2500659 width=14)
-> Hash (cost=431.01..431.01 rows=275 width=8)
-> Table Scan on source_table (cost=0.00..431.01 rows=275 width=8)
上述批量的Update方式，減少了SQL編譯、節點間通信、事務等開銷，可以大大提升數據更新性能並減少對資源的消耗。
批量Delete
對於Delete操作，采用和上述批量Update類似的中間表，然後使用下麵的帶有“Using”子句的Delete來實現批量刪除：
delete from target_table using source_table where target_table.c1 = source_table.c1;
可以看到，這種批量的Delete同樣使用了索引。
explain delete from target_table using source_table where target_table.c1 = source_table.c1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Delete (slice0; segments: 4) (rows=50 width=10)
-> Nested Loop (cost=0.00..41124.40 rows=50 width=10)
-> Seq Scan on source_table (cost=0.00..6.00 rows=50 width=4)
-> Index Scan using target_table_pkey on target_table (cost=0.00..205.58 rows=1 width=14)
Index Cond: target_table.c1 = source_table.c1
利用Delete + Insert做數據合並
回到本文剛開始的問題，如何實現批量的數據合並？做數據合並時，我們先把待合入的數據放入中間表中。如果我們預先知道待合入的數據，在目標表中都已經有對應的數據行，即我們通過Update語句即可實現數據合入。但多數情況下，待合入的數據中，一部分是在目標表中已存在記錄的數據，還有一部分是新增的，目標表中沒有對應記錄。這時候，使用一次批量的Delete + 一次批量的Insert即可：
set optimizer=on;
delete from target_table using source_table where target_table.c1 = source_table.c1;
insert into target_table select * from source_table;
利用Values()表達式做實時更新
使用中間表，需要維護中間表生命周期。有的用戶想實時的批量更新數據到HybridDB，即持續性的同步數據或合並數據到HybridDB。如果采用上麵的方法，需要反複的創建、刪除（或Truncate）中間表。其實，可以利用Values表達式，達到類似中間表的效果，但不用維護表。方法是先將待更新的數據拚成一個Values表達式，然後按如下方式執行Update或Delete：
update target_table set c2 = t.c2 from (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1=t.c1
delete from target_table using (values(1,1),(2,2),(3,3),…(2000,2000)) as t(c1,c2) where target_table.c1 = t.c1
注意，使用set optimizer=on;或set enable_nestloop=on;都可以生成使用索引的查詢計劃。比較複雜的情形，比如索引字段有多個、涉及分區表等，必須要使用ORCA優化器才能匹配上索引。
總結
上麵我們簡單介紹了HybridDB的批量數據合並和更新的最佳實踐。利用這些方法，無論是在每天一次或多次的ETL操作，還是實時更新數據的場景，都可以把HybridDB的數據更新效率充分發揮出來。
最後更新：2017-05-21 09:01:46