在所有 SQL 語句基估計過程中，以 JOIN 語句的計算過程最復雜，而 JOIN 語句恰恰是進行性能優化的重點。本文主要關注的是 DB2 優化器在進行基估計時采用的相關計算方法、輸入等。

簡介

優化器是 DB2 的心臟和靈魂(可以把它類比成寶馬 730 或波音 747 的發動機引擎一樣)。它分析 SQL 語句并確定可以滿足每條語句的最有效的存取路徑。 DB2 SQL 優化器可以估計每個備選訪問計劃的執行成本，并根據其估計結果選擇一個最佳訪問計劃。

在優化器在優化一個 SQL 語句的過程中使用到兩個非常重要的概念：selectivity 和 cardinality 。 selectivity 是指一個 SQL 操作的得出結果集占原來結果集的百分比，而 cardinality 就是指一個 SQL 操作的得出結果集的行數。

為正確地確定每種訪問計劃的成本，DB2 優化器都會對每個步驟產生的結果集大小即返回的行數進行估計，這就是優化器的基估計。 DB2 優化器需要準確的基數估計值?；鶖倒烙嬍沁@樣一種過程：在應用了謂詞或執行了聚集之后，優化器使用統計信息確定部分查詢結果的大小。對于訪問計劃的每個操作符，優化器將估計該操作符的基數輸出。一個或更多謂詞的應用可以減少輸出流基數。

JOIN 謂詞

當我們在 SQL 里面需要對多個表進行 join 的時候，DB2 會首先選擇其中的 2 個表進行 join，并獲取到一個中間的結果集，然后 DB2 可能會用這個中間的結果集和第三個表做 join，再次獲得中間的結果集(當然也可能是把另外 2 個表做 join，然后把兩個中間的結果集進行 join 操作)，不管是怎么操作，DB2 一次能夠 join 的表的個數肯定是兩個。因此當優化器在考慮 Join 如何處理的時候，join 的順序就是一個很重要的問題，因為我們總是希望能夠在最開始就把結果集控制的盡量小。

一個 JOIN 謂詞一般描述如下所示：

T1.joincol=T2.joincol 

在實際應用過程中，Where 子句中除 JOIN 謂詞外，一般都還有本地謂詞，形式如下：

T1.joincol=T2.joincol and T1.filter=literal_1 and T2.filter=literal_2 

謂詞 T1.filter=literal_1 用于對 T1 表進行過來，T2.filter=literal_2 用于多 T2 表進行過濾，然后兩個經過過濾的表進行 JOIN 操作。至于 JOIN 采用 hash join 還是 Merge Join 或者 NestLoop Join 取決于 DB2 的優化級別、參數設置以及成本估計。

DB2 Join 謂詞選擇性計算公式如下：

Selectivity (T1.y = T2.y)= 1/max(colcard(T1. joincol), colcard(T2. joincol)) 

其中，colcard(T1. joincol) 指 T1 表 joincol 列的不同值的個數，colcard(T2. joincol) 指 T2 表 joincol 列的不同值的個數，兩者取較大的一個作為 Join 謂詞計算依據。此公式存在兩個假設：

包含性，即 T2. joincol 的所有取值都在 T1 joincol 取值范圍內，反之也行。

均衡性，即兩個連接列上的數據分布均勻。

DB2 優化器中針對 JOIN 語句的結果集估計

作者: 駱洪青, 　出處:IT專家網論壇,　責任編輯: 陳子琪,　2009-06-24 07:00

DB2 優化器在為 SQL 語句生成執行計劃時，都會對每個步驟產生的結果集大小進行估計，這就是DB2 優化器的基估計。在所有 SQL 語句基估計過程中，以 JOIN 語句的計算過程最復雜，而 JOIN 語句恰恰是進行性能優化的重點。

DB2 Join 謂詞基估計計算公式如下：

Join Cardinality =Join Selectivity *   
filtered cardinality(t1) *   
filtered cardinality(t2)  

其中 filtered cardinality(t1) 是在 T1 表上應用本地謂詞后獲得結果集，filtered cardinality(t2) 是在 T2 表上應用本地謂詞后獲得結果集。

示例#p#

創建測試表

我們創建以下測試表：

T1 表擁有 10000 行數據。參加進行 JOIN 操作的列 join1 數據均勻分布，取值范圍在 0 ～ 29 之間，沒有空值。對 T1 進行過濾的列 filter1 數據也均勻分布，取值在在 0 ～ 24 之間。 V1 列從 0 自然增長到 9999 。

T2 表擁有 10000 行數據。參加進行 JOIN 操作的列 join2 數據均勻分布，取值范圍在 0 ～ 29 之間，沒有空值。對 T2 進行過濾的列 filte2r 數據也均勻分布，取值在在 0 ～ 24 之間。 V2 列從 0 自然增長到 9999 。

