用 TPCH 练习性能优化 Q17
一、 SQL及分析
查询SQL语句如下：
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,part
where
p_partkey = l_partkey
and p_brand = 'Brand#33'
and p_container = 'LG DRUM'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
);
这种在子查询中用等值条件与主表关联的情况，都可以转换成JOIN来计算，相当于如下SQL：
select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,part,
(select
l_partkey lp,
0.2*avg(l_quantity) lq
from
lineitem
group by
l_partkey
) lpq
where
p_partkey = l_partkey
and p_brand = 'Brand#33'
and p_container = 'LG DRUM'
and l_partkey = lp
and l_quantity < lq;
子查询是分组聚合运算，主查询就是多表关联后的聚合运算。
二、 SPL实现
A |
|
1 |
=now() |
2 |
>brand="Brand#33" |
3 |
>container="LG DRUM" |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY;P_BRAND==brand && P_CONTAINER==container).fetch().keys@im(P_PARTKEY) |
5 |
=file("lineitem.ctx").open() |
6 |
=A5.cursor@m(L_PARTKEY,L_QUANTITY;A4.find(L_PARTKEY)) |
7 |
=A6.groups@u(L_PARTKEY;avg(L_QUANTITY):avg).run(avg/=5).keys@im(L_PARTKEY) |
8 |
=A5.cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A7) |
9 |
=A8.total(sum(if(L_QUANTITY<L_PARTKEY.avg,L_EXTENDEDPRICE,0)))/7.0 |
10 |
=interval@ms(A1,now()) |
三、 进一步优化
1. 优化方法
本例中要使用Q1中介绍的字符串整数化方法，part中的P_BRAND已在Q16中转化，本例还需转化P_CONTAINER。还要使用Q2中介绍的维表主键序号化方法，part中的P_PARTKEY已在Q16中转化，lineitem中的L_PARTKEY已在之前的例子中转换过了。
2. 数据转换代码
2.1 lineitem转换
复制lineitem_15.ctx重命名为lineitem_17.ctx。
2.2 part转换
A |
|
1 |
=file("part_16.ctx").open().cursor().fetch() |
2 |
=A1.id(P_CONTAINER).sort() |
3 |
=file("p_container.txt").export(A2) |
4 |
=A1.run(P_CONTAINER=A2.pos@b(P_ CONTAINER)) |
5 |
=file("part_17.ctx").create(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE, P_COMMENT) |
6 |
>A5.append(A4.cursor()) |
3. 数据转换后的计算代码
维表和字符串字段取值列表需要预加载，加载代码如下：
A |
|
1 |
>env(p_brand,file("p_brand.txt").import@si()) |
2 |
>env(p_container,file("p_container.txt").import@si()) |
3 |
>env(part, file("part_17.ctx").open().import()) |
在查询计算之前，需要先运行预加载代码，将小维表读入内存。
计算代码：
A |
|
1 |
=now() |
2 |
>brand=p_brand.pos@b("Brand#33") |
3 |
>container=p_container.pos@b("LG DRUM") |
4 |
=part.@m(P_BRAND==brand && P_CONTAINER==container) |
5 |
=file("lineitem_17.ctx").open() |
6 |
=A5.cursor@m(L_PARTKEY,L_QUANTITY;A4(L_PARTKEY)) |
7 |
=A6.groups@u(L_PARTKEY;avg(L_QUANTITY):avg).run(A4(L_PARTKEY)=avg*0.2) |
8 |
=A5.cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A4:#) |
9 |
=A8.total(sum(if(L_QUANTITY<L_PARTKEY,L_EXTENDEDPRICE,0)))/7.0 |
10 |
=interval@ms(A1,now()) |
四、 使用企业版列式计算
1. 原始数据
A |
|
1 |
=now() |
2 |
>brand="Brand#33" |
3 |
>container="LG DRUM" |
4 |
=file("part.ctx").open().cursor@mv(P_PARTKEY;P_BRAND==brand && P_CONTAINER==container).fetch().keys@im(P_PARTKEY) |
5 |
=file("lineitem.ctx").open() |
6 |
=A5.cursor@mv(L_PARTKEY,L_QUANTITY;A4.find(L_PARTKEY)) |
7 |
=A6.groups@u(L_PARTKEY;avg(L_QUANTITY)*0.2:avg).keys@im(L_PARTKEY) |
8 |
=A5.cursor@mv(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A7) |
9 |
=A8.total(sum(if(L_QUANTITY<L_PARTKEY.avg,L_EXTENDEDPRICE,0)))/7.0 |
10 |
=interval@ms(A1,now()) |
2. 优化后数据
维表和字符串字段取值列表需要预加载，加载代码如下：
A |
|
1 |
>env(p_brand,file("p_brand.txt").import@si()) |
2 |
>env(p_container,file("p_container.txt").import@si()) |
3 |
>env(part, file("part_17.ctx").open().import@v()) |
在查询计算之前，需要先运行预加载代码，将小维表读入内存。
计算代码：
A |
|
1 |
=now() |
2 |
>brand=p_brand.pos@b("Brand#33") |
3 |
>container=p_container.pos@b("LG DRUM") |
4 |
=part.@m(P_BRAND==brand && P_CONTAINER==container) |
5 |
=file("lineitem_17.ctx").open() |
6 |
=A5.cursor@mv(L_PARTKEY,L_QUANTITY;A4(L_PARTKEY)) |
7 |
=A6.groups@u(L_PARTKEY;avg(L_QUANTITY):avg) |
8 |
clear A4 |
9 |
=part.(null) |
10 |
=A7.run@m(A9(L_PARTKEY)=avg*0.2) |
11 |
=A5.cursor@mv(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A9:#) |
12 |
=A11.total(sum(if(L_QUANTITY<L_PARTKEY,L_EXTENDEDPRICE,0)))/7.0 |
13 |
=interval@ms(A1,now()) |
五、 测试结果
单位：秒
常规 |
列式 |
|
优化前 |
15.4 |
7.7 |
优化后 |
14.8 |
6.8 |
本例lineitem过滤后数据记录只有60多万条，数据量太少，序号化的效果体现不出来，优化效果不明显。如果改变A4中的过滤条件，将&&改成||，lineitem过滤后有3800多万条数据，此时再测试，优化效果就非常明显。
英文版