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

版本 9.0

8.4 目前

MySQL 9.0 參考手冊 / ... / 使用半聯結和反聯結轉換優化 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 ...) 為非真
EXISTS (SELECT ... FROM ...) 為非真.
IN (SELECT ... FROM ...) 為假
EXISTS (SELECT ... FROM ...) 為假.

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

反聯結是一種只返回沒有匹配的行的操作。考慮以下查詢

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 中的該行。

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

外部查詢規範中允許使用外聯結和內聯結語法，且表格引用可以是基本表格、衍生表格、視圖引用或通用表格表達式。

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

它必須是 IN、= ANY 或 EXISTS 謂詞的一部分，且該謂詞出現在 WHERE 或 ON 子句的頂層，可能作為 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 修改的表達式的引數。
它必須是單個 SELECT，且沒有 UNION 結構。
它不得包含 HAVING 子句。
它不得包含任何彙總函數（無論是顯式分組還是隱式分組）。
它不得包含 LIMIT 子句。
該語句不得在外部查詢中使用 STRAIGHT_JOIN 聯結類型。
不得存在 STRAIGHT_JOIN 修飾符。
外部和內部表格的數量加起來必須小於聯結中允許的最大表格數量。
子查詢可以是相關的或不相關的。去相關性會檢視 EXISTS 的引數中子查詢的 WHERE 子句中微不足道相關的謂詞，並使其可以進行最佳化，就像它在 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> 的列表示。

半聯結轉換也可以應用於單表格 UPDATE 或 DELETE 語句，該語句使用 [NOT] IN 或 [NOT] EXISTS 子查詢謂詞，前提是該語句不使用 ORDER BY 或 LIMIT，並且最佳化工具提示或 optimizer_switch 設定允許進行半聯結轉換。