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