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

一、  问题背景与适用场景

在《性能优化技巧：部分预关联》一文中，我们介绍了将维表内存化并预关联的技术，但事实表与维表关联时，仍需进行hash计算和比对，怎么提高这一步的性能呢？我们今天再介绍一种优化技巧：外键序号化。

外键序号化的思路是，如果维表的主键是从1开始的自然数(也就是记录所在行号)，那么就可以用键值直接按行号定位维表记录，而不再需要计算和比对HASH值了，从而加快与维表关联的速度，进一步提升性能。而且，直接用序号定位，还不需要建立索引，占用内存也会小很多。

我们下面来介绍如何在SPL中使用外键序号化技巧，并使用上文的测试环境，针对同一个查询问题，以序号化后的数据与之前的数据做对照实验，验证序号化的性能提升效果。

二、  序号化准备

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

1）新建一个键值-序号对应表，保存维表的键值和自然序号的对应关系；

2）把维表的键值替换为自然序号，得到一个新的维表文件；

3）把事实表里的外键值修改为序号，修改的依据是键值-序号对应表，修改后得到一个新的事实表。

本次实验要用到的三张维表分别是supplier、part与orders，事实表是lineitem，下面分别对它们实现序号化。

1.  supplier序号化

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

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

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

3.  orders序号化

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

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

4.  lineitem序号化

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

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

加载维表并建索引。

编写SPL测试脚本如下：

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

2.  序号化数据测试

维表预加载SPL脚本如下：

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

编写SPL测试脚本如下：

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

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

3.  分析与结论

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

四、  进一步说明

序号化的字段必须是维表的主键，但不限主键字段的数据类型，整数、字符串、日期、时间等等都可以序号化。对于多主键维表，可以新增一个序号字段，建立多个键值与序号的对应关系表，并依此对事实表进行序号化。

一般来说，外键序号化技巧可以很方便地适用于历史数据的查询，只需对历史数据进行一次序号化就可以使用了，键值与序号对应关系表也无须再保留。

但外键序号化技巧同样适用于有新增数据的查询，只是会多增加些步骤。

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

1）先取得维表新增加的记录，追加键值与序号对应关系表；

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

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

2.  仅事实表有新增数据

在维表数据保持不变的情况下，仅需进行上述第3)步即可。

处理好新增数据以后，就可以使用外键序号化关联查询技巧了。