性能优化技巧:大事实表与大维表关联

一、  问题背景与适用场景

在《性能优化技巧:小事实表与大维表关联》中,我们尝试了小事实表与大维表关联时的性能优化方法,该方法利用了小事实表可以装入内存的特点,将关联键汇集排序后到大维表中查找,避免了遍历大维表的动作。如果事实表与维表都大到不能装载到内存时,这个办法就不再有效了,那么,还有什么办法提高性能呢?

SQL的方案是对两个表做HASH分堆,拆小到内存可以放下的地步,分别写入外存,然后再分别读入进行内存关联。如果运气不好,拆出来的某个堆还是太大,就需要做二次HASH。同时,两个表都需要做一遍HASH分堆动作,也就是需要把所有数据都缓存一遍。

如果维表是有序存放的,我们就可以将平均分段,由于有序存储,所以可以计算出每一段值的边界值,然后再用这个边界值将事实表再分堆。这样,维表本身由于有序存储,可以直接按段读取,而不需要再分堆;只有事实表被缓存出去,也就是只有一个表被分堆缓存,所以这种办法可以称为单边方案。而且,由于维表可以被相对平均分段,不可能象HASH方法那样出现运气不好导致某堆太大的情况,一次分堆一定能解决问题,性能将得到保障。

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

 

二、  测试环境与任务

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

在虚拟机上创建维表account,共三个字段accountidnamestate,总记录共10亿行。创建事实表trade,总记录共16亿行,共四个字段tradedateoutid(转出帐户)receiveid(接收帐户)amount(转帐金额)account表中的accountid是事实表中outidreceiveid的外键,都是一对多的关系。

在《性能优化技巧:小事实表与大维表关联》中我们测试的是outidreceiveid两个字段都要与account表中的accountid关联,称之为双维表。测试结果可见当事实表记录数为1500万行时,Oracle运行时间已经接近5小时,而这次测试的事实表最少记录数为10亿行,用Oracle运行的时间就会超过24小时了,所以测试只有outid关联的情况,称之为单维表。测试任务为查询某段时期内各州转出资金总额。

SPL测试中,会用双维表与单维表作对比测试。

为缩短测试时间,全部采用4个并行。

三、  测试

1.  Oracle测试

编写查询测试SQL如下:

select    /*+ parallel(4) */

       state,

       sum(amount) as amount

from

       account,

       trade

where

       outid = accountid

       and tradedate >= date '2008-01-01' + interval '1500' day(4)

group by

       state

order by

       state;

其中/*+ parallel(4) */ 表示4个并行。

 

2.  SPL测试

编写SPL脚本如下:


A

1

=now()

2

=elapse(date("2008-01-01"),1500)

3

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

4

=file(path+"trade.ctx").create().cursor@m(outid,amount;tradedate>=A2;4)

5

=A4.joinx@u(outid,A3:accountid,state;4000000)

6

=A5.groups(state;sum(amount):amount)

7

=interval@s(A1,now())

joinx时加选项@u就适用于大事实表与大维表关联,它的最后一个参数指明把游标拆分为多路时,每次从游标中读取的记录数,在内存能装下的情况下,此值越大性能越高。

 

3.  测试结果及分析

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

事实表过滤后记录数

10亿

12亿

14亿

15亿

16亿

Oracle

730

802

860

894

>10小时

SPL

486

562

643

681

730

经测算,10亿行数据正常情况会超过8G内存,优秀的Oracle可能采用了数据压缩技术,致使能装下15亿行数据。但是在16亿行数据时,内存就怎么也放不下了,开始发生大量占用swap区的现象,也造成运行速度奇慢,测试中等了11小时也没查询出来,只好终止了。而SPL这种单边技术,不受数据量大小的限制,本来就是面向外存设计,而且一次分堆就能解决,时间基本上呈线性增加。

 

四、  SPL双维表与单维表对比测试

1.  单维表

编写单维表测试SPL脚本如下:


A

1

=now()

2

=elapse(date("2008-01-01"),1500)

3

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

4

=file(path+"trade.ctx").create().cursor@m(outid,receiveid,amount;tradedate>=A2;4)

5

=A4.joinx@u(outid,A3:accountid,state;4000000)

6

=A5.groups(state;sum(amount):amount)

7

=interval@s(A1,now())

 

2.  双维表

编写双维表测试SPL脚本如下


A

1

=now()

2

=elapse(date("2008-01-01"),1500)

3

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

4

=file(path+"trade.ctx").create().cursor@m(outid,receiveid,amount;tradedate>=A2;4)

5

=A4.joinx@u(outid,A3:accountid,state:out_state;receiveid,A3:accountid,state:receive_state;4000000)

6

=A5.groups(out_state;sum(amount):amount)

7

=interval@s(A1,now())

 

3.  测试结果及分析

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

事实表过滤后记录数

10亿

12亿

14亿

16亿

单维表

500

614

664

782

双维表

1146

1375

1501

1957

双维表比单维表多了一倍的关联计算量,运算时间也仅仅略多于一倍,也是呈线性增加的,不会发生完全不可控的局面。

 

 


以下是广告时间

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



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