性能优化技巧：维表过滤或计算时的关联

在事实表与维表的关联查询时，常常会遇到需要对维表的数据进行过滤或者针对维表做计算的情况，这时可以有两种处理方式：

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中读入维表并创建索引，A2中读入事实表，A3中进行预关联，这些时间都不计入测试时间，从A4才开始计时。

2. 重建索引

编写SPL脚本如下：

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中不创建索引。A4中用customer:#表示用O_CUSTKEY的值与customer的行号关联。

5. 序号化后对位序列

预加载数据表时加载序号化处理过的组表customer_xh.ctx和orders_xh.ctx。

编写SPL脚本如下：

在A4中用customer.(过滤条件) 算出一个与记录数等长的、值为true或false的序列，我们称之为对位序列；orders表中的O_CUSTKEY已经序号化处理过，它的值对应于customer的记录行号，所以在A5就可以用A4(O_CUSTKEY)来判断orders中此行数据是否满足过滤条件。

6. 测试结果与分析

实验获得测试结果如下(单位：秒)：

这个实验中，维表数据记录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中读入维表并创建索引，这不计入测试时间，从A2才开始计时。

由于事实表很大，使用游标读取数据，并与维表关联后再过滤。

2. 重建索引

编写SPL脚本如下：

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脚本如下：

A4中用orders:#表示用L_ORDERKEY的值与orders的行号关联。

5. 序号化后对位序列

预加载数据表时加载序号化处理过的组表orders_xh.ctx，且不用创建索引。

编写SPL脚本如下：

查询实现原理与全内存时相同。

6. 测试结果与分析

实验获得测试结果如下(单位：秒)：

这个实验中，维表数据记录7500万行，事实表lineitem数据记录3亿行，是维表的4倍。

查询过程的计算原理与上一节分析的相同，但事实表与维表的数据规模对比倍数下降，由10倍变为了4倍，外键序号化与复用索引相比，速度差距不是很明显了，甚至在维表过滤掉的记录较少时，因为序号化关联比hash值比对关联更占优势，查询速度还略快。

四、 总结

根据前面的测试结果和分析，对于维表有过滤或计算时的查询，应该采用何种优化技巧来获得最佳性能，我们作如下总结。

1、事实表数据记录比维表小

1) 如果数据表能够全部装进内存，采用预关联。

2) 如果不能装进内存，但对维表和外键做了序号化处理，采用先序号化关联再对事实表过滤。

3) 如果不能装进内存，又没有做序号化处理，采用先按外键值关联再对事实表过滤。

2、事实表数据记录远大于维表

1) 如果数据表做了序号化处理，采用对位序列技术。

2) 如果数据表没做序号化处理，采用先对维表过滤并复用索引，再按外键值关联查询。

3、事实表数据记录比维表大得不多

1) 如果数据表做了序号化处理，采用对位序列技术。

2) 如果数据表没做序号化处理，采用预关联(能装进内存的情况下)还是复用索引，建议最好是实测一下。