性能优化技巧：大维表查找
一、 问题背景与适用场景
在主子表关联查询中，有时会遇到这样一种情况：按条件过滤后的事实表数据量很小，能够全部装载进内存或仅比内存略大一点；而要关联的维表数据量很大，比内存要大很多。这种时候，如果维表是按键有序存储时，因为事实表涉及的维表记录较少，可以一次性用二分查找方法找出来，而不必象HASH运算那样遍历这个大维表，从而提高运算性能。
SPL提供了这种方法，我们实例测试一下，并且与使用HASH JOIN算法的Oracle对比。
不过，因为要把事实表的关联键都取出来排序后到维表中去找，要一次性汇集尽量多的关联键值去维表中查找，而主要时间成本也消耗在这次查询中，其它关联本身的运算开销不大。所以，这种办法对于多线程是不敏感的，我们后面也测试一下并行时的情况。
二、 测试环境与任务
测试机有两个Intel2670 CPU，主频2.6G，共16核，内存64G，SSD固态硬盘。在此机上安装虚拟机来测试，设置虚拟机为16核、8G内存。
在虚拟机上创建维表account，共三个字段accountid、name、state，总记录共5亿行。创建事实表trade1、trade2、trade3，各有记录分别为30万、300万、1500万行，其表结构都相同，共四个字段tradedate、outid(转出帐户)、receiveid(接收帐户)、amount(转帐金额)。account表中的accountid是事实表中outid和receiveid的外键，都是一对多的关系。
测试任务为分别用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 |
四、 结论
从测试结果可以看出， 单线程情况下，SPL比Oracle的关联性能提高了很多倍，因为避免了对维表的遍历动作。
在此场景下并行时，SPL算法对并行不敏感，性能没有显著变化。ORACLE可以对并行有效，性能有所提高，但因为遍历的成本太高，并行后仍比SPL慢数倍。
系列性能优化技巧：
性能优化技巧：遍历复用
性能优化技巧：TopN
性能优化技巧：预关联
性能优化技巧：部分预关联
性能优化技巧：外键序号化
性能优化技巧：维表过滤或计算时的关联
性能优化技巧：有序归并
性能优化技巧：有序定位关联提速主子关联后的过滤
性能优化技巧：附表
性能优化技巧：大维表查找
性能优化技巧：单边分堆
性能优化技巧：有序分组
性能优化技巧：后半有序分组
性能优化技巧：前半有序时的排序
英文版