表分布定义

本文介绍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被重新分布,数据不再倾斜。