GaussDB数据库对象设计--表设计

GaussDB-表设计

GaussDB是分布式架构。数据分布在各个DN上。总体上讲，良好的表设计需要遵循以下原则：

• 将表数据均匀分布在各个DN上。数据均匀分布，可以防止数据在部分DN上集中分布，从而导致因存储倾斜造成集群有效容量下降。通过选择合适的分布列，可以避免数据倾斜。
• 将表的扫描压力均匀分散在各个DN上。避免扫描压力集中在部分DN上，而导致性能瓶颈。例如，在事实表上使用等值过滤条件时，将会导致扫描压力不均匀。
• 减少需要扫描的数据量。通过分区表的剪枝机制可以大幅减少数据的扫描量。
• 尽量减少随机I/O。通过聚簇可以实现热数据的连续存储，将随机I/O转换为连续I/O，从而减少扫描的I/O代价。
• 尽量避免数据shuffle。shuffle是指在物理上，数据从一个节点传输到另一个节点。shuffle占用了大量宝贵的网络资源，减小不必要的数据shuffle，可以减少网络压力，使数据的处理本地化，以提高集群的性能和可支持的并发度。通过对关联条件和分组条件的仔细设计，能够尽可能的减少不必要的数据shuffle。

选择分布方案

表的分布方式的选择如表2 表的分布方式及使用场景所示。

表1 表的分布方式及使用场景

分布方式	描述	适用场景
Hash	表数据通过Hash方式散列到集群中的所有DN上。	数据量较大的事实表。
Replication	集群中每一个DN都有一份全量表数据。	维度表、数据量较小的事实表。
Range	表数据对指定列按照范围进行映射，分布到对应DN。	用户需要自定义分布规则的场景。
List	表数据对指定列按照具体值进行映射，分布到对应DN。	用户需要自定义分布规则的场景。

典型的分布表定义如下：

--定义一个表，表中每行存在所有DN中。
CREATE TABLE warehouse_d1
(
    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;

--定义一个表，使用HASH分布。
CREATE TABLE warehouse_d2
(
    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),
    CONSTRAINT W_CONSTR_KEY3 UNIQUE(W_WAREHOUSE_SK)
)DISTRIBUTE BY HASH(W_WAREHOUSE_SK);

--定义一个表，使用RANGE分布。
CREATE TABLE warehouse_d3
(
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 RANGE(W_WAREHOUSE_ID)
(
   SLICE s1 VALUES LESS THAN (10) DATANODE dn1,
   SLICE s2 VALUES LESS THAN (20) DATANODE dn2,
   SLICE s3 VALUES LESS THAN (30) DATANODE dn3,
   SLICE s4 VALUES LESS THAN (MAXVALUE) DATANODE dn4
);
 
--定义一个表，使用LIST分布。
CREATETABLEwarehouse_d4
(
W_WAREHOUSE_SKINTEGERNOTNULL,
W_WAREHOUSE_IDCHAR(16)NOTNULL,
W_WAREHOUSE_NAMEVARCHAR(20),
W_WAREHOUSE_SQ_FTINTEGER,
W_STREET_NUMBERCHAR(10),
W_STREET_NAMEVARCHAR(60),
W_STREET_TYPECHAR(15),
W_SUITE_NUMBERCHAR(10),
W_CITYVARCHAR(60),
W_COUNTYVARCHAR(30),
W_STATECHAR(2),
W_ZIPCHAR(10),
W_COUNTRYVARCHAR(20),
W_GMT_OFFSETDECIMAL(5,2)
)DISTRIBUTEBYLIST(W_COUNTRY)
(
SLICEs1VALUES('USA')DATANODEdn1,
SLICEs2VALUES('CANADA')DATANODEdn2,
SLICEs3VALUES('UK')DATANODEdn3,
SLICEs4VALUES(DEFAULT)DATANODEdn4
);

 

更多的表分布语法信息请参见CREATE TABLE。

选择分布键

分布表的分布键选择至关重要，如果分布键选择不当，可能会导致数据倾斜，从而导致查询时，I/O负载集中在部分DN上，影响整体查询性能。因此，在确定分布表的分布策略之后，需要对表数据进行倾斜性检查，以确保数据的均匀分布。分布键的选择一般需要遵循以下原则：

• 选作分布键的字段取值应该比较离散，以便数据能在各个DN上均匀分布。当单个字段无法满足离散条件时，可以考虑使用多个字段一起作为分布键。一般情况下，可以考虑选择表的主键作为分布键。例如，在人员信息表中选择证件号码作为分布键。
• 在满足第一条原则的情况下，尽量不要选取在查询中存在常量过滤条件的字段作为分布键。例如，在表dwcjk相关的查询中，字段zqdh存在常量过滤条件“zqdh='000001'”，那么尽量不选择zqdh字段作为分布键。
• 在满足前两条原则的情况，尽量选择查询中的关联条件为分布键。当关联条件作为分布键时，join任务的相关数据都分布在DN本地，将极大减少DN之间的数据流动代价。

选择分区方案

当表中的数据量很大时，应当对表进行分区，一般需要遵循以下原则：

• 使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。
• 分区名称应当体现分区的数据特征。例如，关键字+区间特征。
• 将分区上边界的分区值定义为MAXVALUE，以防可能出现的数据溢出。

表2 表的分区方式及使用场景

分区方式	描述
Range	表数据通过范围进行分区。
List	表数据通过指定列按照具体值进行分区。
Hash	表数据通过Hash散列方式进行分区。

典型的分区表定义如下：

--创建Range分区表。
CREATE TABLE staffS_p1
(
  staff_ID       NUMBER(6) not null,
  FIRST_NAME     VARCHAR2(20),
  LAST_NAME      VARCHAR2(25),
  EMAIL          VARCHAR2(25),
  PHONE_NUMBER   VARCHAR2(20),
  HIRE_DATE      DATE,
  employment_ID  VARCHAR2(10),
  SALARY         NUMBER(8,2),
  COMMISSION_PCT NUMBER(4,2),
  MANAGER_ID     NUMBER(6),
  section_ID     NUMBER(4)
)
PARTITION BY RANGE (HIRE_DATE)
( 
   PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'),
   PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'),
   PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE)
);

--创建List分区表。
CREATE TABLE test_list (col1 int, col2 int)
partition by list(col1)
(
partition p1 values (2000),
partition p2 values (3000),
partition p3 values (4000),
partition p4 values (5000)
);

--创建Hash分区表。
CREATE TABLE test_hash (col1 int, col2 int)
partition by hash(col1)
(
partition p1,
partition p2
);

 

更多的表分区语法信息请参见CREATE TABLE PARTITION。

 

更多详情请参考GaussDB 文档中心：https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html