"一、 SQL 及分析查询SQL语句如下： select s_name,s_address from supplier,nation where s_suppkey in ( select p .."

sjr 北京
乾学院 32 号会员
1 回帖 • 715 浏览 • 1 年前

用 TPCH 练习性能优化 Q20

计算＆AI

一、 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. 用2和3的结果当作外键表去匹配过滤partsupp表

5. 用4的结果当作外键表去与lineitem表连接，并将PS_AVAILQTY字段选出，然后按L_PARTKEY、L_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@0(S_NAME)
16	=interval@ms(A1,now())

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

三、进一步优化

1. 优化方法

本例中要使用Q1中介绍的日期整数化优化方法，lineitem中的L_SHIPDATE已在Q1中转换过了。还要使用Q2中介绍的维表主键序号化方法，part中的P_PARTKEY、supplier中的S_SUPPKEY、partsupp中的PS_PARTKEY和PS_SUPPKEY、lineitem中的L_PARTKEY和L_SUPPKEY都已在之前的例子中转换过了。

2. 数据转换代码

复制nation_11.btx、part_19.ctx、supplier_16.ctx、partsupp_16.ctx、lineitem_19.ctx分别重命名为nation_20.btx、part_20.ctx、supplier_20.ctx、partsupp_20.ctx、lineitem_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@0(S_NAME)
17	=interval@ms(A1,now())

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

四、使用企业版列式计算

1. 原始数据

	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@mv(P_PARTKEY;pos@h(P_NAME,partname)).fetch().keys@im(P_PARTKEY)
8	=file("supplier.ctx").open().cursor@mv(S_SUPPKEY,S_NAME,S_ADDRESS;S_NATIONKEY==A6).fetch().keys@im(S_SUPPKEY)
9	=file("partsupp.ctx").open().cursor@mv(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@mv(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@mv(PS_AVAILQTY*2>quantity).id(L_SUPPKEY)
14	=A8.join@im(S_SUPPKEY,A13:~)
15	=A14.new@m(S_NAME,S_ADDRESS).sort@o(S_NAME)
16	=interval@ms(A1,now())

2. 优化后数据