摘要 ：表结构设计是数据库建模的一个关键环节，表定义好坏间接决定了集群的无效容量以及业务查问性能，本文从产品架构、性能实现以及业务特色的角度论述在 GaussDB(DWS) 的中表定义时须要关注的一些关键因素。
前言
GaussDB(DWS)是企业级的大规模并行处理关系型数据库，采纳 Shared-nothing 架构的 MPP(Massive Parallel Processing)零碎，反对 PB 级别数据量的解决，实用于详单查问、数据仓库、混合负载和大数据分析等场景。Shared-nothing 架构人造反对数据打散散布到各个数据节点 (DataNode) 以及多节点协同计算机制，同时这种机制对表定义波及提出了更高的诉求，表定义会间接影响集群的无效容量以及业务查问性能。本文从产品架构、性能实现以及业务特色的角度论述 GaussDB(DWS)的中表定义须要关注的一些关键因素。
1 存储形式设计
GaussDB(DWS)反对行存储 (row-based storage) 和列存储 (column-based storage) 两种存储形式，这两种存储格局别离实用不同的业务场景。通常来讲典型的点查问为主的场景举荐应用行存储，典型的统计分析型业务举荐应用列存储。
1.1 行存储
行存储模式下，一条数据的所有列组合在一起称之为一个 tuple 多个 tuple 组成一个 page，所有的 page 形成表的数据文件。pages 是行存数据存取的最小单元，一个 page 默认 8KB。page 的根本构造如下
行存储模式下，所有数据列集中存储在一个 tuple 中，所以行存储的更新(UPDATE)、删除(DELETE)、索引点查性能较好，然而当查问列只波及所有列的很少一部分的时候，所有列的数据也都会被读取，导致大量的有效 IO，因而举荐比较简单点查场景或者存在频繁的数据更新的业务采纳行存储。
1.2 列存储
列存储下把数据表中的每一列独自存储，每个列的 6w 条数据组成一个 CU，每个列的所有的 CU 形成一个列的数据文件，每个列都会有独自的数据文件。CU 的根本构造如下
列数据之间具备更高的类似度，所以列存储的压缩性能更好。当只查问少量列且查问数据量较大时，列存储的 IO 性能收益很显著。因为数据分列存储，导致更新(UPDATE)、删除(DELETE)、索引点查性的时候须要拜访或者刷新更多的文件，导致大量的随机 IO；因而相比行存储，列存储的更新、删除、索引点查问的性能较差。同时列存储人造的能够跟向量化执行引擎对接，在表关联、汇聚等重计算场景下能够应用向量化执行引擎晋升计算性能，因而统计分析等重 IO 和重计算型业务举荐应用列存储。
1.3 表存储形式抉择
表的存储类型是表定义设计的第一步，客户业务属性是表的存储类型的决定性因素 。依据以上咱们对行存储和列存储原理的介绍，重查问剖析(大量的多表关联、group by 操作) 场景举荐应用应用列存表，典型的无数仓场景；以点查问为主的场景举荐应用行存表，典型的有详单查问场景。
GaussDB(DWS)反对单个 database 中同时存储行存储和列存储类型的表，以更好的反对混合负载场景
1.4 表存储形式定义
表的行 / 列存储通过表定义的 orientation 属性定义。当指定 orientation 属性为 row 时，表为行存储；当指定 orientation 属性为 column 时，表为列存储；如果不指定，默认为行存储。
行存储表定义形式如下：
CREATE TABLE storage
(
c_id int,
c_d_id int NOT NULL,
c_w_id int NOT NULL,
c_first varchar(16) NOT NULL
)WITH(orientation=row)
DISTRIBUTE BY HASH(c_d_id);列存储表定义形式如下：
CREATE TABLE storage
(
c_id int,
c_d_id int NOT NULL,
c_w_id int NOT NULL,
c_first varchar(16) NOT NULL
)WITH(orientation=column)
DISTRIBUTE BY HASH(c_d_id);2 数据分布形式设计
GaussDB(DWS)的 MPP 架构，人造反对通过散列的形式进行程度分表，将业务数据表的元组打散存储到各个数据节点 (DataNode) 上，通过并行利用各个数据节点的 IO 能力晋升数据扫描的效率。为了优化高频关联小表的查问性能，GuassDB(DWS)反对复制的数据分布形式。表的散布形式取决于表的业务属性，事实表个别数据量较大，且数据减少或者变动很频繁，倡议应用散列散布；维度表数据量较小，且数据个别不会变动，只有定期更新操作，倡议应用复制散布。
2.1 散列散布
散列散布是依照某种散列规定，把表数据 map 到指定的数据节点 (DataNode) 上进行存储的形式。散列散布能够利用各个节点的 IO 资源，晋升各个数据节点的 IO 能力。GaussDB(DWS)中采纳 hash 的散列策略，依照表定义时指定的散布列组合，对一条记录的某一个或几个字段进行 hash 运算后，生成对应的 hash 值，而后依据 DN 实例与哈希值的映射关系取得该元组的指标存储地位。
对于散列散布的表，散布列的抉择十分重要。当散布列抉择正当时，Hash 散列策略能够大大减小计算节点之间的数据交互，大幅晋升查问性能；然而当 hash 散布列抉择不合理时，会导致数据歪斜(某个或者某些 DataNode 的数据量重大超过其它 DataNode 的数据量)，因为短板效应导致集群的无效容量降落。
散列次要应用于客户业务表，这些表有数据量大、数据量逐步减少的特色，实用散列散布能够无效的晋升表查问性能。
2.2 复制散布
复制散布 (replication) 策略将表中的全量数据在集群的每一个 DN 实例上保留一份。在关联操作中复制表能够防止数据重散布操作，减小网络开销，同时缩小了 plan segment(每个 plan segment 都会起对应的线程)的个数；然而复制散布策略会导致比较严重的数据冗余，因而只有小表才适宜复制散布策略。
理论生产上只有小数据量、查问频繁、更新 (DELETE/INSERT/UFPATE) 很少的表 (根本都是维度表) 才会定义 replication 散布策略
2.3 散布形式抉择
表数据分布形式次要根据表的业务属性和数据属性决定，简略总结如下
2.4 散布列定义
表的复制散布属性能够通过表定义指定：
CREATE TABLE storage
(
c_id int,
c_d_id int NOT NULL,
c_w_id int NOT NULL,
c_first varchar(16) NOT NULL
)WITH(orientation=row)
DISTRIBUTE BY REPLICATRRION;表的散列分散布属性能够通过表定义：
CREATE TABLE storage
(
c_id int,
c_d_id int NOT NULL,
c_w_id int NOT NULL,
c_first varchar(16) NOT NULL
)WITH(orientation=row)
DISTRIBUTE BY HASH(c_d_id);3 散布列设计
对于采取散列散布策略的表，散布列的抉择取决于表数据的特色以及表相干的业务查问特色，举荐应用常常做关联查问的列、且数据分布平均的列作为散布列。好的散布列能够通过缩小跨节点的数据打算节俭网络资源开销，优化查问性能。
3.1 散布列抉择策略
Hash 散布表的散布列选取至关重要，须要满足以下准则：
a) 列值应比拟离散，以便数据可能均匀分布到各个 DN
散布列值散布不平均会导致数据在数据节点散布不平均(某些 DataNode 上数据量大，某些 DataNode 上数据量小)，这会导致不同 DataNode 上数据扫面的计算量不平衡，从而拖慢整个表扫描的性能；同时会因为局部 DataNode 的磁盘容量提前爆满，集群只读，导致集群无效容量降落。通常状况下应用表的主键列或者惟一索引列作为表的散布列是一个不错的抉择
b) 思考抉择查问中的连贯条件为散布列
GaussDB(DWS)的散列策略是 hash，依据 GaussDB(DWS)的分布式查问框架，当两表等值关联 (join) 列刚好是表的散布列时(如果散布列是多列，那么要求所有列都存在等值关联条件)，join 工作能够不再数据重散布的状况下间接 Join，这样能够省去数据重散布的工夫开销和网络资源开销，从而晋升查问计算性能。
c) 在满足后面两条准则的状况下尽量不要选取存在常量等值 filter 的列
GaussDB(DWS)会协调节点 (Coordinator) 上进行工作布局，此时会依据表的过滤条件 (Filter) 进行扫面操作剪枝优化，以较小 IO 资源开销。如果表 dwcjk 的散布列是 zqdh，且表 dwcjk 扫描时存在 Filter 条件 zqdh=’000001’，而依据散列策略 zqdh=’000001’的值都散布在数据节点 DN1 上，那么协调节点 (Coordinator) 上进行工作布局时会对 dwcjk 表的扫描操作进行剪枝(指定只有在数据节点 DN1 对表 dwcjk 进行数据扫描操作)。这样对于表扫描的理论压力会值落在节点 DN1，导致不同数据节点的 IO 压力不平衡。
留神此策略次要实用于统计分析类的重查问场景，对于详单查问等以点查为次要场景的查问类业务，在满足前两个束缚的前提下，能够优选存在常量等值 Filter 束缚列作为散布列。因为这种场景在数据节点上应用索引减速查问，查问耗时往往以 ms 或者几十 ms 计，通过剪枝把查问工作 map 到具体的某个数据节点上执行，节俭有效操作(不必连贯到所有的数据节点上操作)，同时也会大大的进步并发能力
3.2 散布列抉择的限度
GaussDB(DWS)的列存储格局的表不反对主键和惟一束缚，行存储格局表反对主键和惟一束缚。然而存储格局表的主键和惟一束缚的创立存在严格束缚：散布列的汇合是主键列或者索引列的子集。
多个列作为散布列时，散布列的程序会影响数据分布，即同一条记录在 distribute by hash(col1, col2)形式下，跟在 distribute by hash(col2, col1)散布形式下可能会 map 到不同的 DataNode 上进行存储。
GaussDB(DWS)对散布列的个数没有限度，然而倡议散布列的个数尽量少，一方面能够减小数据 map 到不同 DN 的计算开销，同时也能够更好的全匹配 join 条件，晋升查问性能。
3.3 散布列离散性校验
对于以后已创立并且导入数据的表，能够应用如下 SQL 测验表数据分布的离散型
-- 'public' 是表的 schema 名称，'storage' 是表名
SELECT * FROM table_distribution('public.storage') ORDER BY dnsize;
对于曾经创立并且导入数据的表，如果咱们认为以后的散布列不够离散，在批改为其它列之前，可尝试应用如下 SQL 判断指标散布列的离散性
-- 'public' 是表的 schema 名称，'storage' 是表名，c_id 是要检测的列名
SELECT * FROM table_skewness('public.storage', 'c_id') ORDER BY seqnum;
当确定指标散布列之后，能够应用如下 SQL 实现散布列的批改
-- 'public' 是表的 schema 名称，'storage' 是表名，c_id 是批改后的指标散布列
ALTER TABLE public.storage DISTRIBUTE BY HASH(c_id);4 表分区设计
艰深的讲表，分区就是把一个大表依照条件宰割为若干个小表，这种宰割体现在数据库外部的数据管理上，对表数据的惯例操作 (UPDATE/DELETE/INSERT/SELECT) 是通明的。个别对数据和查问都有显著区间段特色的表应用分区策略，可通过缩小不必要的数据扫描晋升查问性能。如下 case 中，应用分区表能够缩小 60% 的数据扫描量，SQL 查问整体性能晋升 46 左右。
4.1 分区表的劣势
分区表把逻辑上的一张表依据范畴分区策略分成几张物理块库进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据理论是存储在分区上的。分区表和一般表相比具备以下长处：
a) 改善查问性能
对分区对象的查问能够仅搜寻本人关怀的分区，进步检索效率
b) 加强可用性
如果分区表的某个分区呈现故障，表在其余分区的数据依然可用
c) 晋升可维护性
对于须要周期性删除的过期历史数据，能够通过 drop/truncate 分区的形式疾速高效解决
4.2 分区策略抉择
当表有以下特色时，能够思考应用表分区策略
a) 数据具备显著区间性的字段
分区表须要依据有显著区间性字段进行表分区。通常咱们比方日期、区域、数值等字段进行分区，工夫字段是最常见的分区字段。
b) 业务查问有显著的区间范畴特色
查问数据可落到区间范畴指定的分区内，这样能力通过分区剪枝，只扫描查问须要的分区，从而晋升数据扫描效率，升高数据扫描的 IO 开销。
c) 表数据量比拟大
小表扫描自身耗时不大，分区表的性能收益不显著，因而只倡议对大表采取分区策略。列存储模式下因为每个列是独自的文件出处，且最小的存储单元 CU 可存储 6w 行数据，因而对于列存分区表，倡议每个分区的数据不小于 DN 个数 *6w
4.3 分区表定义
分区表策略定义分为两种形式
a) 简略区间切割
这种是最常见的通用的分区定义策略，适宜所有的分区定义场景。
CREATE TABLE web_returns_p1
(
wr_returned_date_sk integer,
wr_returned_time_sk integer,
wr_item_sk integer NOT NULL,
wr_refunded_customer_sk integer
)
WITH (orientation = column)
DISTRIBUTE BY HASH (wr_item_sk)
PARTITION BY RANGE(wr_returned_date_sk)
(PARTITION p2016 VALUES LESS THAN(20161231),
PARTITION p2017 VALUES LESS THAN(20171231),
PARTITION p2018 VALUES LESS THAN(20181231),
PARTITION p2019 VALUES LESS THAN(20191231),
PARTITION pxxxx VALUES LESS THAN(maxvalue)
);b) 指定策略切割
此形式实用于分区距离固定、批量创立分区的场景。当分区个数很多时，此形式可大大节俭创立分区的工作量
CREATE TABLE web_returns_p1
(
wr_returned_date_sk integer,
wr_returned_time_sk integer,
wr_item_sk integer NOT NULL,
wr_refunded_customer_sk integer
)
WITH (orientation = column)
DISTRIBUTE BY HASH (wr_item_sk)
PARTITION BY RANGE(wr_returned_date_sk)
(PARTITION p2016 START(20161231) END(20191231) EVERY(10000),
PARTITION p0 END(maxvalue)
);4.4 分区键抉择限度
相似表散布列的抉择，对于行存储格局的表，如果表存在主键或者惟一束缚，分区键该当是是主键列或者惟一束缚的索引列的子集。
4.5 分区表查问
只有查问语句能够进行分区剪枝的时候，分区表查问才会产生数据扫描上的性能收益。个别只有当分区键跟常量值存在间接的比拟 (>、<、=、<=、>=) 操作时，分区表才能够失常剪枝。咱们能够通过对查问语句执行 explain 命令查看分区剪枝的成果
有时咱们冀望编写的 SQL 语句能够进行分区剪枝，然而实际上并没有走到分区剪枝，这种预期外的行为往往是因为以下因素导致
a) 分区键上有函数
当分区键上存在函数调用时，分区表无奈剪枝
b) 分区键字段的存在隐式类型转换
这种场景往往是因为分区键跟常量值的数据类型不统一，导致打算布局时候区键的数据类型产生隐式类型转换，导致分区无奈剪枝
5 字段设计
表字段设计时须要留神以下内容
a) 应用执行效率比拟高的数据类型
一般来说整型数据的运算 (包含 =、>、
b) 应用短字段的数据类型
长度较短的数据类型不仅能够减小数据文件的大小，晋升 IO 性能；同时也能够减小计算相干计算时的内存耗费，晋升计算性能。比方咱们须要一个整型数据，如果能够用 smallint 就尽量不必 int，如果能够用 int 就尽量不必 bigint。
c) 关联列应用统一的数据类型
表关联列尽量应用雷同的数据类型。如果表关联列数据类型不同，在执行时数据库会动静地转化为雷同的数据类型进行比拟，这种转换会带来肯定的性能开销，同时可能会因为类型转换导致表关联操作时产生数据重散布，导致额定的性能和资源开销。
6 束缚设计
1) 非空 (not null) 束缚
明确不存在 null 值的字段加上 not null 束缚。在特定场景下，优化器会对蕴含 not null 的查问语句进行主动优化，晋升查问效率。
2) 主键 / 惟一束缚
行存储表反对惟一 / 主键束缚，如果表是散列散布，那么束缚列必须蕴含所有的散布列；如果表做了分区，那么束缚列也必须蕴含所有的分区列。
3) 部分聚簇束缚
部分聚簇 (partial cluster key，简称 PCK) 是列存储表一种部分聚簇技术，这种技术能够让表数据在批量入库的时，先对表进行部分排序，而后再写盘。这样能够让雷同 / 类似的数据间断存储，进步数据的压缩比；同时在查问时也能够依赖列存储表的 min/max 稠密索引实现表的 CU 过滤，从实现高效的数据过滤成果 (参见《GaussDB(DWS) 性能调优：列存表 scan 性能优化》)。一张表上只能建设一个 PCK，一个 PCK 能够蕴含多列，然而个别不倡议超过 2 列。通常咱们应用经常出现的、过滤成果比拟好的简略表达式中的列作为 PCK 列，部分聚簇束缚的定义形式跟主键束缚的定义形式相似
CREATE TABLE web_returns_p1
(
wr_returned_date_sk integer,
wr_returned_time_sk integer,
wr_item_sk integer NOT NULL,
wr_refunded_customer_sk integer,
PARTIAL CLUSTER KEY(wr_returned_date_sk)
)
WITH (orientation = column)
DISTRIBUTE BY HASH (wr_item_sk);
7 表定义总结
最初简略总结下表定义流程
点击关注，第一工夫理解华为云陈腐技术~