写点什么

GaussDB(DWS) 性能调优系列实现篇六:十八般武艺 Plan hint 运用

发布于: 2021 年 01 月 14 日

摘要:本文介绍 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 表的统计信息删除。原始语句和生成的初始计划如下。


示例语句:


explain performanceselect a.ca_state state, count(*) cnt  from customer_address a     ,customer c     ,store_sales s    ,date_dim d     ,item i where a.ca_address_sk = c.c_current_addr_sk and c.c_customer_sk = s.ss_customer_sk and s.ss_sold_date_sk = d.d_date_sk and s.ss_item_sk = i.i_item_sk and d.d_month_seq =     (select distinct (d_month_seq)       from date_dim               where d_year = 2000         and d_moy = 2 ) and i.i_current_price > 1.2 *             (select avg(j.i_current_price)      from item j      where j.i_category = i.i_category) group by a.ca_state having count(*) >= 10 order by cnt limit 100;
复制代码


image


Plan hint 的应用


Join 顺序的 hint


语法:


格式 1:


leading(table_list)


仅指定 join 顺序,不指定内外表顺序


格式 2:


leading((table_list))


同时指定 join 顺序和内外表顺序,内外表顺序仅在最外层生效


说明:


table_list 为要调整 join 顺序的表名列表,表之间使用空格分隔。可以包含当前层的任意个表(别名),或对于子查询提升的场景,也可以包含子查询的 hint 别名,同时任意表可以使用括号指定优先级。


注意:


  1. 表只能用单个字符串表示,不能带 schema。

  2. 表如果存在别名,需要优先使用别名来表示该表。

  3. list 中的表在当前层或提升的子查询中必须是唯一的。如果不唯一,需要使用不同的别名进行区分。

  4. 同一个表只能在 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 作为内表,减少中间结果集大小。语句改写如下:


explain performanceselect /*+ leading((s d)) */ a.ca_state state, count(*) cnt from customer_address a     ,customer c     ,store_sales s     ,date_dim d     ,item i where a.ca_address_sk = c.c_current_addr_sk   and c.c_customer_sk = s.ss_customer_sk   and s.ss_sold_date_sk = d.d_date_sk   and s.ss_item_sk = i.i_item_sk   and d.d_month_seq =      (select distinct (d_month_seq)       from date_dim      where d_year = 2000        and d_moy = 2 ) and i.i_current_price > 1.2 *             (select avg(j.i_current_price)              from item j             where j.i_category = i.i_category) group by a.ca_state having count(*) >= 10 order by cnt limit 100;
复制代码


增加了 join 顺序 hint 的查询计划如下:


image


通过调整 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])


说明:


  1. no 表示提示优化器不使用这种方法。

  2. table 表示 hint 指定的表,只能指定一个表,如果表存在别名应优先使用别名进行 hint。

  3. index 表示使用 indexscan 或 indexonlyscan 的 hint 时,指定的索引名称,当前只能指定一个。


示例 2-1:


示例 1 中得到的执行计划,由于 store_sales 表的行数估算不准,store_sales 和 date_dim 采用了效率不好的 nestloop 方式进行 join。现在通过本节的 hint 方法来指示优化器不使用 nestloop 方式进行 join。


explain performanceselect /*+ leading((s d)) no nestloop(s d) */ a.ca_state state, count(*) cnt from customer_address a     ,customer c     ,store_sales s     ,date_dim d     ,item i where a.ca_address_sk = c.c_current_addr_sk   and c.c_customer_sk = s.ss_customer_sk   and s.ss_sold_date_sk = d.d_date_sk   and s.ss_item_sk = i.i_item_sk   and d.d_month_seq =      (select distinct (d_month_seq)       from date_dim      where d_year = 2000        and d_moy = 2 ) and i.i_current_price > 1.2 *             (select avg(j.i_current_price)              from item j             where j.i_category = i.i_category) group by a.ca_state having count(*) >= 10 order by cnt limit 100;
复制代码


增加了 join 方式 hint 后的计划如下:


image


从上面的计划中可以看到,优化器对 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 做索引扫描。


