一、 問題背景與適用場(chǎng)景
在《性能優(yōu)化技巧：小事實(shí)表與大維表關(guān)聯(lián)》中，我們嘗試了小事實(shí)表與大維表關(guān)聯(lián)時(shí)的性能優(yōu)化方法，該方法利用了小事實(shí)表可以裝入內(nèi)存的特點(diǎn)，將關(guān)聯(lián)鍵匯集排序后到大維表中查找，避免了遍歷大維表的動(dòng)作。如果事實(shí)表與維表都大到不能裝載到內(nèi)存時(shí)，這個(gè)辦法就不再有效了，那么，還有什么辦法提高性能呢？
SQL的方案是對(duì)兩個(gè)表做HASH分堆，拆小到內(nèi)存可以放下的地步，分別寫入外存，然后再分別讀入進(jìn)行內(nèi)存關(guān)聯(lián)。如果運(yùn)氣不好，拆出來的某個(gè)堆還是太大，就需要做二次HASH。同時(shí)，兩個(gè)表都需要做一遍HASH分堆動(dòng)作，也就是需要把所有數(shù)據(jù)都緩存一遍。
如果維表是有序存放的，我們就可以將平均分段，由于有序存儲(chǔ)，所以可以計(jì)算出每一段值的邊界值，然后再用這個(gè)邊界值將事實(shí)表再分堆。這樣，維表本身由于有序存儲(chǔ)，可以直接按段讀取，而不需要再分堆；只有事實(shí)表被緩存出去，也就是只有一個(gè)表被分堆緩存，所以這種辦法可以稱為單邊方案。而且，由于維表可以被相對(duì)平均分段，不可能象HASH方法那樣出現(xiàn)運(yùn)氣不好導(dǎo)致某堆太大的情況，一次分堆一定能解決問題，性能將得到保障。
SPL提供了這種關(guān)聯(lián)方法，下面我們實(shí)例測(cè)試一下，并且與使用HASH JOIN算法的Oracle對(duì)比。
二、 測(cè)試環(huán)境與任務(wù)
測(cè)試機(jī)有兩個(gè)Intel2670 CPU，主頻2.6G，共16核，內(nèi)存64G，SSD固態(tài)硬盤。在此機(jī)上安裝虛擬機(jī)來測(cè)試，設(shè)置虛擬機(jī)為16核、8G內(nèi)存。
在虛擬機(jī)上創(chuàng)建維表account，共三個(gè)字段accountid、name、state，總記錄共10億行。創(chuàng)建事實(shí)表trade，總記錄共16億行，共四個(gè)字段tradedate、outid(轉(zhuǎn)出帳戶)、receiveid(接收帳戶)、amount(轉(zhuǎn)帳金額)。account表中的accountid是事實(shí)表中outid和receiveid的外鍵，都是一對(duì)多的關(guān)系。
在《性能優(yōu)化技巧：小事實(shí)表與大維表關(guān)聯(lián)》中我們測(cè)試的是outid、receiveid兩個(gè)字段都要與account表中的accountid關(guān)聯(lián)，稱之為雙維表。測(cè)試結(jié)果可見當(dāng)事實(shí)表記錄數(shù)為1500萬行時(shí)，Oracle運(yùn)行時(shí)間已經(jīng)接近5小時(shí)，而這次測(cè)試的事實(shí)表最少記錄數(shù)為10億行，用Oracle運(yùn)行的時(shí)間就會(huì)超過24小時(shí)了，所以測(cè)試只有outid關(guān)聯(lián)的情況，稱之為單維表。測(cè)試任務(wù)為查詢某段時(shí)期內(nèi)各州轉(zhuǎn)出資金總額。
在SPL測(cè)試中，會(huì)用雙維表與單維表作對(duì)比測(cè)試。
為縮短測(cè)試時(shí)間，全部采用4個(gè)并行。
三、 測(cè)試
1. Oracle測(cè)試
編寫查詢測(cè)試SQL如下：
select /*+ parallel(4) */
state,
sum(amount) as amount
from
account,
trade
where
outid = accountid
and tradedate >= date '2008-01-01' + interval '1500' day(4)
group by
state
order by
state;
其中/*+ parallel(4) */ 表示4個(gè)并行。
2. SPL測(cè)試
編寫SPL腳本如下：
joinx時(shí)加選項(xiàng)@u就適用于大事實(shí)表與大維表關(guān)聯(lián)，它的最后一個(gè)參數(shù)指明把游標(biāo)拆分為多路時(shí)，每次從游標(biāo)中讀取的記錄數(shù)，在內(nèi)存能裝下的情況下，此值越大性能越高。
3. 測(cè)試結(jié)果及分析
事實(shí)表不同數(shù)據(jù)量時(shí)的測(cè)試結(jié)果如下(單位：秒)：
經(jīng)測(cè)算，10億行數(shù)據(jù)正常情況會(huì)超過8G內(nèi)存，優(yōu)秀的Oracle可能采用了數(shù)據(jù)壓縮技術(shù)，致使能裝下15億行數(shù)據(jù)。但是在16億行數(shù)據(jù)時(shí)，內(nèi)存就怎么也放不下了，開始發(fā)生大量占用swap區(qū)的現(xiàn)象，也造成運(yùn)行速度奇慢，測(cè)試中等了11小時(shí)也沒查詢出來，只好終止了。而SPL這種單邊技術(shù)，不受數(shù)據(jù)量大小的限制，本來就是面向外存設(shè)計(jì)，而且一次分堆就能解決，時(shí)間基本上呈線性增加。
四、 SPL雙維表與單維表對(duì)比測(cè)試
1. 單維表
編寫單維表測(cè)試SPL腳本如下：
2. 雙維表
編寫雙維表測(cè)試SPL腳本如下：
3. 測(cè)試結(jié)果及分析
事實(shí)表不同數(shù)據(jù)量時(shí)的測(cè)試結(jié)果如下(單位：秒)：
雙維表比單維表多了一倍的關(guān)聯(lián)計(jì)算量，運(yùn)算時(shí)間也僅僅略多于一倍，也是呈線性增加的，不會(huì)發(fā)生完全不可控的局面。
