查看原文
其他

性能优化技巧:外键序号化

乾学院 2022-11-07

一、问题背景与适用场景

在《性能优化技巧:部分预关联》一文中,我们介绍了将维表内存化并预关联的技术,但事实表与维表关联时,仍需进行hash计算和比对,怎么提高这一步的性能呢?我们今天再介绍一种优化技巧:外键序号化。
外键序号化的思路是,如果维表的主键是从1开始的自然数(也就是记录所在行号),那么就可以用键值直接按行号定位维表记录,而不再需要计算和比对HASH值了,从而加快与维表关联的速度,进一步提升性能。而且,直接用序号定位,还不需要建立索引,占用内存也会小很多。
我们下面来介绍如何在SPL中使用外键序号化技巧,并使用上文的测试环境,针对同一个查询问题,以序号化后的数据与之前的数据做对照实验,验证序号化的性能提升效果。

二、序号化准备

要使用外键序号化技巧,必须要保证维表的主键值正好是序号(记录行号),而实际业务中维表的主键值往往不是这样,所以要先将维表的主键转换成序号。转换的方法如下:

1)新建一个键值-序号对应表,保存维表的键值和自然序号的对应关系;

2)把维表的键值替换为自然序号,得到一个新的维表文件;

3)把事实表里的外键值修改为序号,修改的依据是键值-序号对应表,修改后得到一个新的事实表。
本次实验要用到的三张维表分别是supplier、part与orders,事实表是lineitem,下面分别对它们实现序号化。

1.  supplier序号化

实现序号化的SPL脚本如下:

