DB2 優(yōu)化器中針對 JOIN 語句的結(jié)果集估計
在所有 SQL 語句基估計過程中，以 JOIN 語句的計算過程最復(fù)雜，而 JOIN 語句恰恰是進行性能優(yōu)化的重點。本文主要關(guān)注的是 DB2 優(yōu)化器在進行基估計時采用的相關(guān)計算方法、輸入等。
簡介
優(yōu)化器是 DB2 的心臟和靈魂(可以把它類比成寶馬 730 或波音 747 的發(fā)動機引擎一樣)。它分析 SQL 語句并確定可以滿足每條語句的最有效的存取路徑。 DB2 SQL 優(yōu)化器可以估計每個備選訪問計劃的執(zhí)行成本，并根據(jù)其估計結(jié)果選擇一個最佳訪問計劃。
在優(yōu)化器在優(yōu)化一個 SQL 語句的過程中使用到兩個非常重要的概念：selectivity 和 cardinality 。 selectivity 是指一個 SQL 操作的得出結(jié)果集占原來結(jié)果集的百分比，而 cardinality 就是指一個 SQL 操作的得出結(jié)果集的行數(shù)。
為正確地確定每種訪問計劃的成本，DB2 優(yōu)化器都會對每個步驟產(chǎn)生的結(jié)果集大小即返回的行數(shù)進行估計，這就是優(yōu)化器的基估計。 DB2 優(yōu)化器需要準確的基數(shù)估計值。基數(shù)估計是這樣一種過程：在應(yīng)用了謂詞或執(zhí)行了聚集之后，優(yōu)化器使用統(tǒng)計信息確定部分查詢結(jié)果的大小。對于訪問計劃的每個操作符，優(yōu)化器將估計該操作符的基數(shù)輸出。一個或更多謂詞的應(yīng)用可以減少輸出流基數(shù)。
JOIN 謂詞
當我們在 SQL 里面需要對多個表進行 join 的時候，DB2 會首先選擇其中的 2 個表進行 join，并獲取到一個中間的結(jié)果集，然后 DB2 可能會用這個中間的結(jié)果集和第三個表做 join，再次獲得中間的結(jié)果集(當然也可能是把另外 2 個表做 join，然后把兩個中間的結(jié)果集進行 join 操作)，不管是怎么操作，DB2 一次能夠 join 的表的個數(shù)肯定是兩個。因此當優(yōu)化器在考慮 Join 如何處理的時候，join 的順序就是一個很重要的問題，因為我們總是希望能夠在最開始就把結(jié)果集控制的盡量小。
一個 JOIN 謂詞一般描述如下所示：
- T1.joincol=T2.joincol
在實際應(yīng)用過程中，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 表進行過濾，然后兩個經(jīng)過過濾的表進行 JOIN 操作。至于 JOIN 采用 hash join 還是 Merge Join 或者 NestLoop Join 取決于 DB2 的優(yōu)化級別、參數(shù)設(shè)置以及成本估計。
DB2 Join 謂詞選擇性計算公式如下：
- Selectivity (T1.y = T2.y)= 1/max(colcard(T1. joincol), colcard(T2. joincol))
其中，colcard(T1. joincol) 指 T1 表 joincol 列的不同值的個數(shù)，colcard(T2. joincol) 指 T2 表 joincol 列的不同值的個數(shù)，兩者取較大的一個作為 Join 謂詞計算依據(jù)。此公式存在兩個假設(shè)：
包含性，即 T2. joincol 的所有取值都在 T1 joincol 取值范圍內(nèi)，反之也行。
均衡性，即兩個連接列上的數(shù)據(jù)分布均勻。
DB2 優(yōu)化器中針對 JOIN 語句的結(jié)果集估計
作者: 駱洪青, 出處:IT專家網(wǎng)論壇, 責任編輯: 陳子琪, 2009-06-24 07:00
DB2 優(yōu)化器在為 SQL 語句生成執(zhí)行計劃時，都會對每個步驟產(chǎn)生的結(jié)果集大小進行估計，這就是DB2 優(yōu)化器的基估計。在所有 SQL 語句基估計過程中，以 JOIN 語句的計算過程最復(fù)雜，而 JOIN 語句恰恰是進行性能優(yōu)化的重點。
DB2 Join 謂詞基估計計算公式如下：
- Join Cardinality =Join Selectivity *
- filtered cardinality(t1) *
- filtered cardinality(t2)
其中 filtered cardinality(t1) 是在 T1 表上應(yīng)用本地謂詞后獲得結(jié)果集，filtered cardinality(t2) 是在 T2 表上應(yīng)用本地謂詞后獲得結(jié)果集。
示例#p#
創(chuàng)建測試表
我們創(chuàng)建以下測試表：
T1 表擁有 10000 行數(shù)據(jù)。參加進行 JOIN 操作的列 join1 數(shù)據(jù)均勻分布，取值范圍在 0 ～ 29 之間，沒有空值。對 T1 進行過濾的列 filter1 數(shù)據(jù)也均勻分布，取值在在 0 ～ 24 之間。 V1 列從 0 自然增長到 9999 。
T2 表擁有 10000 行數(shù)據(jù)。參加進行 JOIN 操作的列 join2 數(shù)據(jù)均勻分布，取值范圍在 0 ～ 29 之間，沒有空值。對 T2 進行過濾的列 filte2r 數(shù)據(jù)也均勻分布，取值在在 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
- ;
在表創(chuàng)建完成后，我們收集 T1 和 T2 的統(tǒng)計信息，在收集統(tǒng)計信息是只包括表的基本統(tǒng)計和列的統(tǒng)計信息，不包括列的分布信息。
- db2 "runstats on table db2inst1.t1 on all COLUMNS "
- db2 "runstats on table db2inst1.t2 on all COLUMNS "
使用 db2look 從系統(tǒng)統(tǒng)計視圖中提取 T1、T2 的統(tǒng)計信息如下。
表 1. T1 統(tǒng)計信息
統(tǒng)計屬性 值 說明
表 CARD 10000 表的行數(shù)
表 NPAGES 68 表占用的頁面數(shù)
列 FILTER1 的 COLCARD 25 列的不同取值個數(shù)
列 FILTER1 的 NUMNULLS 0 列的空值行數(shù)
列 JOIN1 的 COLCARD 30 列的不同取值個數(shù)
列 JOIN1 的 NUMNULLS 0 列的空值行數(shù)
表 2. T2 統(tǒng)計信息
統(tǒng)計屬性 值 說明
表 CARD 10000 表的行數(shù)
表 NPAGES 68 表占用的頁面數(shù)
列 FILTER2 的 COLCARD 50 列的不同取值個數(shù)
列 FILTER2 的 NUMNULLS 0 列的空值行數(shù)
列 JOIN2 的 COLCARD 40 列的不同取值個數(shù)
列 JOIN2 的 NUMNULLS 0 列的空值行數(shù)
測試一
我們首先執(zhí)行以下查詢來驗證公式。
- 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;
在表創(chuàng)建完成后，我們收集 T1 和 T2 的統(tǒng)計信息，在收集統(tǒng)計信息是只包括表的基本統(tǒng)計和列的統(tǒng)計信息，不包括列的分布信息。
- db2 "runstats on table db2inst1.t1 on all COLUMNS "
- db2 "runstats on table db2inst1.t2 on all COLUMNS "
使用 db2look 從系統(tǒng)統(tǒng)計視圖中提取 T1、T2 的統(tǒng)計信息如下。
表 1. T1 統(tǒng)計信息
統(tǒng)計屬性 值 說明
表 CARD 10000 表的行數(shù)
表 NPAGES 68 表占用的頁面數(shù)
列 FILTER1 的 COLCARD 25 列的不同取值個數(shù)
列 FILTER1 的 NUMNULLS 0 列的空值行數(shù)
列 JOIN1 的 COLCARD 30 列的不同取值個數(shù)
列 JOIN1 的 NUMNULLS 0 列的空值行數(shù)
表 2. T2 統(tǒng)計信息
統(tǒng)計屬性 值 說明
表 CARD 10000 表的行數(shù)
表 NPAGES 68 表占用的頁面數(shù)
列 FILTER2 的 COLCARD 50 列的不同取值個數(shù)
列 FILTER2 的 NUMNULLS 0 列的空值行數(shù)
列 JOIN2 的 COLCARD 40 列的不同取值個數(shù)
列 JOIN2 的 NUMNULLS 0 列的空值行數(shù)
測試一
我們首先執(zhí)行以下查詢來驗證公式。
- 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;
以上的相關(guān)內(nèi)容就是對DB2 優(yōu)化器中針對 JOIN 語句的結(jié)果集估計的介紹，望你能有所收獲。
【編輯推薦】
