MySQL :: MySQL 8.4 參考手冊 :: 10.2.2.1 使用半聯結和反聯結轉換來最佳化 IN 和 EXISTS 子查詢謂詞

版本 8.4

9.0

MySQL 8.4 參考手冊 / ... / 使用半聯結和反聯結轉換來最佳化 IN 和 EXISTS 子查詢謂詞

10.2.2.1 使用半聯結和反聯結轉換來最佳化 IN 和 EXISTS 子查詢謂詞

半聯結是一種準備時間轉換，可啟用多種執行策略，例如資料表拉出、重複項清除、首次比對、寬鬆掃描和具體化。最佳化工具使用半聯結策略來改善子查詢執行，如本節所述。

對於兩個資料表之間的內部聯結，聯結會傳回一個資料表的列，次數與另一個資料表中的比對次數相同。但對於某些問題，唯一重要的資訊是是否存在比對，而不是比對的次數。假設有名為 class 和 roster 的資料表，分別列出課程表中的課程和班級名冊（每班的學生），若要列出實際有學生註冊的課程，您可以使用此聯結

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

但是，結果會為每位註冊學生列出每個班級一次。對於所問的問題，這是對資訊不必要的重複。

假設 class_num 是 class 資料表中的主鍵，則可以使用 SELECT DISTINCT 來進行重複項抑制，但先產生所有比對列，然後再消除重複項是無效率的。

可以使用子查詢來取得相同的無重複項結果

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

在這裡，最佳化工具可以識別出 IN 子句需要子查詢僅從 roster 資料表傳回每個班級編號的一個執行個體。在這種情況下，查詢可以使用半聯結；也就是說，一種僅傳回 class 中與 roster 中的列比對的每列的一個執行個體的操作。

以下陳述式（其中包含 EXISTS 子查詢謂詞）與先前包含 IN 子查詢謂詞的陳述式等效

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

任何具有 EXISTS 子查詢謂詞的陳述式都與具有等效 IN 子查詢謂詞的陳述式一樣，會受到相同的半聯結轉換。

以下子查詢會轉換為反聯結

NOT IN (SELECT ... FROM ...)
NOT EXISTS (SELECT ... FROM ...).
IN (SELECT ... FROM ...) IS NOT TRUE
EXISTS (SELECT ... FROM ...) IS NOT TRUE.
IN (SELECT ... FROM ...) IS FALSE
EXISTS (SELECT ... FROM ...) IS FALSE.

簡而言之，任何形式為 IN (SELECT ... FROM ...) 或 EXISTS (SELECT ... FROM ...) 的子查詢的否定都會轉換為反聯結（antijoin）。

反聯結是一種僅傳回沒有符合項的資料列的操作。考慮以下顯示的查詢：

SELECT class_num, class_name
    FROM class
    WHERE class_num NOT IN
        (SELECT class_num FROM roster);

此查詢會在內部重寫為反聯結 SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num，它會傳回 class 中每個未與 roster 中任何資料列相符的資料列的實例。這表示，對於 class 中的每個資料列，只要在 roster 中找到符合項，就可以捨棄 class 中的該資料列。

如果比較的運算式是可為 null 的，則在大多數情況下無法套用反聯結轉換。此規則的例外情況是，(... NOT IN (SELECT ...)) IS NOT FALSE 及其等效項 (... IN (SELECT ...)) IS NOT TRUE 可以轉換為反聯結。

在外部查詢規格中允許使用外部聯結和內部聯結語法，且資料表參考可以是基本資料表、衍生資料表、檢視表參考或通用資料表運算式。

在 MySQL 中，子查詢必須符合以下條件才能作為半聯結處理（如果 NOT 修改了子查詢，則為反聯結）：

它必須是出現在 WHERE 或 ON 子句最上層的 IN、= ANY 或 EXISTS 述詞的一部分，也可能是 AND 運算式中的一個項。例如：
```
SELECT ...
    FROM ot1, ...
    WHERE (oe1, ...) IN
        (SELECT ie1, ... FROM it1, ... WHERE ...);
```
在這裡，ot_i 和 it_i 代表查詢外部和內部部分的資料表，而 oe_i 和 ie_i 代表參考外部和內部資料表中欄位的運算式。
子查詢也可以是受 NOT、IS [NOT] TRUE 或 IS [NOT] FALSE 修改的運算式的引數。
它必須是沒有 UNION 建構的單一 SELECT。
它不得包含 HAVING 子句。
它不得包含任何彙總函式（無論是明確分組還是隱含分組）。
它不得有 LIMIT 子句。
該陳述式不得在外部查詢中使用 STRAIGHT_JOIN 聯結類型。
不得存在 STRAIGHT_JOIN 修飾詞。
外部和內部資料表的總數必須小於聯結中允許的最大資料表數。
子查詢可能是相關的或不相關的。取消相關性會檢視 WHERE 子句中用作 EXISTS 引數的子查詢中簡單相關的述詞，並使其能夠像在 IN (SELECT b FROM ...) 中使用一樣進行最佳化。術語簡單相關表示述詞是相等述詞，它是 WHERE 子句中的唯一述詞（或與 AND 結合），且一個運算元來自子查詢中參考的資料表，另一個運算元來自外部查詢區塊。
允許使用 DISTINCT 關鍵字，但會忽略它。半聯結策略會自動處理重複資料刪除。
允許使用 GROUP BY 子句，但會忽略它，除非子查詢還包含一或多個彙總函式。
允許使用 ORDER BY 子句，但會忽略它，因為排序與半聯結策略的評估無關。

