性能优化案例课程 TPCH-Q19
select
sum(l_extendedprice * (1 - l_discount)) as revenue
from
lineitem,part
where
(
p_partkey = l_partkey
and p_brand = 'Brand#32'
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
and l_quantity >= 7 and l_quantity <= 7 + 10
and p_size between 1 and 5
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#23'
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
and l_quantity >= 18 and l_quantity <= 18 + 10
and p_size between 1 and 10
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
)
or
(
p_partkey = l_partkey
and p_brand = 'Brand#45'
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
and l_quantity >= 22 and l_quantity <= 22 + 10
and p_size between 1 and 15
and l_shipmode in ('AIR', 'AIR REG')
and l_shipinstruct = 'DELIVER IN PERSON'
);
这是两表连接的过滤查询汇总，结构上比较简单，但是过滤条件比较复杂。
1. 数据存储
lineitem 和 part 没有特殊的有序要求，按照主键有序存储。
继续使用题目 Q3 中的 lineitem.ctx 和 Q2 的 part.ctx，复制到本题的主目录中。
2. 一般实现
条件表达式中有不少项只与较小的维表 part 相关，而且这些项的计算复杂度较高（有 in 运算），如果先针对 part 表把这些项计算出来，则计算量将比针对两表连接结果集再计算要少得多，因为连接结果集是和 lineitem 表规模相同，要大得多。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
>brand1="Brand#32" |
>quantity1=7 |
3 |
>brand2="Brand#23" |
>quantity2=18 |
4 |
>brand3="Brand#45" |
>quantity3=22 |
5 |
=["SM CASE", "SM BOX", "SM PACK", "SM PKG"] |
|
6 |
=["MED BAG", "MED BOX", "MED PKG", "MED PACK"] |
|
7 |
=["LG CASE", "LG BOX", "LG PACK", "LG PKG"] |
|
8 |
=[A5,A6,A7].conj().sort() |
=["AIR","AIR REG"] |
9 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_BRAND,P_SIZE,P_CONTAINER,0:FLAG;P_SIZE>=1 && P_SIZE<=15 && [brand1,brand2,brand3].contain(P_BRAND) && A8.contain(P_CONTAINER)) |
|
10 |
=A9.run(FLAG=if(P_BRAND==brand1 && A5.contain(P_CONTAINER) && P_SIZE<=5:1, P_BRAND==brand2 && A6.contain(P_CONTAINER) && P_SIZE<=10:2, P_BRAND==brand3 && A7.contain(P_CONTAINER) && P_SIZE<=15:3; 0)).select(FLAG>0).fetch().derive@o().keys@im(P_PARTKEY) |
|
11 |
=file("lineitem.ctx").open().cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;L_PARTKEY:A10,L_SHIPINSTRUCT=="DELIVER IN PERSON" && B8.contain(L_SHIPMODE) && L_QUANTITY>= quantity1 && L_QUANTITY<=quantity3+10) |
|
12 |
=A11.groups(;sum(if(case(L_PARTKEY.FLAG,1:L_QUANTITY<=quantity1+10,2:L_QUANTITY>=quantity2 && L_QUANTITY<=quantity2+10;L_QUANTITY>=quantity3),L_EXTENDEDPRICE*(1-L_DISCOUNT),0))) |
|
13 |
=interval@ms(A1,now()) |
|
A9 将后续的条件合并，写到 part 的游标前过滤条件中，减少数据读取量。
A10 在 part 表中新产生一个 FLAG 字段，用于将 part 相关的复杂条件项先计算出来。当记录满足 SQL 中的 or 条件 1、2、3 时分别赋值为 1、2、3，都不满足则赋值 0，然后用 FLAG 值是否大于 0 来过滤。
在 A12 中再根据 FLAG 的值来选择用哪个 quantity 值对 L_QUANTITY 进行过滤。FLAG 相关的复杂计算式的计算次数，和 part 表的行数相同。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
5 |
3. 数据转换
本题要使用前面题目介绍的维表主键序号化方法，可以使用 Q8 中的 lineitem_4.ctx，复制到本题主目录中。
还要使用枚举型字符串数字化方法，part 中的 P_BRAND 和 P_CONTAINER 已分别在 Q16、Q17 中转换过了，复制到本题主目录。
还要在 lineitem_4 基础上转换 L_SHIPMODE、L_SHIPINSTRUCT 两个枚举型字符串字段。
转换代码如下：
A |
|
1 |
=file("lineitem_4.ctx").open().cursor(L_SHIPINSTRUCT,L_SHIPMODE) |
2 |
=A1.id(L_SHIPINSTRUCT,L_SHIPMODE) |
3 |
=A2.(~.sort()) |
4 |
=file("l_shipinstruct.btx").export@b(A3(1)) |
5 |
=file("lineitem_4.ctx").open().cursor() |
6 |
=A5.run(L_SHIPINSTRUCT=A3(1).pos@b(L_SHIPINSTRUCT),L_SHIPMODE=A3(2).pos@b(L_SHIPMODE)) |
7 |
=file("lineitem_19_3.ctx").create@py(#L_ORDERKEY,#L_LINENUMBER,L_PARTKEY, L_SUPPKEY, L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE, L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT) |
8 |
>A7.append(A6) |
转换后的计算代码：
A |
B |
|
1 |
=now() |
|
2 |
=file("p_brand.btx").import@b().(_1) |
|
3 |
=file("p_container.btx").import@b().(_1) |
|
4 |
=file("l_shipinstruct.btx").import@b().(_1) |
|
5 |
=file("l_shipmode.btx").import@b().(_1) |
|
6 |
>brand1=A2.pos@b("Brand#32") |
>quantity1=7 |
7 |
>brand2=A2.pos@b("Brand#23") |
>quantity2=18 |
8 |
>brand3=A2.pos@b("Brand#45") |
>quantity3=22 |
9 |
=A3.(["SM CASE", "SM BOX", "SM PACK", "SM PKG"].contain(~)) |
|
10 |
=A3.(["MED BAG", "MED BOX", "MED PKG", "MED PACK"].contain(~)) |
|
11 |
=A3.(["LG CASE", "LG BOX", "LG PACK", "LG PKG"].contain(~)) |
|
12 |
=A9.(~||A10(#)||A11(#)) |
|
13 |
=A5.(["AIR","AIR REG"].contain(~)) |
=A4.pos@b("DELIVER IN PERSON") |
14 |
=file("part_17_3.ctx").open() |
=A14.cursor@m().skip().(null) |
15 |
=A14.cursor@m(P_PARTKEY,P_SIZE,P_BRAND,P_CONTAINER;P_SIZE>=1 && P_SIZE<=15 && [brand1,brand2,brand3].contain(P_BRAND) && A12(P_CONTAINER)) |
|
16 |
=A15.fetch().(B14(P_PARTKEY)=if(P_BRAND==brand1 && A9(P_CONTAINER) && P_SIZE>=1 && P_SIZE<=5:1, P_BRAND==brand2 && A10(P_CONTAINER) && P_SIZE<=10:2, P_BRAND==brand3 && A11(P_CONTAINER) && P_SIZE<=15:3; null)) |
|
17 |
=file("lineitem_19_3.ctx").open().cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;L_PARTKEY:B14:#,L_SHIPINSTRUCT==B13 && A13(L_SHIPMODE) && L_QUANTITY>= quantity1 && L_QUANTITY<=quantity3+10) |
|
18 |
=A17.groups(;sum(if(case(L_PARTKEY,1:L_QUANTITY<=quantity1+10,2:L_QUANTITY>=quantity2 && L_QUANTITY<=quantity2+10,3:L_QUANTITY>=quantity3),L_EXTENDEDPRICE*(1-L_DISCOUNT),0))) |
|
19 |
=interval@ms(A1,now()) |
|
A9、A10、A11、A13、B14 都是对位序列。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
5 |
数据转换 |
4 |
4. 列式计算
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
=file("p_brand.btx").import@b().(_1) |
|
3 |
=file("p_container.btx").import@b().(_1) |
|
4 |
=file("l_shipinstruct.btx").import@b().(_1) |
|
5 |
=file("l_shipmode.btx").import@b().(_1) |
|
6 |
>brand1=A2.pos@b("Brand#32") |
>quantity1=7 |
7 |
>brand2=A2.pos@b("Brand#23") |
>quantity2=18 |
8 |
>brand3=A2.pos@b("Brand#45") |
>quantity3=22 |
9 |
=A3.(["SM CASE", "SM BOX", "SM PACK", "SM PKG"].contain(~)) |
|
10 |
=A3.(["MED BAG", "MED BOX", "MED PKG", "MED PACK"].contain(~)) |
|
11 |
=A3.(["LG CASE", "LG BOX", "LG PACK", "LG PKG"].contain(~)) |
|
12 |
=A9.(~||A10(#)||A11(#)) |
|
13 |
=A5.(["AIR","AIR REG"].contain(~)) |
=A4.pos@b("DELIVER IN PERSON") |
14 |
=file("part_17_3.ctx").open() |
=A14.cursor@m().skip().(null) |
15 |
=A14.cursor@m(P_PARTKEY,P_SIZE,P_BRAND,P_CONTAINER;P_SIZE>=1 && P_SIZE<=15 && [brand1,brand2,brand3].contain(P_BRAND) && A12(P_CONTAINER)) |
|
16 |
=A15.fetch().(B14(P_PARTKEY)=if(P_BRAND==brand1 && A9(P_CONTAINER) && P_SIZE<=5:1, P_BRAND==brand2 && A10(P_CONTAINER) && P_SIZE<=10:2, P_BRAND==brand3 && A11(P_CONTAINER):3; null)) |
|
17 |
=file("lineitem_19_3.ctx").open().cursor@mv(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT;B14(L_PARTKEY),L_SHIPINSTRUCT==B13 && A13(L_SHIPMODE) && L_QUANTITY>= quantity1 && L_QUANTITY<=quantity3+10) |
|
18 |
=A17.groups(;sum(if(case(B14(L_PARTKEY),1:L_QUANTITY<=quantity1+10,2:L_QUANTITY>=quantity2 && L_QUANTITY<=quantity2+10,3:L_QUANTITY>=quantity3),L_EXTENDEDPRICE*(1-L_DISCOUNT),0))) |
|
19 |
=interval@ms(A1,now()) |
|
列式计算不能使用 switch 给字段赋值，A17 用 B14(L_PARTKEY) 代替了 L_PARTKEY:B14:#，只实现过滤，没有赋值。A18 还要用 B14(L_PARTKEY) 再计算一次。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
5 |
数据转换 |
4 |
列式计算 |
2 |
英文版