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

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

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万
预关联	41	39	38	37	35
重建索引	39	34	29	25	19
复用索引	35	31	27	23	17
外键序号化	53	51	49	48	46
对位序列	25	23	21	19	16

这个实验中，维表数据记录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万
关联后过滤	101	98	97	94	92
重建索引	102	98	92	73	53
复用索引	85	82	77	74	57
外键序号化	79	78	76	75	72
对位序列	53	49	47	43	39

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

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

四、总结

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

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

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

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

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

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

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

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

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

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

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

更多性能优化技巧，可在底部“阅读原文”中查看

重磅！开源SPL交流群成立了

简单好用的SPL开源啦！

为了给感兴趣的小伙伴们提供一个相互交流的平台，

特地开通了交流群（群完全免费，不广告不卖课）

需要进群的朋友，可长按扫描下方二维码

本文感兴趣的朋友，请转到阅读原文去收藏 ^_^