操作步骤

分布方式及分布键设计

• 表的分布方式的选择一般遵循以下原则：

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

• 分布键选择

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

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

以下为分布方式及分布键设计的一个简单示例，介绍了如何指定分布方式及分布键设计的语法。

1. 使用root用户登录数据库。
2. 创建表，选择分布列和分布方式。

   --REPLICATION分布。
   gaussdb=# CREATE TABLE tb_t1(c1 int, c2 int)DISTRIBUTE BY REPLICATION;
   
   --HASH分布。
   gaussdb=# CREATE TABLE tb_t2(c1 int,c2 int)DISTRIBUTE BY HASH(c1);
   
   --RANGE分布。
   gaussdb=# CREATE TABLE tb_t3(c1 int,c2 int) 
   DISTRIBUTE BY RANGE(c1)(
       SLICE s1 VALUES LESS THAN (100),
       SLICE s2 VALUES LESS THAN (200),
       SLICE s3 VALUES LESS THAN (MAXVALUE) 
   );
   gaussdb=# CREATE TABLE tb_t4(c1 int,c2 int)
   DISTRIBUTE BY RANGE(c1)(
       SLICE s1 START (1) END (100),
       SLICE s2 START (100) END (200),
       SLICE s3 START (200) END (MAXVALUE) 
   );
   
   --LIST分布。
   gaussdb=# CREATE TABLE tb_t5(id INT,name VARCHAR(20),country VARCHAR(30))
   DISTRIBUTE BY LIST(country)(
       SLICE s1 VALUES ('China'),
       SLICE s2 VALUES ('USA'),
       SLICE s3 VALUES (DEFAULT) 
   );
   
   --删除创建的表对象。
   gaussdb=# DROP TABLE tb_t1,tb_t2,tb_t3,tb_t4,tb_t5;

数据类型设计

在数据类型设计时，基于查询效率的考虑，一般遵循以下原则：

• 尽量选择高效的数据类型。选择数值类型时，在满足业务精度的情况下，选择数据类型的优先级从高到低依次为整数、浮点数以及numeric。
• 当多个表存在逻辑关系时，表示同一个含义的字段应该使用相同的数据类型。
• 对于字符串数据，需要根据实际情况选择定长或者变长字符类型。对于varchar以及char等类型，需要指定一个最大长度。这个长度的设置既要考虑可以存储所有可能的数据，也要考虑存储空间避免不必要的资源浪费。

在进行字段设计时，需要根据数据特征选择相应的数据类型。GaussDB支持的数据类型请参见《开发指南》中“SQL参考 > 数据类型”章节。

分区策略

• 简介

  分区是数据库优化的一种技术，通过将大表按规则拆分成多个分区以提高查询和维护效率。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的，这些分区的数据可以存储在不同的存储设备上。GaussDB目前支持范围分区、哈希分区以及列表分区。分区表的优缺点如下：

  • 优点：
    • 提高查询性能：通过减少扫描的数据量使查询性能有显著地提升。
    • 优化存储：通过把不同分区存放到不同的存储介质上，来平衡性能和成本。
    • 增加可维护性：分区表的维护操作（数据清理、重建索引）可以从分区的粒度来进行，减少对整个系统的影响。
    • 提高并发性：分区表可以提高并发性，因为多个分区可以并行处理。例如，多个查询可以同时访问不同的分区，而不会相互干扰。
  • 缺点：
    • 内存资源占用：分区表使用内存大致为（分区数 * 3 / 1024）MB，当分区数太多导致内存不足时，会间接导致性能急剧下降。
    • 分区策略复杂性：制定和实施合适的分区策略需要技术知识和经验。如果分区策略选择不当，可能会导致数据分布不均衡，进一步影响性能。
    • 备份恢复的复杂性：虽然可以单独备份和恢复分区，但这也同时要求需要更加细致的备份策略及管理工作。
• 适合使用分区表的场景
  • 提高查询性能：表的数据量大，且具有某些特性的数据在其中某个场景中经常会用到，可以通过减少查询时扫描数据量来提高性能。如：经常以月、季度、年为单位做分析的表。
  • 平衡性能和成本：表的数据量过大，需要将冷数据（不常访问的数据）移动到低成本存储，而将热数据（频繁访问的数据）保留在高性能存储上。
  • 大数据量表管理：表的数据量过大，需要在多个存储介质上存储的场景。
• 设计阶段注意事项
  • 分区键选择：
    选择分区表的分区键是一个重要的设计决策，因为它直接影响到数据库的性能、可维护性以及数据管理的效率。

    • 优化查询：选择常用查询的分区键。例如，经常根据日期进行查询的表，可以选择日期字段为分区键。
    • 数据分布：选择分区键时，考虑数据的分布情况。避免某些分区数据量过大而其他分区数据量过小的情况。
    • 分区数量和管理：控制分区数量，避免创建过多分区，分区过多会导致管理复杂度上升和性能下降。
  • 分区类型选择：
    • 范围分区：适合分区键的值是连续的值（如时间字段）。
    • 列表分区：适用于离散的但是类型不多的分区键（如地区、状态码等字段）。
    • 哈希分区：用于均匀的分散数据（如用户id）。

以下为分区策略设计的一个简单示例，介绍了如何声明指定分区方式的语法。

