我們今天主要向大家講述的是在 DB2 優(yōu)化器中正確使用分布統(tǒng)計(jì)信息的操作步驟，我們大家都知道DB2 優(yōu)化器是一種基于成本的優(yōu)化器。它根據(jù)表和索引的相關(guān)統(tǒng)計(jì)信息作出決策。DB2 在生成統(tǒng)計(jì)信息時(shí)。

不但能提供基本統(tǒng)計(jì)信息，還允許創(chuàng)建所謂的分布統(tǒng)計(jì)信息。本文解釋什么是分布統(tǒng)計(jì)信息、分布統(tǒng)計(jì)信息在哪些情況下尤為重要，以及應(yīng)用程序開(kāi)發(fā)人員應(yīng)該考慮些什么，才能使 DB2 優(yōu)化器創(chuàng)建有效的訪問(wèn)計(jì)劃。

簡(jiǎn)介

為了執(zhí)行查詢或 DML 語(yǔ)句（INSERT、UPDATE、DELETE），DB2 必須創(chuàng)建一個(gè)訪問(wèn)計(jì)劃（access plan）。訪問(wèn)計(jì)劃定義按什么順序訪問(wèn)表，使用哪些索引，以及用何種連接（join）方法來(lái)關(guān)聯(lián)數(shù)據(jù)。好的訪問(wèn)計(jì)劃對(duì)于 SQL 語(yǔ)句的快速執(zhí)行至關(guān)重要。DB2 優(yōu)化器可以創(chuàng)建訪問(wèn)計(jì)劃。這是一種基于成本的優(yōu)化器，這意味著它是根據(jù)表和索引的相關(guān)統(tǒng)計(jì)信息來(lái)作出決策的。

DB2 在生成統(tǒng)計(jì)信息時(shí)，不但能提供基本統(tǒng)計(jì)信息，還允許創(chuàng)建所謂的分布統(tǒng)計(jì)信息。不但數(shù)據(jù)庫(kù)管理員要理解分布統(tǒng)計(jì)信息，而且應(yīng)用程序開(kāi)發(fā)人員也要理解分布統(tǒng)計(jì)信息。應(yīng)用程序開(kāi)發(fā)人員必須小心謹(jǐn)慎，因?yàn)樵谀承┣闆r下分布統(tǒng)計(jì)信息對(duì)于 DB2 優(yōu)化器來(lái)說(shuō)非常重要。

主變量或參數(shù)標(biāo)記（在 Java 中為 java.sql.PreparedStatement）的使用可能會(huì)造成阻礙，使優(yōu)化器無(wú)法***限度地利用分布統(tǒng)計(jì)信息。本文解釋什么是分布統(tǒng)計(jì)信息、分布統(tǒng)計(jì)信息在哪些情況下尤為重要，以及應(yīng)用程序開(kāi)發(fā)人員應(yīng)該考慮些什么，才能使 DB2 優(yōu)化器創(chuàng)建有效的訪問(wèn)計(jì)劃。

基本統(tǒng)計(jì)信息和分布統(tǒng)計(jì)信息

在研究分布統(tǒng)計(jì)信息之前，我們先來(lái)看看基本統(tǒng)計(jì)信息，只要執(zhí)行 RUNSTATS 即可收集這些表的相關(guān)統(tǒng)計(jì)信息。

表的相關(guān)統(tǒng)計(jì)信息：

當(dāng)前使用的頁(yè)面數(shù)

包含記錄行的頁(yè)面數(shù)

溢出的行數(shù)

表中的行數(shù)（基數(shù)）

對(duì)于 MDC 表，還有包含數(shù)據(jù)的塊（block）數(shù)

表中各列的相關(guān)統(tǒng)計(jì)信息：

列的基數(shù)

列的平均長(zhǎng)度

列中第二大的值

列中第二小的值

列中 NULL 值的個(gè)數(shù)

通常，執(zhí)行 RUNSTATS 時(shí)，不但可以收集到關(guān)于表的統(tǒng)計(jì)信息，而且還可以收集到相應(yīng)的索引的相關(guān)統(tǒng)計(jì)信息。要了解為索引而收集的統(tǒng)計(jì)信息，請(qǐng)參閱 DB2 Administration Guide: Performance - Statistical information that is collected。

觀察一個(gè)表的基本統(tǒng)計(jì)信息，您可以看到，DB2 優(yōu)化器知道一個(gè)表由多少行組成（表的基數(shù)），以及一個(gè)列包含多少個(gè)不同的值（列的基數(shù)）。但是，還有一些信息是基本統(tǒng)計(jì)信息無(wú)法提供的。例如，基本統(tǒng)計(jì)信息不能告訴優(yōu)化器一個(gè)列中某些值出現(xiàn)的頻率。假設(shè)表 TABLE_X 有大約 1,000,000 行，在該表上執(zhí)行這樣一條查詢：

