資料庫查詢優化：巢狀查詢

wenliang.zhang發表於2017-01-24
資料庫優化巢狀

Table of Contents

巢狀查詢是 SQL 中表達能力很強的一種機制，既給應用帶來了方便也給查詢優化帶來了很大的挑戰。本文總結一下經典的單機系統對巢狀查詢的優化。

1 巢狀查詢的分類和優化概述

比較好的分類和處理了典型巢狀查詢的經典文獻是 Kim 的 On Optimizing an SQL-like Nested Query 1。不過 Kim 的演算法很快被發現在特定場景下存在一些小缺陷，需要打補丁修復。例如 Ganski 等對此做了改進 2。SQL 語言的進化過程中不斷引入的新特性，也會影響到巢狀查詢的處理，例如某些系統支援的 LIMIT 語句。具體產品中的實現可以從 ORACLE 的部落格中得到一些啟示：3，4。

2 Kim: On Optimizing an SQL-like Nested Query

Kim 定義了巢狀查詢的 5 種基本形式並給出了轉換演算法。最後組合成一個通用演算法來處理任意複雜的巢狀查詢（一般稱為巢狀查詢的非巢狀化）。在一個 SQL 語句中訪問多個表的典型機制為： 連線謂詞（JOIN）、巢狀謂詞、除法謂詞。非巢狀化就是把其他兩種形式的查詢轉換為 JOIN。巢狀謂詞會形成 4 種形式的巢狀查詢，而除法謂詞會形成另 1 種形式的巢狀查詢，因此總共是 5 種。考慮到除法幾乎沒有系統實現它，後續可以略過。

2.1 巢狀查詢的分類

首先，定義巢狀的層數。如果查詢中只有一個查詢塊（SELECT、FROM、WHERE），顯然不存在巢狀查詢，此時巢狀的層數為0。如果查詢中有兩個查詢塊，外查詢的叫做外部塊，內查詢的叫做內部塊，此時巢狀層數為1。查詢塊巢狀的層次數顯然可以更多，而且一個 WHERE 條件中可以有多個巢狀的子查詢。查詢塊的 FROM 子句後面可以出現多個表。WHERE 條件以及子查詢的結果列也可以出現多個，例如：(SNO, SNAME) = (SELECT SNO, SNAME FROM SUPPLY)。Kim 劃分巢狀查詢種類是從子查詢有沒有連線條件以及聚集函式這兩個角度考慮的。

2.1.1 A 類

內查詢塊沒有對外查詢塊的表的引用（非相關子查詢），並且查詢結果是聚集函式（不帶 GROUP BY，結果集是單行）。

SELECT SNO
FROM SHIPMENT
WHERE PNO = (SELECT MAX(PNO)
             FROM PART
             WHERE PRICE > 25)

SELECT SNO FROM SHIPMENT WHERE PNO = (SELECT MAX(PNO) FROM PART WHERE PRICE > 25)

2.1.2 N 類

內查詢塊沒有對外查詢塊的表的引用（非相關子查詢），並且查詢結果沒有聚集函式（結果集是很可能是多行）。

SELECT SNO
FROM SHIPMENT
WHERE PNO IN (SELECT PNO
              FROM PART
              WHERE PRICE > 25)

SELECT SNO FROM SHIPMENT WHERE PNO IN (SELECT PNO FROM PART WHERE PRICE > 25)

2.1.3 J 類

內查詢塊有對外查詢塊的表的引用（相關子查詢），並且查詢結果沒有聚集函式。

SELECT SNO
FROM SHIPMENT
WHERE PNO IN (SELECT PNO
              FROM PROJECT
              WHERE SHIPMENT.SNO = PROJECT.JNO AND JLOC = `NEW YORK`)

SELECT SNO FROM SHIPMENT WHERE PNO IN (SELECT PNO FROM PROJECT  WHERE SHIPMENT.SNO = PROJECT.JNO AND JLOC = `NEW YORK`)

2.1.4 JA 類

內查詢塊有對外查詢塊的表的引用（相關子查詢），並且查詢結果集有聚集函式。

