在DB2最佳化器中使用分布統計資訊

本文配套源碼

簡介

為了執行查詢或 DML 語句（INSERT、UPDATE、DELETE），DB2 必須建立一個訪問計劃（access plan）。訪問計劃定義按什麼順序訪問表，使用哪些索引，以及用何種串連（join）方法來關聯資料。好的訪問計劃對於 SQL 陳述式的快速執行至關重要。DB2 最佳化器可以建立訪問計劃。這是一種基於成本的最佳化器，這意味著它是根據表和索引的相關統計資訊來作出決策的。DB2 在產生統計資訊時，不但能提供基本統計資訊，還允許建立所謂的分布統計資訊。不但資料庫管理員要理解分布統計資訊，而且應用程式開發人員也要理解分布統計資訊。應用程式開發人員必須小心謹慎，因為在某些情況下分布統計資訊對於 DB2 最佳化器來說非常重要。主變數或參數標記（在 Java 中為 java.sql.PreparedStatement）的使用可能會造成阻礙，使最佳化器無法最大限度地利用分布統計資訊。本文解釋什麼是分布統計資訊、分布統計資訊在哪些情況下尤為重要，以及應用程式開發人員應該考慮些什麼，才能使 DB2 最佳化器建立有效訪問計劃。

基本統計資訊和分布統計資訊

在研究分布統計資訊之前，我們先來看看基本統計資訊，只要執行 RUNSTATS 即可收集這些表的相關統計資訊。

表的相關統計資訊：

當前使用的頁面數

包含記錄行的頁面數

溢出的行數

表中的行數（基數）

對於 MDC 表，還有包含資料的塊（block）數

表中各列的相關統計資訊：

列的基數

列的平均長度

列中第二大的值

列中第二小的值

列中 NULL 值的個數

通常，執行 RUNSTATS 時，不但可以收集到關於表的統計資訊，而且還可以收集到相應的索引的相關統計資訊。要瞭解為索引而收集的統計資訊，請參閱 DB2 Administration Guide: Performance - Statistical information that is collected。

觀察一個表的基本統計資訊，您可以看到，DB2 最佳化器知道一個表由多少行組成（表的基數），以及一個列包含多少個不同的值（列的基數）。但是，還有一些資訊是基本統計資訊無法提供的。例如，基本統計資訊不能告訴最佳化器一個列中某些值出現的頻率。假設表 TABLE_X 有大約 1,000,000 行，在該表上執行這樣一條查詢：

SELECT * FROM TABLE_X WHERE COLUMN_Y = 'VALUE_Z'

難道 DB2 最佳化器知道 TABLE_X 中有多少行滿足條件 COLUMN_Y = 'VALUE_Z' 不重要嗎？換句話說：知道這個查詢將返回 1 行、100 行、1000 行還是 10000 行有什麼不好呢？

實際上，通過基本統計資訊，DB2 最佳化器只能估計 'VALUE_Z' 在 COLUMN_Y 中出現的頻率。在這種情況下，最佳化器認為所有值在 COLUMN_Y 中是平均分布的，這意味著它認為所有的值都有相同的出現頻率。如果事實碰巧如此，這樣估計並無大礙。但是，如果有些值比其他值出現得更頻繁一些（例如，如果 'VALUE_Z' 出現 900,000 次，即占所有行的 90%），那麼最佳化器不能考慮到這一點，因而產生的訪問計劃就不是最優的。而分布統計資訊可以填補這一空白。分布統計資訊可以提供關於資料出現頻率及其分布情況的資訊，如果資料庫中儲存了很多重複值，並且資料在表中並非平均分布的時候，分布統計資訊對於基本統計資訊是一個重要的補充。

分布統計資訊的類型 —— 頻率（frequency）統計資訊和分位元（quantile）統計資訊

有兩種不同類型的分布統計資訊 —— 頻率統計資訊和分位元統計資訊。讓我們通過一個樣本表來研究一下這兩種不同類型的分布統計資訊。

樣本表 “CARS” 表示一家汽車製造商，對於生產的每一輛汽車，在表中都有相應的一行。每輛汽車可以由它的 ID 來標識，因此 “ID” 是表 “CARS” 的主鍵（PK）。此外，表中有一個 “STATE” 列，表明汽車當前處在製造流程中的哪一步。一輛汽車的製造流程從第 1 步開始，然後是第 2 步、第 3 步，...、第 49 步、第 50 步、第 51 步、...、第 98 步、第 99 步，一直到第 100 步 —— 第 100 步意味著汽車已經完工了。已完工的汽車所對應的行仍然保留在表中，後續流程（例如投訴管理、品質保證等）仍要用到這些行。汽車製造商生產 10 種不同型號（“TYPE” 列）的汽車。為了簡化問題，在這個樣本表中，各種汽車型號命名為 A、B、C、D、...、J。除主鍵索引（在 “ID” 列上）之外，“STATE” 列上也有一個索引（“I_STATE”），在 “TYPE” 列上還有一個索引（“I_TYPE”）。實際上，一個 “CARS” 表包含的列遠不止 “ID”、“STATE” 和 “TYPE”。為簡單起見，樣本表中沒有出現其他這些列。