用 TPCH 练习性能优化 Q21

一、 SQL及分析

查询SQL语句如下:

select * from (
    select
        s_name,
        count(*) as numwait
    from
        supplier,lineitem l1,orders,nation
    where
        s_suppkey = l1.l_suppkey
        and o_orderkey = l1.l_orderkey
        and o_orderstatus = 'F'
        and l1.l_receiptdate > l1.l_commitdate
        and exists (
            select
                *
            from
                lineitem l2
            where
                l2.l_orderkey = l1.l_orderkey
                and l2.l_suppkey <> l1.l_suppkey
        )
        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
        )
        and s_nationkey = n_nationkey
        and n_name = 'CHINA'
    group by
        s_name
    order by
        numwait desc,
        s_name
) where rownum<=100;

这主查询比较简单,就是主子表和外键表的连接分组聚合运算,过滤条件比较复杂,有两个exists的子查询。

二、 SPL实现

两个exists子查询都是针对同一个l_orderkey下的lineitem记录的一些运算。

我们知道,lineitem已经按l_orderkey排序,而且子表记录可以看作是主表的集合字段,如果我们将orderslineitem的连接结果集按orderkey做有序分组(但先不聚合),可以得到一个个由相同l_orderkey值的lineitem记录的小集合,再在这个小集合中去计算上述那两个exists条件就会比较简单一些。


A

1

=now()

2

>name="CHINA"

3

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

4

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

5

=file("orders.ctx").open().cursor@m(O_ORDERKEY;O_ORDERSTATUS=="F")

6

=file("lineitem.ctx").open().news(A5,L_ORDERKEY,L_SUPPKEY,L_COMMITDATE,L_RECEIPTDATE)

7

=A6.group(L_ORDERKEY)

8

=A7.conj(if((t=~.select(~.L_RECEIPTDATE>~.L_COMMITDATE)) && (s=t.m(1).L_SUPPKEY)&& !t.select@1(~.L_SUPPKEY!=s) && ~.select@1(~.L_SUPPKEY!=s),t,null))

9

=A8.switch@i(L_SUPPKEY,A4)

10

=A9.groups@u(L_SUPPKEY.S_NAME:s_name;count(1):numwait)

11

=A10.top(100;-numwait,s_name)

12

=interval@ms(A1,now())

A6中对orderslineitem做连接,A7再对其做有序分组,分成由相同l_orderkey值的lineitem记录集合,A8中对这些小集合实现exists判断,过滤掉不满足条件的。后面的代码把其它外键表关联上再做常规分组运算即可。

三、 进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法,lineitem中的L_COMMITDATEL_RECEIPTDATE已在前面例子中转换过了。要使用Q1中介绍的字符串整数化方法,本例要对orders中的O_ORDERSTATUS进行转换。还要使用Q2中介绍的维表主键序号化方法,supplier中的S_SUPPKEYlineitem中的L_SUPPKEY都已在之前的例子中转换过了

2. 数据转换代码

2.1 nationsupplierlineitem转换

复制nation_20.btxsupplier_20.ctxlineitem_20.ctx分别重命名为nation_21.btxsupplier_21.ctxlineitem_21.ctx

2.2 orders转换


A

1

=file("orders.ctx").open().cursor(O_ORDERSTATUS)

2

=A1.id(O_ORDERSTATUS).sort()

3

=file("o_orderstatus.txt").export(A2)

4

=file("orders_18.ctx").open().cursor()

5

=A4.run(O_ORDERSTATUS=A2.pos@b(O_ORDERSTATUS))

6

=file("orders_21.ctx").create(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT)

7

>A6.append(A5)

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

维表和字符串字段取值列表需要预加载,加载代码如下:


A

1

>env(o_orderstatus,file("o_orderstatus.txt").import@si())

2

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

3

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

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

计算代码:


A

1

=now()

2

>name="CHINA"

3

=o_orderstatus.pos@b("F")

4

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

5

=supplier.@m(if(S_NATIONKEY==A4,S_NAME,null))

6

=file("orders_21.ctx").open().cursor@m(O_ORDERKEY;O_ORDERSTATUS==A3)

7

=file("lineitem_21.ctx").open().news(A6,L_ORDERKEY,L_SUPPKEY,L_COMMITDATE,L_RECEIPTDATE)

8

=A7.group(L_ORDERKEY)

9

=A8.conj(if((t=~.select(~.L_RECEIPTDATE>~.L_COMMITDATE)) && (s=t.m(1).L_SUPPKEY)&& !t.select@1(~.L_SUPPKEY!=s) && ~.select@1(~.L_SUPPKEY!=s),t,null) )

10

=A9.select(A5(L_SUPPKEY))

11

=A10.groups@u(L_SUPPKEY:s_name;count(1):numwait).run@m(s_name=A5(s_name))

12

=A11.top(100;-numwait,s_name)

13

=interval@ms(A1,now())

四、 测试结果

单位:秒

并行数

4

8

优化前

49.6

26.3

优化后

45.2

24.6

数据转换并预加载后,性能能提高6%左右。这个运算的复杂度主要在针对分组子集的复杂运算,序号化带来的性能提升占比不大,优化效果不明显。


以下是广告时间

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



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