性能优化案例课程 TPCH-Q4
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1995-10-01'
and o_orderdate < date '1995-10-01' + interval '3' month
and exists (
select * from lineitem
where l_orderkey = o_orderkey and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority;
SQL 语句有一个 exists 子查询，找出子表 lineitem 中有满足条件 l_commitdate < l_receiptdate 的记录。exists 子句都可以改写成 join，将 exists 改写成 join 之后是这样：
select
o_orderpriority,
count(*) as order_count
from (
select
o_orderkey,o_orderpriority
from orders join (
select l_orderkey
from lineitem
where l_commitdate < l_receiptdate
group by l_orderkey
) on l_orderkey = o_orderkey
where
o_orderdate >= date '1995-10-01'
and o_orderdate < date '1995-10-01' + interval '3' month
)
group by
o_orderpriority
order by
o_orderpriority;
改写之后可以发现，这是对子表进行过滤分组聚合，然后再与主表一对一连接的问题。
1. 数据存储
大表 orders、lineitem 按照主键 orderkey 有序存储，子表 lineitem 按照主键 orderkey 有序分组聚合后依然对主键有序，可以和主表 order 有序归并。
这样，直接使用 Q3 中的 order.ctx、lineitem.ctx 即可。
将这些表复制到本题的主目录中。
2. 先分组聚合后连接
按照先对子表 lineitem 有序分组，然后再与主表 order 有序归并的思路计算。
计算代码如下：
A |
B |
|
1 |
=now() |
|
2 |
1995-10-01 |
3 |
3 |
=elapse@m(A2,B2) |
|
4 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=A2 && O_ORDERDATE<A3) |
|
5 |
=file("lineitem.ctx").open().cursor(L_ORDERKEY;L_COMMITDATE<L_RECEIPTDATE;A4) |
|
6 |
=A5.group(L_ORDERKEY) |
|
7 |
=A4.joinx@im(O_ORDERKEY,A6:L_ORDERKEY) |
|
8 |
=A7.groups(O_ORDERPRIORITY;count(1):order_count) |
|
9 |
=interval@ms(A1,now()) |
|
A4、A5 使用游标前过滤机制。
A6 对 lineitem 按照主键 orderkey 有序分组，结果依然对 orderkey 有序。
A7 用分组后的 lineitem 和主表 order 按照主键有序归并。
测试结果：
测试项目 |
执行时间（秒） |
先聚合后归并 |
13 |
3. 连接时直接聚合
采用前面题目提到的 news 函数，在连接主子表的时候，可以将子表直接聚合，能省去复制主表字段等计算过程。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-10-01 |
3 |
3 |
=elapse@m(A2,B2) |
|
4 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=A2 && O_ORDERDATE<A3) |
|
5 |
=file("lineitem.ctx").open().news@r(A4,O_ORDERPRIORITY;L_COMMITDATE<L_RECEIPTDATE) |
|
6 |
=A5.groups(O_ORDERPRIORITY;count(1):order_count) |
|
7 |
=interval@ms(A1,now()) |
|
A5 中 news 函数加上 @r 选项，表示以主表 orders 为准，不带 r 选项是默认是后一个表为准。以主表为准，子表就要先做聚合计算再连接。
测试结果：
测试项目 |
执行时间（秒） |
先聚合后归并 |
13 |
连接时直接聚合 |
7 |
4. 数据变换
数据变换采用前面题目提到的枚举型字符串字段数字化、日期整数化手段。
Orders 转换代码：
A |
|
1 |
=file("orders.ctx").open().cursor(O_ORDERPRIORITY) |
2 |
=A1.id(O_ORDERPRIORITY).sort() |
3 |
=file("o_orderpriority.btx").export@b(A2) |
4 |
=file("orders.ctx").open().cursor() |
5 |
=A4.run(O_ORDERPRIORITY=A2.pos@b(O_ORDERPRIORITY),O_ORDERDATE=days@o(O_ORDERDATE)) |
6 |
=file("orders_4.ctx").create(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT) |
7 |
>A6.append(A5) |
Lineitem 转换代码：
A |
|
1 |
=file("lineitem.ctx").open().cursor() |
2 |
=A1.run(L_COMMITDATE=days@o(L_COMMITDATE),L_RECEIPTDATE=days@o(L_RECEIPTDATE)) |
3 |
=file("lineitem_4.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) |
4 |
>A3.append(A2) |
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-10-01 |
3 |
3 |
>o_orderpriority=file("o_orderpriority.btx").import@b().(_1) |
|
4 |
=days@o(elapse@m(A2,B2)) |
=days@o(A2) |
5 |
=file("orders_4.ctx").open().cursor@m(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=B4 && O_ORDERDATE<A4) |
|
6 |
=file("lineitem_4.ctx").open().news@r(A5,O_ORDERPRIORITY;L_COMMITDATE<L_RECEIPTDATE) |
|
7 |
=A6.groups(O_ORDERPRIORITY;count(1):order_count) |
|
8 |
=A7.run(O_ORDERPRIORITY=o_orderpriority(O_ORDERPRIORITY)) |
|
9 |
=interval@ms(A1,now()) |
|
测试结果：
测试项目 |
执行时间（秒） |
先聚合后归并 |
13 |
连接时直接聚合 |
7 |
数据变换 |
6 |
5. 列式计算
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-10-01 |
3 |
3 |
>o_orderpriority=file("o_orderpriority.btx").import@b().(_1) |
|
4 |
=days@o(elapse@m(A2,B2)) |
=days@o(A2) |
5 |
=file("orders_4.ctx").open().cursor@mv(O_ORDERKEY,O_ORDERPRIORITY;O_ORDERDATE>=B4 && O_ORDERDATE<A4) |
|
6 |
=file("lineitem_4.ctx").open().news@r(A5,O_ORDERPRIORITY;L_COMMITDATE<L_RECEIPTDATE) |
|
7 |
=A6.groups(O_ORDERPRIORITY;count(1):order_count) |
|
8 |
=A7.new(o_orderpriority(O_ORDERPRIORITY):O_ORDERPRIORITY,order_count) |
|
9 |
=interval@ms(A1,now()) |
|
A10 中要将整数化的 O_ORDERPRIORITY 再转回字符串，前面常规的做法是用 run 函数重设 O_ORDERPRIORITY 的值，但列式中不能改变列的数据类型，所以这里要用 new。
测试结果：
测试项目 |
执行时间（秒） |
先聚合后归并 |
13 |
连接时直接聚合 |
7 |
数据变换 |
6 |
列式计算 |
2 |
英文版