在事实表与维表的关联查询时，常常会遇到需要对维表的数据进行过滤或者针对维表做计算的情况，这时可以有两种处理方式：
1、先做关联（如果是内存则可以是预关联），再对关联后的事实表进行过滤。就象在《性能优化技巧：预关联》和《性能优化技巧：外键序号化》中做的那样。
2、先对维表进行过滤，再与事实表做关联。我们知道，建立关联时需要维表有索引，过滤之后，原先的索引不再可用了，需要重建索引来产生新的索引。
这两种方式孰优孰劣，不能一概而论，应当和维表与事实表的数据规模对比有关。下面我们通过实验来探讨一下这些性能优化技巧的效果。
一、测试环境
采用TPCH标准生成的8张数据表，共50G数据。TPCH数据表的结构网上有很多介绍，这里就不再赘述了。
测试机有两个Intel2670 CPU，主频2.6G，共16核，内存128G，SSD固态硬盘。
为方便看出差距，下面测试都用单线程计算，多核不起作用。
二、数据表全内存
所谓全内存，就是预先把要用到的数据表全都加载到内存里。我们选择customer作为维表，共750万条记录；用orders作为事实表，共7500万条记录。
查询时对维表的过滤条件是left(C_NAME,4)!="shen" && C_NATIONKEY>-1 && C_ACCTBAL>bal，求满足这些条件的订单总价。其中前两个条件总是为真(为了增加维表过滤的计算量，以增强实验的对比效果)，bal是个参数，用来测试维表过滤后不同数据规模下的效果。

1.   预关联

我们先看预关联后的情况，编写SPL脚本如下：
A
1>customer=file("/home/ctx/customer.ctx").open().memory().keys@i(C_CUSTKEY)
2>orders=file("/home/ctx/orders.ctx").open().memory()
3=orders.switch(O_CUSTKEY,customer)
4=now()
5=orders.select(left(O_CUSTKEY.C_NAME,4)!="shen" && O_CUSTKEY.C_NATIONKEY>-1 && O_CUSTKEY.C_ACCTBAL>bal)
6=A5.sum(O_TOTALPRICE)
7=interval@s(A4,now())
A1中读入维表并创建索引，A2中读入事实表，A3中进行预关联，这些时间都不计入测试时间，从A4才开始计时。

2.  重建索引

编写SPL脚本如下：
A
1>customer=file("/home/ctx/customer.ctx").open().memory().keys@i(C_CUSTKEY)
2>orders=file("/home/ctx/orders.ctx").open().memory()
3=now()
4=customer.select(left(C_NAME,4)!="shen" && C_NATIONKEY>-1 && C_ACCTBAL>bal).derive@o().keys@i(C_CUSTKEY)
5=orders.switch@i(O_CUSTKEY,A4)
6=A5.sum(O_TOTALPRICE)
7=interval@s(A3,now())
A4中customer过滤后再重建索引，A5中进行关联。

3.   复用索引

SPL支持在过滤后复用已有的索引，只需将上述A4单元格脚本改为：
=customer.select@i(left(C_NAME,4)!="shen" && C_NATIONKEY>-1 && C_ACCTBAL>bal)
select加选项@i表示复用customer原来的索引。

4.   外键序号化

