一文終結SQL 子查詢優化

子查詢（Subquery）的優化一直以來都是 SQL 查詢優化中的難點之一。關聯子查詢的基本執行方式類似於 Nested-Loop，但是這種執行方式的效率常常低到難以忍受。當資料量稍大時，必須在優化器中對其進行去關聯化（Decoorelation 或 Unnesting），將其改寫為類似於 Semi-Join 這樣的更高效的運算元。

前人已經總結出一套完整的方法論，理論上能對任意一個查詢進行去關聯化。本文結合 SQL Server 以及 HyPer 的幾篇經典論文，由淺入深地講解一下這套去關聯化的理論體系。它們二者所用的方法大同小異，基本思想是想通的。

本文的例子都基於 TPC-H 的表結構，這裡 有一份供你參考。

子查詢簡介

子查詢是定義在 SQL 標準中一種語法，它可以出現在 SQL 的幾乎任何地方，包括 SELECT, FROM, WHERE 等子句中。

總的來說，子查詢可以分為關聯子查詢（Correlated Subquery）和非關聯子查詢（Non-correlated Subquery）。後者非關聯子查詢是個很簡單的問題，最簡單地，只要先執行它、得到結果集並物化，再執行外層查詢即可。下面是一個例子：

SELECT c_count, count(*) AS custdist FROM (      SELECT c_custkey, count(o_orderkey) AS c_count      FROM CUSTOMER      LEFT OUTER JOIN ORDERS ON c_custkey = o_custkey      AND o_comment NOT LIKE '%pending%deposits%'      GROUP BY c_custkey      ) c_orders GROUP BY c_count ORDER BY custdist DESC, c_count DESC;

▲ TPCH-13 是一個非關聯子查詢

非關聯子查詢不在本文討論範圍之列，除非特別宣告，以下我們說的子查詢都是指關聯子查詢。

關聯子查詢的特別之處在於，其本身是不完整的：它的閉包中包含一些外層查詢提供的引數。顯然，只有知道這些引數才能執行該查詢，所以我們不能像對待非關聯子查詢那樣。

根據產生的資料來分類，子查詢可以分成以下幾種：

標量（Scalar-valued） 子查詢：輸出一個只有一行一列的結果表，這個標量值就是它的結果。如果結果為空（0 行），則輸出一個 NULL。但是注意，超過 1 行結果是不被允許的，會產生一個執行時異常。

標量子查詢可以出現在任意包含標量的地方，例如 SELECT、WHERE 等子句裡。下面是一個例子：

SELECT c_custkey FROM CUSTOMER WHERE 1000000 < (     SELECT SUM(o_totalprice)     FROM ORDERS     WHERE o_custkey = c_custkey )

▲ Query 1: 一個出現在 WHERE 子句中的標量子查詢，關聯引數用紅色字型標明瞭

SELECT o_orderkey, (     SELECT c_name     FROM CUSTOMER     WHERE c_custkey = o_custkey ) AS c_name FROM ORDERS

▲ Query 2: 一個出現在 SELECT 子句中的標量子查詢

存在性檢測（Existential Test） 子查詢：特指 EXISTS 的子查詢，返回一個布林值。如果出現在 WHERE 中，這就是我們熟悉的 Semi-Join。當然，它可能出現在任何可以放布林值的地方。

SELECT c_custkey FROM CUSTOMER WHERE c_nationkey = 86 AND EXISTS(     SELECT * FROM ORDERS     WHERE o_custkey = c_custkey )

▲ Query 3: 一個 Semi-Join 的例子

集合比較（Quantified Comparision） 子查詢：特指 IN、SOME、ANY 的查詢，返回一個布林值，常用的形式有：x = SOME(Q) （等價於 x IN Q）或 X <> ALL(Q)（等價於 x NOT IN Q）。同上，它可能出現在任何可以放 原始碼交易 布林值的地方。

SELECT c_name FROM CUSTOMER WHERE c_nationkey <> ALL (SELECT s_nationkey FROM SUPPLIER)

▲ Query 4: 一個集合比較的非關聯子查詢

原始執行計劃

我們以 Query 1 為例，直觀地感受一下，為什麼說關聯子查詢的去關聯化是十分必要的。

下面是 Query 1 的未經去關聯化的原始查詢計劃（Relation Tree）。與其他查詢計劃不一樣的是，我們特地畫出了表示式樹（Expression Tree），可以清晰地看到：子查詢是實際上是掛在 Filter 的條件表示式下面的。

img實際執行時，查詢計劃執行器（Executor）在執行到 Filter 時，呼叫表示式執行器（Evaluator）；由於這個條件表示式中包含一個標量子查詢，所以 Evaluator 又會呼叫 Executor 計算標量子查詢的結果。

這種 Executor - Evaluator - Executor 的交替呼叫十分低效！考慮到 Filter 上可能會有上百萬行資料經過，如果為每行資料都執行一次子查詢，那查詢執行的總時長顯然是不可接受的。

Apply 運算元

上文說到的 Relation - Expression - Relation 這種交替引用不僅執行效能堪憂，而且，對於優化器也是個麻煩的存在——我們的優化規則都是在匹配並且對 Relation 進行變換，而這裡的子查詢卻藏在 Expression 裡，令人無從下手。

為此，在開始去關聯化之前，我們引入 Apply 運算元：

Apply 運算元（也稱作 Correlated Join）接收兩個關係樹的輸入，與一般 Join 不同的是，Apply 的 Inner 輸入（圖中是右子樹）是一個帶有引數的關係樹。

Apply 的含義用下圖右半部分的集合表示式定義：對於 Outer Relation RR 中的每一條資料 rr，計算 Inner Relation E(r)E(r)，輸出它們連線（Join）起來的結果 r⊗E(r)r⊗E(r)。Apply 的結果是所有這些結果的並集（本文中說的並集指的是 Bag 語義下的並集，也就是 UNION ALL）。

“
Apply 是 SQL Server 的命名，它在 HyPer 的文章中叫做 Correlated Join。它們是完全等價的。考慮到 SQL Server 的文章發表更早、影響更廣，本文中都沿用它的命名。

根據連線方式（⊗⊗）的不同，Apply 又有 4 種形式：

Cross Apply A×A×：這是最基本的形式，行為剛剛我們已經描述過了；

Left Outer Apply ALOJALOJ：即使 E(r)E(r) 為空，也生成一個 r∘{NULLs}r∘{NULLs}。

Semi Apply A∃A∃：如果 E(r)E(r) 不為空則返回 rr，否則丟棄；

Anti-Semi Apply A∄A∄：如果 E(r)E(r) 為空則返回 rr，否則丟棄；

我們用剛剛定義的 Apply 運算元來改寫之前的例子：把子查詢從 Expression 內部提取出來。