划重点!DWS开发的五大要点

摘要:高效使用数据库是一个合格的开发工程师的必备技能,如何使用DWS进行高效开发,提升应用效率,技术干货来喽~~~

高效使用数据库是一个合格的开发工程师的必备技能,如何使用DWS进行高效开发,提升应用效率,下面这5个要点可以指导你进行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列为分布列可以最大程度的实现这点

怎么选择存储类型:

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

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

怎么选择分布方式:

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

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

怎么选择分布列:

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

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

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

怎么使用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并把执行结果存入临时表

本文分享自华为云社区《DWS开发指导》,原文作者: 独孤求败马? 。

 

点击关注,第一时间了解华为云新鲜技术~

已标记关键词 清除标记