用 TPCH 练习性能优化 Q11
一、 SQL及分析
查询SQL语句如下：
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by
ps_partkey
having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.000001
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
)
order by
value desc;
我们把下面的子查询看成为视图V：
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by
ps_partkey
则原主体查询等价于
select
ps_partkey,
value
from V
where value>0.000001*(select sum(value) from V)
这个V已经是经过分组后的结果集，数量较小，遍历V比直接针对partsupp遍历的计算量要小很多。
二、 SPL实现
A |
|
1 |
=now() |
2 |
>name="CHINA" |
3 |
>percent=0.000001 |
4 |
=file("nation.btx").import@b().select@1(N_NAME== name).N_NATIONKEY |
5 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY;S_NATIONKEY==A4).fetch().keys@i(S_SUPPKEY) |
6 |
=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A5.find(PS_SUPPKEY)) |
7 |
=A6.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value) |
8 |
=A7.sum(value)*percent |
9 |
=A7.select(value>A8).sort@z(value) |
10 |
=interval@ms(A1,now()) |
partsupp表是按主键PS_PARTKEY、PS_SUPPKEY有序的，也就是对PS_PARTKEY有序，这时候针对该字段的分组可以使用有序分组方案，以提高计算V的性能。A7用groups@o执行有序分组，相当于计算出视图V，然后A8、A9对A7进行两次遍历计算出结果。
三、 进一步优化
1. 优化方法
本例中要使用Q2中介绍的维表主键序号化方法，supplier中的S_SUPPKEY、partsupp中的PS_SUPPKEY都已在之前的例子中转换过了。
2. 数据转换代码
复制 nation_10.btx、supplier_9.ctx、partsupp_9.ctx分别重命名为nation_11.btx、supplier_11.ctx、partsupp_11.ctx。
3. 数据转换后的计算代码
维表需要预加载，加载代码如下：
A |
|
1 |
>env(nation, file("nation_11.btx").import@b()) |
2 |
>env(supplier, file("supplier_11.ctx").open().import()) |
在查询计算之前，需要先运行预加载代码，将小维表读入内存。
计算代码：
A |
|
1 |
=now() |
2 |
>name="CHINA" |
3 |
>percent=0.000001 |
4 |
=nation.select@1(N_NAME== name).N_NATIONKEY |
5 |
=supplier.@m(S_NATIONKEY==A4) |
6 |
=file("partsupp_11.ctx").open().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A5(PS_SUPPKEY)) |
7 |
=A6.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value) |
8 |
=A7.sum(value)*percent |
9 |
=A7.select(value>A8).sort@z(value) |
10 |
=interval@ms(A1,now()) |
四、 使用企业版列式计算
1. 原始数据
A |
|
1 |
=now() |
2 |
>name="CHINA" |
3 |
>percent=0.000001 |
4 |
=file("nation.btx").import@b().select@1(N_NAME== name).N_NATIONKEY |
5 |
=file("supplier.ctx").open().cursor@mv(S_SUPPKEY;S_NATIONKEY==A4).fetch().keys@i(S_SUPPKEY) |
6 |
=file("partsupp.ctx").open().cursor@mv(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A5.find(PS_SUPPKEY)) |
7 |
=A6.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value) |
8 |
=A7.sum(value)*percent |
9 |
=A7.select@v(value>A8).sort@z(value) |
10 |
=interval@ms(A1,now()) |
2. 优化后数据
维表需要预加载，加载代码如下：
A |
|
1 |
>env(nation, file("nation_11.btx").import@bv()) |
2 |
>env(supplier, file("supplier_11.ctx").open().import@v()) |
在查询计算之前，需要先运行预加载代码，将小维表读入内存。
计算代码：
A |
|
1 |
=now() |
2 |
>name="CHINA" |
3 |
>percent=0.000001 |
4 |
=nation.select@1(N_NAME== name).N_NATIONKEY |
5 |
=supplier.(S_NATIONKEY==A4) |
6 |
=file("partsupp_11.ctx").open().cursor@mv(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A5(PS_SUPPKEY)) |
7 |
=A6.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value) |
8 |
=A7.sum(value)*percent |
9 |
=A7.select@v(value>A8).sort@z(value) |
10 |
=interval@ms(A1,now()) |
五、 测试结果
单位：秒
常规 |
列式 |
|
优化前 |
2.7 |
1.1 |
优化后 |
2.0 |
0.8 |
对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅
嗯，还不太了解好多乾？
英文版