GreenPlum系列-6-表设计之存储类型、分布、分区的介绍

在实际的生产中创建表之前需要对业务需求分析，包括使用堆存储还是追加优化存储、选择行存还是列存是否需要压缩、分布键的选择、分区的使用。

这章主要介绍以上内容。

一、堆存储（heap storage） 和 追加优化存储（append-optimized storage）

1. 堆存储

默认存储类型，堆表存储在OLTP类型负载下表现最好，数据会在初始载入后被频繁的修改。适合较小的表，如维度表。

2. 追加优化存储

在数据仓库环境中的非规范表表现最好。该类表通常被批量加载后只被只读查询访问。如事实表，同时追加优化表对批量数据加载性能有优化，不建议单行insert操作。追加优化表不支持cluster、declare... for update 和触发器。

3. 堆表和追加表的选择

如果表在后续会有一定的并发的 insert/delete/update 需要选择 堆表；

如果后续只是批量加载且少更新，可选择追加表。

4. 创建

堆表： create table foo (a int, b text) DISTRIBUTED BY (a);

追加优化表： create table bar (a int, b text) with （appendoptimized=true） DISTRIBUTED BY (a);

二、列存和行存

1. 列存

适用于在少量列参与计算，同时这些列一旦载入以后极少会更新。

2. 行存

适用于具有 很多迭代事务的OLTP类型以及一次需要多列的单行，检索性能高；

3. 行存和列存的选择

表数据的更新，如果频繁装载和更新表数据，选择面向行的堆表；面向列的表只适合追加优化表。

频繁的插入，如果频繁的想表中插入行，选择面向行的模型；列存没有对写操作优化。

查询中要求的列数，如果查询列数或者条件自居中包含所有或大部分列，选择行存；列存适合查询单列汇总或单行过滤。

压缩，列数据具有相同的数据类型，很好的支持压缩。

4. 创建

面向列

create table bar (a int, b text) with (appendoptimized=true, orientation=column) distributed by (a);

三、分布

1. 为什么选择使用分布键

所有的join、sort、aggregation 都可以在本地segment 上完成，不需要motion动作在segment之间传递数据。当然这是理想状况，出于对性能的考虑，greenplum 系统所有的数据能够在segment 分布均匀，有利于后续的查询、聚合等操做，更好的利用MPP的并发特性。如果数据分布不均匀，出现数据倾斜，直接影响就是木桶效应，效率低下。默认情况下GP会指定随机分布。

2. 分布键的类型

gp6支持3种分布类型，分别为 哈希分布、随机分布、复制分布。

哈希分布：默认的分布键。选择一列或多列作为数据表的分布键，通过hash计算，将插入的数据路由到特定的segment上。当建表未定义分布键时，如果表有主键则主键作为默认分布键，如果表无主键，则默认按照第一个字段来分布。

随机分布：数据随机分布在每一个segment上，可以保证数据平均分布，但在执行关联操作时需要将数据重新分布，性能较差。通过distributed randomly 指定，如果表上有主键则不能使用随机分布。

复制分布：每一条记录都会分布到整改集群的所有实例上，通过 DISTRIBUTED REPLICATED 指定。当需要在segment上执行用户自定义的函数，且函数需要访问表中的所有行时使用复制分布，或者 有大小表join 时，把小表指定为复制分布，性能会有提升。

3. 如何选择分布键

经常需要join的列，选择分布键关联代价会减少。

分布均匀的列或多列，如果没有分布太均匀的字段，则将多个字段进行组合分布。

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

不要轻易使用随机分布，如果多表管理会出现查询多节点重分布，效率低下。

不会被修改的字段，分布键不能出现在update语句中。

4. 分布键的操作

查询数据倾斜：

select gp_segment_id,count(*) from ods_events GROUP BY gp_segment_id;

查看分布键

#\d table;

修改分布键（会重新分布表sales）

ALTER TABLE sales SET DISTRIBUTED BY (customer_id);

修改分布策略（会自动重新分配表数据）

ALTER TABLE sales SET DISTRIBUTED RANDOMLY|REPLICATED;

四、分区

表分区是指数据库在逻辑上划分大表来提升查询性能并有利于数据仓库任务维护。分区不会改变数据在segment之间的物理分布。

