性能优化技巧：部分预关联
一、 问题背景与适用场景
在《性能优化技巧：预关联》中，我们测试了将数据表事先全部加载进内存并做好关联后的查询性能优化问题，但如果内存不够大，不能将维表和事实表全部装入，那怎么办呢？此时，可以将维表预先装入内存，建好索引，实现维表部分的预关联，省去一半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. 测试结果
两表关联 |
六表关联 |
|
运行时间(秒) |
235 |
2669 |
这两个测试数据依然是多次运行后取最快的那次。
从测试结果可以看出，六表关联比两表关联慢了2669/235=11.4倍！性能下降非常多。
三、 SPL部分预关联测试
1. 部分预关联
实现预关联的SPL脚本如下：
A |
|
1 |
>env(region,file(path+"region.ctx").open().memory().keys@i(R_REGIONKEY)) |
2 |
>env(nation,file(path+"nation.ctx").open().memory().keys@i(N_NATIONKEY)) |
3 |
>env(supplier,file(path+"supplier.ctx").open().memory().keys@i(S_SUPPKEY)) |
4 |
>env(customer,file(path+"customer.ctx").open().memory().keys@i(C_CUSTKEY)) |
5 |
>env(part,file(path+"part.ctx").open().memory().keys@i(P_PARTKEY)) |
6 |
>env(orders,file(path+"orders.ctx").open().memory().keys@i(O_ORDERKEY)) |
7 |
>nation.switch(N_REGIONKEY,region) |
8 |
>customer.switch(C_NATIONKEY,nation) |
9 |
>supplier.switch(S_NATIONKEY,nation) |
10 |
>orders.switch(O_CUSTKEY,customer) |
脚本中前6行分别将6个维表读入内存，生成内表，并建好索引，再设成全局变量。后4行完成维表间连接。在SPL服务器启动时，就先运行此脚本，完成环境准备。
2. 两表关联
编写SPL脚本如下：
A |
|
1 |
=file("/home/btx/lineitem.btx").cursor@tb(L_PARTKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE) |
2 |
=A1.switch@i(L_PARTKEY,part).select(len(L_PARTKEY.P_TYPE)>2) |
3 |
=A2.groups(year(L_SHIPDATE):l_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue) |
临时装载需要用游标，然后在游标上进行关联，之后的写法和全内存差不多。
3. 六表关联
编写SPL脚本如下：
A |
|
1 |
=file("/home/btx/lineitem.btx").cursor@tb(L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE) |
2 |
=A1.switch@i(L_ORDERKEY,orders;L_PARTKEY,part;L_SUPPKEY,supplier) |
3 |
=A2.select(len(L_PARTKEY.P_TYPE)>2 && L_ORDERKEY.O_CUSTKEY.C_NATIONKEY.N_NAME!=null && L_SUPPKEY.S_NATIONKEY.N_NAME!=null && L_SUPPKEY.S_SUPPKEY>0 ) |
4 |
=A3.groups(year(L_SHIPDATE):l_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue) |
类似地，建立好游标及关联后的写法和全内存差不多，一样非常简洁易懂。
4. 运行结果
两表关联 |
六表关联 |
|
运行时间(秒) |
266 |
472 |
六表关联仅仅比两表关联慢1.8倍，增加的时间主要用于事实表lineitem中L_ORDERKEY和L_SUPPKEY字段的关联以及增加的过滤条件计算量（引用这些关联表字段）的时间。因为有了部分预关联，维表之间关联运算本身不再消耗时间，而维表与lineitem表关联的时间，也因为事先建好索引而提高了性能（可以减少一半的hash计算）。
四、 结论
测试结果汇总：
运行时间(秒) |
两表关联 |
六表关联 |
性能降低倍数 |
SQL |
235 |
2669 |
11.4 |
SPL预关联 |
266 |
472 |
1.8 |
六表关联比两表关联，SQL慢了11.4倍，说明SQL处理JOIN消耗CPU很大，性能降低明显。而采用部分预关联机制后的SPL只慢1.8倍，多JOIN几个表影响不大，性能不会明显下降。
在进行关联表较多的查询时，如果内存大到足以将除事实表之外的维表数据全部读入内存，使用部分预关联技术依然能有效地提升计算性能！而关系数据库用在关联表很多的时候会发生数据库引擎不会优化的问题，导致性能下降很严重。
系列性能优化技巧：
性能优化技巧：遍历复用
性能优化技巧：TopN
性能优化技巧：预关联
性能优化技巧：部分预关联
性能优化技巧：外键序号化
性能优化技巧：维表过滤或计算时的关联
性能优化技巧：有序归并
性能优化技巧：有序定位关联提速主子关联后的过滤
性能优化技巧：附表
性能优化技巧：大维表查找
性能优化技巧：单边分堆
性能优化技巧：有序分组
性能优化技巧：后半有序分组
性能优化技巧：前半有序时的排序
