摘要：高效应用数据库是一个合格的开发工程师的必备技能，如何应用 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) 尽量应用执行效率比拟高的数据类型，一般来说整型数据运算 (包含 =、>、
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 开发领导》，原文作者：独孤求败马？。
点击关注，第一工夫理解华为云陈腐技术~