文档首页 > > 开发指南> 优化查询性能> 实际调优案例> 案例:设置cost_param对查询性能优化

案例:设置cost_param对查询性能优化

分享
更新时间: 2019/06/24 GMT+08:00

现象描述1

cost_param的bit0值为1时,表示对于求!=连接的选择率时选择一种改良机制,此方法在自连接(两个相同的表之间连接)的估算中更加准确。下面查询的例子是cost_param的bit0为1时的优化场景。V300R002C00版本开始已弃用cost_param & 1不为0时的路径,默认选择已优化的估算公式。

注:选择率是两表join时,满足join条件的行数在join结果集中所占的比率。

表结构如下所示:

CREATE TABLE LINEITEM
(
L_ORDERKEYBIGINT NOT NULL
, L_PARTKEYBIGINT NOT NULL
, L_SUPPKEYBIGINT NOT NULL
, L_LINENUMBERBIGINT NOT NULL
, L_QUANTITYDECIMAL(15,2) NOT NULL
, L_EXTENDEDPRICEDECIMAL(15,2) NOT NULL
, L_DISCOUNTDECIMAL(15,2) NOT NULL
, L_TAXDECIMAL(15,2) NOT NULL
, L_RETURNFLAGCHAR(1) NOT NULL
, L_LINESTATUSCHAR(1) NOT NULL
, L_SHIPDATEDATE NOT NULL
, L_COMMITDATEDATE NOT NULL
, L_RECEIPTDATE DATE NOT NULL
, L_SHIPINSTRUCT CHAR(25) NOT NULL
, L_SHIPMODECHAR(10) NOT NULL
, L_COMMENTVARCHAR(44) NOT NULL
) with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(L_ORDERKEY);

CREATE TABLE ORDERS
(
O_ORDERKEYBIGINT NOT NULL
, O_CUSTKEYBIGINT NOT NULL
, O_ORDERSTATUSCHAR(1) NOT NULL
, O_TOTALPRICEDECIMAL(15,2) NOT NULL
, O_ORDERDATEDATE NOT NULL
, O_ORDERPRIORITYCHAR(15) NOT NULL
, O_CLERKCHAR(15) NOT NULL
, O_SHIPPRIORITYBIGINT NOT NULL
, O_COMMENTVARCHAR(79) NOT NULL
)with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(O_ORDERKEY);

查询语句如下所示:

explain verbose select
count(*) as numwait 
from
lineitem l1,
orders 
where
o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
order by
numwait desc;

当cost_param的bit0为0时,执行计划如下图所示:

优化分析1

以上查询为lineitem表自连接的Anti Join,当使用cost_param的bit0为0时,估算Anti Join的行数与实际行数相差很大,导致查询性能下降。可以通过设置cost_param的bit0为1时,使Anti Join的行数估算更准确,从而提高查询性能。优化后的执行计划如下:

现象描述2

当cost_param的bit1为1时,表示求多个过滤条件(Filter)的选择率时,选择最小的作为总的选择率,而非两者乘积,此方法在过滤条件的列之间关联性较强时估算更加准确。下面查询的例子是cost_param的bit1为1时的优化场景。

表结构如下所示:

CREATE TABLE NATION
(
N_NATIONKEYINT NOT NULL
, N_NAMECHAR(25) NOT NULL
, N_REGIONKEYINT NOT NULL
, N_COMMENTVARCHAR(152)
) distribute by replication;
CREATE TABLE SUPPLIER
(
S_SUPPKEYBIGINT NOT NULL
, S_NAMECHAR(25) NOT NULL
, S_ADDRESSVARCHAR(40) NOT NULL
, S_NATIONKEYINT NOT NULL
, S_PHONECHAR(15) NOT NULL
, S_ACCTBALDECIMAL(15,2) NOT NULL
, S_COMMENTVARCHAR(101) NOT NULL
) distribute by hash(S_SUPPKEY);
CREATE TABLE PARTSUPP
(
PS_PARTKEYBIGINT NOT NULL
, PS_SUPPKEYBIGINT NOT NULL
, PS_AVAILQTYBIGINT NOT NULL
, PS_SUPPLYCOSTDECIMAL(15,2)NOT NULL
, PS_COMMENTVARCHAR(199) NOT NULL
)distribute by hash(PS_PARTKEY);

查询语句如下所示:

explain verbose select
nation,
sum(amount) as sum_profit 
from
(
select
n_name as nation,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
supplier,
lineitem,
partsupp,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and s_nationkey = n_nationkey
) as profit 
group by nation 
order by nation;

当cost_param的bit1为0时,执行计划如下图所示:

优化分析2

在以上查询中,supplier、lineitem、partsupp三表做hashjoin的条件为(lineitem.l_suppkey = supplier.s_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey),此hashjoin条件中存在两个过滤条件,这前一个过滤条件中的lineitem.l_suppkey和后一个过滤条件中的lineitem.l_partkey同为lineitem表的两列,这两列存在强相关的关联关系。在这种情况,估算hashjoin条件的选择率时,如果使用cost_param的bit1为0时,实际是将AND的两个过滤条件分别计算的2个选择率的值相乘来得到hashjoin条件的选择率,导致行数估算不准确,查询性能较差。所以需要将cost_param的bit1为1时,选择最小的选择率作为总的选择率估算行数比较准确,查询性能较好,优化后的计划如下图所示:

分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区