explain performanceselect /*+ leading((s d)) no nestloop(s d) indexscan(i i_item) */ a.ca_state state, count(*) cnt from customer_address a     ,customer c     ,store_sales s     ,date_dim d     ,item i where a.ca_address_sk = c.c_current_addr_sk and c.c_customer_sk = s.ss_customer_sk and s.ss_sold_date_sk = d.d_date_sk and s.ss_item_sk = i.i_item_sk and d.d_month_seq =      (select distinct (d_month_seq)       from date_dim      where d_year = 2000        and d_moy = 2 ) and i.i_current_price > 1.2 *             (select avg(j.i_current_price)              from item j             where j.i_category = i.i_category) group by a.ca_state having count(*) >= 10 order by cnt limit 100;
复制代码


使用 scan 的 hint 指示扫描 item 表时采用 indexscan 后的查询计划如下:


image


从上面的执行结果看,使用索引扫描后(s 和 d join 后,再和 item 的 join 采用了 mergejoin 方式)反而使性能略有下降,所以后面的用例中,我们将不对 item 表采用索引扫描的方法。


Stream 方法的 hint


用于指示优化器采用哪种 stream 方法,可以为 broadcast 和 redistribute。


语法:


[no] broadcast|redistribute(table_list)


说明:


  1. no 表示不使用 hint 的 stream 方式。

  2. table_list 为进行 stream 操作的单表或多表 join 结果集


示例 3:


此处作为演示,修改语句如下,通过 hint 指示优化器对 item 表扫描的结果使用 broadcast 方式进行分布。


explain performanceselect /*+ leading((s d)) no nestloop(s d) broadcast(i) */ a.ca_state state, count(*) cnt from customer_address a     ,customer c     ,store_sales s     ,date_dim d     ,item i Where a.ca_address_sk = c.c_current_addr_sk   and c.c_customer_sk = s.ss_customer_sk   and s.ss_sold_date_sk = d.d_date_sk   and s.ss_item_sk = i.i_item_sk and d.d_month_seq =      (select distinct (d_month_seq)       from date_dim      where d_year = 2000        and d_moy = 2 ) and i.i_current_price > 1.2 *             (select avg(j.i_current_price)               from item j             where j.i_category = i.i_category) group by a.ca_state having count(*) >= 10 order by cnt limit 100;
复制代码


指示优化器使用 broadcast 方式分布 item 结果的查询计划如下:


image


可以看出,之前在 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:


explain performanceselect /*+ rows(s #2880404) */ a.ca_state state, count(*) cnt from customer_address a     ,customer c    ,store_sales s     ,date_dim d     ,item i Where a.ca_address_sk = c.c_current_addr_sk and c.c_customer_sk = s.ss_customer_sk and s.ss_sold_date_sk = d.d_date_sk and s.ss_item_sk = i.i_item_sk and d.d_month_seq =      (select distinct (d_month_seq)       from date_dim      where d_year = 2000         and d_moy = 2 ) and i.i_current_price > 1.2 *             (select avg(j.i_current_price)               from item j              where j.i_category = i.i_category) group by a.ca_state having count(*) >= 10 order by cnt limit 100;
复制代码


具体查询计划如下:


image


指定了 store_sales 表的准确行数后,优化器生成的计划执行时间直接从最初的 34268.322ms 将为 1991.843ms,提升了 17 倍。这也充分的说明了优化器对统计信息准确性的强烈依赖。


除了可以指明单表的行数,还可指明中间结果集的行数。比如上例中 8 号算子的实际行数和估计行数也相差较大,我们指明 8 号算子的结果集行数看看效果。在下面这个例子中,还使用了子链接块名的 hint,为子链接指定了一个别名,便于在行数 hint 中指定子链接。


explain performanceselect /*+ rows(s #2880404) rows(s i tt c a d #2512) */ a.ca_state state, count(*) cnt from customer_address a     ,customer c     ,store_sales s     ,date_dim d     ,item i where       a.ca_address_sk = c.c_current_addr_sk and c.c_customer_sk = s.ss_customer_sk and s.ss_sold_date_sk = d.d_date_sk and s.ss_item_sk = i.i_item_sk and d.d_month_seq =      (select distinct (d_month_seq)       from date_dim               where d_year = 2000         and d_moy = 2 ) and i.i_current_price > 1.2 *             (select /*+ blockname (tt)*/ avg(j.i_current_price)      from item j      where j.i_category = i.i_category) group by a.ca_state having count(*) >= 10 order by cnt limit 100;
复制代码