1. 使用root用户登录数据库。
2. 创建表，选择分区。

   --范围分区。
   gaussdb=# CREATE TABLE tb_t1(id INT,info VARCHAR(20)) 
   PARTITION BY RANGE (id) (
       PARTITION p1 START(1) END(600) EVERY(200),    
       PARTITION p2 START(600) END(800),
       PARTITION pmax START(800) END(MAXVALUE)
   );
   gaussdb=# CREATE TABLE tb_t2(
       id INT, 
       info VARCHAR(20)
   ) PARTITION BY RANGE (id) (
       PARTITION p1 VALUES LESS THAN (100), 
       PARTITION p2 VALUES LESS THAN (200),
       PARTITION p3 VALUES LESS THAN (MAXVALUE)
   );
   
   --列表分区表示例。
   gaussdb=# CREATE TABLE tb_t3(NAME VARCHAR ( 50 ), area VARCHAR ( 50 )) 
   PARTITION BY LIST (area) (
       PARTITION p1 VALUES ('bj'),
       PARTITION p2 VALUES ('sh'),
       PARTITION pdefault VALUES (DEFAULT)
   );
   
   
   --哈希分区表示例。
   gaussdb=# CREATE TABLE tb_t4(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;
   gaussdb=# CREATE TABLE tb_t5(c1 int) PARTITION BY HASH(C1)(
       PARTITION pa,
       PARTITION pb,
       PARTITION pc
   );
   
   --删除创建的表对象。
   gaussdb=# DROP TABLE tb_t1,tb_t2,tb_t3,tb_t4,tb_t5;

约束配置

• 建议创建约束时，在命名上可明确标识约束类型和约束所在的表名，例如主键约束命名包含PK、表名、构成字段的方式命名。
• DEFAULT约束：需要谨慎选择DEFAULT约束，如果能在业务层面补全字段值，则不建议使用DEFAULT约束。
• 给明确不存在NULL值的字段加上NOT NULL约束，优化器会在特定场景下对其进行自动优化。

以下为添加约束的一个简单示例，介绍了如何指定约束的语法。

1. 使用root用户登录数据库。
2. 创建表，为表添加约束。

   --非空约束。
   gaussdb=# CREATE TABLE tb_t1(id int not null,name varchar(50));
   
   --唯一约束。
   gaussdb=# CREATE TABLE tb_t2(id int UNIQUE,name varchar(50));
   gaussdb=# CREATE TABLE tb_t3(id int, name varchar(50),CONSTRAINT unq_t3_id UNIQUE(id));
   
   --主键约束。
   gaussdb=# CREATE TABLE tb_t4(id int PRIMARY KEY, name varchar(50));
   gaussdb=# CREATE TABLE tb_t5(
       id int,
       name varchar(50),
       CONSTRAINT pk_person5_id PRIMARY KEY(id)
   );
   
   --检查约束。
   gaussdb=# CREATE TABLE tb_t6(name varchar(50),age int CHECK(age > 0 AND age < 200));
   gaussdb=# CREATE TABLE tb_t7(
       name varchar(50),
       age int,
       CONSTRAINT chk_t6_age CHECK (age > 0 AND age < 200) 
   );
   
   --删除创建的表对象。
   gaussdb=# DROP TABLE tb_t1,tb_t2,tb_t3,tb_t4,tb_t5,tb_t6,tb_t7;

索引设计

索引可以提高数据访问速度，但同时也增加了插入、更新和删除操作的处理时间。所以是否要为表增加索引，索引建立在哪些字段上，是创建索引前必须要考虑的问题。建立索引建议遵循以下原则：

• 在经常使用连接的列上创建索引，可以加快连接速度。
• 在经常需要排序的列上创建索引，因为索引列已经排序，加快排序查询的速度。
• 对于WHERE子句经常使用的列上进行创建索引，加快条件的判断速度。
• 复合索引可以包含多个列，但列数越多，索引体积越大，维护开销越高。
• 在频繁更新的字段上避免使用索引，索引会增加数据更新的维护成本，因此尽量避免在频繁更新的字段上创建索引。
• 索引定义里的所有函数和操作符都必须是immutable类型的，即它们的结果必须只能依赖于它们的输入参数，而不受任何外部的影响（如另外一个表的内容或者当前时间）。这个限制可以确保该索引的行为是定义良好的。要在一个索引上或WHERE中使用用户定义函数，请把它标记为immutable类型函数。
• 分区表索引分为LOCAL索引与GLOBAL索引，LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。
• 定期维护索引，在以下几种情况下需要使用REINDEX重建索引：
  • 索引崩溃，并且不再包含有效的数据。
  • 索引变得“臃肿”，包含大量的空页或接近空页。
  • 为索引更改了存储参数（例如填充因子），并且希望这个更改完全生效。
  • 使用CONCURRENTLY选项创建索引失败，留下了一个“非法”索引。
• 在索引名称中加入表名和索引锁涉及的关键列。例如idx_test_c1表示这是在test表的c1字段上创建的索引。

以下为索引设计的一个简单示例，介绍了如何为表添加索引的语法。

1. 使用root用户登录数据库。
2. 创建表并为表添加索引。

   gaussdb=# CREATE TABLE tb_t1(id int not null,name varchar(50));
   
   --为表增加索引。
   gaussdb=# CREATE INDEX idx_t1_id ON tb_t1(id);
   
   --删除创建的表对象。
   gaussdb=# DROP TABLE tb_t1;

存储参数调优

• 填充因子

  一个表的填充因子（fillfactor）是一个介于10和100之间的百分数。在Ustore存储引擎下，该值的默认值为92，在ASTORE存储引擎下默认值为100（完全填充）。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是最佳选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。示例如下：

  CREATE TABLE test(c1 int,c2 int) WITH (FILLFACTOR = 80);

• 存储引擎

  指定存储引擎类型，该参数设置成功后就不再支持修改。示例如下：

  CREATE TABLE test(c1 int,c2 int) WITH (STORAGE_TYPE = USTORE);

  • USTORE，表示表支持Inplace-Update存储引擎。使用USTORE表，必须要开启track_counts和track_activities参数，否则会引起空间膨胀。
  • ASTORE，表示表支持Append-Only存储引擎。