DWS最佳实践（一）DWS表设计

1. 行列存选择

（1）推荐选择：列存表适合统计分析类查询（关联，分组操作较多的场景）；

• 即系查询（查询条件不确定，行存表扫描难以使用索引的场景）；

• 空间占用最优，压缩比平均在5倍左右；

• 列存表是DWS推荐的主要存储方式，具有高压缩比，关联速度快等优点。

（2）谨慎选择：如果列存在以下场景不能满足诉求，则谨慎考虑选择行存表。

• 适合点查询（返回记录少，基于索引的简单查询）；

• 增删改操作较多的场景；

  行存表压缩比较低，当数据量非常大时，数据膨胀非常大，因此谨慎选择行存表。

2. 压缩比

针对行存和列存，在数据入库的时候有不同的压缩比，进而入库的性能也会不同。针对IO读写量大，CPU富足（计算相对小）的场景，选择高压缩比；反之选择低压缩比，行存和列存支持的压缩比的取值如下：

（1）行存表：yes/no，默认值no;

（2）列存表：yes/no/low/middle/high，默认值low

如果单纯的只针对入库的场景的话，列存压缩比low的情况是所有情况中入库速度最快的，会比行存压缩比no的性能高出3~5倍，并且入库后的存储容量也大为不同，例如：在列存压缩比low的一张表存储空间为15G左右的话，换成行存压缩比no的情况，数据会膨胀到70~80G左右。

3. 分布列的选择

 对于hash表，分布列的选择非常重要，选择不当的分布列可能导致数据倾斜，进而严重影响查询性能，对于hash表，分布列选择应该遵循下面几个原则：

（1）列值比较离散，以便数据能够均匀分布到各个DN；

（2）在满足第一条的情况下，尽量不要选择存在常量的filter列；

（3）在满足上面两项的情况下，考虑选择查询中的连接条件作为分布列；这样Jon任务能够下推到各个DN中执行

 对于Hash表的策略，如果分布列选择不大方，可能导致数据倾斜，查询时出现部分DN的IO短板，从而影响查询整体性能。因此在采用Hash分表策略之后需对表的数据进行数据倾斜性检查，以确保数据在各个DN上分布是均匀的。登录数据库后，使用下面的SQL语句来检查数据倾斜性：

select xc_node_id,count(1) from {table_name} group by 1 order by 2;

其中xc_node_id对应DN，一般来说，不同的DN的数据量相差5%以上即可视为数据倾斜，如果相差10%以上就必须调整分布列。DWS支持多分布列特性，可以根据数据的特点选择不同列的组合来作为表的分布列。

注：如果发现表的分布列选择不合适，需要重新选择分布列的时候，目前DWS还不支持直接在原表上修改分布列，需要重新建表，导入数据。

数据倾斜的解决办法：

重建该表，选择合适的分布键，然后通过insert into select语法，把原始数据导入到新表，再对新表做重命名。

重命名示例如下：

set search_path to '{schema_name}';
alter table {new_tablename} rename to {old_tablename};

建表示例：

Create table test
(
   id int,
   name varchar
)
with(orientation=column,compression=low) --指定行列存和压缩比
distribute by hash(id); --指定分布列

4. 分区

分区表是把逻辑上的一张表根据某种方案分割成几张物理块进行存储。逻辑上叫分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表的优点：

(1) 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率；

(2) 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用；

(3) 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可；

(4) 当前DWS的分区表为范围分区表：将数据基于范围映射到一个分区。这个范围是有创建分区表时指定的分区键决定的；

根据业务场景和数据量，建议按周分区，如果每个分区的数据过大，可以考虑按天分区。但是分区表需要例行维护。一次性创建定期的分区，或者一次性删除历史不用的分区。此外，每个分区的数量不应过大，最多不要超过1billion，否则需要重新考虑分区的划分粒度。

下面是分区的例子：

(1) 创建分区表：

Create table demo_partition
(
  id      varchar(50),
  start_time  timestamp without time zone
) with (orientation=column, compression=low)
distribute by hash(id)
Partition by range (start_time)
(
  partition p1 values less than ('2019-04-11 00:00:00'),
  partition p2 values less than ('2019-04-12 00:00:00'),
  partition p3 values less than ('2019-04-19 00:00:00'),
  partition p4 values less than (MAXVALUE)
);

(2) 删除最大分区pmx

alter table demo_partition drop partition pmx;

(3) 增加分区

alter table demo_partition add partition p4 values less than('2019-04-20 00:00:00');

(4) 删除某个分区中的数据

alter table demo_partition truncate partition p1;

(5) 查询某个分区的行数

select count(1) from demo_partition partition(p1);

(6) 分割某个分区（目前只有行存支持，列存不支持此功能）

alter table demo_partition split partition p3 at('2019-04-15 00:00:00') into
(
  partition p31,
  partition p32
);

(7)合并分区

alter table demo_partition merge partitions p1,p2 into partition p12;

5. 索引的取舍

索引可以用来提高数据查询性能，但是不恰当的使用将导致数据性能下降。建议仅在匹配如下某条原则时创建索引：

1. 经常执行查询的字段；

2. 在连接条件上创建索引；

3. Where子句的过滤条件上；

4. 经常出现在order by，group by和distinct字段；

Btree索引：使用一种类似B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。Btree适合支持比较查询和查询范围。

缺点：因为其本身的数据结构，当每插入一条数据其实都要遍历btree找到合适的位置，而当数据量非常大的时候，btree树会非常深，每条数据的入库速度会变得越来越慢，占用的内存也会越来越大，严重的影响了入库性能。

另外，只有一个查询的结果集时表的千分之一以下的才会走btree索引，否则在列存表上索引不生效。

DWS索引支持还包括：

(1) gin:GIN索引是倒排索引，可以处理包含多个键的值（比如数组）

(2) gist:Gist适用于几何和地理等多维数据类型和集合数据类型；

(3) Psort:针对列存表进行局部排序索引；

行存表支持的索引类型：btree(行存表缺省值)，gin,gist。列存表支持的索引类型：Psort(列存表缺省值)，btree，gin。

下面列举索引的一些例子：

(1) 在表上创建一个索引：

create index test-index on test using btree(vid);

(2) 在分区表上创建分区索引

create index test-index on test using btree(vid) local;

(3) 重建索引

alter index test-index rebuild;

(4) 设置索引不可用

alter index test-index unusable;

(5) 删除索引

drop index test-index;

6. 局部聚簇

局部聚簇（Partial Cluster Key）是列存下的一种技术。这种技术可以通过min/max稀疏索引较快的事先基表扫描的filter过滤。局部聚簇可以指定多列，但是不建议超过2列，选择原则如下：

a. 受基表的简单表达式约束。这种约束一般形如col op const,其中col为列名，op为操作符=，<，>，<=，>=，const为常量值；

b. 尽量采用选择度比较高（过滤更多数据）的简单表达式的列；

c. 尽量把选择度比较低的约束列放在局部聚簇中的前面；

d. 尽量把枚举类型的列放在局部聚簇的前面；

例子：

(1) 创建一个带局部聚簇列的表：

create table test(
id        int,
volume    text,
partial cluster key (volume)
) with (orientation=column, compression=low)
Distribute by hash (id);

(2) 为普通表创建一个PCK，并使其生效：‍

alter table {table_name} add partial cluster key ({column_name});
vacuum full {table_name};

(3) 删除表中的局部聚簇列：

alter table {table_name} drop constraint {partial_cluster_key_name};