SELECT * FROM TABLE_X WHERE COLUMN_Y = 'VALUE_Z'

難道 DB2 優(yōu)化器知道 TABLE_X 中有多少行滿足條件 COLUMN_Y = 'VALUE_Z' 不重要嗎？換句話說(shuō)：知道這個(gè)查詢將返回 1 行、100 行、1000 行還是 10000 行有什么不好呢？

實(shí)際上，通過(guò)基本統(tǒng)計(jì)信息，DB2 優(yōu)化器只能估計(jì) 'VALUE_Z' 在 COLUMN_Y 中出現(xiàn)的頻率。在這種情況下，優(yōu)化器認(rèn)為所有值在 COLUMN_Y 中是平均分布的，這意味著它認(rèn)為所有的值都有相同的出現(xiàn)頻率。如果事實(shí)碰巧如此，這樣估計(jì)并無(wú)大礙。但是，如果有些值比其他值出現(xiàn)得更頻繁一些（例如，如果 'VALUE_Z' 出現(xiàn) 900,000 次，即占所有行的 90%）。

那么優(yōu)化器不能考慮到這一點(diǎn)，因而生成的訪問(wèn)計(jì)劃就不是***的。而分布統(tǒng)計(jì)信息可以填補(bǔ)這一空白。分布統(tǒng)計(jì)信息可以提供關(guān)于數(shù)據(jù)出現(xiàn)頻率及其分布情況的信息，如果數(shù)據(jù)庫(kù)中存儲(chǔ)了很多重復(fù)值，并且數(shù)據(jù)在表中并非平均分布的時(shí)候，分布統(tǒng)計(jì)信息對(duì)于基本統(tǒng)計(jì)信息是一個(gè)重要的補(bǔ)充。

分布統(tǒng)計(jì)信息的類型 —— 頻率（frequency）統(tǒng)計(jì)信息和分位數(shù)（quantile）統(tǒng)計(jì)信息

有兩種不同類型的分布統(tǒng)計(jì)信息 —— 頻率統(tǒng)計(jì)信息和分位數(shù)統(tǒng)計(jì)信息。讓我們通過(guò)一個(gè)示例表來(lái)研究一下這兩種不同類型的分布統(tǒng)計(jì)信息。

示例表 “CARS” 表示一家汽車(chē)制造商，對(duì)于生產(chǎn)的每一輛汽車(chē)，在表中都有相應(yīng)的一行。每輛汽車(chē)可以由它的 ID 來(lái)標(biāo)識(shí)，因此 “ID” 是表 “CARS” 的主鍵（PK）。此外，表中有一個(gè) “STATE” 列，表明汽車(chē)當(dāng)前處在制造流程中的哪一步。

一輛汽車(chē)的制造流程從第 1 步開(kāi)始，然后是第 2 步、第 3 步，...、第 49 步、第 50 步、第 51 步、...、第 98 步、第 99 步，一直到第 100 步 —— 第 100 步意味著汽車(chē)已經(jīng)完工了。已完工的汽車(chē)所對(duì)應(yīng)的行仍然保留在表中，后續(xù)流程（例如投訴管理、質(zhì)量保證等）仍要用到這些行。

汽車(chē)制造商生產(chǎn) 10 種不同型號(hào)（“TYPE” 列）的汽車(chē)。為了簡(jiǎn)化問(wèn)題，在這個(gè)示例表中，各種汽車(chē)型號(hào)命名為 A、B、C、D、...、J。除主鍵索引（在 “ID” 列上）之外，“STATE” 列上也有一個(gè)索引（“I_STATE”），在 “TYPE” 列上還有一個(gè)索引（“I_TYPE”）。實(shí)際上，一個(gè) “CARS” 表包含的列遠(yuǎn)不止 “ID”、“STATE” 和 “TYPE”。為簡(jiǎn)單起見(jiàn)，示例表中沒(méi)有出現(xiàn)其他這些列。

頻率統(tǒng)計(jì)信息

假設(shè)表 CARS 現(xiàn)在有大約 1,000,000 條記錄，不同的型號(hào)在表中出現(xiàn)的頻率如下所示：

表 1. 表 CARS 中 TYPE 列的頻率統(tǒng)計(jì)信息

