用 TPCH 练习性能优化 Q10
一、 SQL及分析
查询SQL语句如下：
select * from (
select
c_custkey,c_name,
sum(l_extendedprice * (1 - l_discount)) as revenue,
c_acctbal,n_name,c_address,c_phone,c_comment
from
customer,orders,lineitem,nation
where
c_custkey = o_custkey
and l_orderkey = o_orderkey
and o_orderdate >= date '1993-05-01'
and o_orderdate < date '1993-05-01' + interval '3' month
and l_returnflag = 'R'
and c_nationkey = n_nationkey
group by
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
order by
revenue desc
) where rownum <=20;
这是多表关联(其中包括主子表orders和lineitem关联)过滤后的分组聚合运算，然后按分组聚合值排序后取前20条记录。
二、 SPL实现
实现原理与Q3相同，group by子句中有7个字段，在SPL中只需对第一个字段分组即可。
A |
|
1 |
=now() |
2 |
1993-5-1 |
3 |
=elapse@m(A2,3) |
4 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE<A3) |
5 |
=file("lineitem.ctx").open().news@r(A4,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG=="R") |
6 |
=A5.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue) |
7 |
=A6.top(-20;revenue) |
8 |
=file("nation.btx").import@b().keys@i(N_NATIONKEY) |
9 |
=file("customer.ctx").open() |
10 |
=A7.joinx@q(c_custkey,A9:C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT) |
11 |
=A10.switch(C_NATIONKEY,A8:N_NATIONKEY) |
12 |
=A11.new(c_custkey:C_CUSTKEY,C_NAME,revenue,C_ACCTBAL,C_NATIONKEY.N_NAME,C_ADDRESS,C_PHONE,C_COMMENT) |
13 |
=A12.sort@z(revenue) |
14 |
=interval@ms(A1,now()) |
先把orders和lineitem表的连接结果集上的分组汇总运算做完，然后再基于这个结果集继续做外部相关的外键表关联运算。因为最终结果只要取20条记录，可以只针对这20条记录再做剩下的关联计算，所以不必在分组前做，否则计算量会增大。
A7算出来之后，因为customer表对c_custkey有序，用A7.joinx@q从customer表中有序匹配快速地把相关记录取出来，再去做其它join，无须遍历customer表，减少数据读取量。
三、 进一步优化
1. 优化方法
本例中要使用Q1中介绍的日期整数化优化方法，orders中的O_ORDERDATE已在Q3中转换过了。要使用Q1中介绍的字符串整数化方法，lineitem中的L_RETURNFLAG已在Q1中转化过了。还要使用Q2中介绍的维表主键序号化方法，nation中的N_NATIONKEY、customer中的C_CUSTKEY和C_NATIONKEY、orders中的O_CUSTKEY都已在之前的例子中转换过了。
2. 数据转换代码
复制 nation_9.btx、customer_8.ctx、orders_9.ctx、lineitem_9.ctx分别重命名为nation_10.btx、customer_10.ctx、orders_10.ctx、lineitem_10.ctx。
3. 数据转换后的计算代码
维表需要预加载，加载代码如下：
A |
|
1 |
>env(nation, file("nation_10.btx").import@b()) |
2 |
>env(customer, file("customer_10.ctx").open().import()) |
3 |
>env(l_returnflag,file("l_returnflag.txt").import@si()) |
在查询计算之前，需要先运行预加载代码，将小维表读入内存。
计算代码：
A |
|
1 |
=now() |
2 |
1993-5-1 |
3 |
=days@o(elapse@m(A2,3)) |
4 |
=days@o(A2) |
5 |
=l_returnflag.pos@b("R") |
6 |
=file("orders_10.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A4 && O_ORDERDATE<A3) |
7 |
=file("lineitem_10.ctx").open().news@r(A6,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG==A5) |
8 |
=A7.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue) |
9 |
=A8.top(-20;revenue) |
10 |
=A9.new((c=customer(c_custkey)).C_CUSTKEY,c.C_NAME,revenue,c.C_ACCTBAL,nation(c.C_NATIONKEY).N_NAME,c.C_ADDRESS,c.C_PHONE,c.C_COMMENT) |
11 |
=A10.sort@z(revenue) |
12 |
=interval@ms(A1,now()) |
四、 使用企业版列式计算
1. 原始数据
A |
|
1 |
=now() |
2 |
1993-5-1 |
3 |
=elapse@m(A2,3) |
4 |
=file("orders.ctx").open().cursor@mv(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE<A3) |
5 |
=file("lineitem.ctx").open().news@r(A4,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG=="R") |
6 |
=A5.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue) |
7 |
=A6.top(-20;revenue) |
8 |
=file("nation.btx").import@bv().keys@i(N_NATIONKEY) |
9 |
=file("customer.ctx").open() |
10 |
=A7.joinx@q(c_custkey,A9:C_CUSTKEY,C_NAME,C_ADDRESS,C_NATIONKEY,C_PHONE,C_ACCTBAL,C_COMMENT) |
11 |
=A10.join(C_NATIONKEY,A8,N_NAME) |
12 |
=A11.new(c_custkey:C_CUSTKEY,C_NAME,revenue,C_ACCTBAL,N_NAME,C_ADDRESS,C_PHONE,C_COMMENT) |
13 |
=A12.sort@z(revenue) |
14 |
=interval@ms(A1,now()) |
2. 优化后数据
维表需要预加载，加载代码如下：
A |
|
1 |
>env(nation, file("nation_10.btx").import@bv()) |
2 |
>env(customer, file("customer_10.ctx").open().import@v()) |
3 |
>env(l_returnflag,file("l_returnflag.txt").import@si()) |
在查询计算之前，需要先运行预加载代码，将小维表读入内存。
计算代码：
A |
|
1 |
=now() |
2 |
1993-5-1 |
3 |
=days@o(elapse@m(A2,3)) |
4 |
=days@o(A2) |
5 |
=l_returnflag.pos@b("R") |
6 |
=file("orders_10.ctx").open().cursor@mv(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A4 && O_ORDERDATE<A3) |
7 |
=file("lineitem_10.ctx").open().news@r(A6,sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):dp,O_CUSTKEY;L_RETURNFLAG==A5) |
8 |
=A7.groups@u(O_CUSTKEY:c_custkey;sum(dp):revenue) |
9 |
=A8.top(-20;revenue) |
10 |
=A9.new(customer(c_custkey):c,revenue).new(c.C_CUSTKEY,c.C_NAME,revenue,c.C_ACCTBAL,nation(c.C_NATIONKEY).N_NAME,c.C_ADDRESS,c.C_PHONE,c.C_COMMENT) |
11 |
=A10.sort@z(revenue) |
12 |
=interval@ms(A1,now()) |
五、 测试结果
单位：秒
常规 |
列式 |
|
优化前 |
13.3 |
7.4 |
优化后 |
12.2 |
6.2 |
对润乾产品感兴趣的小伙伴，一定要知道软件还能这样卖哟性价比还不过瘾？ 欢迎加入好多乾计划。
这里可以低价购买软件产品，让已经亲民的价格更加便宜！
这里可以销售产品获取佣金，赚满钱包成为土豪不再是梦！
这里还可以推荐分享抢红包，每次都是好几块钱的巨款哟！
来吧，现在就加入，拿起手机扫码，开始乾包之旅
嗯，还不太了解好多乾？
英文版