一、問題背景與適用場景

通常分組計算都採用 hash 方案，即先計算分組欄位的 hash 值，hash 值相同的記錄被分揀到一個小集合裡，然後在這個小集合中遍歷找分組欄位值相同的聚合成一組。分組的複雜度 (比較次數)，取決於 hash 函式的重位元速率。在 hash 空間比較小時，重位元速率就高，比較次數就會多，效能會受較大影響。為了提高效能，就需要分配較大的記憶體來存放 hash 表。另外，有些資料型別（長字串）的 hash 計算也比較慢，這也會影響效能。

如果分組欄位是有序的，在分組的時候，每條記錄只與上一條記錄比較，發現有不同時則新建一個分組，相同則聚合到當前組中。這樣的分組運算的複雜度為 n(被分組集合的長度)，而且沒有 hash 計算和重位元速率的問題，可以獲得比 hash 分組更快的效能，而且並不需要太多記憶體用於存放 hash 表。

SPL 提供了這種分組方法，我們例項測試一下，並且與使用 hash 分組演算法的 Oracle 對比。

二、測試環境

測試機有兩個 Intel2670 CPU，主頻 2.6G，共 16 核，記憶體 64G，SSD 固態硬碟。在此機上安裝虛擬機器來測試，設定虛擬機器為 16 核、8G 記憶體。

三、小資料量小結果集測試

在虛擬機器上建立資料表 orderdetail_1，共三個欄位：orderid(整數)、detailid(整數)、amount(實數)，前兩個欄位是主鍵，生成資料記錄 8 千萬行。將此表資料匯入 Oracle 資料庫，同時用它生成集算器 SPL 組表來進行測試。

orderid 欄位資料升序排列，按 orderid 進行分組，共有 50 組，統計每張訂單的總金額和明細條數。

1.Oracle 測試

編寫查詢測試 SQL 如下：

select /*+ parallel(n) */

orderid, sum(amount) as amount, count(detailid) as details

from orderdetail_1

group by orderid;

其中 /*+ parallel(n) */ 用於並行測試，n 為並行數。

2.SPL 測試

編寫 SPL 指令碼如下：

groups 分組時加選項 @o 就適用分組欄位有序時，只比較相鄰行的值進行有序分組。

3. 測試結果

測試結果如下，單位 (秒)：

在 8 千萬行資料的情況下，SPL 有序分組的效能提高了一倍左右，並且並行的效果非常好，效能呈線性上升。而使用 hash 分組的 Oracle 並行提速效果並不明顯。

效能提高程式與資料量有關，當資料量很小時，分組時間佔整個查詢時間的比例很小，對整體效能的提高也就不明顯。但隨著資料量的增加，提升效果就會越來越顯著。

下面我們再來看看大資料量測試的情況。

四、大資料量大結果集測試

在虛擬機器上建立資料表 orderdetail_2，共三個欄位：orderid(字串)、detailid(整數)、amount(實數)，前兩個欄位是主鍵，生成資料記錄 24 億行。將此表資料匯入 Oracle 資料庫，同時用它生成集算器 SPL 組表來進行測試。

orderid 欄位資料升序排列，按 orderid 進行分組，共有 8 億組，統計每張訂單的總金額和明細條數。由於查詢出的大結果集在 Oracle 輸出需要很長的時間，所以對分組結果再進行一次過濾，只輸出訂單總金額小於 35 元的訂單，結果只有 12 條，輸出就幾乎不佔時間了。

1.Oracle 測試

編寫查詢測試 SQL 如下：

select * from (

select /*+ parallel(n) */

orderid, sum(amount) sum_amount, count(detailid) as details

from orderdetail_2

group by orderid

)

where sum_amount<35;

其中 /*+ parallel(n) */ 用於並行測試，n 為並行數。

2.SPL 測試

編寫 SPL 指令碼如下：

由於分組結果集很大，無法全部裝載到記憶體，所以使用 group 函式進行有序分組，返回分組結果集對應的遊標，再對遊標過濾後取得需要的查詢結果。

3. 測試結果

測試結果如下，單位 (秒)：

在不併行的情況下，SPL 有序分組比 Oracle 效能提升了近 6 倍左右。因 SPL 有序分組方法很適合並行，隨著並行數的增加，效能提升的效果就越好。