TYPE COUNT(TYPE)   
A 506135   
B 301985   
C 104105   
D 52492   
E 19584   
F 10123   
G 4876   
H 4589   
I 4403   
J 3727  

型號(hào)為 A 的汽車(chē)最受購(gòu)買(mǎi)者的青睞，因此生產(chǎn)的汽車(chē)中大約有 50% 是這種型號(hào)。型號(hào) B 和型號(hào) C 僅次于型號(hào) A ，分別占所有汽車(chē)的 30% 和 10%。其他所有型號(hào)加在一起僅占 10%。

上面的表顯示了 “TYPE” 列的頻率統(tǒng)計(jì)信息。通過(guò)基本統(tǒng)計(jì)信息，DB2 優(yōu)化器只能了解到該表包含 1,000,000 行（表的基數(shù)）和 10 種不同的值（型號(hào)），即 A 到 J。如果沒(méi)有分布統(tǒng)計(jì)信息，優(yōu)化器會(huì)認(rèn)為每種值以相同的頻率出現(xiàn)，大約都是出現(xiàn) 100,000 次。而一旦生成了關(guān)于 “TYPE” 列的分布統(tǒng)計(jì)信息，優(yōu)化器即可了解每種型號(hào)真正的出現(xiàn)頻率。因此，優(yōu)化器清楚各種已有型號(hào)出現(xiàn)的不同頻率。

優(yōu)化器使用頻率統(tǒng)計(jì)信息來(lái)計(jì)算用于檢查相等或不等的謂詞的過(guò)濾因子。例如：

SELECT * FROM CARS WHERE TYPE = 'H'  

分位數(shù)統(tǒng)計(jì)信息

與頻率統(tǒng)計(jì)信息不同，分位數(shù)統(tǒng)計(jì)信息與不同值的出現(xiàn)頻率無(wú)關(guān)，而與一個(gè)表中有多少行小于或大于某個(gè)值（或者有多少行介于兩個(gè)值之間）相關(guān)。分位數(shù)統(tǒng)計(jì)信息提供關(guān)于一個(gè)列中的值是否聚合的信息。為獲得這樣的信息，DB2 假定列中的值是按升序排列的，并根據(jù)正則行間隔確定相應(yīng)的值。

我們來(lái)看看表 CARS 中的 “STATE” 列，該列按升序排列。根據(jù)正則行間隔，即可確定 “STATE” 的對(duì)應(yīng)值。

表 2. CARS 表中 STATE 列的分位數(shù)統(tǒng)計(jì)信息

COUNT(row) STATE ASC   
5479 1   
54948 10   
109990 21   
159885 31   
215050 42   
265251 52   
320167 63   
370057 73   
424872 84   
475087 94   
504298 100   
... 100   
1012019 100  

由于已完工的汽車(chē)仍然沒(méi)有從表中刪除，因此狀態(tài)為 100 （＝完工）的汽車(chē)數(shù)量比所有處于其他狀態(tài)的汽車(chē)總和還多。已完工的汽車(chē)占表中所有記錄的 50%。

注意： 在實(shí)際情況下，已完工的汽車(chē)數(shù)量甚至還要更多（例如超過(guò) 99%）。在后文中的具體例子中可看到這種情況。

上表顯示了 “STATE” 列的分位數(shù)統(tǒng)計(jì)信息。有了這種關(guān)于有多少行分別小于和大于確定值的信息，DB2 優(yōu)化器即可計(jì)算出用于測(cè)試小于（小于等于）、大于（大于等于）或介于兩值之間的謂詞的過(guò)濾因子。例如：

SELECT * FROM CARS WHERE STATE < 100   
SELECT * FROM CARS WHERE STATE BETWEEN 50 AND 70   

根據(jù)已有的分位數(shù)統(tǒng)計(jì)信息計(jì)算出來(lái)的過(guò)濾因子不是很精確，但即使只收集 20 個(gè)值，其誤差仍然低于 5%。

完整內(nèi)容的學(xué)習(xí)，請(qǐng)?jiān)L問(wèn)：

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0606fechner/

【編輯推薦】

1. DB2數(shù)據(jù)庫(kù)物化視圖之MQT物化查詢表如何操作？
2. DB2 9.5分區(qū)管理以及其應(yīng)用實(shí)踐的詳細(xì)描述
3. DB2數(shù)據(jù)庫(kù)在創(chuàng)建存儲(chǔ)過(guò)程中有哪些錯(cuò)誤出現(xiàn)？
4. DB2 CMO安裝7.1.2.6補(bǔ)丁之后會(huì)出現(xiàn)什么？
5. IBM DB2中新手要了解的東西有哪些？