一、 問題背景與適用場景
在《 》一文中，我們介紹了將維表內存化並預關聯的技術，但事實表與維表關聯時，仍需進行hash計算和比對，怎麼提高這一步的性能呢？我們今天再介紹一種優化技巧：外鍵序號化。
外鍵序號化的思路是，如果維表的主鍵是從1開始的自然數(也就是記錄所在行號)，那麼就可以用鍵值直接按行號定位維表記錄，而不再需要計算和比對HASH值了，從而加快與維表關聯的速度，進一步提升性能。而且，直接用序號定位，還不需要建立索引，佔用內存也會小很多。
我們下面來介紹如何在SPL中使用外鍵序號化技巧，並使用上文的測試環境，針對同一個查詢問題，以序號化後的數據與之前的數據做對照實驗，驗證序號化的性能提升效果。
二、 序號化準備
要使用外鍵序號化技巧，必須要保證維表的主鍵值正好是序號(記錄行號)，而實際業務中維表的主鍵值往往不是這樣，所以要先將維表的主鍵轉換成序號。轉換的方法如下：
1）新建一個鍵值-序號對應表，保存維表的鍵值和自然序號的對應關係；
2）把維表的鍵值替換為自然序號，得到一個新的維表文件；
3）把事實表裡的外鍵值修改為序號，修改的依據是鍵值-序號對應表，修改後得到一個新的事實表。
本次實驗要用到的三張維表分別是supplier、part與orders，事實表是lineitem，下面分別對它們實現序號化。
1. supplier序號化
實現序號化的SPL腳本如下：
A1 從原始數據文件supplier.tbl中讀出數據，並用derive函數新增加一列xh，用行號作為字段值
A2 將A1中的S_SUPPKEY、xh兩字段輸出到集文件supplier_xh_key.btx中，生成鍵值與序號對應關係表，以備序號化事實表時使用
A3 以xh字段值代替S_SUPPKEY字段，重構supplier序表
A4 創建序號化後的組表文件supplier_xh.ctx
A5 將重構後的supplier序表保存到組表文件supplier_xh.ctx中
2. part序號化
實現序號化的SPL腳本如下：
腳本原理與supplier表序號化相同，生成的鍵值與序號對應關係表為part_xh_key.btx，序號化後的組表文件叫part_xh.ctx。
3. orders序號化
實現序號化的SPL腳本如下：
腳本原理與supplier表序號化相同，生成的鍵值與序號對應關係表為orders_xh_key.btx，序號化後的組表文件叫orders_xh.ctx。
4. lineitem序號化
實現序號化的SPL腳本如下：
A1 創建讀入lineitem原始數據的遊標
A2/A3/A4 分別讀入orders、part、supplier鍵值與序號對應關係表
A5 用L_ORDERKEY與orders對應關係表關聯，用L_PARTKEY與part對應關係表關聯，用L_SUPPKEY與supplier對應關係表關聯
A6 用關聯後的序號值替換鍵值生成新的遊標
A7 創建序號化後的組表文件lineitem_xh.ctx
A8 將序號化後的遊標數據寫入組表lineitem_xh.ctx
三、 序號化測試
1. 原始數據測試
維表預加載SPL腳本如下：
加載維表並建索引。
編寫SPL測試腳本如下：
先運行維表預加載腳本，再運行測試腳本，得到測試腳本運行時間為450秒。
2. 序號化數據測試
維表預加載SPL腳本如下：
加載的是序號化後的維表，不需要建立索引。
編寫SPL測試腳本如下：
注意：A2中關聯時使用“:#”與維表關聯，表示用事實表中的鍵值與維表的行號進行關聯，假如鍵值是7，則直接與維表的第7行關聯。
先運行維表預加載腳本，再運行測試腳本，得到測試腳本運行時間為269秒。
3. 分析與結論
在上面兩組對照實驗中，序號化後的數據與序號化前相比，維表的記錄數、字段數完全相同，事實表的記錄數與字段數也完全相同，僅有相關的鍵值換成了序號。查詢過程相比，過濾條件完全相同，過濾後的參與計算的數據完全相同，也就是說過濾與分組求和所用的時間完全相同，所不同的僅有關聯的方式(行號關聯、鍵值的hash值比對關聯)，而運行時間減少了450-269=181秒，可見，外鍵序號化對性能提升效果十分顯著。
四、 進一步說明
序號化的字段必須是維表的主鍵，但不限主鍵字段的數據類型，整數、字符串、日期、時間等等都可以序號化。對於多主鍵維表，可以新增一個序號字段，建立多個鍵值與序號的對應關係表，並依此對事實表進行序號化。
一般來說，外鍵序號化技巧可以很方便地適用於歷史數據的查詢，只需對歷史數據進行一次序號化就可以使用了，鍵值與序號對應關係表也無須再保留。
但外鍵序號化技巧同樣適用於有新增數據的查詢，只是會多增加些步驟。
1. 維表與事實表均有新增數據
1）先取得維表新增加的記錄，追加鍵值與序號對應關係表；
2）再把新增記錄追加到序號化後的維表，追加時依據鍵值與序號對應關係表；
3）再把事實表新增加的記錄追加到序號化後的事實表，追加時依據鍵值與序號對應關係表；
2. 僅事實表有新增數據
在維表數據保持不變的情況下，僅需進行上述第3)步即可。
處理好新增數據以後，就可以使用外鍵序號化關聯查詢技巧了。
友乾營是專注數據技術的知識分享平臺。這裡，你將有機會與近百位技術專家共同溝通交流，尋找優勢互補，達成資源對接。另外，友乾營將定期（每週一次，週三晚19:30）安排專題技術直播活動。
歡迎IT從業者或對數據相關技術感興趣的人員入群交流、分享。共同打造“有熱度的話題，有溫度的情感，有深度的思想，有高度的評論”高品質的友乾營社群。
識別下面二維碼，在頁面上加友乾營小助手為好友
閱讀更多 大數據小諸葛 的文章
