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上物理文件个数：列数*分区数，使用分区将使物理文件个数快速增长，增加管理负担。