性能优化技巧：单边分堆
一、 问题背景与适用场景
在《性能优化技巧：小事实表与大维表关联》中，我们尝试了小事实表与大维表关联时的性能优化方法，该方法利用了小事实表可以装入内存的特点，将关联键汇集排序后到大维表中查找，避免了遍历大维表的动作。如果事实表与维表都大到不能装载到内存时，这个办法就不再有效了，那么，还有什么办法提高性能呢？
SQL的方案是对两个表做HASH分堆，拆小到内存可以放下的地步，分别写入外存，然后再分别读入进行内存关联。如果运气不好，拆出来的某个堆还是太大，就需要做二次HASH。同时，两个表都需要做一遍HASH分堆动作，也就是需要把所有数据都缓存一遍。
如果维表是有序存放的，我们就可以将平均分段，由于有序存储，所以可以计算出每一段值的边界值，然后再用这个边界值将事实表再分堆。这样，维表本身由于有序存储，可以直接按段读取，而不需要再分堆；只有事实表被缓存出去，也就是只有一个表被分堆缓存，所以这种办法可以称为单边方案。而且，由于维表可以被相对平均分段，不可能象HASH方法那样出现运气不好导致某堆太大的情况，一次分堆一定能解决问题，性能将得到保障。
SPL提供了这种关联方法，下面我们实例测试一下，并且与使用HASH JOIN算法的Oracle对比。
二、 测试环境与任务
测试机有两个Intel2670 CPU，主频2.6G，共16核，内存64G，SSD固态硬盘。在此机上安装虚拟机来测试，设置虚拟机为16核、8G内存。
在虚拟机上创建维表account，共三个字段accountid、name、state，总记录共10亿行。创建事实表trade，总记录共16亿行，共四个字段tradedate、outid(转出帐户)、receiveid(接收帐户)、amount(转帐金额)。account表中的accountid是事实表中outid和receiveid的外键，都是一对多的关系。
在《性能优化技巧：小事实表与大维表关联》中我们测试的是outid、receiveid两个字段都要与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").open() |
4 |
=file(path+"trade.ctx").open().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").open() |
4 |
=file(path+"trade.ctx").open().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. 双维表
A |
|
1 |
=now() |
2 |
=elapse(date("2008-01-01"),1500) |
3 |
=file(path+"account.ctx").open() |
4 |
=file(path+"trade.ctx").open().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 |
双维表比单维表多了一倍的关联计算量，运算时间也仅仅略多于一倍，也是呈线性增加的，不会发生完全不可控的局面。
系列性能优化技巧：
性能优化技巧：遍历复用
性能优化技巧：TopN
性能优化技巧：预关联
性能优化技巧：部分预关联
性能优化技巧：外键序号化
性能优化技巧：维表过滤或计算时的关联
性能优化技巧：有序归并
性能优化技巧：有序定位关联提速主子关联后的过滤
性能优化技巧：附表
性能优化技巧：大维表查找
性能优化技巧：单边分堆
性能优化技巧：有序分组
性能优化技巧：后半有序分组
性能优化技巧：前半有序时的排序
英文版