某些最佳化適用於使用 IN (或 =ANY) 運算子測試子查詢結果的比較。本節討論這些最佳化，特別是關於 NULL 值所帶來的挑戰。討論的最後一部分建議您如何協助最佳化工具。

考慮以下子查詢比較

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL 會「由外而內」評估查詢。也就是說，它首先取得外部運算式 outer_expr 的值，然後執行子查詢並擷取它產生的列。

一個非常有用的最佳化是「告知」子查詢，只有內部運算式 inner_expr 等於 outer_expr 的列才是感興趣的列。這是透過將適當的相等性下推到子查詢的 WHERE 子句來使其更具限制性。轉換後的比較看起來像這樣

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

轉換後，MySQL 可以使用下推的相等性來限制它必須檢查的列數，以評估子查詢。

更一般而言，將 N 個值與返回 N 個值列的子查詢進行比較，也會進行相同的轉換。如果 oe_i 和 ie_i 代表對應的外部和內部運算式值，則此子查詢比較

(oe_1, ..., oe_N) IN
  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

變成

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

為了簡單起見，以下討論假設一對外部和內部運算式值。

只有在下列任一條件為真時，剛才描述的「下推」策略才有效

假設已知 outer_expr 為非 NULL 值，但子查詢未產生 outer_expr = inner_expr 的列。然後 outer_expr IN (SELECT ...) 的評估方式如下

在此情況下，尋找 outer_expr = inner_expr 資料列的方法不再有效。有必要尋找此類資料列，但如果找不到，也需要尋找 inner_expr 為 NULL 的資料列。粗略地說，子查詢可以轉換為類似這樣的情況

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr=inner_expr OR inner_expr IS NULL))

之所以需要評估額外的 IS NULL 條件，是因為 MySQL 具有 ref_or_null 存取方法

mysql> EXPLAIN
       SELECT outer_expr IN (SELECT t2.maybe_null_key
                             FROM t2, t3 WHERE ...)
       FROM t1;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref_or_null
possible_keys: maybe_null_key
          key: maybe_null_key
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Using index
...

unique_subquery 和 index_subquery 子查詢特定的存取方法也有 「或 NULL」 變體。

額外的 OR ... IS NULL 條件使查詢執行稍微複雜（並且子查詢內的一些最佳化變得不適用），但一般來說這是可以容忍的。

當 outer_expr 可以為 NULL 時，情況會更糟。根據 SQL 將 NULL 解釋為 「未知值，」 NULL IN (SELECT inner_expr ...) 應評估為

為了進行正確的評估，有必要能夠檢查 SELECT 是否已產生任何資料列，因此 outer_expr = inner_expr 無法下推到子查詢中。這是一個問題，因為許多真實世界的子查詢，除非可以下推等式，否則會變得非常慢。

基本上，必須根據 outer_expr 的值以不同的方式執行子查詢。

最佳化工具會選擇符合 SQL 標準而不是速度，因此它會考慮到 outer_expr 可能為 NULL 的可能性

為了解決是否要將條件下推到子查詢中的困境，條件會包裝在 「觸發器」 函數中。因此，以下形式的運算式

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

會轉換為

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(outer_expr=inner_expr))

更廣泛地說，如果子查詢比較基於多組外部和內部運算式，則轉換會採用此比較

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

並將其轉換為此運算式

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(oe_1=ie_1)
                          AND ...
                          AND trigcond(oe_N=ie_N)
       )

每個 trigcond(X) 都是一個特殊函數，其評估結果為以下值

包裝在 trigcond() 函數內的等式對於查詢最佳化工具來說不是第一級的述詞。大多數最佳化無法處理可能在查詢執行時開啟和關閉的述詞，因此它們會假設任何 trigcond(X) 都是未知函數而忽略它。這些最佳化可以使用觸發的等式

當最佳化工具使用觸發的條件來建立某種基於索引查找的存取時（如上述清單中的前兩項所示），它必須針對條件關閉的情況採取後備策略。此後備策略始終相同：執行完整資料表掃描。在 EXPLAIN 輸出中，後備顯示為 Extra 欄中的 Full scan on NULL key

mysql> EXPLAIN SELECT t1.col1,
       t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
        ...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: index_subquery
possible_keys: key1
          key: key1
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Full scan on NULL key

如果您執行 EXPLAIN，然後執行 SHOW WARNINGS，您可以看到觸發的條件

*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
         <in_optimizer>(`test`.`t1`.`col1`,
         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
         on key1 checking NULL
         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
         from `test`.`t1`

使用觸發的條件會產生一些效能影響。 NULL IN (SELECT ...) 運算式現在可能會導致完整資料表掃描（速度較慢），而先前不會這樣做。這是為取得正確結果付出的代價（觸發條件策略的目標是提高相容性，而不是速度）。

對於多資料表子查詢，執行 NULL IN (SELECT ...) 速度特別慢，因為聯結最佳化工具不會針對外部運算式為 NULL 的情況進行最佳化。它假設左側具有 NULL 的子查詢評估非常罕見，即使有統計資料顯示相反的情況也是如此。另一方面，如果外部運算式可能為 NULL 但實際上永遠不會為 NULL，則不會有任何效能損失。

為了幫助查詢最佳化工具更好地執行您的查詢，請使用以下建議

subquery_materialization_cost_based 系統變數的 optimizer_switch 旗標可讓您控制子查詢實體化和 IN 到 EXISTS 子查詢轉換之間的選擇。請參閱第 10.9.2 節「可切換的最佳化」。