预加载数据表时加载序号化处理过的组表customer_xh.ctx和orders_xh.ctx。
编写SPL脚本如下：
A
1>customer=file("/home/ctx/customer_xh.ctx").open().memory()
2>orders=file("/home/ctx/orders_xh.ctx").open().memory()
3=now()
4=orders.switch@i(O_CUSTKEY,customer:#)
5=A4.select(left(O_CUSTKEY.C_NAME,4)!="shen" && O_CUSTKEY.C_NATIONKEY>-1 && O_CUSTKEY.C_ACCTBAL>bal)
6=A5.sum(O_TOTALPRICE)
7=interval@s(A3,now())
序号化关联不需要索引，所以A1中不创建索引。A4中用customer:#表示用O_CUSTKEY的值与customer的行号关联。

5.  序号化后对位序列

预加载数据表时加载序号化处理过的组表customer_xh.ctx和orders_xh.ctx。
编写SPL脚本如下：
A
1>customer=file("/home/ctx/customer_xh.ctx").open().memory()
2>orders=file("/home/ctx/orders_xh.ctx").open().memory()
3=now()
4=customer.(left(C_NAME,4)!="shen" && C_NATIONKEY>-1 && C_ACCTBAL>bal)
5=orders.select(A4(O_CUSTKEY))
6=A5.sum(O_TOTALPRICE)
7=interval@s(A3,now())
在A4中用customer.(过滤条件) 算出一个与记录数等长的、值为true或false的序列，我们称之为对位序列；orders表中的O_CUSTKEY已经序号化处理过，它的值对应于customer的记录行号，所以在A5就可以用A4(O_CUSTKEY)来判断orders中此行数据是否满足过滤条件。

6.   测试结果与分析

实验获得测试结果如下(单位：秒)：
维表过滤后记录数716万613万477万273万68万
预关联4139383735
重建索引3934292519
复用索引3531272317
外键序号化5351494846
对位序列2523211916
这个实验中，维表数据记录750万行，事实表orders数据记录7500万行，是维表的10倍。
在预关联和外键序号化测试中，采用的是先关联后再过滤的处理方式，复杂的过滤计算要在事实表的行上进行，也就是说过滤计算量是直接过滤维表的10倍！所以整个查询的运行时间是最长的。预关联与外键序号化相比，在查询时，前者会省去关联这一步，所以比后者速度快。
在重建索引和复用索引测试中，采用的是先对维表过滤后再与事实表关联的处理方式，复杂的过滤计算只在维表的行上进行，所以比预关联和外键序号化要快。复用索引与重建索引相比，过滤、关联、求和的计算量相同，但会在创建索引这一步上节约时间，所以查询速度也更快。随着维表过滤后的数据规模越来越小，重建索引的时间也会减少，整体差距就会变小。
在对位序列测试中，过滤计算也是只在维表的行上进行，计算出对位序列后，只对事实表进行一次过滤，而不用与事实表关联，不用建索引也不用计算hash值，所以速度是最快的！
三、维表内存、事实表外存
这次我们选择orders作为维表，共7500万条记录；用lineitem作为事实表，共3亿条记录。
查询时对维表的过滤条件是left(O_ORDERPRIORITY,2)!="9-" && O_ORDERSTATUS!="A" && O_ORDERDATE>date("1990-01-01") && O_TOTALPRICE>price，求满足这些条件的订单总价。其中前三个条件总是为真(为了增加维表过滤的计算量，以增强实验的对比效果)，price是个参数，用来测试维表过滤后不同数据规模下的效果。

1.   关联后再过滤

我们先看关联后再过滤的情况，编写SPL脚本如下：
A
1>orders=file("/home/ctx/orders.ctx").open().memory().keys@i(O_ORDERKEY)
2=now()
3=file("/home/ctx/lineitem.ctx").open().cursor(L_ORDERKEY,L_EXTENDEDPRICE)
4=A3.switch@i(L_ORDERKEY,orders)
5=A4.select(left(L_ORDERKEY.O_ORDERPRIORITY,2)!="9-" && L_ORDERKEY.O_ORDERSTATUS!="A" && L_ORDERKEY.O_ORDERDATE>date("1990-01-01") && L_ORDERKEY.O_TOTALPRICE>price)
6=A5.total(sum(L_EXTENDEDPRICE))
7=interval@s(A2,now())
A1中读入维表并创建索引，这不计入测试时间，从A2才开始计时。
由于事实表很大，使用游标读取数据，并与维表关联后再过滤。

2.   重建索引

编写SPL脚本如下：
A
1>orders=file("/home/ctx/orders.ctx").open().memory().keys@i(O_ORDERKEY)
2=now()
3=orders.select(left(O_ORDERPRIORITY,2)!="9-" && O_ORDERSTATUS!="A" && O_ORDERDATE>date("1990-01-01") && O_TOTALPRICE>price).derive@o().keys@i(O_ORDERKEY)
4=file("/home/ctx/lineitem.ctx").open().cursor(L_ORDERKEY,L_EXTENDEDPRICE).switch@i(L_ORDERKEY,A3)
5=A4.total(sum(L_EXTENDEDPRICE))
6=interval@s(A2,now())
A3中orders过滤后再重建索引。

3.   复用索引

只需将上述A3单元格脚本改为：
=orders.select@i(left(O_ORDERPRIORITY,2)!="9-" && O_ORDERSTATUS!="A" && O_ORDERDATE>date("1990-01-01") && O_TOTALPRICE>price)
select加选项@i表示复用orders原来的索引。

4.  外键序号化

预加载数据表时加载序号化处理过的组表orders_xh.ctx，且不用创建索引。
编写SPL脚本如下：
A
1>orders=file("/home/ctx/orders_xh.ctx").open().memory()
2=now()
3=file("/home/ctx/lineitem_xh.ctx").open().cursor(L_ORDERKEY,L_EXTENDEDPRICE)
4=A3.switch@i(L_ORDERKEY,orders:#)
5=A4.select(left(L_ORDERKEY.O_ORDERPRIORITY,2)!="9-" && L_ORDERKEY.O_ORDERSTATUS!="A" && L_ORDERKEY.O_ORDERDATE>date("1990-01-01") && L_ORDERKEY.O_TOTALPRICE>price)
6=A5.total(sum(L_EXTENDEDPRICE))
7=interval@s(A2,now())
A4中用orders:#表示用L_ORDERKEY的值与orders的行号关联。

5.  序号化后对位序列

预加载数据表时加载序号化处理过的组表orders_xh.ctx，且不用创建索引。
编写SPL脚本如下：
A
1>orders=file("/home/ctx/orders_xh.ctx").open().memory()
2=now()
3=orders.(left(O_ORDERPRIORITY,2)!="9-" && O_ORDERSTATUS!="A" && O_ORDERDATE>date("1990-01-01") && O_TOTALPRICE>price)
4=file("/home/ctx/lineitem_xh.ctx").open().cursor(L_ORDERKEY,L_EXTENDEDPRICE).select(A3(L_ORDERKEY))
5=A4.total(sum(L_EXTENDEDPRICE))
6=interval@s(A2,now())
查询实现原理与全内存时相同。

6.  测试结果与分析

实验获得测试结果如下(单位：秒)：
维表过滤后记录数6443万4995万3590万2249万428万
关联后过滤10198979492
重建索引10298927353
复用索引8582777457
外键序号化7978767572
对位序列5349474339
这个实验中，维表数据记录7500万行，事实表lineitem数据记录3亿行，是维表的4倍。
查询过程的计算原理与上一节分析的相同，但事实表与维表的数据规模对比倍数下降，由10倍变为了4倍，外键序号化与复用索引相比，速度差距不是很明显了，甚至在维表过滤掉的记录较少时，因为序号化关联比hash值比对关联更占优势，查询速度还略快。
四、总结
根据前面的测试结果和分析，对于维表有过滤或计算时的查询，应该采用何种优化技巧来获得最佳性能，我们作如下总结。
1、事实表数据记录比维表小
1) 如果数据表能够全部装进内存，采用预关联。
2) 如果不能装进内存，但对维表和外键做了序号化处理，采用先序号化关联再对事实表过滤。
3) 如果不能装进内存，又没有做序号化处理，采用先按外键值关联再对事实表过滤。
2、事实表数据记录远大于维表
1) 如果数据表做了序号化处理，采用对位序列技术。
2) 如果数据表没做序号化处理，采用先对维表过滤并复用索引，再按外键值关联查询。
3、事实表数据记录比维表大得不多
1) 如果数据表做了序号化处理，采用对位序列技术。
2) 如果数据表没做序号化处理，采用预关联(能装进内存的情况下)还是复用索引，建议最好是实测一下。
更多性能优化技巧，可在底部“阅读原文”中查看
重磅！开源SPL交流群成立了
简单好用的SPL开源啦！
为了给感兴趣的小伙伴们提供一个相互交流的平台，
特地开通了交流群（群完全免费，不广告不卖课）
需要进群的朋友，可长按扫描下方二维码
本文感兴趣的朋友，请转到阅读原文去收藏 ^_^
继续阅读
阅读原文