性能优化技巧：有序分组
一、 问题背景与适用场景
通常分组计算都采用hash方案，即先计算分组字段的hash值，hash值相同的记录被分拣到一个小集合里，然后在这个小集合中遍历找分组字段值相同的聚合成一组。分组的复杂度(比较次数)，取决于hash函数的重码率。在hash空间比较小时，重码率就高，比较次数就会多，性能会受较大影响。为了提高性能，就需要分配较大的内存来存放 hash 表。另外，有些数据类型（长字串）的 hash 计算也比较慢，这也会影响性能。
如果分组字段是有序的，在分组的时候，每条记录只与上一条记录比较，发现有不同时则新建一个分组，相同则聚合到当前组中。这样的分组运算的复杂度为n(被分组集合的长度)，而且没有 hash 计算和重码率的问题，可以获得比 hash 分组更快的性能，而且并不需要太多内存用于存放 hash 表。
SPL提供了这种分组方法，我们实例测试一下，并且与使用hash分组算法的Oracle对比。
二、 测试环境
测试机有两个Intel2670 CPU，主频2.6G，共16核，内存64G，SSD固态硬盘。在此机上安装虚拟机来测试，设置虚拟机为16核、8G内存。
三、 小数据量小结果集测试
在虚拟机上创建数据表orderdetail_1，共三个字段：orderid(整数)、detailid(整数)、amount(实数)，前两个字段是主键，生成数据记录8千万行。将此表数据导入Oracle数据库，同时用它生成集算器SPL组表来进行测试。
orderid字段数据升序排列，按orderid进行分组，共有50组，统计每张订单的总金额和明细条数。
1. Oracle测试
编写查询测试SQL如下：
select /*+ parallel(n) */
orderid, sum(amount) as amount, count(detailid) as details
from orderdetail_1
group by orderid;
其中/*+ parallel(n) */ 用于并行测试，n为并行数。
2. SPL测试
编写SPL脚本如下：
A |
|
1 |
=now() |
2 |
=file("/home/ctx/orderdetail_1.ctx").open().cursor@m(orderid,detailid,amount;;1) |
3 |
=A2.groups@o(orderid;sum(amount):amount,count(detailid):details) |
4 |
=interval@s(A1,now()) |
groups分组时加选项@o就适用分组字段有序时，只比较相邻行的值进行有序分组。
3. 测试结果
测试结果如下，单位(秒)：
并行数 |
1 |
2 |
4 |
8 |
16 |
Oracle |
24 |
19 |
16 |
13 |
13 |
SPL |
11 |
6 |
3 |
2 |
1 |
在8千万行数据的情况下，SPL有序分组的性能提高了一倍左右，并且并行的效果非常好，性能呈线性上升。而使用hash分组的Oracle并行提速效果并不明显。
性能提高程序与数据量有关，当数据量很小时，分组时间占整个查询时间的比例很小，对整体性能的提高也就不明显。但随着数据量的增加，提升效果就会越来越显著。
下面我们再来看看大数据量测试的情况。
四、 大数据量大结果集测试
在虚拟机上创建数据表orderdetail_2，共三个字段：orderid(字符串)、detailid(整数)、amount(实数)，前两个字段是主键，生成数据记录24亿行。将此表数据导入Oracle数据库，同时用它生成集算器SPL组表来进行测试。
orderid字段数据升序排列，按orderid进行分组，共有8亿组，统计每张订单的总金额和明细条数。由于查询出的大结果集在Oracle输出需要很长的时间，所以对分组结果再进行一次过滤，只输出订单总金额小于35元的订单，结果只有12条，输出就几乎不占时间了。
1. Oracle测试
编写查询测试SQL如下：
select * from (
select /*+ parallel(n) */
orderid, sum(amount) sum_amount, count(detailid) as details
from orderdetail_2
group by orderid
)
where sum_amount<35;
其中/*+ parallel(n) */ 用于并行测试，n为并行数。
2. SPL测试
编写SPL脚本如下：
A |
|
1 |
=now() |
2 |
=file("/home/ctx/orderdetail_2.ctx").open().cursor@m(orderid,detailid,amount;;1) |
3 |
=A2.group(orderid;sum(amount):amount,count(detailid):details).select(amount<35).fetch() |
4 |
=interval@s(A1,now()) |
由于分组结果集很大，无法全部装载到内存，所以使用group函数进行有序分组，返回分组结果集对应的游标，再对游标过滤后取得需要的查询结果。
3. 测试结果
测试结果如下，单位(秒)：
并行数 |
1 |
2 |
4 |
8 |
16 |
Oracle |
2647 |
1345 |
1092 |
806 |
737 |
SPL |
451 |
235 |
119 |
65 |
48 |
在不并行的情况下，SPL有序分组比Oracle性能提升了近6倍左右。因SPL有序分组方法很适合并行，随着并行数的增加，性能提升的效果就越好。
系列性能优化技巧：
性能优化技巧：遍历复用
性能优化技巧：TopN
性能优化技巧：预关联
性能优化技巧：部分预关联
性能优化技巧：外键序号化
性能优化技巧：维表过滤或计算时的关联
性能优化技巧：有序归并
性能优化技巧：有序定位关联提速主子关联后的过滤
性能优化技巧：附表
性能优化技巧：大维表查找
性能优化技巧：单边分堆
性能优化技巧：有序分组
性能优化技巧：后半有序分组
性能优化技巧：前半有序时的排序
