实践系列-GaussDB(DWS)开发指导

                              开发指导

一、     怎么创建索引？

•在经常需要搜索查询的列上创建索引，可以加快搜索的速度。

•在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。

•在经常使用连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度。

•在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。

•在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

•在经常使用WHERE子句的列上创建索引，加快条件的判断速度。

•为经常出现在关键字ORDER BY、GROUP BY、DISTINCT后面的字段建立索引。

二、     怎么建立表结构？

建表的原则：

    （1）、表数据均匀分布在各个DN上，以防止单个DN对应的存储设备空间不足造成集群有效容量下降。选择合适分布列，避免数据分布倾斜可以实现该点

    （2）、表Scan压力均匀分散在各个DN上，以避免单DN的Scan压力过大，形成Scan的单节点瓶颈。分布列不选择基表上等值filter中的列可以实现该点

    （3）、减少扫描数据数据量。通过分区的剪枝机制可以实现该点

    （4）、尽量极少随机IO。通过聚簇/局部聚簇可以实现该点

    （5）、尽量避免数据shuffle，减小网络压力。通过选择join-condition或者group by列为分布列可以最大程度的实现这点

1、 怎么选择存储类型：

a)   行存表：点查询，返回记录少，基于索引的简单查询；增删改较多的表

b)   列存表：大表，统计分析类查询，group、join比较多的表

2、 怎么选择分布方式：

a)   复制表：适用于记录较少的的维度表

b)   哈希表：数据量比较大的实事表

3、 怎么选择分布列：

a)   列值应比较离散，以便数据能够均匀分布到各个DN。例如，考虑选择表的主键为分布列，如在人员信息表中选择身份证号码为分布列

b)   在满足第一条原则的情况下尽量不要选取存在常量filter的列。例如，表dwcjk相关的部分查询中出现dwcjk的列zqdh存在常量的约束(例如zqdh=’000001’)，那么就应当尽量不用zqdh做分布列

c)   在满足前两条原则的情况，考虑选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN之间的通信数据量

4、 怎么使用PCK局部聚簇：

a)   受基表中的简单表达式约束。这种约束一般形如col op const，其中col为列名，op为操作符 =、>、>=、<=、<，const为常量值

b)   尽量采用选择度比较高(过滤掉更多数据)的简单表达式中的列

c)   尽量把选择度比较低的约束col放在Partial Cluster Key中的前面

d)   尽量把枚举类型的列放在Partial Cluster Key中的前面

三、     怎么创建分区表？

分区表创建使用原则如下

1、 对于记录数小于100万的表，可以不使用分区表。

2、 对于记录数超过100万、低于500万的表，宜使用分区表。

3、 对于记录数超过500万且空间占用超过2GB的表，应使用分区表。

4、 分区表的单个分区记录数可超过500万，空间占用不宜超过2GB。

5、 暂不支持复合分区、二级分区。

6、 对于如下特殊场景，可不使用分区表：

A） 备份表或者老化表

此类数据表存放应用系统不再使用到的数据，在联机程序和批量程序均不访问此类数据，仅用于某些特殊场景下（例如生产问题排查、公/检/法查询等）使用，通过直接查询数据库的方式访问，相关数据应使用truncate或者drop来进行清理。

B） 交换分区表

此类数据表是存放分区表某个分区的数据，通过交换分区技术与分区表进行数据传递。

C） 采用分库分表设计的表

此类数据表已通过分库、分表策略进行了数据分割，可不使用分区表。

D） 批量处理中使用到的中间表、临时表，可不使用分区表。

E） 外公司产品中达到分区表条件的数据表，经评估如因外公司产品原因无法分区，应按应用维度提交规范例外，并随规范例外管理流程定期与外公司确认分区的可行性。

F） 对于存量应用达到分区条件的数据表，如应用规划废止，可不使用分区表。

7、 对于记录数超过100万且需要进行历史数据清理的表，宜通过业务发生日期等数据清理条件进行分区，通过分区truncate或exchange技术进行数据清理。

8、 分区的关键字应是where字句中的查询条件之一，分区的关键字不宜进行更新操作，避免数据因分区条件变化进行分区移动，导致性能下降。

9、 从数据的维护和使用效率情况看，除非是业务的特别需求，宜使用分区索引并设计为前缀分区索引。

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

−   应使用具有明显区间性的字段进行分区，比如日期、区域等字段上建立分区。

−   分区名称应当体现分区的数据特征。例如，关键字+区间特征。

−   将分区上边界的分区值定义为MAXVALUE，以防止可能出现的数据溢出。

典型的分区表定义如下：

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)

);

四、     怎么选择数据类型？

a)   尽量使用执行效率比较高的数据类型，一般来说整型数据运算(包括=、＞、＜、≧、≦、≠等常规的比较运算，以及group by)的效率比字符串、浮点数要高

b)   尽量使用短字段的数据类型，长度较短的数据类型不仅可以减小数据文件的大小，提升IO性能；同时也可以减小相关计算时的内存消耗，提升计算性能。比如对于整型数据，如果可以用smallint就尽量不用int，如果可以用int就尽量不用bigint

c)   使用一致的数据类型，表关联列尽量使用相同的数据类型。如果表关联列数据类型不同，数据库必须动态地转化为相同的数据类型进行比较，这种转换会带来一定的性能开销

d)   应尽量使用高效数据类型。选择数值类型时，在满足业务精度的情况下，选择数据类型的优先级从高到低依次为整数、浮点数、NUMREIC

e)   对于字符串数据，建议使用变长字符串数据类型，并指定最大长度。请务必确保指定的最大长度大于需要存储的最大字符数，避免超出最大长度时出现字符截断现象。除非明确知道数据类型为固定长度字符串，否则，不建议使用CHAR(n)、BPCHAR(n)、NCHAR(n)、CHARACTER(n)

f)   对于日期类型，时间精度要求大于等于1天的，可以使用varchar2存储；时间精度要求大于等于1秒的，宜使用date类型；时间精度要求小于1秒的，应使用timestamp类型

g)   在需要数据类型转换（不同数据类型进行比较或转换）时，应使用强制类型转换，以防隐式类型转换结果与预期不符

五、     sql开发经验总结

a)   使用union all代替union，union在合并两个集合时会执行去重操作，而union all则直接将两个结果集合并、不执行去重。执行去重会消耗大量的时间，因此，在一些实际应用场景中，如果通过业务逻辑已确认两个集合不存在重叠，可用union all替代union以便提升性能。

b)   join列增加非空过滤条件，若join列上的NULL值较多，则可以加上is not null过滤条件，以实现数据的提前过滤，提高join效率

c)   not in转not exists，not in语句需要使用nestloop anti join来实现，而not exists则可以通过hash anti join来实现。在join列不存在null值的情况下，not exists和not in等价。因此在确保没有null值时，可以通过将not in转换为not exists，通过生成hash join来提升查询效率

d)   避免对索引使用函数或表达式运算，对索引使用函数或表达式运算会停止使用索引转而执行全表扫描

e)   尽量避免在where子句中使用!=或<>操作符、null值判断、or连接、参数隐式转换

f)   对复杂SQL语句进行拆分，对于过于复杂并且不易通过以上方法调整性能的SQL可以考虑拆分的方法，把SQL中某一部分拆分成独立的SQL并把执行结果存入临时表