性能优化技巧：维表过滤或计算时的关联
在事实表与维表的关联查询时，常常会遇到需要对维表的数据进行过滤或者针对维表做计算的情况，这时可以有两种处理方式：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脚本如下：
A1>customer=file("/home/ctx/customer.ctx").create().memory().keys@i(C_CUSTKEY)2>orders=file("/home/ctx/orders.ctx").create().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脚本如下：
A1>customer=file("/home/ctx/customer.ctx").create().memory().keys@i(C_CUSTKEY)2>orders=file("/home/ctx/orders.ctx").create().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脚本如下：
A1>customer=file("/home/ctx/customer_xh.ctx").create().memory()2>orders=file("/home/ctx/orders_xh.ctx").create().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脚本如下：
A1>customer=file("/home/ctx/customer_xh.ctx").create().memory()2>orders=file("/home/ctx/orders_xh.ctx").create().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脚本如下：
A1>orders=file("/home/ctx/orders.ctx").create().memory().keys@i(O_ORDERKEY)2=now()3=file("/home/ctx/lineitem.ctx").create().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脚本如下：
A1>orders=file("/home/ctx/orders.ctx").create().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").create().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脚本如下：
A1>orders=file("/home/ctx/orders_xh.ctx").create().memory()2=now()3=file("/home/ctx/lineitem_xh.ctx").create().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脚本如下：
A1>orders=file("/home/ctx/orders_xh.ctx").create().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").create().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) 如果数据表没做序号化处理，采用预关联(能装进内存的情况下)还是复用索引，建议最好是实测一下。