# 操作步骤
表设计主要包含数据类型设计、分区策略、约束配置、索引设计和存储参数调优等步骤。
#### 数据类型设计
在数据类型设计时，基于查询效率的考虑，一般遵循以下原则：
- 建议选择高效的数据类型。选择数值类型时，在满足业务精度的情况下，选择数据类型的优先级从高到低依次为整数、浮点数以及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存储引擎。