PostgreSQL 原始碼解讀（17）- 查詢語句#2（查詢優化基礎）

本文簡單介紹了資料庫系統實現中查詢優化的關係代數基礎,包括優化所基於的關係代數等價規則等.
查詢優化的主要目標是把表示式樹變換成等價的表示式樹，使得在樹中的子表示式生成的關係的平均大小比優化前更小。次要目標是在一個單一查詢中，或在要同時求值多於一個查詢的時候的所有這些查詢中，儘可能形成公共子表示式。

一、等價規則

一個關係表示式可以表示成多種形式，前提是這些形式是等價的。如何把一個表示式變換為其他形式的表示式，遵循哪些關係代數規則，下面作一簡要描述。

1、選擇

冪等性
選擇是冪等性的,也就是說多次執行同一個選擇運算,跟只執行一次效果一樣:
σ_F(R)=σ_Fσ_Fσ_F(R)

滿足交換律
σ_F1σ_F2(R)=σ_F2σ_F1(R)

可分解
σ_F1∧F2(R)=σ_F1(σ_F2(R))=σ_F2(σ_F1(R))
σ_F1∨F2(R)=σ_F1(R)∪σ_F2(R)

選擇下推
笛卡爾積耗費的資源巨大，在應用笛卡爾積之前最大可能減少兩個關係的大小,把選擇下推至參與運算的關係中。
σ_F(R × S)，假設F可以分解為F1、F2、F3，即σ_F1σ_F2σ_F3，F2只與R有關，F3只與S有關，F1與R和S均有關係，那麼：
σ_F(R × S)=σ_F1(σ_F2(R) × σ_F3(S))

選擇和θ連線
σ_θ(R × S)= R ⋈_θ S
這其實是θ連線的定義.
另外,選擇運算在下面兩個條件下對θ連線運算具有分配律：
當選擇條件θ₀中的所有屬性只涉及參與連線運算的表示式之一時:
σ_θ₀(R₁ ⋈_θ R₂) = (σ_θ₀(R₁)) ⋈_θ R₂
當選擇條件θ₁只涉及R1的屬性,θ₂只涉及R2的屬性時:
σ_θ₁∧_θ₂(R₁ ⋈_θ R₂) =(σ_θ₁(R₁)) ⋈_θ (σ_θ₂(R₂))

選擇和集合運算
選擇在差、交和並運算上均有分配性:
σ_F(R ∪ S) = σ_F(R) ∪ σ_F(S)
σ_F(R ∩ S) = σ_F(R) ∩ σ_F(S)
σ_F(R - S) = σ_F(R) - σ_F(S)

選擇和投影
選擇與投影具有交換性(要求選擇中的列是投影欄位的子集):
π_{a₁,a₂,...}(σ_F(R))=σ_F(π_{a₁,a₂,...}(R))

2、投影

級聯
一系列投影運算中只有最後一個運算是必需的,其他的可省略:
π_{a₁,a_n,...}(π_{a₁,a₂,...}(π_{a₁,a₂,...}(R)))=π_{a₁,a_n,...}(R)

投影和連線
令L₁和L₂分別代表R1和R2的屬性,假設連線條件θ只涉及L₁∪L₂的屬性,則投影在θ連線上具有分配律:
π_L₁∪L₂(R₁ ⋈_θ R₂) = π_L₁(R₁) ⋈_θ π_L₂(R₂)

投影和集合運算
投影在差、交和並運算上均有分配性:
π_{a₁,a₂,...}(R ∪ S) = π_{a₁,a₂,...}(R) ∪ π_{a₁,a₂,...}(S)
π_{a₁,a₂,...}(R ∩ S) = π_{a₁,a₂,...}(R) ∩ π_{a₁,a₂,...}(S)
π_{a₁,a₂,...}(R - S) = π_{a₁,a₂,...}(R) - π_{a₁,a₂,...}(S)

3、連線

θ(自然)連線滿足交換律
R ⋈_θ S = S ⋈_θ R

θ連線滿足結合律
R₁ ⋈_θ₁ (R₂ ⋈_θ₂∧_θ₃ R₃)=R₁ ⋈_θ₁∧_θ₃ (R₂ ⋈_θ₂ R₃)
其中θ₂只涉及R2和R3的屬性.

自然連線滿足結合律
R₁ ⋈ (R₂ ⋈ R₃)=(R₁ ⋈ R₂) ⋈ R₃

4、集合運算

集合並和交滿足交換律
R₁ ∪ R₂ = R₂ ∪ R₁
R₁ ∩ R₂ = R₂ ∩ R₁

集合並和交滿足結合律
(R₁ ∪ R₂) ∪ R₃ = R₁ ∪ (R₂ ∪ R₃)
(R₁ ∩ R₂) ∩ R₂ = R₁ ∩ (R₂ ∩ R₃)

二、優化原則

儘可能早地執行選擇操作，儘可能在葉子節點完成選擇運算；
儘可能早地執行投影操作，儘可能在葉子節點完成投影運算；
避免笛卡兒積運算，儘可能把笛卡兒積之前和之後的選擇和投影運算合併一起完成。

三、案例研究

現有以下三個關係:
1、單位資訊T_DWXX(以下簡稱DW)

DWMC	DWBH	DWDZ
X有限公司	1001	廣東省廣州市荔灣區
Y有限公司	1002	北京市海淀區
Z有限公司	1003	廣西南寧市五象區

2、個人資訊T_GRXX(以下簡稱GR)

DWBH	GRBH	XM	NL
1001	901	張三	23
1002	902	李四	33
1002	903	王五	43

3、個人繳費資訊T_JFXX(以下簡稱JF)

GRBH	NY	JE
901	201801	401.30
901	201802	401.30
901	201803	401.30
902	201801	513.10
902	201802	513.10
902	201804	513.10
903	201801	372.22
903	201804	372.22

現要求列出單位編號為1001和1002的個人編號、姓名和繳費金額.
初始結果表示式為(純粹為了演示需要,把單位資訊加入到連線中,實際並不需要)：
π_GRBH,XM,JE(σ_{(DWBH=1001∨}_{DWBH=1002)∧}_{(DW.DWBH=GR.DWBH)∧(GR.GRBH=JF.GRBH)}(DW × GR × JF))
轉換為語法樹:

初始表示式

DW、GR和JF直接進行笛卡爾積，代價很高，執行選擇下推：
1、選擇下推，把查詢條件下推：

選擇下推

2、二次選擇下推，把選擇（連線）下推，右邊樹形成中間結果

二次選擇下推

3、投影下推

投影下推

通過以上轉換，減少了連線前的元組數量和參與運算的欄位，達到優化目的。

四、小結

1、等價規則：關係代數表示式可以遵循等價規則進行轉換；
2、優化：表示式通過等價規則可以改寫為更優的等價表示式。

五、參考

維基百科
《資料庫系統概念》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2374899/，如需轉載，請註明出處，否則將追究法律責任。