表设计

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

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

选择存储方案

【建议】表的存储类型是表定义设计的第一步,客户业务类型是决定表的存储类型的主要因素,表存储类型的选择依据请参考表1

表 1 表的存储类型及场景

存储类型

适用场景

行存

  • 点查询(返回记录少,基于索引的简单查询)。
  • 增、删、改操作较多的场景。

列存

  • 统计分析类查询(关联、分组操作较多的场景)。
  • 即席查询(查询条件不确定,行存表扫描难以使用索引)。

选择分布方案

【建议】表的分布方式的选择一般遵循以下原则:

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

分布方式

描述

适用场景

Hash

表数据通过Hash方式散列到数据库实例中的所有DN上。

数据量较大的事实表。

Replication

数据库实例中每一个DN都有一份全量表数据。

维度表、数据量较小的事实表。

Range

表数据对指定列按照范围进行映射,分布到对应DN。

用户需要自定义分布规则的场景。

List

表数据对指定列按照具体值进行映射,分布到对应DN。

用户需要自定义分布规则的场景。

选择分区方案

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

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

典型的分区表定义如下:

  1. CREATE TABLE staffS_p1
  2. (
  3. staff_ID NUMBER(6) not null,
  4. FIRST_NAME VARCHAR2(20),
  5. LAST_NAME VARCHAR2(25),
  6. EMAIL VARCHAR2(25),
  7. PHONE_NUMBER VARCHAR2(20),
  8. HIRE_DATE DATE,
  9. employment_ID VARCHAR2(10),
  10. SALARY NUMBER(8,2),
  11. COMMISSION_PCT NUMBER(4,2),
  12. MANAGER_ID NUMBER(6),
  13. section_ID NUMBER(4)
  14. )
  15. PARTITION BY RANGE (HIRE_DATE)
  16. (
  17. PARTITION HIRE_19950501 VALUES LESS THAN ('1995-05-01 00:00:00'),
  18. PARTITION HIRE_19950502 VALUES LESS THAN ('1995-05-02 00:00:00'),
  19. PARTITION HIRE_maxvalue VALUES LESS THAN (MAXVALUE)
  20. );

选择分布键

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

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