AnalyticDBPostgreSQL版如何选择表分布策略_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

本文介绍AnalyticDB PostgreSQL版如何选择表分布策略。

选择表分布策略

AnalyticDB PostgreSQL支持哈希（HASH）分布、随机（RANDOMLY）分布、复制（REPLICATED）分布三种分布方式。

CREATE TABLE <table_name> (...) [ DISTRIBUTED BY (<column>  [,..] ) | DISTRIBUTED RANDOMLY | DISTRIBUTED REPLICATED ]

说明

AnalyticDB PostgreSQL版4.3版本只支持哈希（HASH）分布和随机（RANDOMLY）分布，复制（REPLICATED）分布为AnalyticDB PostgreSQL版6.0版本新增特性。

建表语句CREATE TABLE支持如下三个分布策略的子句：

分布方式

说明

哈希分布

DISTRIBUTED BY (column, [ ... ])

数据将根据分布列的哈希值将各个行分布到指定计算节点上，相同的哈希值会始终散列到同一计算节点。为保障数据可以均匀分布在各个节点上，建议您选择唯一键（例如主键）作为分布键。

AnalyticDB PostgreSQL版的默认分布策略为哈希分布，如果建表时未指定DISTRIBUTED子句，系统会选择主键或表的第一个合适的列作为分布键。如果表中没有合适的列，系统将会使用随机分布策略。

随机分布

DISTRIBUTED RANDOMLY

系统会按循环的方式将数据分布到各个计算节点上，但是相同值的数据可能不会分布到同一个计算节点。

随机分布仅建议您在没有合适的列作为分布列时使用。

复制分布

DISTRIBUTED REPLICATED

系统会在每个计算节点都保存一份表的全量数据。

如果数据库中存在大表与小表join的场景，您可以将足够小的表设置为复制分布来提升性能。

示例如下：

哈希分布

CREATE TABLE products (name varchar(40), 
                       prod_id integer,
                       supplier_id integer)
                       DISTRIBUTED BY (prod_id);

随机分布

CREATE TABLE random_stuff (things text,
                           doodads text,
                           etc text)
                           DISTRIBUTED RANDOMLY;

复制分布

CREATE TABLE replicated_stuff (things text,
                           doodads text,
                           etc text)
                           DISTRIBUTED REPLICATED;

AnalyticDB PostgreSQL支持节点裁剪功能，对于按分布键的简单查询（包括UPDATE和DELETE等语句），支持按节点的分布键进行数据节点裁剪。例如products表的分布键为prod_id列，以下查询语句只会被发送到满足prod_id=101的计算节点上执行，可以有效提升该查询的执行性能。

select * from products where prod_id = 101;

表分布键选择原则

合理规划分布键，对表查询的性能至关重要，有以下原则需要关注：

尽量选择数据分布均匀的列作为分布键，若分布键数据分布不均匀，可能会导致数据倾斜。数据倾斜会导致部分计算节点存储的数据过多，查询负载大，查询耗时变长。因此请不要选择bool类型、时间日期类型的列作为分布键。
选择经常作为查询条件的列作为分布键，可以实现按分布键进行节点裁剪。
您可以选择一个或多个列作为分布键，示例如下：
```
create table t1(c1 int, c2 int) distributed by (c1,c2);
```
尽量不要选择随机分布。使用随机分布将无法使用本地关联以及节点裁剪等功能。
选择经常需要JOIN的列作为分布键，可以实现本地关联（Collocated JOIN）计算（如图一所示），因为JOIN键和分布键一致时，可以在计算节点内部完成JOIN。否则需要将一个表进行重分布（Redistribute motion）来实现重分布关联（Redistributed Join）（如图二所示）或者广播其中小表（Broadcast motion）来实现广播关联（Broadcast Join）（如图三所示），重分布关联和广播关联两种方式都会产生较大的网络开销。

本地关联重分布关联广播关联

表分布键的约束

分布键的列不能被更新（UPDATE）。

主键和唯一键必须包含分布键。例如：

create table t1(c1 int, c2 int, primary key (c1)) distributed by (c2);

说明

由于主键c1不包含分布键c2，所以建表语句返回失败。

ERROR: PRIMARY KEY and DISTRIBUTED BY definitions incompatible

Geometry类型和用户自定义数据类型不能作为分布键。

数据倾斜检查和处理

当某些表上的查询性能差时，可以查看是否是分区键设置不合理造成了数据倾斜，例如：

create table t1(c1 int, c2 int) distributed by (c1);

您可以通过下述语句来查看表的数据倾斜情况。

select gp_segment_id,count(1) from  t1 group by 1 order by 2 desc;
 gp_segment_id | count  
---------------+--------
             2 | 131191
             0 |     72
             1 |     68
(3 rows)

如果发现某些 Segment上存储的数据明显多于其他 Segment，该表存在数据倾斜。建议选取数据分布平均的列作为分布列，比如通过ALTER TABLE命令更改C2为分布键。

alter table t1 set distributed by (c2);

表t1的分布键被改为c2，该表的数据按照c2被重新分布，数据不再倾斜。