查看原文
其他

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

乾学院 2022-12-10
在事实表与维表的关联查询时,常常会遇到需要对维表的数据进行过滤或者针对维表做计算的情况,这时可以有两种处理方式:
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开源啦!

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

特地开通了交流群(群完全免费,不广告不卖课)

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

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

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存