性能优化案例课程 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 |
英文版