性能优化案例课程 TPCH-Q14
select
100.00 * sum(
case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount)
else 0 end)
/ sum(l_extendedprice * (1 - l_discount) ) as promo_revenue
from
lineitem,
part
where
l_partkey = p_partkey
and l_shipdate >= date '1995-04-01'
and l_shipdate < date '1995-04-01' + interval '1' month;
这是两表关联过滤后的聚合运算。
1. 数据存储
lineitem 和 part 表没有什么特殊要求，按照主键有序存储。
继续使用题目 Q3 中的 lineitem.ctx、part.ctx。
将这些表复制到本题的主目录中。
2. 一般实现
这是一个常规的连接后求和计算，充分利用多线程并行即可。
A |
|
1 |
=now() |
2 |
1995-4-1 |
3 |
=elapse@m(A2,1) |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_TYPE).fetch().keys@i(P_PARTKEY) |
5 |
=file("lineitem.ctx").open().cursor@m(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A2 &&L_SHIPDATE<A3,L_PARTKEY:A4) |
6 |
=A5.run(L_EXTENDEDPRICE*=(1-L_DISCOUNT),L_DISCOUNT=if(pos@h(L_PARTKEY.P_TYPE,"PROMO"),L_EXTENDEDPRICE,0)) |
7 |
=A6.total(sum(L_DISCOUNT),sum(L_EXTENDEDPRICE)) |
8 |
=100.00*A7(1)/A7(2) |
9 |
=interval@ms(A1,now()) |
代码中用到了前面讲到过的维表属性化、多线程并行等优化方法。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
103 |
part 表数据条数比较多，加上 p_type 是较长的字符串字段，全部读入时占用内存较大。part 读入内存和 lineitem 表连接后，进行字符串比较计算的次数和 lineitem 记录数大致相同。而 lineitem 记录数非常多，所以性能较差。
3. 维表计算提前
上一节，字符串比较计算是在两表连接后进行的。实际上，这是针对维表字段的计算，可以提前完成，写成 SQL 大致是这样的：
select
100.00 * sum(
case when p_type then l_extendedprice * (1 - l_discount)
else 0 end)
/ sum(l_extendedprice * (1 - l_discount) ) as promo_revenue
from
lineitem,
(select
p_partkey,
(case when p_type like 'PROMO%' then true else false end) as p_type
from
part
) p
where
l_partkey = p_partkey
and l_shipdate >= date '1995-04-01'
and l_shipdate < date '1995-04-01' + interval '1' month;
计算代码：
A |
|
1 |
=now() |
2 |
1995-4-1 |
3 |
=elapse@m(A2,1) |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_TYPE).run(P_TYPE=pos@h(P_TYPE,"PROMO")).fetch().keys@i(P_PARTKEY) |
5 |
=file("lineitem.ctx").open().cursor@m(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=A2 &&L_SHIPDATE<A3,L_PARTKEY:A4) |
6 |
=A5.run(L_EXTENDEDPRICE*=(1-L_DISCOUNT),L_DISCOUNT=if(L_PARTKEY.P_TYPE,L_EXTENDEDPRICE,0)) |
7 |
=A6.total(sum(L_DISCOUNT),sum(L_EXTENDEDPRICE)) |
8 |
=100.00*A7(1)/A7(2) |
9 |
=interval@ms(A1,now()) |
A4 中进行字符串比较计算，计算次数和 part 表记录数相同，比 lineitem 表记录数小很多。最终读入内存的是 1 或者 null，占用内存也比字符串小很多。
A5 不必再做字符串比较计算了。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
103 |
维表计算提前 |
8 |
4. 数据转换
本题中要使用前面介绍的日期整数化优化和维表主键序号化方法。
可以直接使用以前的题目 Q2 中的 part_2.ctx、p_type.btx。
将这些表复制到本题的主目录中。
Q8 转换的 lineitem_4.ctx 实现了 l_partkey 的序号化，但没有完成日期 l_shipdate 的整数化，这里还要转换一下。
转换代码：
A |
|
1 |
=file("lineitem_4.ctx").open().cursor().run(L_SHIPDATE=days@o(L_SHIPDATE)) |
2 |
=file("lineitem_14_4.ctx").create(#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) |
3 |
>A2.append(A1) |
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-4-1 |
PROMO |
3 |
=days@o(elapse@m(A2,1)) |
=days@o(A2) |
4 |
=p_type=file("p_type.btx").import@b().(pos@h(_1,B2)) |
|
5 |
=file("part_2.ctx").open() |
=A5.cursor@m().skip().(false) |
6 |
=A5.cursor@m(P_PARTKEY;A4(P_TYPE)).fetch().(B5(P_PARTKEY)=true) |
|
7 |
=file("lineitem_14_4.ctx").open().cursor@m(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=B3 && L_SHIPDATE<A3) |
|
8 |
=A7.run(L_EXTENDEDPRICE*=(1-L_DISCOUNT),L_DISCOUNT=if(B5(L_PARTKEY),L_EXTENDEDPRICE,0)) |
|
9 |
=A8.total(sum(L_DISCOUNT),sum(L_EXTENDEDPRICE)) |
|
10 |
=100.00*A9(1)/A9(2) |
|
A4、B5 是对位序列。
Part 表的 p_type 字段已经转换为整数了，A6 不再取出原 p_type 字段的字符串值，而是只取出满足新 p_type 字段条件的 p_partkey 整数值，外存读取量和占用内存都大幅减少，性能提升明显。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
103 |
维表计算提前 |
8 |
数据变换 |
6 |
5. 列式计算
A |
B |
|
1 |
=now() |
|
2 |
1995-4-1 |
PROMO |
3 |
=days@o(elapse@m(A2,1)) |
=days@o(A2) |
4 |
=p_type=file("p_type.btx").import@b().(pos@h(_1,B2)) |
|
5 |
=file("part_2.ctx").open() |
=A5.cursor@m().skip().(false) |
6 |
=A5.cursor@m(P_PARTKEY;A4(P_TYPE)).fetch().(B5(P_PARTKEY)=true) |
|
7 |
=file("lineitem_3.ctx").open().cursor@mv(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=B3 && L_SHIPDATE<A3) |
|
8 |
=A7.derive@o(L_EXTENDEDPRICE*(1-L_DISCOUNT):dp,if(B5(L_PARTKEY),dp,0):dp1) |
|
9 |
=A8.total(sum(dp1),sum(dp)) |
|
10 |
=100.00*A9(1)/A9(2) |
|
列式计算不能给字段赋值，A8 将上一节的赋值语句改为新增字段 dp 和 dp1。
测试结果：
测试项目 |
执行时间（秒） |
一般实现 |
103 |
维表计算提前 |
8 |
数据转换 |
6 |
列式计算 |
3 |
英文版