性能优化技巧:有序分组

一、  问题背景与适用场景

通常分组计算都采用hash方案,即先计算分组字段的hash值,hash值相同的记录被分拣到一个小集合里,然后在这个小集合中遍历找分组字段值相同的聚合成一组。分组的复杂度(比较次数),取决于hash函数的重码率。在hash空间比较小时,重码率就高,比较次数就会多,性能会受较大影响。为了提高性能,就需要分配较大的内存来存放 hash 表。另外,有些数据类型(长字串)的 hash 计算也比较慢,这也会影响性能。

如果分组字段是有序的,在分组的时候,每条记录只与上一条记录比较,发现有不同时则新建一个分组,相同则聚合到当前组中。这样的分组运算的复杂度为n(被分组集合的长度),而且没有 hash 计算和重码率的问题,可以获得比 hash 分组更快的性能,而且并不需要太多内存用于存放 hash 表。

SPL提供了这种分组方法,我们实例测试一下,并且与使用hash分组算法的Oracle对比。

 

二、  测试环境

测试机有两个Intel2670 CPU,主频2.6G,共16核,内存64GSSD固态硬盘。在此机上安装虚拟机来测试,设置虚拟机为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").create().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").create().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有序分组方法很适合并行,随着并行数的增加,性能提升的效果就越好。

 


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