1. 数据库对象及操作
1) 创建实例
a) 规格选型
• Segment：数量越多，单条查询性能越好；CPU核数越多，并发能力越好。
• 计算节点/计算组：CPU内存资源被该节点/组内所有Segment共享。
b) 实例选型
• 选型因素：性能维度
资源（CPU和MEM）：高并发场景，建议选择单节点大的计算规格。
存储介质：高性能场景，建议选择ESSD或SSD存储介质。
• 选型因素：容量维度
MPP架构，计算能力及存储容量，随节点数线性增加。
c) 规格计算示例
以存储预留模式的单节点4core SSD存储为例，单节点存储容量320GB。假设客户数据量为4TB，且一般预留70%水位，那需要规划4TB/（1-70%）/320GB，即实例至少为42个节点。
2) 建表
a) ADB PG逻辑架构
图为ADB PG实例架构从实例到库、模式、表的树形展现
• ADB PG实例：云平台上的一个MPP数据库集群，创建时分配固定资源，包含一组数据库、模式、表对象和数据以及用户。
• 数据库（Database）：一个ADB PG实例中，包含多个逻辑Database，其中可包含模式等对象。
• 模式（Schema）：逻辑概念，数据库中的逻辑空间，包含一系列表，视图等对象。
• 表（Table）：
数据分布定义：按分布键Hash值， 随机Random，或复制Replication三种方式，进行节点间数据分布；
存储格式定义：支持指定按行存储，或者按列存储；
压缩算法定义（可选）：支持多种高性能数据压缩算法；
分区表支持（可选）：对于大表，支持按区间 Range，或值LIST进行分区，且支持多级分区。
b) 表分布方式：在MPP节点间的三种分布方式
• 默认根据分布键的hash值分布，建表语法如下：
CREATE TABLE products( name varchar(40)， prod_id integer， supplier_id integer) DISTRIBUTED BY (prod_id)；
• 若没有适合的列做hash分布，可以采用随机均分，建表语法如下：
CREATE TABLE random_stuff( things text， doodads text， etc(text) DISTRIBUTED RANDOMLY；
• 小表、维度表在各个节点有一份全量复制，建表语法如下：
CREATE TABLE replicated_stuff( things text， doodads test， etc text), DISTRIBUTED REPLICATED；
c) 建表并导入数据-分区与分布
分区可以支持多级分区，可以按照日期做一级分区，也可以同时按日期和时间设置两级分区，分区越多底层存储的文件也越多。建议分区按照业务的查询需要来定义。
d) 分布键：表的分布键选择原则
• 选择数据分布均匀的列
√ 若选择的分布列数值分布不均匀，则可能导致数据倾斜。某些Segment分区节点存储数据多（查询负载高）。
√ 根据木桶原理，时间消耗会卡在数据多的节点上，故不应选择bool类型，时间日期类型数据作为分布键。
• 选择经常需要JOIN的列作为分布键
√ 当JOIN键和分布键一致时，可以在 Segment分区节点内部完成JOIN。
√ 否则需要将一个表进行重分布来实现重分布关联或者广播其中小表来实现广播关联，后两种方式都会有较大的网络开销。
• 选择高频率查询条件列作为分布键
√ 从而可能实现按分布键做节点segment的裁剪。
• 默认表的主键为分布键
√ 若表没有主键，则默认将一列当做分布键。
• 分布键列数
√ 分布键可以被定义为一个或多个列。
• 其他原则
√ 谨慎选择随机分布DISTRIBUTED RANDOMLY，这将使得上述本地关联，或者节点裁剪不可能实现。
√ 小表可选择复制表模式，在所有Segment上均保存一份全量数据。
e) 分布键：节点间数据倾斜的检查和处理
create table t1(cl int， c2 int) distributed by (cl);
通过下述语句查看表数据的倾斜情况：
select gp_segment id，count(1) from t1 group by 1 order by 2 desc;
如果发现某些 Segment上存储的数据明显多于其他 Segment，该表存在数据倾斜，建议选取数据分布平均的列作为分布列。
alter table tl set distributed by (c2);
f) 分区表：支持表按区间或者值进行分区，自动分区裁剪
• 范围（RANGE）分区：基于一个数值型范围划分数据，例如按照日期区间定义。
• 值（LIST）分区：基于一个值列表划分数据，例如按照城市属性定义。
• 多级分区表：上述两种类型的多级组合，最多支持三级分区。
分区表支持多种分区管理操作，包括新增分区，删除分区，重命名分区，清空截断分区，交换分区，分裂分区等。
注意
分区个数建议小于200，否则会影响查询的SQL优化性能。
参考：https://help.aliyun.com/document_detail/118173.html
示例：多级分区表设计实例
一级分区采用按月的区间（Range）分区，二级分区采用按地区的值（List）分区设计。
CREATE TABLE sales(id int， year int， month int， day int， region text) DISTRIBUTED BY(id) PARTITION BY RANGE (month) SUBPARTITION MBYL1ST(region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES('usa')， SUBPARTITION europe VALUES ('europe')， SUBPARTITION asia VALUES ('asia')， DEFAULT SUBPARTITION other_regions) (START(1)END(13)EVERY(1)，DEFAULT PARTITION other months)；
g) 核心功能：同时支持行存与列存
• 行存表：高吞吐更新写入，点查询
√ 数据按行存储，操作某列必须读入整行。
√ 适合较多数据更新操作场景。
√ 通过索引，支持高并发的点查询。
CREATE TABLE foo (a INT，b TEXT) DISTRIBUTED BY (a);
• 列存表：批量加载，全表聚合，压缩率高
√ 数据按列存储-每一列单独存放，数据即是索引。
√ 只访问查询涉及的列-大量降低系统IO。
√ 数据类型一致，数据特征相似-实现高压缩率。
√ 适合更新少，全表聚合操作。
CREATE TABLE foo(a INT， b TEXT) WITH(APPENDONLY=TRUE，ORIENTATION=COLUMN)DISTRIBUTED BY(a);
h) 建表压缩：多种压缩算法，成本和性能取得平衡
数据压缩支持多种压缩算法如下图，成本和性能取得平衡，数据压缩可用于列存表或者行存追加表，平均3倍以上数据压缩率。
示例
CREATE TABLE foo (a int， b text) WITH (appendonly=true，orientation=column，compresstype=zstd，compresslevel=9) DISTRIBUTED BY (a)；
i) 统计信息优化建议
• 统计信息收集方式可基于全库、表、列级别，可根据实际情况确定收集范围。
• 导入数据后、超过20%数据更新（IUD）后、创建索引后，需进行统计信息收集。
• 用户ETL任务过程中，会涉及多次IUD， 可根据客户业务情况，在其中适当添加analyze语句。
• 调优过程中，从执行计划中看到表行数估算为1行，计划中出现较多的Broadcast、Sort+GroupByAgg、NestLoop等算子时，考虑对相应数据表进行analyze。
导入数据语句示例
create table t1 (a int, b int) ； insert into t1 select v, v from generate_series(1，1000) as v;
analyze t1; --第一次加载大量数据后，重新收集统计信息。
ETL过程语句示例
truncate t1; insert into t1 select * from t2; insert into t1 select * from t3; analyze t1; --重建数据后，重新收集统计信息 select a, b, c from t1 join t2 on t1.c=t2.d where t2.b=2;
j) 建表并导入数据 - 建表示例
堆表
CREATE TABLE ORDERS ( O_ORDERKEY BIG INT NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, O_TOTALPRICE DECIMAL(15，2) NOT NULL, O_ORDERDATE DATE NOT NULL, O_ODRDEAPRIDRITY CHAR(15) NOT NULL, O_CLERK CHAR(15) NOT NULL, O_SHIPPRIORITY INTEGER NOT NULL, O_COMMEN TVARCHAR(79) NOT NULL )
DISTRIBUTED BY (O_ORDERKEY) -- 分布列
AO列存表
CREATE TABLE ORDERS ( O_ORDERKEY BIG INT NOT NULL, O_CUSTKEY INTEGER NOT NULL, O_ORDERSTATUS CHAR(1) NOT NULL, O_TOTALPRICE DECIMAL(15，2) NOT NULL, O_ORDERDATE DATE NOT NULL, O_ODRDEAPRIDRITY CHAR(15) NOT NULL, O_CLERK CHAR(15) NOT NULL, O_SHIPPRIORITY INTEGER NOT NULL, O_COMMEN TVARCHAR(79) NOT NULL ) WITH (APPENOONLY=TRUE, ORIENTATION=COLUMN, COMPRESSTYPE=ZSTD, COMPRESSLEVEL=5)
注释
O_ORDERKEY为分布列，APPENOONLY=TRUE为AO列存表，COMPRESSTYPE=ZSTD为压缩算法，COMPRESSLEVEL=5为压缩级别。
建表并导入数据的常用方式为COPY和OSS外表方式，COPY的性能一般为30-50Mb/s，而OSS外表方式为并行导入性能取决于OSS网络带宽与节点个数，DBStack形态还支持gpfdist外表导入。
k) 执行SQL查询
查询语句和查询工具使用，详见操作演示环节。
更多精彩内容，欢迎观看：《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版解析与实践（上）——三、产品相关概念（中）：
https://developer.aliyun.com/article/1222911?spm=a2c6h.13148508.setting.17.75bf4f0e5XwXp0
