用 TPCH 练习性能优化 Q20

一、 SQL及分析

查询SQL语句如下:

select
    s_name,s_address
from
    supplier,nation
where
    s_suppkey in (
        select
            ps_suppkey
        from
            partsupp
        where
            ps_partkey in (
                select
                    p_partkey
                from
                    part
                where
                    p_name like 'bisque%'
            )
            and ps_availqty > (
                select
                    0.5 * sum(l_quantity)
                from
                    lineitem
                where
                    l_partkey = ps_partkey
                    and l_suppkey = ps_suppkey
                    and l_shipdate >= date '1995-01-01'
                    and l_shipdate < date '1995-01-01' + interval '1' year
            )
    )
    and s_nationkey = n_nationkey
    and n_name = 'CHINA'
order by
    s_name;

这个主查询很简单,但过滤条件很复杂,嵌套了好几层。

二、 SPL实现

这个查询看起来很复杂,如果把它分成几步来看,情况就比较清楚:

1. 按条件过滤nation

2. 按条件过滤part

3. 1的结果当作外键表去匹配过滤supplier

4. 23的结果当作外键表去匹配过滤partsupp

5. 4的结果当作外键表去与lineitem表连接,并将PS_AVAILQTY字段选出,然后按L_PARTKEYL_SUPPKEY分组聚合,前面说过,用等值条件与主表关联的子查询都可以改写成分组后再与主表连接的语句。分组聚合后选出满足条件的L_SUPPKEY

6. 再用5的结果作为外键表去过滤supplier

整个过程就是不断地生成中间外键表去做匹配过滤。


A

1

=now()

2

1995-1-1

3

=elapse@y(A2,1)

4

>partname="bisque"

5

>nationname="CHINA"

6

=file("nation.btx").import@b().select@1(N_NAME==nationname).N_NATIONKEY

7

=file("part.ctx").open().cursor@m(P_PARTKEY;pos@h(P_NAME,partname)).fetch().keys@im(P_PARTKEY)

8

=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS;S_NATIONKEY==A6).fetch().keys@im(S_SUPPKEY)

9

=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY;A7.find(PS_PARTKEY),A8.find(PS_SUPPKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)

10

=file("lineitem.ctx").open().cursor@m(L_PARTKEY,L_SUPPKEY,L_QUANTITY;L_SHIPDATE>=A2 && L_SHIPDATE<A3)

11

=A10.join@i(L_PARTKEY:L_SUPPKEY,A9:PS_PARTKEY:PS_SUPPKEY,PS_AVAILQTY)

12

=A11.groups@u(L_PARTKEY,L_SUPPKEY;PS_AVAILQTY,sum(L_QUANTITY):quantity)

13

=A12.select(PS_AVAILQTY*2>quantity).id(L_SUPPKEY)

14

=A8.join@im(S_SUPPKEY,A13:~)

15

=A14.new(S_NAME,S_ADDRESS).sort@o(S_NAME)

16

=interval@ms(A1,now())

A14中使用join@im,选项@i表示删除关联不上的记录,选项@m表示S_SUPPKEYA13都有序时,用有序归并连接加快速度。

三、 进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法,lineitem中的L_SHIPDATE已在Q1中转换过了。还要使用Q2中介绍的维表主键序号化方法,part中的P_PARTKEYsupplier中的S_SUPPKEYpartsupp中的PS_PARTKEYPS_SUPPKEYlineitem中的L_PARTKEYL_SUPPKEY都已在之前的例子中转换过了

2. 数据转换代码

复制nation_11.btxpart_19.ctxsupplier_16.ctxpartsupp_16.ctxlineitem_19.ctx分别重命名为nation_20.btxpart_20.ctxsupplier_20.ctxpartsupp_20.ctxlineitem_20.ctx

3. 数据转换后的计算代码

维表需要预加载,加载代码如下:


A

1

>env(nation, file("nation_20.btx").import@b())

2

>env(part, file("part_20.ctx").open().import())

3

>env(supplier, file("supplier_20.ctx").open().import())

在查询计算之前,需要先运行预加载代码,将小维表读入内存。

计算代码:


A

1

=now()

2

1995-1-1

3

=days@o(elapse@y(A2,1))

4

=days@o(A2)

5

>partname="bisque"

6

>nationname="CHINA"

7

=nation.select@1(N_NAME==nationname).N_NATIONKEY

8

=part.@m(pos@h(P_NAME,partname))

9

=supplier.@m(S_NATIONKEY==A7)

10

=file("partsupp_20.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_AVAILQTY;A8(PS_PARTKEY) && A9(PS_SUPPKEY)).fetch().keys@im(PS_PARTKEY,PS_SUPPKEY)

11

=file("lineitem_20.ctx").open().cursor@m(L_PARTKEY,L_SUPPKEY,L_QUANTITY;L_SHIPDATE>=A4 && L_SHIPDATE<A3)

12

=A11.join@i(L_PARTKEY:L_SUPPKEY,A10:PS_PARTKEY:PS_SUPPKEY,PS_AVAILQTY)

13

=A12.groups@u(L_PARTKEY,L_SUPPKEY;PS_AVAILQTY,sum(L_QUANTITY):quantity)

14

=A13.select(PS_AVAILQTY*2>quantity).run(A9(L_SUPPKEY)=null)

15

=supplier(A9.pselect@a(~==null))

16

=A15.new(S_NAME,S_ADDRESS).sort@o(S_NAME)

17

=interval@ms(A1,now())

A14中先选出满足条件的记录,然后把每条记录的L_SUPPKEY所对应A9的成员值设为nullA15中先计算A9所有null值成员的行号,然后用这些行号从supplier选出对应的记录。

四、 测试结果

单位:秒

并行数

4

8

优化前

28.5

15.2

优化后

26.2

13.9

数据转换并预加载后,性能提高效果不明显,因为lineitem过滤后不足4万条记录,数据量太少,优化算法的优势难以体现。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