一、  问题背景与实用场景

SQL中JOIN的性能是个老大难问题,特地是关联表较多时,计算性能会急剧下降。

SQL实现JOIN个别是采纳HASH分堆的方法,即先计算关联键的HASH值,再将雷同HASH值的记录放到一起再做遍历比照。每一个JOIN都要做一轮这样的运算。

如果数据量绝对于内存并不是很大,能够当时全副加载到内存中,那么能够利用内存指针的机制,当时把关联关系建设好。这样做运算时就不用再做HASH与比照运算了。具体来说,就是在数据加载时一次性把HASH和比照运算做完,用指针形式保留关联后果,而后每次运算能够间接援用到关联记录,从而进步运算的性能。

可怜的是,SQL没有指针数据类型,无奈实现这个优化逻辑,即便数据量能够在内存中装下,也很难利用预关联技巧提速,基于SQL的内存数据库也大都有这个毛病。而SPL有指针数据类型,就能够实现这种机制。

咱们上面来测试一下SQL实现单表计算和多表关联计算的差距,再用SPL利用预关联技巧同样做一遍,看一下两者的差距比照。

二、  测试环境

采纳TPCH规范生成的8张数据表,共50G数据(要小到能放进内存)。TPCH数据表的构造网上有很多介绍,这里就不再赘述了。

测试机有两个Intel2670 CPU,主频2.6G,共16核,内存128G,SSD固态硬盘。

因为 lineitem 表数据量太大,这台服务器内存不足以将它装入,所以创立了一张表构造与它一样的表 orderdetail, 将数据量缩小到内存能装下,上面就用这张表来做测试。

为不便看出差距,上面测试都用单线程计算,多核并不起作用。

三、  SQL测试

这里用 Oracle 数据库作为 SQL 测试的代表,从orderdetail表里查问每年整机订单的总收入。

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

                     orderdetail,

                     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,

                     orderdetail,

                     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.  测试后果

两个查问语句都用了嵌套写法,Oracle主动优化后的计算性能比无嵌套时还要好一些(无嵌套时group by和select有可能会有反复计算)。

这两个测试数据是屡次运行后的后果,在测试中发现,Oracle 在第一次运行某查问时,往往比第 2、3... 主要慢很多,阐明在内存大于数据量时,数据库能够把全副数据都缓存进内存(Oracle的缓存很强),所以咱们取屡次运行中最快那一次的工夫,这样就简直没有硬盘读取工夫,仅是运算工夫。

同时,在下面两组测试中,过滤条件始终都为真,也就是没有对数据产生本质过滤,两个查问都波及orderdetail表的全副记录,计算规模是相当的。

从测试后果能够看出,六表关联比两表关联慢了167/26=6.4倍!性能降落十分多。排除掉硬盘工夫后,这里减少的工夫次要就是表间关联以及针对关联表字段的判断,而这些判断非常简单,所以大部分工夫耗费在表间关联上。

这个测试表明,SQL的JOIN性能的确很差。

四、  SPL预关联测试

1.   预关联

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

脚本中前7行别离将7个组表读入内存,生成内表,并设成全局变量。后5行实现表间连接。在SPL服务器启动时,就先运行此脚本,实现环境筹备。

咱们来看看预关联后,内存中表对象的数据结构,以orderdetail为例:

图中只列了orderdetail的第一条记录的预关联状况,其它记录与此相似。限于版面宽度,各表只列出了局部字段。

2.  两表关联

编写SPL脚本如下:

3.   六表关联

编写SPL脚本如下:

预关联后,SPL代码也非常简单,关联表的字段间接能够作为本表字段的子属性拜访,很易于了解。

4.  运行后果

六表关联仅仅比两表关联慢2倍,基本上就是减少的计算量(援用这些关联表字段)的工夫,而因为有了预关联,关联运算自身不再耗费工夫。

五、   论断

测试后果汇总:

六表关联比两表关联,SQL慢了6.4倍,阐明SQL解决JOIN耗费CPU很大,性能升高显著。而采纳预关联机制后的SPL只慢2倍,多JOIN几个表不再呈现显著的性能降落。

在进行关联表较多的查问时,如果内存大到足以将数据全副读入内存(内存数据库的利用场景),应用预关联技术将极大地晋升计算性能!而关系数据库(包含内存数据库)用SQL语言则无奈实现这一优化技术。