1. 分区策略

小表不建议做分区

分区键和分布键不能是同一个

多级分区最好不要用

对于列存储的表，谨慎使用分区，列存在列级别做了隔离，在每个segment上物理文件个数：列数*分区数，使用分区将使物理文件个数快速增长，增加管理负担。

分区不会改变数据的物理分布。

基于业务需求选择是否分区以及分区类型。

创建太多分区表会拖慢数据管理的工作，如 vacuuming, recovering segments, expanding the cluster, checking disk useage。

2. 分区类型

GP 支持 范围分区、列表分区、两种混合。

范围分区：基于一个数字范围划分数据，如日期、价格等。

列表分区：基于一个值列表划分数据，如按销售范围或产品线划分。

3. 分区操作

3.1 创建表分区

日期范围

CREATE TABLE sales (id int, date date, amt decimal(10,2))

DISTRIBUTED BY (id)

PARTITION BY RANGE (date)

( START (date '2023-01-01') INCLUSIVE

END (date '2024-01-01') EXCLUSIVE

EVERY (INTERVAL '1 day') );

数字范围

CREATE TABLE rank (id int, rank int, year int, gender

char(1), count int)

DISTRIBUTED BY (id)

PARTITION BY RANGE (year)

( START (2020) END (2023) EVERY (1),

DEFAULT PARTITION extra );

列表

CREATE TABLE rank (id int, rank int, year int, gender char(1), count int )

DISTRIBUTED BY (id)

PARTITION BY LIST (gender)

( PARTITION girls VALUES ('F'),

PARTITION boys VALUES ('M'),

DEFAULT PARTITION other );

多级分区

CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)

DISTRIBUTED BY (trans_id)

PARTITION BY RANGE (date)

SUBPARTITION BY LIST (region)

SUBPARTITION TEMPLATE

( SUBPARTITION usa VALUES ('usa'),

SUBPARTITION asia VALUES ('asia'),

SUBPARTITION europe VALUES ('europe'),

DEFAULT SUBPARTITION other_regions)

(START (date '2022-01-01') INCLUSIVE

END (date '2023-01-01') EXCLUSIVE

EVERY (INTERVAL '1 month'),

DEFAULT PARTITION outlying_dates );

3.2 查看用户的分区设计

SELECT partitionboundary, partitiontablename, partitionname,

partitionlevel, partitionrank

FROM pg_partitions

WHERE tablename='sales';

3.3 增加分区

ALTER TABLE sales ADD PARTITION

START (date '2017-02-01') INCLUSIVE

END (date '2017-03-01') EXCLUSIVE;

3.4 重命名分区

alter table sales rename to sales_new;

3.5 删除分区

alter table sales drop partition for (rank(1));

3.6 截断分区

alter table sales truncate partition for (rank(1));

五、压缩

只适合追加优化表，分表级和列级压缩。

1、创建压缩表

CREATE TABLE foo (a int, b text)

WITH (appendoptimized=true, compresstype=zlib, compresslevel=5);

2、其他

比较常用的压缩算法（zstd、zlib），针对一张 12亿数据的表进行压缩，对比 zlib 5 和 zstd 5-10 级别的性能消耗。

测试表：

create table public.test_base_zstd_l6 (

idcard_no character varying

,capture_date date

,capture_hour character varying

,location_trace character varying

,last_province character varying

,last_city character varying

,last_district character varying

,last_address character varying

,create_date date

) with (appendonly=true, orientation=column, compresstype=zstd, compresslevel=5)

Distributed by (idcard_no);

压缩格式	压缩级别	执行时间	CPU消耗比率	CPU Time	压缩后大小	原始大小	压缩比率
zlib	5	48s	19.75%	01:42	32GB	157 GB	79.6%
zstd	5	32s	12.36%	00:51	33GB	157 GB	78.9%
zstd	6	38s	17.2%	01:02	31GB	157 GB	80.3%
zstd	7	42s	20.4%	01:14	30GB	157 GB	80.89%
zstd	8	42s	20.6%	01:25	30GB	157 GB	80.89%
zstd	10	54s	22.57%	01:57	30GB	157 GB	80.89%

六、参考

http://docs-cn.greenplum.org/v6/admin_guide/ddl/ddl-storage.html