DWS最佳实践（五）DWS数据库的SQL调优基础

1.  概述

（1）调优手段之统计信息

• DWS优化器是典型的基于代价的优化。会根据数据库一些特征值计算出整体代价最小的执行方式。这些特征值就是统计信息。准确的统计信息将帮助优化器选择最优的执行计划，周期性的运行ANALYZE，可以收集这些信息。

（2）调优手段之底层存储

• DWS表支持行存表，列存表，底层存储方式的选择严格依赖于客户的具体业务场景。一般来说计算型业务查询场景（以关联，聚合操作为主）建议使用列存表，这个也是DWS推荐的存储选择；点查询，大批量UPDATE/DELETE业务场景适合行存表，但是行存表数据膨胀严重，需要谨慎选择。

（3）调优手段之SQL重写

• 除了上述优化场景，还需要根据数据的SQL的运行机制，在保证客户业务逻辑的前提下，通过一定规则重写SQL语句，可以大幅度提升SQL语句的性能。

2. 执行计划调优

SQL执行计划是一个节点树，会显示DWS执行一条SQL语句时的详细步骤。使用EXPLAIN命令可以查看优化器为每一个查询生成的具体执行计划。

（1）EXPLAIN有以下几种具体的语法说明：

•  EXPLAIN statement:生成执行计划，不实际执行。Statement代表SQL；

•  EXPLAIN ANALYZE statement:生成执行计划，进行执行，并显示执行的概要信息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花掉的总时间和它实际返回的行数。

•  EXPLAIN PERFORMANCE statement:生成执行计划，进行执行，并显示执行期间的全部信息。

（2）执行计划详解：

        执行explain performance statement的输出为：

• Id:执行算子节点编号。

• Operation:具体的执行节点算子名称。

• Vector前缀的算子是指向向量化执行引擎算子，一般出现含有列存表的Query中。

• Streaming是一个特殊的算子，它实现了分布式架构的核心数据shuffle功能，

  Streaming一共有三种状态，分别对应分布式架构下不同数据的shuffle功能：

            -Streaming（type:GATHER）：作用coordinator从DN（数据节点）收集数据；

            -Streaming（type:REDISTRIBUTE）：作用是DN根据选定的列把数据重分布到所有的DN。

            -Streaming（type:BROADCAST）：作用是把当前DN的数据广播给其他所有的DN

• A-time: 当前算子执行完成时间，一般DN上执行的算子的A-time是由[]括起来的两个值，分别表示此算子在所有DN上完成的最短时间和最长时间。

  注：A-time中的两个值偏差越大，表明次算子的计算偏斜（在不同DN上执行时间差异）越大，人工干预调优的必要性越大。

• A-rows: 表示当前算子实际输出元组数。

• E-rows: 每个算子估算的输出行数。

  注：A-rows和E-rows的差异体现了优化器估算和实际执行的偏差度。一般来说，他们偏差越大，越可以认为优化器生成的计划不可信，人工干预调优的必要性就越大。

• Peak Memory: 此算子在每个DN上执行时使用的内存峰值。

• E-memory: DN上每个算子估算的内存使用量，只用DN上执行的算子会显示。某些场景会在估算的内存使用量后用括号显示该算子在内存资源充足下可以自动扩展的内存上限。

• A-width: 表示当前每行元组的实际宽度，仅对于重内存使用算子会显示。

• E-width: 每个算子输出元组的估算宽度。

• E-costs: 每个算子的执行代价。

执行计划层级解读（纵向）：

（1）Cstore Scan on abc

       表扫描算子：用Cstore Scan的方式扫描表abc。这一层的作用是把表abc的数据从buffer或者磁盘上读上来输送给上层节点参与计算。

       表扫描算子还有以下其他几种：

       - Seq Scan：全表顺序扫描。一般表示扫描的是行存表。

       - Index Scan：优化器决定使用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点真实地从表中抓取数据。

       - Partitioned Scan：扫描分区表，只扫描相关的分区。

      以上几种优化器有可能组合起来扫描，如：Partitioned Cstore Scan表示扫描一个列存分区表，其余类似。

（2）Vector Hash Aggregate

       聚合算子，作用是把下层计算输送上来的算子做聚合操作（group by）

（3）Vector Streaming（type:GATHER）

       Shuffle算子，此处GATHER类型的Shuffle算子作用是把数据从DN汇聚到CN。

（4）Row Adapter

        存储格式转化算子，主要作用是把内存中列式数据转为行式数据，以便客户端展示。

        表连接方式：

•  Nested Loop: 嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一个行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。

•  Hash Join: 哈希连接，适用于数据量大的表的连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。

• Merge Join: 归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行融合连接时，并不需要再排序，此时融合连接的性能优于哈希连接。

  运算符：

             - Sort:对结果集进行排序

             - Filter: explain输出显示where子句作为一个filter条件附属于顺序扫描计划节点。

             - Limit: limit限定了执行结果的输出记录数。如果增加了Limit，那么不是所有的行都会被检索到。

3. 调优流程

步骤1：收集SQL中涉及到的所有表的统计信息。在数据库中，统计信息是优化器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成计划严重劣化，从而导致性能问题。可参考第四章的运维操作收集统计信息。

步骤2：通过查看执行计划来查找原因。

步骤3：审视和修改表定义。可参考第一章的表设计。

步骤4：针对执行计划，定位SQL慢的具体原因和改进措施，（具体可参考链接：https://support.huaweicloud.com/devg-dws/dws_04_0925.html）

步骤5：通常情况下，有些SQL语句可以通过查询重写转换成等价的，或特定场景下等价的语句。重写后的语句比原语句更简单，且可以简化某些执行步骤达到提升性能的目的。可参考第二章SQL建议。