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完成后存储会下降，更多详情请参见技术原理。