drop table db2inst1.t1;   
CREATE TABLE db2inst1.t1   
( Filter1 int, join1 int , v1 int, padding1 char(1)   
)   
NOT LOGGED INITIALLY   
;   
INSERT INTO db2inst1.t1 (filter1, join1, v1,padding1)   
WITH TEMP (COUNTER, filter1, join1, v1,padding1) AS   
( VALUES (0, MOD(INT(RAND() * 1000), 25),MOD(INT(RAND() * 1000), 30), 0, 'A')   
UNION ALL SELECT (COUNTER + 1),MOD(INT(RAND() * 1000), 25),   
MOD(INT(RAND() * 1000), 30), (COUNTER + 1), 'A' FROM TEMP WHERE (COUNTER + 1) < 10000   
)   
SELECT Filter1, join1, v1,padding1   
FROM TEMP   
;   
drop table db2inst1.t2;   
CREATE TABLE db2inst1.t2   
( Filter2 int, Join2 int , V2 int, Padding2 char(1)   
)   
NOT LOGGED INITIALLY   
;   
INSERT INTO db2inst1.t2 (filter2, join2, v2,padding2)   
WITH TEMP (COUNTER, filter2, join2, v2,padding2) AS   
( VALUES (0, MOD(INT(RAND() * 1000), 50),MOD(INT(RAND() * 1000), 40), 0, 'A')   
UNION ALL SELECT (COUNTER + 1),MOD(INT(RAND() * 1000), 50),MOD(INT(RAND() * 1000), 40),   
(COUNTER + 1), 'A' FROM TEMP WHERE (COUNTER + 1) < 10000   
)   
SELECT Filter2, join2, v2,padding2   
FROM TEMP   
;  

在表創建完成后，我們收集 T1 和 T2 的統計信息，在收集統計信息是只包括表的基本統計和列的統計信息，不包括列的分布信息。

db2 "runstats on table db2inst1.t1 on all COLUMNS "   
db2 "runstats on table db2inst1.t2 on all COLUMNS "  

使用 db2look 從系統統計視圖中提取 T1、T2 的統計信息如下。

表 1. T1 統計信息

統計屬性 值 說明

表 CARD 10000 表的行數

表 NPAGES 68 表占用的頁面數

列 FILTER1 的 COLCARD 25 列的不同取值個數

列 FILTER1 的 NUMNULLS 0 列的空值行數

列 JOIN1 的 COLCARD 30 列的不同取值個數

列 JOIN1 的 NUMNULLS 0 列的空值行數

表 2. T2 統計信息

統計屬性 值 說明

表 CARD 10000 表的行數

表 NPAGES 68 表占用的頁面數

列 FILTER2 的 COLCARD 50 列的不同取值個數

列 FILTER2 的 NUMNULLS 0 列的空值行數

列 JOIN2 的 COLCARD 40 列的不同取值個數

列 JOIN2 的 NUMNULLS 0 列的空值行數

測試一

我們首先執行以下查詢來驗證公式。

select count(*)   
from (   
select   
t1.v1, t2.v1   
from   
t1,   
t2   
where   
t1.filter = 1   
and t2.join1 = t1.join1   
and t2.filter = 1 )   
as b;  

在表創建完成后，我們收集 T1 和 T2 的統計信息，在收集統計信息是只包括表的基本統計和列的統計信息，不包括列的分布信息。

db2 "runstats on table db2inst1.t1 on all COLUMNS "   
db2 "runstats on table db2inst1.t2 on all COLUMNS "  

使用 db2look 從系統統計視圖中提取 T1、T2 的統計信息如下。

表 1. T1 統計信息

統計屬性 值 說明

表 CARD 10000 表的行數

表 NPAGES 68 表占用的頁面數

列 FILTER1 的 COLCARD 25 列的不同取值個數

列 FILTER1 的 NUMNULLS 0 列的空值行數

列 JOIN1 的 COLCARD 30 列的不同取值個數

列 JOIN1 的 NUMNULLS 0 列的空值行數

表 2. T2 統計信息

統計屬性 值 說明

表 CARD 10000 表的行數

表 NPAGES 68 表占用的頁面數

列 FILTER2 的 COLCARD 50 列的不同取值個數

列 FILTER2 的 NUMNULLS 0 列的空值行數

列 JOIN2 的 COLCARD 40 列的不同取值個數

列 JOIN2 的 NUMNULLS 0 列的空值行數

測試一

我們首先執行以下查詢來驗證公式。

select count(*)   
from (   
select   
t1.v1, t2.v1   
from   
t1,   
t2   
where   
t1.filter = 1   
and t2.join1 = t1.join1   
and t2.filter = 1 )   
as b;  

以上的相關內容就是對DB2 優化器中針對 JOIN 語句的結果集估計的介紹，望你能有所收獲。

【編輯推薦】

1. shell抽取db2數據庫數據的方法示例
2. DB2數據庫日志文件進行歸檔的問題分析
3. DB2數據庫的安裝目錄結構介紹
4. DB2數據庫性能調整的命令介紹
5. DB2數據庫開發常見問題解答