如果子查詢符合上述條件，則 MySQL 會將其轉換為半聯結（如果適用，則轉換為反聯結），並從以下策略中做出以成本為基礎的選擇：

將子查詢轉換為聯結，或使用資料表拉取，並將查詢作為子查詢資料表和外部資料表之間的內部聯結執行。資料表拉取會將資料表從子查詢拉取到外部查詢。
重複刪除：將半聯結視為聯結執行，並使用暫存資料表刪除重複記錄。
首次符合：在掃描內部資料表以取得資料列組合時，如果有多個指定值群組的實例，則選擇一個，而不是全部傳回。這會「捷徑」掃描，並消除產生不必要資料列的情況。
寬鬆掃描：使用索引掃描子查詢資料表，該索引可從每個子查詢的值群組中選擇單一值。
將子查詢具體化為索引的暫存資料表，該資料表用於執行聯結，其中索引用於刪除重複項。稍後在將暫存資料表與外部資料表聯結時，也可能會使用索引來進行查閱；否則，會掃描資料表。如需有關具體化的詳細資訊，請參閱第 10.2.2.2 節「使用具體化最佳化子查詢」。

可以使用下列 optimizer_switch 系統變數旗標啟用或停用這些策略的每一個：

semijoin 旗標會控制是否使用半聯結和反聯結。
如果已啟用 semijoin，則 firstmatch、loosescan、duplicateweedout 和 materialization 旗標可以更精細地控制允許的半聯結策略。
如果停用了 duplicateweedout 半聯結策略，則除非也停用了所有其他適用的策略，否則不會使用它。
如果停用了 duplicateweedout，則最佳化工具偶爾可能會產生遠非最佳的查詢計畫。這會因為貪婪搜尋期間的啟發式修剪而發生，可以透過設定 optimizer_prune_level=0 來避免這種情況。

這些旗標預設為啟用。請參閱第 10.9.2 節「可切換最佳化」。

最佳化工具會將檢視表和衍生資料表的處理差異降至最低。這會影響使用 STRAIGHT_JOIN 修飾詞和具有可以轉換為半聯結的 IN 子查詢的檢視表的查詢。以下查詢說明了這一點，因為處理方式的變更會導致轉換的變更，進而導致不同的執行策略：

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

最佳化工具會先檢視檢視表，並將 IN 子查詢轉換為半聯結，然後檢查是否可以將檢視表合併到外部查詢中。由於外部查詢中的 STRAIGHT_JOIN 修飾詞會阻止半聯結，因此最佳化工具會拒絕合併，導致使用具體化資料表進行衍生資料表評估。

EXPLAIN 輸出會以下列方式指示半聯結策略的使用：

對於擴充的 EXPLAIN 輸出，後續 SHOW WARNINGS 顯示的文字會顯示重寫的查詢，其中會顯示半聯結結構。（請參閱第 10.8.3 節「擴充 EXPLAIN 輸出格式」。）從這裡，您可以瞭解哪些資料表已從半聯結中拉出。如果子查詢已轉換為半聯結，您應該會看到子查詢述詞已消失，且其資料表和 WHERE 子句已合併到外部查詢聯結清單和 WHERE 子句中。
Extra 欄中的 Start temporary 和 End temporary 表示使用暫存資料表進行重複刪除。不在拉取範圍內，且在 EXPLAIN 輸出資料列範圍內（由 Start temporary 和 End temporary 涵蓋）的資料表，其 rowid 會位於暫存資料表中。
Extra 欄中的 FirstMatch(tbl_name) 表示聯結捷徑。
Extra 欄中的 LooseScan(m..n) 表示使用寬鬆掃描策略。m 和 n 是索引鍵部分編號。
select_type 值為 MATERIALIZED 的資料列，以及 table 值為 <subqueryN> 的資料列表示使用暫存資料表進行具體化。

如果陳述式未使用 ORDER BY 或 LIMIT，且最佳化工具提示或 optimizer_switch 設定允許半聯結轉換，則半聯結轉換也可以套用至使用 [NOT] IN 或 [NOT] EXISTS 子查詢述詞的單一資料表 UPDATE 或 DELETE 陳述式。