Greenplum表的管理实践-2 表分布策略

5 Greenplum表的管理实践-2 表分布策略

本文主要从表的分布策略，Greenplum是一个大规模并行处理数据库，它由一个master和多个segment组成，其数据按照设定的分布策略分布于各个segment上。数据表的单个行会被分配到一个或多个segment上，但是有这么多的segment，它到底会被分到哪个或哪些segment上呢？分布策略会告诉我们。

5.1 表分布策略

在 Greenplum 5 中，有2种分布策略：

• 哈希分布
• 随机分布

在 Greenplum 6 中，添加了另一个策略：

• 哈希分布
• 随机分布
• 复制分布

哈希分布

要使用这一策略，需要在创建表使用 “DISTRIBUTED BY（column，[…]）” 子句。

散列算法使分布键将每一行分配给特定 segment。相同值的键将始终散列到同一个 segment。选择唯一的分布键（例如Primary Key）将确保较均匀的数据分布。哈希分布是表的默认分布策略。

如果创建表时未提供 DISTRIBUTED 子句，则将 PRIMARY KEY（如果表真的有的话）或表的第一个合格列用作分布键。什么类型的列是合格列呢？几何类型或用户自定义数据类型的列不能用作 Greenplum 分布键列。如果表中没有合格的列，则退化为随机分布策略。

但是，如果未提供 DISTRIBUTED 子句，Greenplum 最后会选择哪种分布策略还会受其它因素的影响，例如：GUC gp_create_table_random_default_distribution 和当时使用的优化器(optimizer)也将影响最终决定。因此，请千万不要忘记在 CREATE TABLE 时添加 DISTRIBUTED BY 子句

。

Note: 如果创建表时没有指定DISTRIBUTED BY，Greenplum数据库服务器配置参数

gp_create_table_random_default_distribution

[gpadmin@mdw ~]$ gpconfig --show gp_create_table_random_default_distribution
Values on all segments are consistent
GUC          : gp_create_table_random_default_distribution
Master  value: off
Segment value: off
[gpadmin@mdw ~]$

随机分布

要使用这一策略，需要在创建表使用 “DISTRIBUTED RANDOMLY” 子句。

随机分布会将数据行按到来顺序依次循环发送到各个 segment 上。与哈希分布策略不同，具有相同值的数据行不一定位于同一个 segment 上。虽然随机分布确保了数据的平均分布，但只要有可能，应该尽量选择哈希分布策略，哈希分布的性能更加优良。

复制分布

这种分布策略是 GPDB 6 的新增特性。要使用这一策略，需要在创建表使用 “DISTRIBUTED REPLICATED” 子句。

Greenplum 数据库将每行数据分配到每个 segment上。这种分布策略下，表数据将均匀分布，因为每个 segment 都存储着同样的数据行。当您需要在 segment 上执行用户自定义的函数且这些函数需要访问表中的所有行时，就需要用到复制分布策略。或者当有大表与小表 join，把足够小的表指定为 replicated 也可能提升性能。

请注意，有一个例外：catalog 表没有分布策略。

关于3项策略的摘要：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IhPKnWSd-1588921554857)(D:\SYBASE&informix&DB2\Greenplum\4 学习笔记\数据库管理\5 Greenplum表的管理实践-2.assets\3baead233c6f42a4bdc7f48d93c272cc.jpg)]

控制表的分布策略。

更多有关该参数的信息，请见Greenplum数据库参考指南的“服务器配置参数”部分。

在决定表分布策略时，请考虑以下几点。

• 均匀数据分布 — 为了最好的性能，所有的Segment应该包含等量的数据。如果数据不平衡或者倾斜，具有更多数据的Segment就必须做更多工作来执行它那一部分的查询处理。 请选择对于每一个记录都唯一的分布键，例如主键。
• 本地和分布式操作 — 本地操作比分布式操作更快。在Segment层面上，如果与连接、排序或者聚集操作相关的工作在本地完成，查询处理是最快的。在系统层面完成的工作要求在Segment之间分布元组，其效率会低些。当表共享一个共同的分布键时，在它们共享的分布键列上的连接或者排序工作会在本地完成。对于随机分布策略来说，本地连接操作就行不通了。
• 均匀查询处理 — 为了最好的性能，所有的Segment应该处理等量的查询负载。如果一个表的数据分布策略与查询谓词匹配不好，查询负载可能会倾斜。例如，假定一个销售事务表按照客户ID列（分布键）分布。如果查询中的谓词引用了一个单一的客户ID，该查询处理工作会被集中在一个Segment上。

