从 TPCH 测试学习性能优化技巧之 Q13
一、 查询要求
Q13 语句根据消费者的订单数量确定消费者的分布，包括过去和现在都没有订单记录的消费者。它计算统计多少消费者没有订单，多少有 1,2,3……个订单, 统计订单数量时要检查订单备注字段不包含指定的 2 个词，以保证订单没有属于一个特殊的订单类别。
Q13语句的特点是：带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。
二、 Oracle执行
Oracle编写的查询SQL语句如下：
select /*+ parallel(n) */
c_count,
count(*) as custdist
from (
select
c_custkey,
count(o_orderkey) c_count
from
customer left outer join orders on
c_custkey = o_custkey
and o_comment not like '%special%accounts%'
group by
c_custkey
) c_orders
group by
c_count
order by
custdist desc,
c_count desc;
其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
316 |
175 |
89 |
48 |
37 |
三、 SPL优化
这个查询简单看是对orders做两轮常规分组，第一轮按custkey分组计算出每个顾客的下单数，第二轮再按下单数分组计算出每种下单数各有多少顾客。
注意到原SQL中有个左连接，会将没有下单过的顾客（下单数为0）也统计在内，而上述二轮分组过程则会遗漏掉这些数据，需要事后再补充一下。
SPL脚本如下：
A |
|
1 |
=now() |
2 |
>filter="*special*accounts*" |
3 |
=file("orders.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter)) |
4 |
=A3.groups@u(O_CUSTKEY;count(1):c_count) |
5 |
=A4.len() |
6 |
=A4.cursor@m().groups@u(c_count;count(1):custdist) |
7 |
=file("customer.ctx").open().cursor().skip() |
8 |
=A6.insert(0,0,A7-A5) |
9 |
=A6.sort@z(custdist,c_count) |
10 |
return interval@ms(A1,now()) |
A4做第一轮分组，A6做第二轮；A7计算所有客户数，减去已下单的就是没下单的客户数，补充到A6上再一起排序。
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
316 |
175 |
89 |
48 |
37 |
SPL组表 |
352 |
218 |
131 |
99 |
109 |
这个查询SPL没有跑过Oracle，绝大部分时间消耗在第一轮分组（A4）。主要原因在于这里的分组结果集较大，会占用很多内存，而SPL目前还是Java开发，JVM对内存的管理较差，占用内存较多时就会频繁发起垃圾收集动作，消耗很多时间。而C++开发的数据库则没有这个问题。
四、 进一步优化
SPL中groups函数在分组时，如果分组字段是序号，那么可以用@n选项直接定位，避免hash计算。本例中第一轮按O_CUSTKEY分组的，而在数据表中O_CUSTKEY都是整数，可以尝试@n选项。但本例中O_CUSTKEY的值较大，也就是分组数多，占用内存大，并行线程多时如果每个线程中都分一个大组，内存将不够用，所以并行数多时，要减少groups@n的并行数。
SPL脚本如下：
A |
|
1 |
=now() |
2 |
>filter="*special*accounts*" |
3 |
=file("orders.ctx").open().cursor@m(O_CUSTKEY;!like(O_COMMENT,filter)) |
4 |
=A3.groups@n(O_CUSTKEY;count(1):c_count).select@m(c_count) |
5 |
=A4.len() |
6 |
=A4.cursor@m().groups@u(c_count;count(1):custdist) |
7 |
=file("customer.ctx").open().cursor().skip() |
8 |
=A6.insert(0,0,A7-A5) |
9 |
=A6.sort@z(custdist,c_count) |
10 |
return interval@ms(A1,now()) |
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
316 |
175 |
89 |
48 |
37 |
SPL组表 |
352 |
218 |
131 |
99 |
109 |
SPL组表(@n) |
228 |
137 |
95 |
67 |
57 |
测试结果可见，效率确实有所提高，在低并行时性能超过了Oracle，高并行时因为内存占用过大导致垃圾收集时间消耗过多而又被Oracle反超。
