性能优化案例课程 TPCH-Q16
select
p_brand,p_type,p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#21'
and p_type not like 'SMALL%'
and p_size in (2, 15, 17, 23, 25, 41, 44, 45)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size;
这是多表关联过滤后的分组聚合运算。
1. 数据存储
partsupp、part、supplier 表没有什么特殊要求，按照主键有序存储。
继续使用题目 Q2 中的 partsupp.ctx、part.ctx、supplier.ctx。
将这些表复制到本题的主目录中。
2. 一般实现
这个查询是将 partsupp 用两个维表 part 和 supplier 进行匹配过滤后再分组，分组时将用维表字段。
可以采用前面题目的技巧，先过滤维表，再对主表做外键属性化，和外键表匹配不上的记录直接过滤掉。在分组时可直接引用维表字段。
A |
B |
|
1 |
=now() |
|
2 |
="Brand#21" |
="SMALL" |
3 |
=[2,15,17,23,25,41,44,45] |
|
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_BRAND,P_TYPE,P_SIZE;P_BRAND!=A2 && !pos@h(P_TYPE,B2) && A3.contain@b(P_SIZE)).fetch().keys@im(P_PARTKEY) |
|
5 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY;!like(S_COMMENT,"*Customer*Complaints*")).fetch().keys@im(S_SUPPKEY) |
|
6 |
=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:A4,PS_SUPPKEY:A5) |
|
7 |
=A6.groups@u(PS_PARTKEY.P_BRAND,PS_PARTKEY.P_TYPE,PS_PARTKEY.P_SIZE;icount(PS_SUPPKEY):supplier_cnt) |
|
8 |
=A7.sort@m(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE) |
|
9 |
=interval@ms(A1,now()) |
|
A4 和 A5 读取并过滤维表，A6 建立游标同时做外键连接和过滤。
注意 A4 中做 in 判断时使用了 contain@b，表示这里将采用二分法。当 in 判断的集合成员较多时，将成员先排序后使用二分法可以减少比较次数，从而提高性能。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
18 |
3. 数据转换
本题要使用维表主键序号化方法，Q2 中的 partsupp_2.ctx、supplier_2.ctx 可以直接使用，复制到本题的主目录。
还要在 Q2 的 part_2.ctx 基础上，把枚举型字符串字段 p_brand 数字化。
A |
|
1 |
=file("part_2.ctx").open().cursor().fetch() |
2 |
=A1.id(P_BRAND).sort() |
3 |
=file("p_brand.btx").export@b(A2) |
4 |
=A1.run(P_PARTKEY=#, P_BRAND=A2.pos@b(P_BRAND)) |
5 |
=file("part_16_3.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()) |
数据转换代码：
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
Brand#21 |
SMALL |
3 |
[2,15,17,23,25,41,44,45] |
|
4 |
=file("p_brand.btx").import@b().(_1) |
=A4.pos@b(A2) |
5 |
=file("p_type.btx").import@b().( !pos@h(_1,B2)) |
|
6 |
=file("part_16_3.ctx").open() |
=A6.cursor@m().skip().(null) |
7 |
=A6.cursor@m(P_PARTKEY,P_BRAND,P_TYPE,P_SIZE;P_BRAND!=B4 && A3.contain@b(P_SIZE) && A5(P_TYPE)).fetch().(B6(P_PARTKEY)=~) |
|
8 |
=file("supplier_2.ctx").open() |
=A8.cursor@m().skip().(false) |
9 |
=A8.cursor@m(S_SUPPKEY;!like(S_COMMENT,"*Customer*Complaints*")).fetch().(B8(S_SUPPKEY)=true) |
|
10 |
=file("partsupp_2.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:B6:#,B8(PS_SUPPKEY)) |
|
11 |
=A4.len()+1 |
|
12 |
=A10.groups@u(PS_PARTKEY.P_TYPE*A11*46+PS_PARTKEY.P_SIZE*A11+PS_PARTKEY.P_BRAND:gk;icount(PS_SUPPKEY):supplier_cnt) |
|
13 |
=A12.new@m(A4(gk%A11):P_BRAND,A5(gk\(A11*46)):P_TYPE,gk%(A11*46)\A11:P_SIZE,supplier_cnt) |
|
14 |
=A13.sort(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE) |
|
15 |
=interval@ms(A1,now()) |
|
A5、B6、B8 中使用了前面介绍的对位序列技巧。A12 中使用了分组键技巧，将三个字段分组转变成按一个字段 gk 分组，A13 中再用 gk 返算出原来的三个分组字段。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
18 |
数据变换 |
14 |
4. 列式计算
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
Brand#21 |
SMALL |
3 |
[2,15,17,23,25,41,44,45] |
|
4 |
=file("p_brand.btx").import@b().(_1) |
=A4.pos@b(A2) |
5 |
=file("p_type.btx").import@b().( !pos@h(_1,B2)) |
|
6 |
=file("part_16_3.ctx").open() |
=A6.cursor@m().skip().(null) |
7 |
=A6.cursor@mv(P_PARTKEY,P_BRAND,P_TYPE,P_SIZE;P_BRAND!=B4 && A3.contain@b(P_SIZE) && A5(P_TYPE)).fetch().(B6(P_PARTKEY)=~) |
|
8 |
=file("supplier_2.ctx").open() |
=A8.cursor@m().skip().(false) |
9 |
=A8.cursor@mv(S_SUPPKEY;!like(S_COMMENT,"*Customer*Complaints*")).fetch().(B8(S_SUPPKEY)=true) |
|
10 |
=file("partsupp_2.ctx").open().cursor@mv(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:B6:#,B8(PS_SUPPKEY)) |
|
11 |
=A4.len()+1 |
|
12 |
=A10.groups@u(PS_PARTKEY.P_TYPE*A11*46+PS_PARTKEY.P_SIZE*A11+PS_PARTKEY.P_BRAND:gk;icount(PS_SUPPKEY):supplier_cnt) |
|
13 |
=A12.new@m(A4(gk%A11):P_BRAND,A5(gk\(A11*46)):P_TYPE,gk%(A11*46)\A11:P_SIZE,supplier_cnt) |
|
14 |
=A13.sort(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE) |
|
15 |
=interval@ms(A1,now()) |
|
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
18 |
数据转换 |
14 |
列式计算 |
5 |
英文版