选择法则：

1）尽量选择经常需要 JOIN 的列

当关联键和分布键均一致时，可以在 Segment 中完成 JOIN，不需要重分布或者广播小表。

当关联键和分布键不一致时，则需要重分布不一致的表或者广播小表，带来额外的开销。

2）尽量选择分布均匀的列或者多列

若选择的分布列值分布不均匀，则可能导致数据倾斜。某些 Segment 非常大（压力大），某些 Segment 非常小。根据木桶原理，时间消耗会卡在数据多的节点上。

3）尽量选择高并发查询的条件列（指该查询条件产生的中间结果集小的，如果中间结果集很大，那就让所有节点都来参与运算更好，因此不选）

如果数据经常被高并发的键值或离散查询，可以将查询条件的列作为分布列，这样不需要连接到所有的 Segment 去查，可以大大提高并发能力。

4）不要轻易使用随机分布

复制表分布策略(DISTRIBUTED REPLICATED)应该在小表上使用。 将大表数据复制到每个节点上无论在存储还是维护上都是有代价的。 复制表最基本的用例是：

• 删除用户定义的函数可以对节点执行的操作的限制
• 频繁使用的表不需要广播到所有节点可以提高查询性能。

声明分布键

CREATE TABLE命令的可选子句DISTRIBUTED BY, DISTRIBUTED RANDOMLY和DISTRIBUTED REPLICATED决定了表的分布策略。默认的哈希分布策略使用PRIMARY KEY（如果有的话）或表的第一列作为分布键。几何信息列或用户自定义数据类型的列是不能作为Greenplum数据库分布列的。 如果找不到合适的哈希分布的列，Greenplum数据库就选择随机分布策略。

复制表没有分布列，因为每行都分布在Greenplum数据库所有节点上。

为了保证哈希分布数据的均匀分布，最好选一个唯一键作为分布列。如果找不到，则选择DISTRIBUTED RANDOMLY。例如：

=> 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;

Important: 主键总是表的分布列。如果没有主键，但是有唯一索引存在，则选择它为分布键。

自定义分布键哈希函数

用于哈希分布策略的哈希函数由列的数据类型的哈希运算符类定义。由于默认的Greenplum数据库使用数据类型的默认哈希运算符类，因此用于哈希连接和哈希聚合的运算符类相同，适用于大多数用例。 但是，您可以在DISTRIBUTED BY子句中声明非默认的哈希运算符类。

使用自定义哈希运算符类可以用于支持与默认相等运算符（=）不同的运算符上的共存连接。

自定义哈希操作符类用例

此示例为整数数据类型创建自定义哈希运算符类，该类用于提高查询性能。运算符类比较整数的绝对值。

创建一个函数和一个等于运算符，如果两个整数的绝对值相等，则返回true。

CREATE FUNCTION abseq(int, int) RETURNS BOOL AS
$$
  begin return abs($1) = abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR |=| (
  PROCEDURE = abseq,
  LEFTARG = int,
  RIGHTARG = int,
  COMMUTATOR = |=|,
  hashes, merges);

现在，创建一个使用运算符的哈希函数和运算符类。

CREATE FUNCTION abshashfunc(int) RETURNS int AS
$$
  begin return hashint(abs($1)); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR CLASS abs_int_hash_ops FOR TYPE int4
  USING hash AS
  OPERATOR 1 |=|,
  FUNCTION 1 abshashfunc(int);

并且，为它们创建小于和大于运算符和B树运算符类。我们的查询不需要它们，但是Greenplum数据库的Postgres查询优化器必须依赖它们做连接的co-location。

CREATE FUNCTION abslt(int, int) RETURNS BOOL AS
$$
  begin return abs($1) < abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR |<| (
  PROCEDURE = abslt,
  LEFTARG = int,
  RIGHTARG = int);

CREATE FUNCTION absgt(int, int) RETURNS BOOL AS
$$
  begin return abs($1) > abs($2); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR |>| (
  PROCEDURE = absgt,
  LEFTARG = int,
  RIGHTARG = int);

