表的存储方式分为行存储和列存储。

左图直观的展示了存储方式的差异，但是建表时怎么决定用行存储还是列存储呢？

用户业务类型是决定表的存储类型的主要因素，可以根据以下业务场景选择：

创建列存表

示例：创建表warehouse_t1，存储方式为列存通过ORIENTATION参数指定。

CREATE TABLE warehouse_t1
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2)
) WITH (ORIENTATION = COLUMN);

选择压缩比

对于数据库，IO 相对于CPU通常都是系统的性能瓶颈，合理的压缩手段不仅能节省空间，也能减少IO，提高读取性能。

针对行存和列存，在数据入库的时候有不同的压缩比，进而入库的性能也会不同。对于I/O读写量大，CPU富足（计算相对小）的场景，选择高压缩比；反之选择低压缩比。依据此原则进行不同压缩下的测试和对比，以选择符合自身业务情况的最优压缩比。

压缩比通过COMPRESSION参数指定，其中列存表取值为：YES/NO/LOW/MIDDLE/HIGH，默认值为LOW。

选择压缩比

创建使用压缩比的表

示例：创建列存表warehouse_t2，压缩比通过COMPRESSION参数指定。

CREATE TABLE warehouse_t2
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2)
) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE);

选择分布方式和分布列

---复制表（Replication）将表中的全量数据在集群的每一个DN实例上保留一份，即每个数据节点都有完整的表数据。

---哈希（Hash）表将表中某一个或几个字段进行hash运算后，生成对应的hash值，通过映射，把数据分布到指定DN。对于Hash分布表，在读/写数据时可以利用各个节点的IO资源，大大提升表的读/写速度。

选择分布方式

可根据以下业务场景选择：

Hash分布表的分布列选取至关重要，需要满足以下原则：

1. 列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。

2. 在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如，表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’)，那么就应当尽量不用zqdh做分布列。

3. 在满足前两条原则的情况，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。

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

select xc_node_id, count(1) from tablename group by xc_node_id order by xc_node_id desc;

其中xc_node_id对应DN，一般来说，不同DN的数据量相差5%以上即可视为倾斜，如果相差10%以上就必须要调整分布列。

创建Replication/Hash分布表

示例：创建行存表warehouse_t3，Replication分布方式通过DISTRIBUTE BY参数指定。

CREATE TABLE warehouse_t3
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2)
) DISTRIBUTE BY REPLICATION;

示例：创建列存表warehouse_t4，Hash分布方式通过DISTRIBUTE BY参数指定，分区键为W_WAREHOUSE_SK。

CREATE TABLE warehouse_t4
(
W_WAREHOUSE_SK INTEGER NOT NULL,
W_WAREHOUSE_ID CHAR(16) NOT NULL,
W_WAREHOUSE_NAME VARCHAR(20) ,
W_WAREHOUSE_SQ_FT INTEGER ,
W_STREET_NUMBER CHAR(10) ,
W_STREET_NAME VARCHAR(60) ,
W_STREET_TYPE CHAR(15) ,
W_SUITE_NUMBER CHAR(10) ,
W_CITY VARCHAR(60) ,
W_COUNTY VARCHAR(30) ,
W_STATE CHAR(2) ,
W_ZIP CHAR(10) ,
W_COUNTRY VARCHAR(20) ,
W_GMT_OFFSET DECIMAL(5,2)
)
WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) 
DISTRIBUTE BY HASH (W_WAREHOUSE_SK);

分区的设计

分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。

分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点：

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

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

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

分区使用示例：

---创建分区表
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)
);

---删除最大分区pmx
ALTER TABLE demo_partition DROP partition pmx;

---增加分区
ALTER TABLE demo_partition add partition p4 values less than('2019-04-20 00:00:00');

---删除某个分区中的数据
ALTER TABLE demo_partition truncate partition p1;

---查询某个分区的行数
SELECT count(1) FROM demo_partition partition(p1);

---分割某个分区（目前只有行存支持，列存不支持此功能）
ALTER TABLE demo_partition split partition p3 at('2019-04-15 00:00:00') into( partition p31, partition p32 );

---合并分区
ALTER TABLE demo_partition merge partitions p1,p2 into partition p12;

选择索引

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

• 经常执行查询的字段
• 在连接条件上创建索引
• Where子句的过滤条件上
• 经常出现在order by，group by和distinct字段

DWS索引支持：

• Btree索引：使用一种类似B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。Btree适合支持比较查询和查询范围。但因为其本身的数据结构，当数据量非常大的时候，每条数据的入库速度会变得越来越慢，占用的内存也会越来越大，严重的影响了入库性能。

• GIN索引是倒排索引，可以处理包含多个键的值（比如数组）。

• Gist适用于几何和地理等多维数据类型和集合数据类型。

• Psort:针对列存表进行局部排序索引。

行存表支持的索引类型：btree（行存表缺省值）、gin、gist；列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。

索引使用示例：

---在表上创建一个索引
CREATE INDEX test-index on test using btree(vid);

---在分区表上创建分区索引
CREATE INDEX test-index on test using btree(vid) local;

---重建索引
ALTER INDEX test-index rebuild;

---设置索引不可用
ALTER INDEX test-index unusable;

---删除索引
DROP INDEX test-index;

选择局部聚簇

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

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

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

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

---尽量把枚举类型的列放在局部聚簇的前面。

局部聚簇使用示例：

---创建一个带局部聚簇列的表
CREATE TABLE test(id int,volume text,partial cluster key (volume)) with (orientation=column, compression=low) distribute by hash (id);

---为普通表创建一个PCK，并使其生效
ALTER TABLE table_name add partial cluster key (column_name);
VACUUM FULL table_name;

---删除表中的局部聚簇列：
ALTER TABLE table_name drop constraint partial_cluster_key_name;