UPDATE - Hologres - 阿里雲

UPDATE語句用於更新目標表指定列的行資料。本文為您介紹在Hologres中UPDATE語句的用法。

命令介紹

UPDATE命令的文法如下所示。

UPDATE <table> [ * ] [ [ AS ] <alias> ]
    SET { <column> = { <expression> } |
          ( <column> [, ...] ) = ( { <expression> } [, ...] ) } [, ...]
    [ FROM <from_list> ]
    [ WHERE <condition> ]

參數說明如下表所示。

參數	描述
table	更新目標表的名稱。
alias	別名。目標表的替代名稱。
column	更新目標表中目標列的名稱。
expression	運算式
from_list	資料來源的列名稱。
condition	更新目標表的條件。

技術原理

在Hologres中，每一張表都會有一個資料檔案、主鍵索引檔案以及標記檔案，主鍵索引檔案詳情請參見主鍵Primary Key。標記檔案主要用在DELETE、UPDATE、INSERT ON CONFLICT等有資料刪除、更新的情境中。以如下SQL為例來介紹Hologres UPDATE原理。

create table update_test (
col1 text NOT NULL PRIMARY KEY,
col2 text
);

UPDATE update_test SET col2 = 'tom' where col1 = 'a1';

示意圖如下。 UPDATE 資料更新時，會先將資料寫入到記憶體表（Mem Table），然後非同步Flush到檔案。對於行存表，會直接將新資料Flush成一個新的檔案，然後在Compaction的時候合并掉舊資料。對於列存表，記憶體中會構建一個標記表，記錄刪除資料所在的檔案號（file id）和行號（row id），然後進行Flush操作，其中新資料會Flush成一份新的檔案，標記表也會Flush成一個標記檔案。後台會將標記檔案和資料檔案進行Compaction，在Compaction的過程中就會將舊資料給清理掉，併合並新資料。為了更新的速度儘可能的快，後台會先將資料寫完，待非同步Compaction時再執行壓縮和整理，因此在資料更新過程中，會看到資料的儲存會一定的膨脹，待資料更新之後，Compaction完成後執行個體儲存用量會下降。

從更新的原理中，可以看到列存表總是會有一次標記表的記錄和反查過程，所以更新效率：行存表>列存表。

結果表有主鍵

如果為表設定了主鍵（PK），那麼主鍵索引檔案就可以通過PK快速定位到RID，然後定位到資料檔案。在UPDATE的過程中也是如此，可以通過主鍵快速過濾出要更新的檔案，減少檔案掃描；如果沒有主鍵，更新就很容易退化成全表更新，導致效能變差。詳情請參見主鍵Primary Key。

局部更新

局部更新是指部分列更新，缺失的列不更新。局部更新是Hologres特有的更新功能，能夠滿足更多的業務需求。局部更新的原理同整行更新一致，但在細節處有一些略微差異：

行存表因為是LSM結構，局部更新的資料以Append Only的方式寫入。
列存需要先查詢缺失列的值再寫入，會有更大的消耗。
行列混合存同樣需要先查詢缺失的列，但和列存不同的是，行列混合存查詢缺失列時會通過行存檔案進行查詢，代價會更小。

因此局部更新在效能上：行存>行列共存>列存。

說明

以上描述的是SQL走Fixed Plan情境，局部更新效能：行存>行列共存>列存。但如果局部更新未走Fixed Plan，局部更新相當於是兩表Join，效能：列存>行列共存>行存。

局部更新樣本如下：

--局部更新樣本：
create table update_test2 (
col1 text NOT NULL PRIMARY KEY,
col2 text,
col3 text
);

INSERT INTO update_test2 VALUES ('a1','a2','a3'),('a11','a22','a33');

--update局部更新
UPDATE update_test2 SET col2 = 'tom' where col1 = 'a1';

--局部更新的另外一種寫法，通過insert on conflict局部更新
INSERT INTO update_test2 (col1,col2) VALUES ('a1','tom')
ON CONFLICT(col1) DO UPDATE
SET col2 = EXCLUDED.col2;

--局部更新後的結果
 col1 | col2 | col3
------+------+------
 a1   | tom  | a3
 a11  | a22  | a33
(2 rows)

使用限制

Hologres不支援更新Distribution Key。
Hologres不支援直接更新分區表父表，您只能更新具體的分區表子表。
推薦使用Fixed Plan最佳化Update執行效率，參考UPDATE情境。

使用樣本

更新表的樣本語句如下。

CREATE TABLE update_test (
  a text primary key, 
  b int not null, 
  c text not null, 
  d text);  

INSERT INTO update_test VALUES ('b1', 10, '', '');

UPDATE update_test SET b = b + 10 where a = 'b1';
UPDATE update_test SET c = 'new_' || a, d = null where b = 20;
UPDATE update_test SET (b,c,d) = (1, 'test_c', 'test_d'); 

CREATE TABLE tmp(a int);
INSERT INTO tmp VALUES (2);
UPDATE update_test SET b = tmp.a FROM tmp;

更多關於UPDATE命令的詳情，請參見PostgreSQL官網文檔。

常見問題

在執行UPDATE命令時，為什麼監控指標中儲存用量先上漲，UPDATE完成後儲存用量又下降？常見問題

根據UPDATE的技術原理，UPDATE時會將老資料做標記，新資料會Flush成新的小檔案，後台會將這些小檔案做Compaction，在Compaction的過程中就會將老資料給清理掉，併合並新資料。為了更新速度儘可能的快，後台會先將資料寫完，待非同步Compaction時再執行壓縮和整理，因此會看到在資料更新過程中，資料的儲存會一定的膨脹，等資料更新之後，Compaction完成後儲存會下降，更多詳情請參見技術原理。