CREATE FUNCTION abscmp(int, int) RETURNS int AS
$$
  begin return btint4cmp(abs($1),abs($2)); end;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE OPERATOR CLASS abs_int_btree_ops FOR TYPE int4
  USING btree AS
  OPERATOR 1 |<|,
  OPERATOR 3 |=|,
  OPERATOR 5 |>|,
  FUNCTION 1 abscmp(int, int);

现在，您可以在表中使用自定义哈希运算符类。

CREATE TABLE atab (a int) DISTRIBUTED BY (a abs_int_hash_ops);
CREATE TABLE btab (b int) DISTRIBUTED BY (b abs_int_hash_ops);

INSERT INTO atab VALUES (-1), (0), (1);
INSERT INTO btab VALUES (-1), (0), (1), (2);

执行使用自定义相等运算符

|=|

的连接的查询 可以利用co-location。 使用默认的整数opclass，此查询将需要Redistribute Motion节点，但使用自定义opclass，可以实现更高效的计划。

EXPLAIN (COSTS OFF) SELECT a, b FROM atab, btab WHERE a = b;
                            QUERY PLAN                            
------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)
   ->  Hash Join
         Hash Cond: (btab.b = atab.a)
         ->  Redistribute Motion 3:3  (slice1; segments: 3)
               Hash Key: btab.b
               ->  Seq Scan on btab
         ->  Hash
               ->  Redistribute Motion 3:3  (slice2; segments: 3)
                     Hash Key: atab.a
                     ->  Seq Scan on atab
 Optimizer: Postgres query optimizer
(11 rows)

测试hash分布

1 创建hash分布表

CREATE TABLE testdis (id int primary key, name varchar(20)) DISTRIBUTED BY (id);
archdata=# \dt testdis
              List of relations
 Schema |  Name   | Type  |  Owner  | Storage 
--------+---------+-------+---------+---------
 public | testdis | table | gpadmin | heap
(1 row)

archdata=# \d testdis
           Table "public.testdis"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(20) | 
Indexes:
    "testdis_pkey" PRIMARY KEY, btree (id)
Distributed by: (id)

2 生成数据

CREATE OR REPLACE FUNCTION creatData()
  RETURNS boolean AS
$BODY$
DECLARE
  i int;
BEGIN
  i :=1;
  FOR i IN 1..10000 LOOP
    INSERT INTO testdis(id,name) VALUES(i,'CEIC');
  END LOOP;

  IF i > 0 THEN
    RETURN true;
  ELSE
    RETURN false;
  END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

archdata=# CREATE OR REPLACE FUNCTION creatData()
archdata-#   RETURNS boolean AS
archdata-# $BODY$
archdata$# DECLARE
archdata$#   i int;
archdata$# BEGIN
archdata$#   i :=1;
archdata$#   FOR i IN 1..10000 LOOP
archdata$#     INSERT INTO testdis(id,name) VALUES(i,'CEIC');
archdata$#   END LOOP;
archdata$# 
archdata$#   IF i > 0 THEN
archdata$#     RETURN true;
archdata$#   ELSE
archdata$#     RETURN false;
archdata$#   END IF;
archdata$# END;
archdata$# $BODY$
archdata-# LANGUAGE 'plpgsql' VOLATILE;
CREATE FUNCTION
archdata=# ALTER FUNCTION creatData() OWNER TO gpadmin;
ALTER FUNCTION
archdata=# SELECT * FROM creatData();

3 检查数据分布情况

SELECT gp_segment_id, count(*) FROM testdis GROUP BY 1;
archdata=# SELECT gp_segment_id, count(*) FROM testdis GROUP BY 1;
 gp_segment_id | count 
---------------+-------
             0 |  1654
             2 |  1665
             3 |  1661
             5 |  1683
             1 |  1655
             4 |  1682
(6 rows)

archdata=# select *  FROM testdis where gp_segment_id=0;    
  id   | name 
-------+------
     1 | CEIC
    13 | CEIC
    15 | CEIC
    17 | CEIC
    29 | CEIC
    31 | CEIC
    33 | CEIC
    35 | CEIC
    37 | CEIC
    49 | CEIC
    
    select * from testdis  where gp_segment_id=0 limit 10 offset 11;
    archdata=#   select * from testdis  where gp_segment_id=0 limit 10 offset 11;
 id  | name 