A
1=file("/home/tbl/supplier.tbl").cursor(;,"|").new(_1:S_SUPPKEY, _2:S_NAME, _3:S_ADDRESS, _4:S_NATIONKEY, _5:S_PHONE, _6:S_ACCTBAL).fetch().derive(#:xh)
2=file("/home/btx/supplier_xh_key.btx").export@b(A1,S_SUPPKEY,xh)
3=A1.new(xh:S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL)
4=file("/home/ctx/supplier_xh.ctx").create(#S_SUPPKEY, S_NAME, S_ADDRESS, S_NATIONKEY, S_PHONE, S_ACCTBAL)
5>A4.append(A3.cursor())

A1   从原始数据文件supplier.tbl中读出数据,并用derive函数新增加一列xh,用行号作为字段值

A2   将A1中的S_SUPPKEY、xh两字段输出到集文件supplier_xh_key.btx中,生成键值与序号对应关系表,以备序号化事实表时使用
A3   以xh字段值代替S_SUPPKEY字段,重构supplier序表
A4   创建序号化后的组表文件supplier_xh.ctx
A5   将重构后的supplier序表保存到组表文件supplier_xh.ctx中

2.  part序号化

实现序号化的SPL脚本如下:

A
1=file("/home/tbl/part.tbl").cursor(;,"|").new(_1:P_PARTKEY, _2:P_NAME, _3:P_MFGR, _4:P_BRAND, _5:P_TYPE, _6:P_SIZE, _7:P_CONTAINER, _8:P_RETAILPRICE).fetch().derive(#:xh)
2=file("/home/btx/part_xh_key.btx").export@b(A1,P_PARTKEY,xh)
3=A1.new(xh:P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE)
4=file("/home/ctx/part_xh.ctx").create(#P_PARTKEY, P_NAME,P_MFGR, P_BRAND, P_TYPE, P_SIZE, P_CONTAINER, P_RETAILPRICE)
5>A4.append(A3.cursor())

脚本原理与supplier表序号化相同,生成的键值与序号对应关系表为part_xh_key.btx,序号化后的组表文件叫part_xh.ctx。

3.  orders序号化

实现序号化的SPL脚本如下:

A
1=file("/home/tbl/orders.tbl").cursor(;,"|").new(_1:O_ORDERKEY, _2:O_CUSTKEY, _3:O_ORDERSTATUS,_4:O_TOTALPRICE,_5:O_ORDERDATE, _6:O_ORDERPRIORITY,_7:O_SHIPPRIORITY).fetch().derive(#:xh)
2=file("/home/btx/orders_xh_key.btx").export@b(A1,O_ORDERKEY,xh)
3=A1.new(xh:O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE, O_ORDERDATE,O_ORDERPRIORITY,O_SHIPPRIORITY)
4=file("/home/ctx/orders_xh.ctx").create(#O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_TOTALPRICE,O_ORDERDATE,O_ORDERPRIORITY,O_SHIPPRIORITY)
5>A4.append(A3.cursor())

脚本原理与supplier表序号化相同,生成的键值与序号对应关系表为orders_xh_key.btx,序号化后的组表文件叫orders_xh.ctx。

4.  lineitem序号化

实现序号化的SPL脚本如下:

A
1=file("/home/tbl/lineitem.tbl").cursor(;,"|").new(_1:L_ORDERKEY, _4:L_LINENUMBER, _2:L_PARTKEY, _3:L_SUPPKEY,_5:L_QUANTITY, _6:L_EXTENDEDPRICE,_7:L_DISCOUNT, _8:L_TAX, _9:L_RETURNFLAG, _10:L_LINESTATUS,_11:L_SHIPDATE,_12:L_COMMITDATE, _13:L_RECEIPTDATE,_14:L_SHIPINSTRUCT,_15:L_SHIPMODE, _16:L_COMMENT)
2=file("/home/btx/orders_xh_key.btx").import@b()
3=file("/home/btx/part_xh_key.btx").import@b()
4=file("/home/btx/supplier_xh_key.btx").import@b()
5=A1.switch(L_ORDERKEY,A2:O_ORDERKEY;L_PARTKEY,A3:P_PARTKEY;L_SUPPKEY,A4:S_SUPPKEY)
6=A5.run(L_ORDERKEY=L_ORDERKEY.xh, L_PARTKEY=L_PARTKEY.xh, L_SUPPKEY=L_SUPPKEY.xh)
7=file("/home/ctx/lineitem_xh.ctx").create(#L_ORDERKEY,#L_LINENUMBER,L_PARTKEY, L_SUPPKEY,L_QUANTITY, L_EXTENDEDPRICE,L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE, L_COMMITDATE,L_RECEIPTDATE,L_SHIPINSTRUCT, L_SHIPMODE, L_COMMENT;L_ORDERKEY)
8>A7.append(A6)

A1   创建读入lineitem原始数据的游标

A2/A3/A4   分别读入orders、part、supplier键值与序号对应关系表
A5   用L_ORDERKEY与orders对应关系表关联,用L_PARTKEY与part对应关系表关联,用L_SUPPKEY与supplier对应关系表关联
A6   用关联后的序号值替换键值生成新的游标
A7   创建序号化后的组表文件lineitem_xh.ctx
A8   将序号化后的游标数据写入组表lineitem_xh.ctx

三、序号化测试

1.   原始数据测试

维表预加载SPL脚本如下:

A
1>env(supplier,file("/home/ctx/supplier.ctx").open().memory().keys@i(S_SUPPKEY))
2>env(part,file("/home/ctx/part.ctx").open().memory().keys@i(P_PARTKEY))
3>env(orders,file("/home/ctx/orders.ctx").open().memory().keys@i(O_ORDERKEY))

加载维表并建索引。

编写SPL测试脚本如下:

A
1=file("/home/ctx/lineitem.ctx").open().cursor(L_ORDERKEY,L_PARTKEY, L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE)
2=A1.switch(L_ORDERKEY,orders;L_PARTKEY,part;L_SUPPKEY,supplier)
3=A2.select(L_ORDERKEY.O_TOTALPRICE>0 && L_PARTKEY.P_SIZE>0 && L_SUPPKEY.S_ACCTBAL<999999)
4=A3.groups(year(L_SHIPDATE):l_year; sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)

先运行维表预加载脚本,再运行测试脚本,得到测试脚本运行时间为450秒。

2.  序号化数据测试

维表预加载SPL脚本如下:

A
1>env(supplier,file("/home/ctx/supplier_xh.ctx").open().memory())
2>env(part,file("/home/ctx/part_xh.ctx").open().memory())
3>env(orders,file("/home/ctx/orders_xh.ctx").open().memory())

加载的是序号化后的维表,不需要建立索引。

编写SPL测试脚本如下:

A
1=file("/home/ctx/lineitem_xh.ctx").open().cursor(L_ORDERKEY,L_PARTKEY, L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE)
2=A1.switch(L_ORDERKEY,orders:#;L_PARTKEY,part:#;L_SUPPKEY,supplier:#)
3=A2.select(L_ORDERKEY.O_TOTALPRICE>0 && L_PARTKEY.P_SIZE>0 && L_SUPPKEY.S_ACCTBAL<999999)
4=A3.groups(year(L_SHIPDATE):l_year; sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)

注意:A2中关联时使用“:#”与维表关联,表示用事实表中的键值与维表的行号进行关联,假如键值是7,则直接与维表的第7行关联。

先运行维表预加载脚本,再运行测试脚本,得到测试脚本运行时间为269秒。

3.  分析与结论

在上面两组对照实验中,序号化后的数据与序号化前相比,维表的记录数、字段数完全相同,事实表的记录数与字段数也完全相同,仅有相关的键值换成了序号。查询过程相比,过滤条件完全相同,过滤后的参与计算的数据完全相同,也就是说过滤与分组求和所用的时间完全相同,所不同的仅有关联的方式(行号关联、键值的hash值比对关联),而运行时间减少了450-269=181秒,可见,外键序号化对性能提升效果十分显著。

四、进一步说明

序号化的字段必须是维表的主键,但不限主键字段的数据类型,整数、字符串、日期、时间等等都可以序号化。对于多主键维表,可以新增一个序号字段,建立多个键值与序号的对应关系表,并依此对事实表进行序号化。
一般来说,外键序号化技巧可以很方便地适用于历史数据的查询,只需对历史数据进行一次序号化就可以使用了,键值与序号对应关系表也无须再保留。
但外键序号化技巧同样适用于有新增数据的查询,只是会多增加些步骤。

1.  维表与事实表均有新增数据

1)先取得维表新增加的记录,追加键值与序号对应关系表;

2)再把新增记录追加到序号化后的维表,追加时依据键值与序号对应关系表;

3)再把事实表新增加的记录追加到序号化后的事实表,追加时依据键值与序号对应关系表;

2.  仅事实表有新增数据

在维表数据保持不变的情况下,仅需进行上述第3)步即可。
处理好新增数据以后,就可以使用外键序号化关联查询技巧了。

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



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

简单好用的SPL开源啦!

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

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

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

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

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

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