一、 問題背景與適用場(chǎng)景
SQL中JOIN的性能是個(gè)老大難問題，特別是關(guān)聯(lián)表較多時(shí)，計(jì)算性能會(huì)急劇下降。
SQL實(shí)現(xiàn)JOIN一般是采用HASH分堆的辦法，即先計(jì)算關(guān)聯(lián)鍵的HASH值，再將相同HASH值的記錄放到一起再做遍歷對(duì)比。每一個(gè)JOIN都要做一輪這樣的運(yùn)算。
如果數(shù)據(jù)量相對(duì)于內(nèi)存并不是很大，可以事先全部加載到內(nèi)存中，那么可以利用內(nèi)存指針的機(jī)制，事先把關(guān)聯(lián)關(guān)系建立好。這樣做運(yùn)算時(shí)就不必再做HASH與對(duì)比運(yùn)算了。具體來說，就是在數(shù)據(jù)加載時(shí)一次性把HASH和對(duì)比運(yùn)算做完，用指針方式保存關(guān)聯(lián)結(jié)果，然后每次運(yùn)算可以直接引用到關(guān)聯(lián)記錄，從而提高運(yùn)算的性能。
不幸的是，SQL沒有指針數(shù)據(jù)類型，無法實(shí)現(xiàn)這個(gè)優(yōu)化邏輯，即使數(shù)據(jù)量可以在內(nèi)存中裝下，也很難利用預(yù)關(guān)聯(lián)技巧提速，基于SQL的內(nèi)存數(shù)據(jù)庫也大都有這個(gè)缺點(diǎn)。而SPL有指針數(shù)據(jù)類型，就可以實(shí)現(xiàn)這種機(jī)制。
我們下面來測(cè)試一下SQL實(shí)現(xiàn)單表計(jì)算和多表關(guān)聯(lián)計(jì)算的差距，再用SPL利用預(yù)關(guān)聯(lián)技巧同樣做一遍，看一下兩者的差距對(duì)比。
二、 測(cè)試環(huán)境
采用TPCH標(biāo)準(zhǔn)生成的8張數(shù)據(jù)表，共50G數(shù)據(jù)（要小到能放進(jìn)內(nèi)存）。TPCH數(shù)據(jù)表的結(jié)構(gòu)網(wǎng)上有很多介紹，這里就不再贅述了。
測(cè)試機(jī)有兩個(gè)Intel2670 CPU，主頻2.6G，共16核，內(nèi)存128G，SSD固態(tài)硬盤。
由于 lineitem 表數(shù)據(jù)量太大，這臺(tái)服務(wù)器內(nèi)存不足以將它裝入，所以創(chuàng)建了一張表結(jié)構(gòu)與它一樣的表 orderdetail, 將數(shù)據(jù)量減少到內(nèi)存能裝下，下面就用這張表來做測(cè)試。
為方便看出差距，下面測(cè)試都用單線程計(jì)算，多核并不起作用。
三、 SQL測(cè)試
這里用 Oracle 數(shù)據(jù)庫作為 SQL 測(cè)試的代表，從orderdetail表里查詢每年零件訂單的總收入。
1. 兩表關(guān)聯(lián)
查詢的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. 六表關(guān)聯(lián)
查詢的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. 測(cè)試結(jié)果
兩個(gè)查詢語句都用了嵌套寫法，Oracle自動(dòng)優(yōu)化后的計(jì)算性能比無嵌套時(shí)還要好一些（無嵌套時(shí)group by和select有可能會(huì)有重復(fù)計(jì)算）。
這兩個(gè)測(cè)試數(shù)據(jù)是多次運(yùn)行后的結(jié)果，在測(cè)試中發(fā)現(xiàn)，Oracle 在第一次運(yùn)行某查詢時(shí)，往往比第 2、3... 次要慢很多，說明在內(nèi)存大于數(shù)據(jù)量時(shí)，數(shù)據(jù)庫可以把全部數(shù)據(jù)都緩存進(jìn)內(nèi)存（Oracle的緩存很強(qiáng)），所以我們?nèi)《啻芜\(yùn)行中最快那一次的時(shí)間，這樣就幾乎沒有硬盤讀取時(shí)間，僅是運(yùn)算時(shí)間。
同時(shí)，在上面兩組測(cè)試中，過濾條件始終都為真，也就是沒有對(duì)數(shù)據(jù)產(chǎn)生實(shí)質(zhì)過濾，兩個(gè)查詢都涉及orderdetail表的全部記錄，計(jì)算規(guī)模是相當(dāng)?shù)摹?/p>
從測(cè)試結(jié)果可以看出，六表關(guān)聯(lián)比兩表關(guān)聯(lián)慢了167/26=6.4倍！性能下降非常多。排除掉硬盤時(shí)間后，這里增加的時(shí)間主要就是表間關(guān)聯(lián)以及針對(duì)關(guān)聯(lián)表字段的判斷，而這些判斷非常簡單，所以大部分時(shí)間消耗在表間關(guān)聯(lián)上。
這個(gè)測(cè)試表明，SQL的JOIN性能確實(shí)很差。
四、 SPL預(yù)關(guān)聯(lián)測(cè)試
1. 預(yù)關(guān)聯(lián)
實(shí)現(xiàn)預(yù)關(guān)聯(lián)的SPL腳本如下：
腳本中前7行分別將7個(gè)組表讀入內(nèi)存，生成內(nèi)表，并設(shè)成全局變量。后5行完成表間連接。在SPL服務(wù)器啟動(dòng)時(shí)，就先運(yùn)行此腳本，完成環(huán)境準(zhǔn)備。
我們來看看預(yù)關(guān)聯(lián)后，內(nèi)存中表對(duì)象的數(shù)據(jù)結(jié)構(gòu)，以orderdetail為例：
圖中只列了orderdetail的第一條記錄的預(yù)關(guān)聯(lián)情況，其它記錄與此類似。限于版面寬度，各表只列出了部分字段。
2. 兩表關(guān)聯(lián)
編寫SPL腳本如下：
3. 六表關(guān)聯(lián)
編寫SPL腳本如下：
預(yù)關(guān)聯(lián)后，SPL代碼也非常簡單，關(guān)聯(lián)表的字段直接可以作為本表字段的子屬性訪問，很易于理解。
4. 運(yùn)行結(jié)果
六表關(guān)聯(lián)僅僅比兩表關(guān)聯(lián)慢2倍，基本上就是增加的計(jì)算量（引用這些關(guān)聯(lián)表字段）的時(shí)間，而因?yàn)橛辛祟A(yù)關(guān)聯(lián)，關(guān)聯(lián)運(yùn)算本身不再消耗時(shí)間。
五、 結(jié)論
測(cè)試結(jié)果匯總：
六表關(guān)聯(lián)比兩表關(guān)聯(lián)，SQL慢了6.4倍，說明SQL處理JOIN消耗CPU很大，性能降低明顯。而采用預(yù)關(guān)聯(lián)機(jī)制后的SPL只慢2倍，多JOIN幾個(gè)表不再出現(xiàn)明顯的性能下降。
在進(jìn)行關(guān)聯(lián)表較多的查詢時(shí)，如果內(nèi)存大到足以將數(shù)據(jù)全部讀入內(nèi)存（內(nèi)存數(shù)據(jù)庫的應(yīng)用場(chǎng)景），使用預(yù)關(guān)聯(lián)技術(shù)將極大地提升計(jì)算性能！而關(guān)系數(shù)據(jù)庫（包括內(nèi)存數(shù)據(jù)庫）用SQL語言則無法實(shí)現(xiàn)這一優(yōu)化技術(shù)。