-----+------
  63 | CEIC
  69 | CEIC
  71 | CEIC
  83 | CEIC
  85 | CEIC
 103 | CEIC
 105 | CEIC
 117 | CEIC
 119 | CEIC
 121 | CEIC
(10 rows)


                                                             ^
archdata=# SELECT gp_segment_id, id,name  FROM testdis   order by id;
 gp_segment_id |  id   | name 
---------------+-------+------
             0 |     1 | CEIC    --证明segment_id和ID的对应方式hash方式
             3 |     2 | CEIC
             4 |     3 | CEIC
             1 |     4 | CEIC
             4 |     5 | CEIC
             1 |     6 | CEIC
             4 |     7 | CEIC
             5 |     8 | CEIC
             2 |     9 | CEIC
             5 |    10 | CEIC
             2 |    11 | CEIC
             5 |    12 | CEIC
             0 |    13 | CEIC
             3 |    14 | CEIC
             0 |    15 | CEIC
             3 |    16 | CEIC
             0 |    17 | CEIC
             1 |    18 | CEIC
             
 archdata=# SELECT gp_segment_id, id,name  FROM testdis  order by id  limit 20 offset 20;
 gp_segment_id | id | name 
---------------+----+------
             4 | 21 | CEIC
             1 | 22 | CEIC
             2 | 23 | CEIC
             5 | 24 | CEIC
             2 | 25 | CEIC
             5 | 26 | CEIC
             2 | 27 | CEIC
             3 | 28 | CEIC
             0 | 29 | CEIC
             3 | 30 | CEIC
             0 | 31 | CEIC
             5 | 32 | CEIC
             0 | 33 | CEIC

测试随机random分布

1 创建表

CREATE TABLE testdis_ramdomly (id int primary key, name varchar(20))   DISTRIBUTED RANDOMLY;

archdata=# CREATE TABLE testdis_ramdomly (id int primary key, name varchar(20))   DISTRIBUTED RANDOMLY;
ERROR:  PRIMARY KEY and DISTRIBUTED RANDOMLY are incompatible

说明有主键的表是无法创建随机分布的

2 生成数据

insert into testdis_ramdomly  as select * from testdis;

archdata=# insert into testdis_ramdomly   select * from testdis;
INSERT 0 10000

3 检查数据分布情况

archdata=# SELECT gp_segment_id, count(*) FROM testdis_ramdomly GROUP BY 1 order by 1;
 gp_segment_id | count 
---------------+-------
             0 |  1684
             1 |  1687    --也是比较平均的
             2 |  1677
             3 |  1646
             4 |  1641
             5 |  1665
(6 rows)

archdata=# 

archdata=# SELECT gp_segment_id, id,name  FROM testdis_ramdomly   order by id;
 gp_segment_id |  id   | name 
---------------+-------+------
             2 |     1 | CEIC
             0 |     2 | CEIC
             2 |     3 | CEIC
             5 |     4 | CEIC
             3 |     5 | CEIC
             0 |     6 | CEIC
             2 |     7 | CEIC
             3 |     8 | CEIC
             4 |     9 | CEIC
             4 |    10 | CEIC
             3 |    11 | CEIC
             1 |    12 | CEIC
             1 |    13 | CEIC
             3 |    14 | CEIC

改变表的分布策略

创建一张表,在没有primary key 或者 unique key 的情况下，GreenPlum默认会把第一个column作为分布键

archdata=# create table tab01(id int,name varchar(20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
archdata=# 

CREATE TABLE



archdata=# \d+ tab01
                        Table "public.tab01"
 Column |         Type          | Modifiers | Storage  | Description 
--------+-----------------------+-----------+----------+-------------
 id     | integer               |           | plain    | 
 name   | character varying(20) |           | extended | 
Has OIDs: no
Distributed by: (id)

使用以下语句修改分布键

archdata=#  alter table tab01 set distributed by(name);
ALTER TABLE
archdata=# \d+ tab01
                        Table "public.tab01"
 Column |         Type          | Modifiers | Storage  | Description 
--------+-----------------------+-----------+----------+-------------
 id     | integer               |           | plain    | 
 name   | character varying(20) |           | extended | 
Has OIDs: no
Distributed by: (name)

在不确定哪个column为分布键的情况下可以使用randomly策略