表设计最佳实践

使用分区表

分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区表和普通表相比具有以下优点：

1. 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
2. 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
3. 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。

选择分布方式

复制表（Replication）方式将表中的全量数据在集群的每一个DN实例上保留一份。主要适用于记录集较小的表。这种存储方式的优点是每个DN上都有该表的全量数据，在join操作中可以避免数据重分布操作，从而减小网络开销，同时减少了plan segment（每个plan segment都会起对应的线程）；缺点是每个DN都保留了表的完整数据，造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。

哈希（Hash）表将表中某一个或几个字段进行hash运算后，生成对应的hash值，根据DN实例与哈希值的映射关系获得该元组的目标存储位置。对于Hash分布表，在读/写数据时可以利用各个节点的I/O资源，大大提升表的读/写速度。一般情况下大表定义为Hash表。

范围（Range）和列表（List）分布是由用户自定义的分布策略，根据分布列的取值落入满足一定范围或者具体值的对应目标DN，这两种分布方式便于用户灵活地进行数据管理，但对用户本身的数据抽象能力有一定的要求。

如图1所示，复制表如图中的表T1，哈希表如图中的表T2。

图1 复制表和哈希表

• 在对复制表进行数据插入、修改、删除等操作时，如果用户使用声明为可下推（shippable或者immutable）的函数对不可下推的成分进行封装，则可能会导致复制表不同DN数据不一致。
• 使用带有窗口函数、rownum、limit子句和用户自定义函数等结果不稳定的语句对复制表进行数据插入或修改，可能会导致不同节点数据不完全相同。

表压缩级别

在创建表时，可以自定义字段的压缩级别及压缩水平。压缩不仅影响到数据加载，也影响到数据查询。表压缩级别由参数COMPRESSION控制。

参数说明：

COMPRESSION指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。

取值范围：

• 行存表的有效值为YES/NO，默认值为NO。

客户可根据不同场景依据表2选择不同压缩级别。

选择分布列

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

1. 列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列。
2. 在满足上述条件的情况下，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量。

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

1
2
3
4
5

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

示例如下：

CREATE TABLE t1(c1 int) distribute by hash(c1);
INSERT INTO t1 values(generate_series(1,100));
select xc_node_id, count(1) from t1 group by xc_node_id order by xc_node_id desc;
DROP TABLE t1;

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

GaussDB支持多分布列特性，可以更好地满足数据分布的均匀性要求。

Range/List分布表的分布列由用户根据实际需要进行选择。除了需选择合适的分布列，还需要注意分布规则对数据分布的影响。

选择数据类型

高效数据类型，主要包括以下三方面：

1. 尽量使用执行效率比较高的数据类型

   一般来说整型数据运算（包括=、＞、＜、>=、<=、!=等常规的比较运算，以及GROUP BY）的效率比字符串、浮点数要高。

2. 尽量使用短字段的数据类型

   长度较短的数据类型不仅可以减小数据文件的大小，提升I/O性能；同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用SMALLINT就尽量不用INT，如果可以用INT就尽量不用BIGINT。

3. 使用一致的数据类型

   表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销。

查看表所在节点

用户在建表时可以指定表如何在节点之间分布或者复制，详情请参见•DISTRIBUTEBY，分布方式介绍请参见选择分布方式。

用户在建表时也可设置“Node Group”来指定表所在的Group，详情请参见•TO{GROUPgroupname|...。

用户还可以通过以下命令查看表所在实例。

1. 查询表所在的schema。

   select t1.nspname,t2.relname from pg_namespace t1,pg_class t2 where t1.oid = t2.relnamespace and t2.relname = 'table1';

   上述命令中，“nspname”为schema的名称，“relname”为表、索引、视图等对象的名称，“oid”为行标识符，“relnamespace”为包含这个关系的名称空间的OID，“table1”为表名称。

2. 查看表的relname和nodeoids。

   select t1.relname,t2.nodeoids from pg_class t1, pgxc_class t2, pg_namespace t3  where t1.relfilenode =  t2.pcrelid and t1.relnamespace=t3.oid and t1.relname = 'table1' and t3.nspname ='schema1';

   上述命令中，“nodeoids”为表分布的节点OID列表，“relfilenode”为这个关系在磁盘上的文件的名称，“pcrelid”为表的OID，“schema1”为1中查询出的该表所在schema。

3. 根据查询到的表分布的节点，查询表所在实例。

   select * from pgxc_node where oid in (nodeoids1, nodeoids2, nodeoids3);

   上述命令中的“nodeoids1, nodeoids2, nodeoids3”为2中查询到的3个nodeoids，操作时以实际查询到的为准，各nodeoids间以“,”隔开。