用 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. 用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. 优化后数据
维表需要预加载，加载代码如下：
A |
|
1 |
>env(nation, file("nation_20.btx").import@bv()) |
2 |
>env(part, file("part_20.ctx").open().import@v()) |
3 |
>env(supplier, file("supplier_20.ctx").open().import@v()) |
在查询计算之前，需要先运行预加载代码，将小维表读入内存。
计算代码：
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.(pos@h(P_NAME,partname)) |
9 |
=supplier.(S_NATIONKEY==A7) |
10 |
=file("partsupp_20.ctx").open().cursor@mv(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@mv(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@mv(PS_AVAILQTY*2>quantity).run@m(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()) |
五、 测试结果
单位：秒
常规 |
列式 |
|
优化前 |
10.6 |
5.9 |
优化后 |
8.8 |
3.9 |
对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅
嗯，还不太了解好多乾？
英文版