Database | 淺談Query Optimization (1)

珠璣位發表於2021-04-09
Database

綜述

由於SQL是宣告式語言(declarative)，使用者只告訴了DBMS想要獲取什麼，但沒有指出如何計算。因此，DBMS需要將SQL語句轉換成可執行的查詢計劃(Query Plan)。但是對同樣的資料可以有多種查詢方案，效能也差距很大，查詢優化器(Query Optimizer)的任務就是從給定的查詢中選擇一個最優的方案。

最早的查詢優化器實現是IBM在1970s設計的 System R，其中的概念和設計到現在依然有很多使用。對於查詢優化通常有兩種方案：

  1. 基於啟發式規則：啟發式優化將查詢的部分與已知的模式進行匹配，以重組計劃。這些規則對查詢進行轉換，消除低效率的部分，這種方式不需要檢查資料本身。
  2. 基於代價的搜尋：需要讀取資料並估計執行計劃的成本。然後從各個計劃中選擇成本最低的方案。

等價的關係代數(啟發式規則)

如果兩個關係代數表示式生成相同的元組集，則它們是等價的。DBMS 可以在沒有成本模型的條件下生成更優的查詢計劃，即查詢重寫(Query Rewriting)

當應用程式向資料庫傳送SQL查詢，DBMS首先要將SQL解析成語法樹的標記，Binder 查詢系統目錄將語法樹標記替換為內部識別符號，生成邏輯查詢計劃，最後由查詢優化器選擇最高效的執行方案。

image

一種等價的關係代數是謂詞下推，對於這樣的SQL查詢：

SELECT s.name, e.cid
FROM student AS s, enrolled AS e
WHERE s.sid = e.sid
AND e.grade = 'A'

相比於先連線再過濾，應當更早地對資料進行過濾，以減少連線時的元素數量。

image

有關選擇(selection)的優化：

  • 儘早執行過濾
  • 重排謂詞，將最具選擇性的謂詞優先應用
  • 分解複雜的謂詞，將之往下推

有關投影(projection)的優化：（列儲存無需進行這兩條優化）

  • 儘早進行投影以建立更小的元組並減少中間結果
  • 只投影被需要的屬性

有關連線(join)的優化：

  • R⋈S = S⋈R，因此可以重排多個表的連線順序

  • 但對於n個表，不同的連線順序為卡特蘭數(\(≈4^n\))

image

如果要對所有順序窮舉的話，當n較大時效率會非常低。連線順序通常由cost based search選擇最優/較優的方案。

SELECT ARTIST.NAME
FROM ARTIST, APPEARS, ALBUM
WHERE ARTIST.ID=APPEARS.ARTIST_ID 
AND APPEARS.ALBUM_ID=ALBUM.ID
AND ALBUM.NAME="Andy's OG Remix"

對於這樣的SQL查詢，最樸素的查詢方案可能是左圖所示，但通過：

  1. 分解複雜謂詞並向下推
  2. 將笛卡爾積替換為連線
  3. 在連線前消除不必要的屬性

可以優化為右圖所示的方案

image

其他優化包括：

忽略不必要的join、projection

SELECT A1.*
FROM A AS A1 JOIN A AS A2
ON A1.id = A2.id;  //unnecessary

SELECT * FROM A AS A1
WHERE EXISTS(SELECT val FROM A AS A2  //unnecessary
WHERE A1.id = A2.id);

合併謂詞：

SELECT * FROM A
WHERE val BETWEEN 1 AND 100
OR val BETWEEN 50 AND 150;

對於巢狀查詢，有兩種方案：

  1. 重寫，將其轉化為單次查詢
  2. 先進行子查詢，將結果儲存在臨時表中。得出最終結果後將臨時表丟棄。

基於代價的搜尋

這種優化方式分為兩個步驟：

  1. 成本估計
  2. 方案選擇

成本估計

首先要為特定的執行計劃生成成本估算，而訪問磁碟的消耗始終是查詢中最主要的消耗，並且還要考慮順序訪問還是隨機訪問，這兩者在效能上也有極大差異。

選擇基數

DBMS 在目錄中儲存有關屬性、索引的內部資訊。對於每個關係R，DBMS維護以下資訊：

  • \(N_R\) ：R中的元組數量
  • \(V(A, R)\)：R中在屬性A上不同值的數目

則選擇基數(selection cardinality SC(A,R))為給定屬性的值的平均數量 \(SC(A, R) = N_R / V(A,R)\)

在計算cost的時候，需要考慮不同謂詞選擇的範圍。謂詞的選擇性(selective)即是一個謂詞限定的部分。

比如對SC(A,R)=2的關係R中，若A的資料為1-100的連續整數，則對於查詢

SELECT * FROM R 
WHERE A >50

可以計算出\(sel(A>50) = 50/100 = 1/2\)

SELECT * FROM R
WHERE A = 2
OR B LIKE 'A%'

\(sel(P1 ⋁ P2) = sel(P1) + sel(P2) – sel(P1⋀P2) = sel(P1) + sel(P2) – sel(P1) ∙ sel(P2)\)

也可以說，選擇性就是指這個範圍的資料出現的概率。

但以上的估計基於三個假設：

  1. 資料是均勻分佈的
  2. 多個謂詞之間相互獨立，可以獨立計算概率
  3. 內部關係中的key在外表中同樣存在

因此得出的結果是一個估計值，並不精確。

統計直方圖

可以對第一個假設進行優化，在每個表中儲存有關資料的直方圖，將資料按範圍進行統計，在計算sel時從直方圖中計算相應的比例。

image

樣本估算

現代DBMS從表中選擇一定的樣本估算sel，當底層表發生顯著變化時更新樣本。

方案選擇

對於簡單的單表查詢(OLTP)，通過啟發式規則，利用索引和二分搜尋足以獲取良好的效能。

但是對於OLAP中的多表查詢，不同的連線順序會對效能造成很大影響。而由於關係的增加會導致可選擇方案指數增長(\(4^n\))，因此需要約束可選擇的空間。

System R 中只考慮左深連線樹(Left Deep Join)，將選擇空間縮小到 \(n!\)，但現代DBMS中不再總做出這樣的假設。

左深連線樹即連線的右表一定為一個基本表，通過流水線連線，中間結果不寫入臨時檔案。

image

對於連線，需要考慮連線的順序，不同表之間連線的方式(Hash join, Sort-Merge join)，獲取資料的方式。通過動態規劃對方案進行剪枝。

除了通過動態規劃剪枝之外，當連線表過多時，會選擇一些區域性最優解的方式：

  1. greedy join enumeration algorithm

    在每次迴圈中，選擇使總代價最低的方案

    • 多項式時間演算法，但結果不一定最優

  2. Randomized algorithm

    隨機重寫查詢方案，利用模擬退火等演算法進行優化

  3. Genetic algorithm(遺傳演算法)

    通過連線方案（結合子代）和隨機突變進行優化

至於為什麼是左深連線樹，而不是右深連線樹？動態規劃的執行優化又是如何實現的？留待後面分析

相關文章