GaussDB(DWS) 性能调优系列实现篇六：十八般武艺 Plan hint 运用
摘要：本文介绍 GaussDB(DWS)另一种可以人工干预计划生成的功能--plan hint。
前言
数据库的使用者在书写 SQL 语句时，会根据自己已知的情况尽力写出性能很高的 SQL 语句。但是当需要写大量 SQL 语句，且有些 SQL 语句的逻辑极为复杂时，数据库使用者就很难写出性能较高的 SQL 语句。
而每个数据库都有一个类似人的大脑的查询优化器模块，它接收来自语法分析模块传递过来的查询树，在这个查询树的基础上进行逻辑上的等价变换、物理执行路径的筛选，并且把选择出的最优的执行路径传递给数据库的执行器模块。查询优化器是提升查询效率非常重要的一个手段。
数据库查询优化器的分类详见博文《GaussDB(DWS)性能调优系列基础篇一：万物之始analyze统计信息》性能调优系列基础篇一：万物之始 analyze 统计信息")。
Plan hint 的引入
由于优化器基于统计信息和估算模型生成计划，当估算出现偏差时，计划可能出现问题，性能较差，使语句的执行变得奇慢无比。
通常，查询优化器的优化过程对数据库使用者是透明的。在上一篇博文《GaussDB(DWS)性能调优系列实战篇五：十八般武艺之路径干预》性能调优系列实战篇五：十八般武艺之路径干预")中，Gauss DB(DWS)提供了可通过配置 GUC 参数的方式，全局的干预查询计划的路径生成。本次，将介绍另一种可以人工干预计划生成的功能--plan hint。Hint 是一种通过 SQL 语句中的注释传递给优化器的指令，优化器使用 hint 为语句选择执行计划。在测试或开发环境中，hint 对于测试特定访问路径的性能非常有用。例如，您可能知道某些表优先进行连接，可以有效减少中间结果集大小，在这种情况下，可以使用提示来指示优化器使用更好的执行计划。
Plan hint 功能属于语句级的调控，仅对当前语句的当前层次生效，可以帮助我们在调优的过程中，针对特定的语句，通过 plan hint 进行人工干预，选择更高效的执行计划。
GaussDB(DWS)的 Plan hint 有以下种类:
Join 顺序的 hint：调整 join 顺序
Scan/Join 方法的 hint：指定或避免 scan/join 的方法
Stream 方法的 hint：指定或避免 redistribute/broadcast
行数 hint：对于给定结果集，指定行数，或对原有估算值进行计算调整
倾斜值 hint：在倾斜优化时，指定需要倾斜处理的特殊值
下面分别对以上几种 plan hint 的功能及其在实际中的运用做一下介绍。在下面几节的介绍中，除倾斜值 hint 外，都以 tpcds 中的 Q6 作为示例。为了能明显看到 hint 在查询优化过程中的作用，我们将 store_sales 表的统计信息删除。原始语句和生成的初始计划如下。
示例语句：
Plan hint 的应用
Join 顺序的 hint
语法：
格式 1：
leading(table_list)
仅指定 join 顺序，不指定内外表顺序
格式 2：
leading((table_list))
同时指定 join 顺序和内外表顺序，内外表顺序仅在最外层生效
说明：
table_list 为要调整 join 顺序的表名列表，表之间使用空格分隔。可以包含当前层的任意个表（别名），或对于子查询提升的场景，也可以包含子查询的 hint 别名，同时任意表可以使用括号指定优先级。
注意：
表只能用单个字符串表示，不能带 schema。
表如果存在别名，需要优先使用别名来表示该表。
list 中的表在当前层或提升的子查询中必须是唯一的。如果不唯一，需要使用不同的别名进行区分。
同一个表只能在 list 里出现一次。
示例 1：
对于示例中的计划，可以看出，17-22 号算子时 store_sales 表和 item 表 join 后生成 hash 表，store_sales 表的数据量很大，store_sales 和 item 表 join 后未过滤掉任何数据，所以这两个表 join 并生成 hash 表的时间都比较长。根据对 tpcds 各表中数据分布的了解，我们知道，store_sales 表和 date_dim 进行 join，可以过滤掉较多数据，所以，可以使用 hint 来提示优化器优将 store_sales 表和 date_dim 表先进行 join，store_sales 作为外表，date_dim 作为内表，减少中间结果集大小。语句改写如下：
增加了 join 顺序 hint 的查询计划如下：
通过调整 join 顺序，使得之后各 join 的中间结果集都大幅减少，执行时间由 34268.322ms 降为 11095.046ms。
Scan/Join 方法的 hint
用于指示优化器使用那种 scan 方法或 join 方法。
语法：
Join 方法的 hint 格式：
[no] nestloop|hashjoin|mergejoin(table_list)
Scan 方法的 hint 格式：
[no] tablescan|indexscan|indexonlyscan(table [index])
说明：
no 表示提示优化器不使用这种方法。
table 表示 hint 指定的表，只能指定一个表，如果表存在别名应优先使用别名进行 hint。
index 表示使用 indexscan 或 indexonlyscan 的 hint 时，指定的索引名称，当前只能指定一个。
示例 2-1：
示例 1 中得到的执行计划，由于 store_sales 表的行数估算不准，store_sales 和 date_dim 采用了效率不好的 nestloop 方式进行 join。现在通过本节的 hint 方法来指示优化器不使用 nestloop 方式进行 join。
增加了 join 方式 hint 后的计划如下：
从上面的计划中可以看到，优化器对 store_sales 和 date_dim 表之间的 join 方法已经由 nestloop 改为了 hashjoin，且这条语句的执行时间也由 11095.046ms 降为 4644.409ms。
示例 2-2：
为了演示 scan 方式的 hint 使用，如下在 item 表的 i_item_sk 列上创建一个名称为 i_item 的索引。
create index i_item on item(i_item_sk);
通过下面的语句指示优化器访问别名为 i 的 item 表时，使用索引 i_item 做索引扫描。
使用 scan 的 hint 指示扫描 item 表时采用 indexscan 后的查询计划如下：
从上面的执行结果看，使用索引扫描后（s 和 d join 后，再和 item 的 join 采用了 mergejoin 方式）反而使性能略有下降，所以后面的用例中，我们将不对 item 表采用索引扫描的方法。
Stream 方法的 hint
用于指示优化器采用哪种 stream 方法，可以为 broadcast 和 redistribute。
语法：
[no] broadcast|redistribute(table_list)
说明：
no 表示不使用 hint 的 stream 方式。
table_list 为进行 stream 操作的单表或多表 join 结果集
示例 3：
此处作为演示，修改语句如下，通过 hint 指示优化器对 item 表扫描的结果使用 broadcast 方式进行分布。
指示优化器使用 broadcast 方式分布 item 结果的查询计划如下：
可以看出，之前在 item 扫描后的结果上是 redistribute 分布方式，现在已经变为了 broadcast 分布方式。Broadcast 分布方式一般用于数据量比较小的结果集上，相反 redistribute 用于数据量比较大的结果集上。所以，根据执行计划中单表或表 join 后的结果集大小，可以通过这种方式，调整结果集的分布方式，从而提升查询的性能。
行数 hint
用于指明中间结果集的大小，支持绝对值和相对值的 hint。
语法：
rows(table_list #|+|-|* const)1. #,+,-,，进行行数估算 hint 的四种操作符号。#表示直接使用后面的行数进行 hint。+,-,表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为 1 行。
说明：
1.#,+,-,，进行行数估算 hint 的四种操作符号。#表示直接使用后面的行数进行 hint。+,-,表示对原来估算的行数进行加、减、乘操作，运算后的行数最小值为 1 行。
2.const 可以是任意非负数，支持科学计数法。
由于 store_sales 表没有统计信息，所以在上面的各个计划中可以看到，store_sales 表的估计行数和实际行数相差非常大，这就会导致生成了最初的效率比较低的计划。下面我们看看使用行数 hint 的效果。
示例 4：
具体查询计划如下：
指定了 store_sales 表的准确行数后，优化器生成的计划执行时间直接从最初的 34268.322ms 将为 1991.843ms，提升了 17 倍。这也充分的说明了优化器对统计信息准确性的强烈依赖。
除了可以指明单表的行数，还可指明中间结果集的行数。比如上例中 8 号算子的实际行数和估计行数也相差较大，我们指明 8 号算子的结果集行数看看效果。在下面这个例子中，还使用了子链接块名的 hint，为子链接指定了一个别名，便于在行数 hint 中指定子链接。
查询计划如下：
8 号算子的估计行数已经和实际行数一致。由于 8 号算子不是计划的瓶颈点，所以性能提升并不明显。
倾斜值 hint
用于指明查询运行时重分布过程中存在倾斜的重分布键和倾斜值，针对 Join 和 HashAgg 运算中的重分布进行优化。
语法：
指定单表倾斜
skew(table (column) [(value)])
指定中间结果倾斜
skew((join_rel) (column) [(values)])
说明：
table 表示存在倾斜的单个表名。
join_rel 表示参与 join 的两个或多个表，如（t1 t2）表示 t1 和 t2 join 后的结果存在倾斜。
column 表示倾斜表中存在倾斜的一个或多个列。
value 表示倾斜的列中存在倾斜的一个或多个值。
示例 5：
本节，我们用 tpcds 中的 Q1 作为示例，未使用 hint 前的查询及计划如下：
with 表达式中 group by 在做 HashAgg 中进行重分布时存在倾斜，对应上图中的 10 和 27 号算子。对 with 表达式中的 hashagg 进行 hint 指定，查询和计划如下：
作了倾斜 hint 的查询计划如下：
从优化后的计划可以看出：对于 HashAgg，由于其重分布存在倾斜，所以优化为双层 Agg。
结语
通过上面各节中的示例，展示了 Gauss DB(DWS)中 plan hint 的使用方法，及其对执行计划的影响。数据库使用者结合自己对数据库对象、数据分布情况及数据量等信息的了解，或者根据 SQL 语句的查询计划分析出其中采用了不正确计划的部分，正确的利用 plan hint，提示优化器采用更高效的计划，可以使查询执行的性能获得大幅的提升，成为性能调优的一件有利的工具。
本文分享自华为云社区《GaussDB(DWS)性能调优系列实现篇六：十八般武艺 Plan hint 运用》，原文作者：wangxiaojuan8 。
版权声明: 本文为 InfoQ 作者【华为云开发者社区】的原创文章。
原文链接:【http://xie.infoq.cn/article/2075c5b8425d7797ed24e1f28】。文章转载请联系作者。
提供全面深入的云计算技术干货 2020.07.14 加入
华为云开发者社区，提供全面深入的云计算前景分析、丰富的技术干货、程序样例，分享华为云前沿资讯动态，方便开发者快速成长与发展，欢迎提问、互动，多方位了解云计算！ 传送门：https://bbs.huaweicloud.com/
评论