利用散列串連從DB2通用資料庫獲得最佳效能

利用散列串連從DB2通用資料庫獲得最佳效能
DB2 UDB 最佳化器可以在執行串連時選擇不同方法：在預設情況下，它在嵌套迴圈串連（nested loop join）與合并串連（merge join）之間選擇。當設定了特殊環境變數時，它還可以選擇散列串連（hash join）。散列串連可顯著提高某些查詢的效能，在決策支援系統（Decision Support System，DSS）環境中尤為突出，因為該環境中的查詢比較複雜。本文旨在說明散列串連的工作原理以及如何正確地調優它以獲得最佳效能。
首先我們將介紹閱讀本文所必需的背景知識。我們將說明不同類型的串連方法，這些方法的工作原理，以及 DB2 UDB 如何針對特定的串連選擇特定的方法。隨後我們將說明調優並監控散列串連所需的元素。在這之後，我們將介紹我們做的幾項實驗和許多有趣的結果。最後是我們的結論。
1. 背景知識
兩個表之間的串連是這樣操作的：將一個表中的行與另一個表中的行共置在一起。另外，可以指定條件以定義共置哪些行。為執行這一操作，DB2 可以選擇不同的串連方法。本節概括了不同串連方法的工作原理以及 DB2 如何針對特定串連選用串連方法。
1.1 串連方法
在串連兩個表時，無論使用哪種串連方法，總有一個表被選為外表（outer table）而另一個表被選為內表（inner table）。最佳化器根據所選串連方法的成本和類型決定哪個是外表、哪個是內表。首先訪問外表，並且只掃描一次。根據串連的類型和存在的索引，可以多次掃描內表。還有一點也很重要，要記住即使您試圖串連兩個以上的表，最佳化器也將每次只串連兩個表，並在必要時儲存中間結果。要理解散列串連方法的優勢，先理解其它串連方法的工作原理也很重要。
嵌套迴圈串連:正如我們前面提到的那樣，外表只被掃描一次。對於嵌套迴圈串連，要在內表中找到與外表中每一行相匹配的行有兩種方法：
掃描內表。即，讀取內表中的每一行，並且針對該行決定是否應將其與正在考慮的外表中的行相串連。
對內表上的串連列進行索引尋找。當用於串連的謂詞所包含的列在內表的索引中時，這種方法是可行的。這極大地減少了在內表中訪問的行數。
在嵌套迴圈串連中，決定哪個是外表、哪個是內表非常重要，因為外表只掃描一次，而針對外表中的每一行，都要訪問一次內表。正如前面提到的那樣，最佳化器用成本模型來決定誰是外表誰是內表。最佳化器做此決定時會考慮幾個因素：表的大小、緩衝、謂詞、排序要求、是否存在索引、串連列不能是 LONG 或 LOB 欄位。
合并串連：合并串連需要一個等式串連謂詞（即具有 table1.column = table2.column 格式的謂詞）。它還要求根據串連列對輸入表進行排序。通過掃描現有索引或在進行串連之前對錶進行排序就可以做到這一點。串連列不能是 LONG 或 LOB 欄位。
同時掃描兩個表，以尋找匹配行。外表和內表都只掃描一次，除非外表中有重複的值，那樣的話可能要再次掃描內表的某些部分。因為表通常只被掃描一次，所以決定哪個是外表、哪個是內表不象在其它串連方法中那麼重要。儘管如此，由於可能有重複的值，所以最佳化器通常選擇重複值較少的表作為外表。但是，最佳化器最終還是使用成本模型來決定誰是外表誰是內表。
散列串連：散列串連需要一個或多個等式串連謂詞，其中每個謂詞的列類型相同。就 CHAR 類型而言，長度必須相同。就 DECIMAL 類型而言，精度和小數位必須相同。同樣，串連列不能是 LONG 或 LOB 欄位。散列串連可處理多個等式謂詞這一事實相對於合并串連是一大優勢，後者只能處理一個等式謂詞。
對於散列串連，首先掃描內表（也稱為構建表，bulid table），表中的行被複製到記憶體緩衝區。根據“散列代碼（hash code）”，這些緩衝區被分為幾個分區，散列代碼是根據串連謂詞中的列計算出來的。如果記憶體中沒有足夠的空間容納整個表，則有些分區被寫入磁碟上的暫存資料表。然後掃描外表（稱為探測表，probe table）。對於探測表中的每一行，對串連列應用同一散列演算法。如果所獲得的散列代碼與構建行的散列代碼相匹配，則比較實際的串連列。如果與探測表行匹配的分區在記憶體中，則比較會立即進行。如果分區被寫入暫存資料表，則探測行也被寫入暫存資料表。最後，處理包含同一分區中的行的暫存資料表以進行匹配。
由於將構建表儲存在記憶體中所具有的好處，最佳化器通常選擇較小的表作為構建表，以避免必須將該表溢出（spill）到磁碟上。但是，要再次強調的是，成本模型最終決定哪個表是內表、哪個表是外表。
讓我們更深入地研究散列串連如何利用 SMP 系統。在 SMP 系統（其中 intra-parallel = ON 且 dft_degree >1）中，一個散列串連可能由多個任務在同一個 CPU 或多個 CPU 上以並行方式執行。在以並行方式執行時，構建表被動態地分為多個並行的元組流，每個流由獨立的任務處理以便將構建元組輸入記憶體。在對構建表流的處理結束時，散列串連進程會調整記憶體的內容，並執行任何必需的將分區移入或移出記憶體的操作。接下來，根據駐留記憶體的分區來處理探測表的多個並行元組流，並且，在需要時，為溢出到暫存資料表的散列串連分區的元組來溢出這些並行元組流。最後，以並行方式處理溢出的分區，每個任務處理一個或多個溢出的分區。
2.2 選擇哪種串連方法？
到目前為止，我們已經討論了在 DB2 UDB 中可用的不同串連方法。正如我們所知，初看起來，某些方法與其它方法相比是更好的選擇。例如，與根據外表的每一行掃描內表的嵌套迴圈串連相比，合并串連具有只對錶掃描一次的優勢。於是，合并串連似乎是一個更好的選擇；但是，如果存在索引的話，則嵌套迴圈會是更好的選擇。
同樣地，與合并串連相比，散列串連似乎是更好的選擇，因為它不需要在執行前對輸入表排序，但如果我們需要保持外表中行的次序，則合并串連或嵌套迴圈串連可能是更好的選擇 — 散列串連不能保證維持次序，因為它可能溢出到磁碟而那樣會破壞次序。
那麼 DB2 UDB 最佳化器如何針對特定串連來決定使用哪種串連方法呢？首先，它必須考慮查詢中謂詞的類型。當選擇了可能的串連方法時，DB2 UDB 最佳化器隨後根據成本模型和選定的最佳化層級來決定使用哪種串連方法。最佳化層級是資料庫設定檔中可配置的參數，它告訴最佳化器要進行多大程度的最佳化。這個值越高，最佳化操作就越多。最佳化層級可能的值為：0、1、2、3、5、7 和 9。這些值對可能的串連方法的影響如下：嵌套迴圈串連在每個最佳化層級都可行、合并串連在最佳化層級 1 及以上層級是可行的、散列串連在最佳化層級 5 及以上層級是可行的。
3. 如何調優和監控散列串連
散列串連可顯著提高某些查詢的效能，在具有複雜查詢的決策支援系統（DDS）中尤為突出。與合并串連相比，散列串連的效能優勢之一是它不要求預先進行任何排序，排序的成本是很高的。散列串連的關鍵是能夠將構建表的所有（或儘可能多的）行放入記憶體，而不必使表溢出到磁碟上。這些緩衝區所用的記憶體來自 sortheap，因此對該參數（以及 sheapthres）的調優是非常重要的。
sortheap 是資料庫可配置參數，它定義可用於排序或散列串連的最大記憶體量。每個排序或散列串連都有獨立的 sortheap，由資料庫管理員按需分配。並非所有的排序和散列串連都要分配該記憶體量；如果不需要全部的記憶體，則可分配較小的 sortheap。根據操作的需求，可以從共用或專用記憶體配置 sortheap。只對 degree >1 的內部並行（intra-parallel）（SMP）查詢使用共用 sortheap；當只有一個代理程式執行排序或散列串連且無需與其它代理程式共用記憶體時，則使用專用 sortheap。一個查詢可能有多個散列串連和／或排序，並且根據計劃的性質可能要求同時分配多個 sortheap，理解這一點也很重要。
sheapthres 是資料庫管理員可配置參數。對該參數的使用因共用和專用排序而有所不同：
對於專用排序或散列串連，sheapthres 所起的作用是對所有並發專用排序能使用的記憶體總量充當執行個體範圍的軟式節流。當所用的記憶體達到該限制時，為另外的新到 sortheap 請求分配的記憶體會顯著減少。
對於共用排序或散列串連，sheapthres 所起的作用是對所有共用排序能使用的記憶體總量充當執行個體範圍的硬限制。當所用的記憶體接近該限制時，為另外的新到 sortheap 請求分配的記憶體會顯著減少，並且最終將不再允許共用排序的記憶體請求。
在單一處理器系統中，散列串連只使用專用 sortheap。在 SMP 系統（其中 intra-parallel = ON 且 dft_degree >1）中，散列串連在操作的第一階段使用共用 sortheap，在最後的階段使用專用 sortheap。有關這些參數的更多資訊，請參閱與 DB2 UDB 一起提供的 Administration Guide: Performance。
如何調優這些參數呢？與大多數調優練習一樣，您需要有一個出發點：一個要測試的工作負載以及測量調優結果的工具。然後就是一次更改一個參數再進行測量的迭代過程。在大多數情況下，現有系統中可能已經設定了 sortheap 和 sheapthres 的值，因此我們建議您先從當前的設定著手。如果您有全新的安裝，那麼您可以遵循適用於 DSS 系統的經驗，將所擁有的記憶體的 50% 分配給緩衝池，另外 50% 則分配給 sheapthres。然後，將 sheapthres 除以要同時執行的複雜並發查詢的數目，再除以一般查詢中並發排序和散列串連的最大數目，就得到 sortheap。（適合開始時採用的數字是 5 或 6。）總結如下：
sortheap = sheapthres / （複雜並發查詢數 * 一般查詢中並發排序和散列串連的最大數目）
請記住“複雜並發查詢數”不等於“並發使用者數”。使用者的數目通常比每次執行的複雜並發查詢數多得多。在大多數 DSS TPC-H 基準程式（它們設法將資料庫的能力發揮至極限）中，對於最大為 10 TB 的資料庫，將不大於 8 或 9 的數作為複雜並發查詢（也稱為流）的數目，因此開始時要保守一些，然後在必要時增加。
設定了初始的 sortheap 和 sheapthres 值以後，運行一般的工作負載，然後收集資料庫和資料庫管理員快照。DB2 UDB 提供了一些監視器元素以便能夠監控散列串連。您不需要開啟任何監視器開關，因為所有的散列串連監視器元素都是用 Basic 監視器開關收集的。此外，所有的元素都是計數器，都是可重新設定的。有關 DB2 UDB 快照集工作原理的詳細資料，請參閱與 DB2 UDB 一起提供的 System Monitor and Reference Guide。
以下是對每個監視器元素的描述：
1.散列串連總數（Total Hash Joins）：該監視器元素計算已執行的散列串連的總數。這個值可用資料庫或應用程式快照來收集。
2.散列串連溢出數（Hash Join Overflows）：該監視器元素計算散列串連設法將構建表中的行放入記憶體時超出了可用 sortheap 的總次數。這個值可用資料庫或應用程式快照來收集。
3.散列串連少量溢出數（Hash Joins Small Overflows）：該監視器元素計算當設法將構建表的行放入記憶體時，散列串連超過可用 sortheap 不到 10% 的總次數。出現較小的溢出表明增加 sortheap 有助於提高效能。這個值可用資料庫或應用程式快照來收集。
4.散列迴圈總數（Total Hash Loops）：該監視器元素計算散列串連的單個分區大於可用 sortheap 的總次數。
請記住，如果 DB2 UDB 不能將構建表的所有行放入記憶體，會將某些分區溢出到暫存資料表以供以後處理。在處理這些暫存資料表時，DB2 會嘗試將每個構建分區裝入一個 sortheap。然後 DB2 UDB 讀取相應的探測行，並設法使它們與構建表匹配。如果 DB2 不能將某些構建分區裝入 sortheap，則 DB2 必須藉助散列迴圈演算法。監視散列迴圈非常重要，因為散列迴圈表明散列串連的執行效率低下，並且可能導致嚴重的效能下降。它可能表明 sortheap 大小對於工作負載太小，或者，更可能是 sheapthres 太小，無法獲得對 sortheap 記憶體的請求。這個值可用資料庫或應用程式快照來收集。
5.散列串連閾值（Hash Join Threshold）：該監視器元素計算散列串連 sortheap 請求由於對共用或專用記憶體堆空間的並發使用而受限的總次數。這意味著散列串連請求了一定量的 sortheap，但得到的少於請求的。它可能表明 sheapthres 對於工作負載太小。這個值可用資料庫管理員快照來收集。
關於hljoin串連的相關測試：
1.sortheap 實驗
正如我們前面提到的那樣，對 sortheap 和 sheapthres 參數的調優是散列串連效能中最重要的因素。本實驗的主要目的是理解不同的 sortheap 大小如何會影響散列串連的效能。在這個測試中，我們使用了在前一節中說明過的所有初始調優，僅僅改變了 sortheap 的大小，同時使 sheapthres 保持其原始值 192000。我們在這些測試中只運行一個流。結果顯示在 圖 5中。
所顯示的時間是與運行 256 頁的 sortheap 所用時間比較而言的。我們把運行 256 頁 sortheap 的工作負載所用的時間看作一個時間單位。在這個實驗中，所有其它的結果都相對於這個單位來表示。
讓我們詳細地分析結果。我們可以看到，通常 sortheap 越大，我們遇到的溢出就越少並且效能也更佳。我們在 40000 頁處獲得最佳效能，其中僅看到三個溢出。
第一個問題是，為什麼更大的 sortheap 不能給我們更佳的效能呢？如果您看一下 圖 6中顯示的監視器資訊，您可以看到在 50000 頁處我們開始命中 sheapthres。而且，sortheap 越大，我們得到的效能就越差，因為我們命中 sheapthres 的次數越多。命中 sheapthres 時效能下降的原因是，當 sortheap 請求命中閾值時，DB2 將力圖滿足它，但所給的記憶體少於請求的。正如我們在這些例子中看到那樣，儘管我們請求的 sortheap 比以前更大，但卻得到更多的溢出。這是因為我們得到的 sortheap 實際上比我們請求的少，而這導致了溢出，於是就降低了散列串連的效能。
通過觀察圖表我們注意到另一現象：隨著 sortheap 從 256 頁到 500 頁，再從 500 頁到 1000 頁，然後一直到 1000 頁，我們在圖表的左邊獲得非常大的改進。為什麼呢？再次觀察監視器資訊後，我們可以發現，散列迴圈的數量隨著 sortheap 的增加而明顯下降，並且在 4000 頁處消失，在這之後增加 sortheap 的效能增益大大小於先前。
我們可以從這些結果得出這樣的結論：散列串連的最大敵人是散列迴圈。儘管命中 sheapthres 會導致效能下降，就象當 sortheap >40000 時我們看到的那樣，但出現散列迴圈時效能下降的數量級遠遠大於命中 sheapthres 時效能下降的數量級，當不可能同時避免這兩種情形時要牢牢記住這一點。
2 sheapthres 實驗
這些實驗的主要目的是理解 sheapthres 的調優，sortheap 和 sheapthres 之間的關係，以及這種關係在散列串連中的影響。針對這一目的，我們將該實驗分為五組測試。在每組測試中，我們固定了 sortheap 的大小並更改 sheapthres，使其大小分別為 sortheap 的兩倍、三倍、四倍、五倍、六倍和七倍。我們使用的五個 sortheap 的大小是 5000、10000、15000、20000 和 30000 頁。結果顯示在 圖 7到圖 9 中。
所顯示的時間是與在 sortheap 為 5000 頁和 sheapthres 為 10000 頁（sortheap 大小的兩倍）時運行所用的時間比較而言的。因此，我們把運行 5000 頁 sortheap 和 10000 頁 sheapthres 的工作負載所用的時間看作一個時間單位。在這個實驗中，所有其它的結果都相對於這個單位來表示。
如果觀察圖 7，我們可以看到對於所有的 sortheap，該特定工作負載的最佳效能出現在 sheapthres 值為 sortheap 值的 6 到 7 倍時。觀察 圖 8和 圖 9中的監視器資料，可以看到正是在這一層級上我們消除了所有的散列串連閾值命中。在所有的例子中，最佳改進出現在 sheapthres 值從 sortheap 值的兩倍變為三倍時，因為散列串連閾值在那時明顯下降。另外，當 sortheap 為 5000 時，散列迴圈在 sheapthres 值從這一大小的兩倍變為三倍時明顯降低。正如 DB2 UDB 手冊所指出的那樣，通常的建議是使 sheapthres 至少為 sortheap 的三倍，但我們希望看到 sheapthres 過小對於散列串連有什麼結果。對於您實際的系統，我們決不會建議使 sheapthres 低於 sortheap 的三倍。
觀察到的另一重要現象是增加 sheapthres 所獲得的效能增益在某些情況下非常小；在資料庫／執行個體的整體效能調優方案中，把記憶體配置給 sheapthres 可能並不划算，而（例如）有更大的 sortheap 或更多緩衝池可能更划算。要清楚如何設定 sheapthres 以及它對整個系統的影響。我們從這些實驗得出的最重要的結論是：調優 sheapthres 對散列串連的效能有一些影響，但這種影響沒有調優 sortheap 那麼顯著。
6. 結束語
正如我們看到的那樣，散列串連只需少量調優就可以明顯地改進效能。正如我們在第一個實驗中示範的那樣，只要遵循一些基本經驗，就可以通過啟用散列串連在我們的 DSS 工作負載中使效能有顯著的改進。此外，我們還示範了可以在有許多流時改進延展性。
我們還從兩個影響散列串連的主要參數 — sortheap 和 sheapthres — 知道 sortheap 似乎是最重要的。通過增加 sortheap，我們有可能避免出現散列迴圈這個散列串連最大的敵人。另外，我們看到，sortheap 越大，溢出就越少並且效能更佳。這並不意味著我們可以忽略 sheapthres，它肯定會影響效能，但通常 sheapthres 是由機器中可用的記憶體量決定的。於是我們可以得出如下結論：調優散列串連的最佳方法是確定您可以給 sheapthres 分配多少記憶體，然後相應地調優 sortheap 值。
調優散列串連的關鍵是：使 sortheap 儘可能地大以避免溢出和散列迴圈，但不要大到超過 sheapthres的限制。