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';示意图如下。
数据更新时，会先将数据写入到内存表（Mem Table），然后异步Flush到文件。对于行存表，会直接将新数据Flush成一个新的文件，然后在Compaction的时候合并掉旧数据。对于列存表，内存中会构建一个标记表，记录删除数据所在的文件号（file id）和行号（row id），然后进行Flush操作，其中新数据会Flush成一份新的文件，标记表也会Flush成一个标记文件。后台会将标记文件和数据文件进行Compaction，在Compaction的过程中就会将旧数据给清理掉，并合并新数据。为了更新的速度尽可能的快，后台会先将数据写完，待异步Compaction时再执行压缩和整理，因此在数据更新过程中，会看到数据的存储会一定的膨胀，待数据更新之后，Compaction完成后实例存储用量会下降。Hologres高性能写入更新原理详情请参见Hologres高性能写入技术大揭秘。
从更新的原理中，可以看到列存表总是会有一次标记表的记录和反查过程，所以更新效率：行存表>列存表。
结果表有主键
如果为表设置了主键（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完成后存储会下降，更多详情请参见技术原理。