性能优化技巧:大维表查找

一、  问题背景与适用场景

在主子表关联查询中,有时会遇到这样一种情况:按条件过滤后的事实表数据量很小,能够全部装载进内存或仅比内存略大一点;而要关联的维表数据量很大,比内存要大很多。这种时候,如果维表是按键有序存储时,因为事实表涉及的维表记录较少,可以一次性用二分查找方法找出来,而不必象HASH运算那样遍历这个大维表,从而提高运算性能。

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

不过,因为要把事实表的关联键都取出来排序后到维表中去找,要一次性汇集尽量多的关联键值去维表中查找,而主要时间成本也消耗在这次查询中,其它关联本身的运算开销不大。所以,这种办法对于多线程是不敏感的,我们后面也测试一下并行时的情况。

 

二、  测试环境与任务

测试机有两个Intel2670 CPU,主频2.6G,共16核,内存64GSSD固态硬盘。在此机上安装虚拟机来测试,设置虚拟机为16核、8G内存。

在虚拟机上创建维表account,共三个字段accountidnamestate,总记录共5亿行。创建事实表trade1trade2trade3,各有记录分别为30万、300万、1500万行,其表结构都相同,共四个字段tradedateoutid(转出帐户)receiveid(接收帐户)amount(转帐金额)account表中的accountid是事实表中outidreceiveid的外键,都是一对多的关系。

测试任务为分别用3张不同数据量的事实表,查询各州之间转账总金额。再用trade2来测试一下并行的情况。

 

三、  测试

1.  Oracle测试

编写查询测试SQL如下:

select   /*+ parallel(n) */

       a1.state,

       a2.state,

       sum(amount) as amount

from

       account a1,

       account a2,

       trade1

where

       outid = a1.accountid

       and receiveid = a2.accountid

group by

       a1.state,

       a2.state

order by

       a1.state,

       a2.state;

其中/*+ parallel(n) */ 用于并行测试,n为并行数。

 

2.  SPL测试

编写SPL脚本如下:


A

1

=now()

2

=file(path+"account.ctx").open()

3

=file(path+"trade1.ctx").open().cursor@m(outid,receiveid,amount;;1)

4

=A3.joinx@q(outid,A2:accountid,state:out_state;receiveid,A2:accountid,state:receive_state;5000000)

5

=A4.groups(out_state,receive_state;sum(amount):amount)

6

=interval@s(A1,now())

joinx时加选项@q就适用于事实表很小、维表很大时来提高关联性能,它的最后一个参数指明每次关联时从游标中读取的记录数,在内存能装下的情况下,此值越大性能越高。

 

3.  测试结果

事实表不同数据量时的测试结果如下(单位:秒)

事实表过滤后记录数

1500

300

30

Oracle

17277

2747

421

SPL

517

106

72

trade2(300万行)测试并行的结果如下(单位:秒)

并行数

1

2

4

8

16

Oracle

2747

1589

1318

1375

1631

SPL

106

109

117

115

109

 

四、  结论

从测试结果可以看出, 单线程情况下,SPLOracle的关联性能提高了很多倍,因为避免了对维表的遍历动作。

在此场景下并行时,SPL算法对并行不敏感,性能没有显著变化。ORACLE可以对并行有效,性能有所提高,但因为遍历的成本太高,并行后仍比SPL慢数倍。

 

 


系列性能优化技巧:
性能优化技巧:遍历复用
性能优化技巧:TopN
性能优化技巧:预关联
性能优化技巧:部分预关联
性能优化技巧:外键序号化
性能优化技巧:维表过滤或计算时的关联
性能优化技巧:有序归并
性能优化技巧:有序定位关联提速主子关联后的过滤
性能优化技巧:附表
性能优化技巧:大维表查找
性能优化技巧:单边分堆
性能优化技巧:有序分组
性能优化技巧:后半有序分组
性能优化技巧:前半有序时的排序