性能优化案例课程 TPCH-Q11
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 遍历的计算量要小很多。
1. 数据存储
Partsupp 表按照主键 ps_partkey 有序存储，可以采用有序分组方法，提高分组汇总计算性能。
其他数据表没有特殊的排序要求，也都按照主键有序存储。
继续使用题目 Q2 中的 partsupp.ctx、supplier.ctx、nation.btx。
将这些表复制到本题的主目录中。
2. 一般实现
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 有序，这时候针对该字段的分组可以使用有序分组方案，以提高计算 V 的性能。A7 用 groups@o 执行有序分组，相当于计算出视图 V，然后 A8、A9 对 A7 进行两次遍历计算出结果。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
7 |
3. 数据变换
数据变换要采用前面题目提到的维表主键序号化手段。
可以直接使用以前的题目 Q2 转换的 partsupp_2.ctx、supplier_2.ctx、nation_2.btx、。
将这些表复制到本题的主目录中。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
>name="CHINA" |
>percent=0.000001 |
3 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME) |
|
4 |
=A3.(N_NAME== name) |
|
5 |
=file("supplier_2.ctx").open() |
=A5.cursor@m().skip().(false) |
6 |
=file("supplier_2.ctx").open().cursor@m(S_SUPPKEY;A4(S_NATIONKEY)).fetch().(B5(S_SUPPKEY)=true) |
|
7 |
=file("partsupp_2.ctx").open().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;B5(PS_SUPPKEY)) |
|
8 |
=A7.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value) |
|
9 |
=A8.sum(value)*percent |
|
10 |
=A8.select(value>A9).sort@z(value) |
|
11 |
=interval@ms(A1,now()) |
|
A4、B5 是对位序列。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
7 |
数据变换 |
4 |
4. 列式计算
A |
B |
|
1 |
=now() |
|
2 |
>name="CHINA" |
>percent=0.000001 |
3 |
=file("nation_2.btx").import@b(N_NATIONKEY,N_NAME) |
|
4 |
=A3.(N_NAME== name) |
|
5 |
=file("supplier_2.ctx").open() |
=A5.cursor@m().skip().(false) |
6 |
=file("supplier_2.ctx").open().cursor@mv(S_SUPPKEY;A4(S_NATIONKEY)).fetch().(B5(S_SUPPKEY)=true) |
|
7 |
=file("partsupp_2.ctx").open().cursor@mv(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;B5(PS_SUPPKEY)) |
|
8 |
=A7.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST*PS_AVAILQTY):value) |
|
9 |
=A8.sum(value)*percent |
|
10 |
=A8.select(value>A9).sort@z(value) |
|
11 |
=interval@ms(A1,now()) |
|
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
7 |
数据变换 |
4 |
列式计算 |
2 |
英文版