性能优化技巧：部分预关联

一、  问题背景与适用场景

在《性能优化技巧：预关联》中，我们测试了将数据表事先全部加载进内存并做好关联后的查询性能优化问题，但如果内存不够大，不能将维表和事实表全部装入，那怎么办呢？此时，可以将维表预先装入内存，建好索引，实现维表部分的预关联，省去一半hash计算。

我们下面再来测试一下这种场景，这次用数据量最大、内存装不下的lineitem表做测试，在SPL部分预关联中，将其它7张表预先装进内存，而lineitem在查询时才实时读入。

二、  SQL测试

依然用 Oracle 数据库作为 SQL 测试的代表，从lineitem表里查询每年零件订单的总收入。

1.  两表关联

查询的SQL语句如下：

select

       l_year,

       sum(volume) as revenue

from

       (

              select

                     extract(year from l_shipdate) as l_year,

                     (l_extendedprice * (1 - l_discount) ) as volume

              from

                     lineitem,

                     part

              where

                     p_partkey = l_partkey

                     and length(p_type)>2

       ) shipping

group by

       l_year

order by

       l_year;

2.   六表关联

查询的SQL语句如下：

select

       l_year,

       sum(volume) as revenue

from

       (

              select

                     extract(year from l_shipdate) as l_year,

                     (l_extendedprice * (1 - l_discount) ) as volume

              from

                     supplier,

                     lineitem,

                     orders,

                     customer,

                     part,

                     nation n1,

                     nation n2

              where

                     s_suppkey = l_suppkey

                     and p_partkey = l_partkey

                     and o_orderkey = l_orderkey

                     and c_custkey = o_custkey

                     and s_nationkey = n1.n_nationkey

                     and c_nationkey = n2.n_nationkey

                     and length(p_type) > 2

                     and n1.n_name is not null

                     and n2.n_name is not null

                     and s_suppkey > 0

       ) shipping

group by

       l_year

order by

       l_year;

3.   测试结果

这两个测试数据依然是多次运行后取最快的那次。

从测试结果可以看出，六表关联比两表关联慢了2669/235=11.4倍！性能下降非常多。

三、  SPL部分预关联测试

1.  部分预关联

实现预关联的SPL脚本如下：

脚本中前6行分别将6个维表读入内存，生成内表，并建好索引，再设成全局变量。后4行完成维表间连接。在SPL服务器启动时，就先运行此脚本，完成环境准备。

2.  两表关联

编写SPL脚本如下：

临时装载需要用游标，然后在游标上进行关联，之后的写法和全内存差不多。

3.    六表关联

编写SPL脚本如下：

类似地，建立好游标及关联后的写法和全内存差不多，一样非常简洁易懂。

4.   运行结果

六表关联仅仅比两表关联慢1.8倍，增加的时间主要用于事实表lineitem中L_ORDERKEY和L_SUPPKEY字段的关联以及增加的过滤条件计算量（引用这些关联表字段）的时间。因为有了部分预关联，维表之间关联运算本身不再消耗时间，而维表与lineitem表关联的时间，也因为事先建好索引而提高了性能（可以减少一半的hash计算）。

四、  结论

测试结果汇总：

六表关联比两表关联，SQL慢了11.4倍，说明SQL处理JOIN消耗CPU很大，性能降低明显。而采用部分预关联机制后的SPL只慢1.8倍，多JOIN几个表影响不大，性能不会明显下降。

在进行关联表较多的查询时，如果内存大到足以将除事实表之外的维表数据全部读入内存，使用部分预关联技术依然能有效地提升计算性能！而关系数据库用在关联表很多的时候会发生数据库引擎不会优化的问题，导致性能下降很严重。