查询计划如下:


image


8 号算子的估计行数已经和实际行数一致。由于 8 号算子不是计划的瓶颈点,所以性能提升并不明显。


倾斜值 hint


用于指明查询运行时重分布过程中存在倾斜的重分布键和倾斜值,针对 Join 和 HashAgg 运算中的重分布进行优化。


语法:


指定单表倾斜


skew(table (column) [(value)])


指定中间结果倾斜


skew((join_rel) (column) [(values)])


说明:


  1. table 表示存在倾斜的单个表名。

  2. join_rel 表示参与 join 的两个或多个表,如(t1 t2)表示 t1 和 t2 join 后的结果存在倾斜。

  3. column 表示倾斜表中存在倾斜的一个或多个列。

  4. value 表示倾斜的列中存在倾斜的一个或多个值。


示例 5:


本节,我们用 tpcds 中的 Q1 作为示例,未使用 hint 前的查询及计划如下:


explain performancewith customer_total_return as    (select sr_customer_sk as ctr_customer_sk          ,sr_store_sk as ctr_store_sk          ,sum(SR_FEE) as ctr_total_return     from store_returns         ,date_dim     where sr_returned_date_sk = d_date_sk       and d_year =2000     group by sr_customer_sk             ,sr_store_sk)select  c_customer_id  from customer_total_return ctr1      ,store      ,customerwhere ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2                                 from customer_total_return ctr2                                where ctr1.ctr_store_sk = ctr2.ctr_store_sk)  and s_store_sk = ctr1.ctr_store_sk  and s_state = 'NM'  and ctr1.ctr_customer_sk = c_customer_skorder by c_customer_idlimit 100;
复制代码


image


with 表达式中 group by 在做 HashAgg 中进行重分布时存在倾斜,对应上图中的 10 和 27 号算子。对 with 表达式中的 hashagg 进行 hint 指定,查询和计划如下:


explain performancewith customer_total_return as    (select /*+ skew(store_returns(sr_store_sk sr_customer_sk)) */sr_customer_sk as ctr_customer_sk          ,sr_store_sk as ctr_store_sk          ,sum(SR_FEE) as ctr_total_return     from store_returns         ,date_dim     where sr_returned_date_sk = d_date_sk       and d_year =2000     group by sr_customer_sk             ,sr_store_sk)select  c_customer_id  from customer_total_return ctr1      ,store      ,customerwhere ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2                                 from customer_total_return ctr2                                where ctr1.ctr_store_sk = ctr2.ctr_store_sk)  and s_store_sk = ctr1.ctr_store_sk  and s_state = 'NM'  and ctr1.ctr_customer_sk = c_customer_skorder by c_customer_idlimit 100;
复制代码


作了倾斜 hint 的查询计划如下:


image


从优化后的计划可以看出:对于 HashAgg,由于其重分布存在倾斜,所以优化为双层 Agg。

结语


通过上面各节中的示例,展示了 Gauss DB(DWS)中 plan hint 的使用方法,及其对执行计划的影响。数据库使用者结合自己对数据库对象、数据分布情况及数据量等信息的了解,或者根据 SQL 语句的查询计划分析出其中采用了不正确计划的部分,正确的利用 plan hint,提示优化器采用更高效的计划,可以使查询执行的性能获得大幅的提升,成为性能调优的一件有利的工具。


本文分享自华为云社区《GaussDB(DWS)性能调优系列实现篇六:十八般武艺 Plan hint 运用》,原文作者:wangxiaojuan8 。


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


发布于: 2021 年 01 月 14 日阅读数: 17
用户头像

提供全面深入的云计算技术干货 2020.07.14 加入

华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态,方便开发者快速成长与发展,欢迎提问、互动,多方位了解云计算! 传送门:https://bbs.huaweicloud.com/

评论

发布
暂无评论
GaussDB(DWS)性能调优系列实现篇六:十八般武艺Plan hint运用