[筆記]CBO的繆論

DBAZine上的文章

Fallacies Of The CBO

好文.

詞彙：

NDV                     唯一值的數目。A.k.a NUM_DISTINCT

Selectivity             表示行集中選定行的比例

FF（FILTER FACTOR）     選擇性的另外一個說法，特別用於多謂詞的組合選擇性

Cardinality             行集中的行數目

Base cardinality        基表中行的數目

Effective cardinality   從基表中選擇的行估計數目。依賴於在基表上不同列指定的謂詞

Join cardinality        兩個行集連線時產生的行數目。由兩個行集的cardinality產生，等於連線謂詞Selectivity的乘積

Distinct cardinality    行集中某列上唯一值的數目

Group cardinality       GROUP BY操作後行集中產生的行數目

§1.1  CBO

Selectivity（選擇性）：表示行集中行的比例。行集可以是基表、檢視或者連線、Group By操作的結果。選擇性與查詢條件繫結。選擇性表示行集中有多少行透過謂詞測試。選擇性的範圍是0.0到1.0。0.0表示沒有行，1.0表示所有行。

Cardinality（基數）：表示行級中的行數目。行集可以是基表、檢視或者連線、Group By操作的結果。基數的有效性依賴於基表中不同的謂詞，這是因為每個謂詞作為基表中選擇行的連續過濾器。有效的基數透過基本基數和所有表中謂詞選擇性的組合。如果表上沒有謂詞，有效性基數等於基本基數，連線基數是兩個行級連線在一起的時候產生的行數目。連線是透過作為結果過濾器的連線謂詞的兩個行集的笛卡兒積。連線基數等於連線謂詞選擇性的乘積。

1.      基本訪問成本

基表訪問成本

表掃描：nblks/k（k取決於oracle的版本和初始化引數db_file_multiblock_read_count）

唯一掃描：blevel + 1

快速全掃描：leaf_blocks/k

索引：blevel + FF*leaf_blocks

範圍掃描：blevel+FF*leaf_blocks+FF*clustering_factor

Selectivity=FF=估計card/基表card ó 估計card=FF*基表card

其中FF表示過濾因子，基表card表示基表的行數目比如(NUM_ROWS)。

2.      連線成本

巢狀迴圈連線

對於外部行集中每一行，都需要訪問內部行集以查詢所有匹配的行用於連線。因此，在巢狀迴圈連線中，內部行集訪問的次數與外部行集的行數目一致。

成本 ＝ 外部訪問成本 ＋ （內部訪問成本*外部基數性）

排序合併連線

將要連線的兩個行集在沒有按照鍵值排序情況下先按連線鍵排序

成本 ＝ 外部訪問成本 ＋ 內部訪問成本＋ 排序成本（如果需要排序）

雜湊連線

內部行集在記憶體中雜湊並使用連線鍵建立雜湊表。外部行集中的每行被雜湊，雜湊表用於探查所有連線匹配的行。假如內部行集非常大，那麼只有一部分在記憶體中雜湊，該部分稱為雜湊分割槽。外部行集中的每一行雜湊用於在雜湊分割槽中探查匹配的行。然後內部行集的下一部分在記憶體中雜湊，外部行集的每一行雜湊用於在雜湊分割槽中探查匹配的行，該過程重複直到內部行集的所有分割槽都用完。

成本 ＝ （外部訪問成本*雜湊分割槽編號） ＋ 內部訪問成本

這三種連線方法有自己本身的成本公式但都依賴於外部的、內部的基本訪問成本和它們有效的基數性。

3.      其他成本

輔助成本比如GROUP BY/ORDER BY的排序通常與操作所基於資料的大小、基數性有關。

我們看到訪問計劃的成本是元件的估計基數性的函式，這就是基數性估計精確性的重要性了。依賴於估計基數性的成本是兩個不同執行計劃不能比較的原因之一。

4.      假定

在關聯式資料庫中查詢最佳化的整個問題其實就是關係理論的深入。有三個核心假定――作者稱為繆論，因為它們通常是錯誤的。

§1.2  繆論一：統一分散式假定

有三種不同形式的分散式假定：A－列值在表所有物理塊中均勻分佈；B－假定列值在表中所有行中均勻分佈；（A、B均獨立的可為真或假）C－屬性值在值譜上均勻分佈，比如在最大和最小值之間。

1.      列值在所有塊中統一分佈

該假定不會營銷基數性估計但會純粹和直接的影響索引訪問的成本。

值得注意的是，統一分佈假定表示最壞的情況：列值的更好叢集性訪問成本越低。如果含有特定列值的所有n行都在1個塊中存在，訪問的I/O成本是1而不是在統一分佈時的n。（參考）。

2.      所有行上列值的統一分佈

屬性值的分佈頻率通常伴隨有權值或者是“Zipf”分佈。

解決方法

使用histograms（柱狀圖）。

但查詢使用了繫結變數，如果沒有柱狀圖，密度等於1/NDV，有柱狀圖密度的計算會不同，柱狀圖甚至會在使用繫結變數的查詢中也有所不同。透過使用不同數目的桶，可以產生密度的不同值和基數性估計。

3.      屬性值在給定值範圍上均勻分佈

比如id值的分佈為0，1－12，998－999，NDV＝15。

解決方法

使用列上的柱狀圖。

§1.3  繆論二：謂詞依賴性假定

當表上有多於一個的謂詞，單個謂詞的選擇性透過下面的規則組合：

P1 AND P2    S(P1&P2) = S(P1)*S(P2)

P1 OR  P2    S(P1|P2) = S(P1) + S(P2) – [S(P1)*S(P2)]

解決方法

如果謂詞不是獨立的，對於不正確的選擇性和基數估計是無法更正的。

§1.4  繆論三：連線一致假定

連線一致假定意思是一張表中的一行近似相等於與第二張表的任意行連線。【The join uniformity assumption states that a row from one table is equally likely to join with any row from the second table.】