性能优化案例课程 TPCH-Q3
select * from (
select
l_orderkey,
sum(l_extendedprice * (1 - l_discount)) as revenue,
o_orderdate,
o_shippriority
from
customer,
orders,
lineitem
where
c_mktsegment = 'BUILDING'
and c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate < date '1995-03-15'
and l_shipdate > date '1995-03-15'
group by
l_orderkey,
o_orderdate,
o_shippriority
order by
revenue desc,
o_orderdate
) where rownum<=10;
这是典型的主子表关联，关联后进行分组统计。
1. 数据存储
这一题是遍历计算，大表 order、lineitem 存成列式组表。customer 相对较大，也存成组表。
order、lineitem 两个表是主子表，要按照关联主键 orderkey 排序。后续计算有较大结果集的分组，分组字段恰好也是 orderkey。
数据转换代码：
A |
|
1 |
=file("customer.tbl").cursor(; , "|").new(_1:C_CUSTKEY, _2:C_NAME, _3:C_ADDRESS, _4:C_NATIONKEY, _5:C_PHONE, _6:C_ACCTBAL, _7:C_MKTSEGMENT, _8:C_COMMENT).sortx(C_CUSTKEY) |
2 |
=file("customer.ctx").create@y(#C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY, C_PHONE, C_ACCTBAL, C_MKTSEGMENT, C_COMMENT) |
3 |
>A2.append(A1) |
4 |
=file("lineitem.tbl").cursor(;,"|").new(_1:L_ORDERKEY,_4:L_LINENUMBER,_2:L_PARTKEY,_3:L_SUPPKEY,_5:L_QUANTITY,_6:L_EXTENDEDPRICE,_7:L_DISCOUNT,_8:L_TAX,_9:L_RETURNFLAG,_10:L_LINESTATUS,_11:L_SHIPDATE,_12:L_COMMITDATE,_13:L_RECEIPTDATE,_14:L_SHIPINSTRUCT,_15:L_SHIPMODE,_16:L_COMMENT).sortx(L_ORDERKEY,L_LINENUMBER;5000000) |
5 |
=file("lineitem.ctx").create@p(#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) |
6 |
>A5.append(A4) |
=file("orders.tbl").cursor(; ,"|").new(_1:O_ORDERKEY,_2:O_CUSTKEY,_3:O_ORDERSTATUS,_4:O_TOTALPRICE,_5:O_ORDERDATE,_6:O_ORDERPRIORITY,_7:O_CLERK, _8:O_SHIPPRIORITY,_9:O_COMMENT).sortx(O_ORDERKEY;5000000) |
|
=file("orders.ctx").create@y(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE, O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT) |
|
>A8.append(A7) |
子表 lineitem 必须按主表 orders 匹配的方法分段存储，因此 A5 创建组表用了 @p，这样才能保证连接计算时的并行分段不会错位。
2. 常规分组
使用前面题目提到的优化算法：游标前过滤、主子表有序归并、关联定位、topN、多线程并行。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-03-15 |
BUILDING |
3 |
=file("customer.ctx").open().cursor@m(C_CUSTKEY;C_MKTSEGMENT==B2).fetch().keys@im(C_CUSTKEY) |
|
4 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;O_ORDERDATE<A2 && A3.find(O_CUSTKEY)) |
|
5 |
=file("lineitem.ctx").open().news(A4,O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>A2) |
|
6 |
=A5.group(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;~.sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue) |
|
7 |
=A6.total(top(10;-revenue,O_ORDERDATE)) |
|
8 |
=A7.run(O_ORDERDATE=date@o(O_ORDERDATE)) |
|
9 |
=interval@ms(A1,now()) |
|
A3、A4、A5 使用了游标建立时过滤的技巧。
A5 中使用了关联定位。
A5 连接的结果对第一个分组字段 ORDERKEY 有序，而另外两个分组字段是被 ORDERKEY 唯一确定的，所以 A6 可以做有序分组。
测试结果：
测试项目 |
执行时间（秒） |
常规分组 |
9 |
3. 冗余分组键
三个分组字段中，后两个 O_ORDERDATE,O_SHIPPRIORITY 可以被第一个 ORDERKEY 唯一确定，所以没必要参加分组计算，可以采用冗余分组键方式计算。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-03-15 |
BUILDING |
3 |
=file("customer.ctx").open().cursor@m(C_CUSTKEY;C_MKTSEGMENT==B2).fetch().keys@im(C_CUSTKEY) |
|
4 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;O_ORDERDATE<A2 && A3.find(O_CUSTKEY)) |
|
5 |
=file("lineitem.ctx").open().news(A4,O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>A2) |
|
6 |
=A5.group(O_ORDERKEY;O_ORDERDATE,O_SHIPPRIORITY,~.sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue) |
|
7 |
=A6.total(top(10;-revenue,O_ORDERDATE)) |
|
8 |
=A7.run(O_ORDERDATE=date@o(O_ORDERDATE)) |
|
9 |
=interval@ms(A1,now()) |
|
A6 中原来的两个分组字段，这里改成写在聚合区，计算时直接取每组第一条记录的字段值，可以不参加分组计算。
测试结果：
测试项目 |
执行时间（秒） |
常规分组 |
9 |
冗余分组键 |
8 |
4. 连接时直接聚合
在连接主子表的时候，可以将子表直接聚合，能省去复制主表字段等计算过程。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
1995-03-15 |
BUILDING |
3 |
=file("customer.ctx").open().cursor@m(C_CUSTKEY;C_MKTSEGMENT==B2).fetch().keys@im(C_CUSTKEY) |
|
4 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;O_ORDERDATE<A2 && A3.find(O_CUSTKEY)) |
|
5 |
=file("lineitem.ctx").open().news@r(A4,O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue;L_SHIPDATE>A2) |
|
6 |
=A5.total(top(10;-revenue,O_ORDERDATE)) |
|
7 |
=A6.run(O_ORDERDATE=date@o(O_ORDERDATE)) |
|
8 |
=interval@ms(A1,now()) |
|
A5 中 news 函数加上 @r 选项，表示以主表 orders 为准，不带 r 选项是默认是后一个表为准。以主表为准，子表就要先做聚合计算再连接。
测试结果：
测试项目 |
执行时间（秒） |
常规分组 |
9 |
冗余分组键 |
8 |
关联直接聚合 |
7 |
5. 数据变换
利用前面题目提到多种数据变换方式：枚举型字符串数字化、日期整数化、维表主键序号化。
数据转换代码：
customer 转换
A |
|
1 |
=file("customer.ctx").open().import() |
2 |
=A1.id(C_MKTSEGMENT).sort() |
3 |
=file("c_mktsegment.btx").export@b(A2) |
4 |
=A1.run(C_CUSTKEY=#, C_MKTSEGMENT=A2.pos@b(C_MKTSEGMENT)) |
5 |
=file("customer_5.ctx").create(#C_CUSTKEY, C_NAME, C_ADDRESS, C_NATIONKEY, C_PHONE, C_ACCTBAL, C_MKTSEGMENT, C_COMMENT) |
6 |
>A5.append(A4.cursor()) |
A2 到 A4 对枚举型字符串字段 C_MKTSEGMENT 数字化。
A4 设主键 C_CUSTKEY 为行号，设 C_MKTSEGMENT 为它在取值列表中的序号。
orders 转换
A |
|
1 |
=file("customer.ctx").open().import(C_CUSTKEY).keys@im(C_CUSTKEY) |
2 |
=file("orders.ctx").open().cursor() |
3 |
=A2.run(O_CUSTKEY=A1.pfind(O_CUSTKEY),O_ORDERDATE=days@o(O_ORDERDATE)) |
4 |
=file("orders_5.ctx").create(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE, O_ORDERDATE,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT) |
5 |
>A4.append(A3) |
A1 读取 customer 表中 C_CUSTKEY 列为主键并创建索引。
A3 在 A1 中查找主键值为 O_CUSTKEY 的记录所在行号，将它给 O_CUSTKEY。将 O_ORDERDATE 转换为小整数。
lineitem 转换
A |
|
1 |
=file("lineitem.ctx").open().cursor() |
2 |
=A1.run(L_SHIPDATE=days@o(L_SHIPDATE)) |
3 |
=file("lineitem_5.ctx").create@p(#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) |
子表 lineitem 必须按主表 orders 匹配的方法分段存储，因此 A3 创建组表用了 @p，这样才能保证连接计算时的并行分段不会错位。
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
=days@o(date("1995-03-15")) |
|
3 |
=file("c_mktsegment.btx").import@b().(_1=="BUILDING") |
|
4 |
=file("customer_5.ctx").open() |
=A4.cursor@m().skip().(false) |
5 |
=A4.cursor@m(C_CUSTKEY;A3(C_MKTSEGMENT)).fetch() |
|
6 |
=A5.(B4(C_CUSTKEY)=true) |
|
7 |
=file("orders_5.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;B4(O_CUSTKEY) && O_ORDERDATE<A2) |
|
8 |
=file("lineitem_5.ctx").open().news@r(A7,O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue;L_SHIPDATE>A2) |
|
9 |
=A8.total(top(10;-revenue,O_ORDERDATE)) |
|
10 |
=A9.run(O_ORDERDATE=date@o(O_ORDERDATE)) |
|
11 |
=interval@ms(A1,now()) |
|
完成枚举型字符串字段数字化和维表主键序号化后，就可以使用 A3、B4 中的对位序列。
测试结果：
测试项目 |
执行时间（秒） |
常规分组 |
9 |
冗余分组键 |
8 |
关联直接聚合 |
7 |
数据变换 |
6 |
6. 列式计算
计算代码：
A |
B |
|
1 |
=now() |
|
2 |
=days@o(date("1995-03-15")) |
|
3 |
=file("c_mktsegment.btx").import@b().(_1=="BUILDING") |
|
4 |
=file("customer_5.ctx").open() |
=A4.cursor@m().skip().(false) |
5 |
=A4.cursor@mv(C_CUSTKEY;A3(C_MKTSEGMENT)).fetch() |
|
6 |
=A5.(B4(C_CUSTKEY)=true) |
|
7 |
=file("orders_5.ctx").open().cursor@mv(O_ORDERKEY,O_ORDERDATE,O_SHIPPRIORITY;B4(O_CUSTKEY) && O_ORDERDATE<A2) |
|
8 |
=file("lineitem_5.ctx").open().news@r(A7,O_ORDERKEY,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue,O_ORDERDATE,O_SHIPPRIORITY;L_SHIPDATE>A2) |
|
9 |
=A8.total(top(10;-revenue,O_ORDERDATE)) |
|
10 |
=A9.new(O_ORDERKEY,date@o(O_ORDERDATE):O_ORDERDATE,O_SHIPPRIORITY,revenue) |
|
11 |
=interval@ms(A1,now()) |
|
A10 中要将整数化的日期再转回日期，前面常规的做法是用 run 函数重设 O_ORDERDATE 的值，但列式中不能改变列的数据类型，所以这里要用 new。
测试结果：
测试项目 |
执行时间（秒） |
常规分组 |
9 |
冗余分组键 |
8 |
关联直接聚合 |
7 |
数据变换 |
6 |
列式计算 |
3 |
英文版