某些最佳化適用於使用 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 節，「可切換的最佳化」。