SELECT SNO
FROM SHIPMENT
WHERE PNO = (SELECT MAX(PNO)
             FROM PROJECT
             WHERE PROJECT.JNO = SHIPMENT.JNO AND JLOC = `NEW YORK`)

SELECT SNO FROM SHIPMENT WHERE PNO = (SELECT MAX(PNO) FROM PROJECT  WHERE PROJECT.JNO = SHIPMENT.JNO AND JLOC = `NEW YORK`)

2.1.5 D 類

連線謂詞與除法謂詞一起形成的查詢中，帶有兩個內查詢塊。任何一個的連線謂詞引用了外查詢塊都會形成 D 型巢狀查詢。

SELECT SNAME
FROM SUPPLIER
WHERE (SELECT PNO
       FROM SHIPMENT
       WHERE SHIPMENT.SNO = SUPPLIER.SNO)
      CONTAINS
      (SELECT PNO
       FROM PART
       WHERE COLOR = `RED` AND PRICE > 25)

2.2 巢狀查詢的優化

如果採用最簡單直接的執行演算法，對外查詢塊的每條記錄，需要執行內查詢塊一次。A 類查詢的子查詢可以只計算一次，因此不再需要做特殊的轉換或優化。N 類沒有這麼直接的優化，有必要做優化。J、JA、D 類存在類似的問題。

N 類的巢狀查詢可以被等價轉換為連線。對於子查詢可能會產生的重複值，可通過 semi-join 來消除。op 可以是 IN 或標量操作符。（注意，標量運算子要求結果集是單行。）巢狀1層的轉換演算法比較直接，命名為 NEST-N-J。J 類的巢狀查詢也可以用類似的演算法來轉換。對於 NOT IN 操作符，要採用 anti-join。而且，對於 J 類的查詢，還要確保 anti-join 的計算是發生在 join 條件之後。

JA 類的查詢可以引入一個做聚集運算的臨時表來等價轉換為 J 類查詢，演算法命名為 NEST-JA。op 是個標量操作（因此不需要考慮重複值），查詢最終被轉換為 join。多層巢狀的 JA 類查詢也可以被轉換為 J 類查詢。


3 Kiessling, SQL-Like and Quel-like correlation queries with aggregates revisited

略。


4 Ganski, Wong: Optimization of Nested SQL Queries Revisited

解決了 Kim 演算法 NEST-JA 中的缺陷，並擴充套件到 SQL 中常見的子句，包括 EXISTS、NOT EXISTS、ANY、ALL 等。

4.1 COUNT

SELECT PNUM FROM PARTS WHERE QOH = (SELECT COUNT(SHIPDATE) FROM SUPPLY WHERE SUPPLY.PNUM = PARTS.PNUM AND SHIPDATE < `1-1-80`)

演算法引入的臨時表在處理聚集函式時會丟失掉記錄，從而導致最終結果少了。臨時表丟失記錄的問題可以通過外連線解決。如果內查詢中用的是 COUNT(*)，還需要在轉換時改成 COUNT(col)，以避免因為外連線引入的 NULL 導致的計數增加。

4.2 非等值條件

類似的，非等值條件也存在丟失資訊的問題，也可以通過連線來解決（如果是 COUNT，則要用外連線）。

4.3 重複值

如果連線的列上有重複值，連線操作會放大結果集的記錄數。不過它們只可能影響 COUNT、AVG、SUM，而不會影響 MAX、MIN。在產生臨時表之前還要加一步，投影去掉連線列上的重複值。

5 總結

容易發現，巢狀查詢的非巢狀化未必是最優的，Kim 等的論文中都有代價分析。逐步改進（打補丁）的做法也逐步增加了轉換後查詢的處理代價，需要代價優化器來判斷轉換是否有必要。

Footnotes:

1  

Kim, On Optimizing an SQL-like Nested Query.

2  

R.A. Ganski etc., Optimization of nested SQL queries revisited.

3  

https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_1

4  

https://blogs.oracle.com/optimizer/entry/optimizer_transformations_subquery_unesting_part_2


相關文章