# 操作步骤 表设计主要包含分布方案及分布键设计、数据类型设计、分区策略、约束配置、索引设计和存储参数调优等步骤。 #### 分布方式及分布键设计 - 表的分布方式的选择一般遵循以下原则： | 分布方式 | 描述 | 适用场景 | |:---|:---|:---| | 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存储引擎。