mysql5.7官網直譯SQL語句優化--使用存在政策來優化子查詢

2.4 Optimizing Subqueries with the EXISTS Strategy 使用存在政策來優化子查詢。

一些優化是适用于對比操作的使用在IN(或者=ANY)操作去測試子查詢結果。這部分讨論這些優化，特别是考慮NULL值存在的挑戰。後續讨論的部分建議你怎麼樣幫助優化器。

考慮如下的子查詢對比：

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

mysql評估查詢從外到内。也就是說，它首先擷取在外部表達式outer_expr的值，然後運作子查詢并且抓取其産生的行資料。

一個非常有用的悠哈是去通知子查詢，隻有内部表達式inner_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)

為了更好的了解，下面的讨論假設是一個單一的對比内外表達式的值。

轉變會和他的限制一起描述。隻有我們忽略可能的null值才有效。也就是壓入政策隻會工作在這些條件都是真的情況下：

>outer_expr和inner_expr不能是null

>你不能差別出NULL從FALSE子查詢結果集中。如果子查詢是WHERE條件中的一個OR或者AND表達式的部分，mysql假設你并不關心。另一個例子其中優化器注意到NUll和FALSE結果在子查詢中不需要被區分是這樣的結構：

... WHERE outer_expr IN (subquery)

 在這種情況下，where條件會拒絕在IN(子查詢)傳回null或者false的行。

 當上述一個或者兩個條件都不滿足，優化器會更加複雜。

 假設outer_expr是不為null的但是子查詢并沒有産生和内部條件相比對的行資料。那麼outer_expr IN (select...) 評估如下：

 1）NUll,如果SELECT 産生任何行其中inner_expr是null.

 2) FALSE,如果SELECT 産生隻有非NULL值或者什麼也不做。

 在這種情況下，關于查找outer_expr=inner_exper的行的捷徑不能再用了。它必須去掃描這些行，即使什麼也沒有，就算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各自獨特的子查詢方法也有對or null的變體。

額外的OR .. IS NULL條件使得查詢執行計劃稍微更混亂(并且在子查詢中的一些優化變的不可用)，但整體上可以忍受。

當outer_expr也能是null時的情況更糟糕。根據SQL解釋關于NULL是未知的值，NULL IN(SELECT inner_expr...)應該評估為：

>NULL,如果SELECT 産生任何行

>FALSE,如果SELECT 不産生行

為了合理的評估, 有必要檢查是否SELECT查詢總是會生成行資料，導緻outer_expr=inner_expr不能被壓入子查詢中。而在真實環境中，除非可以等價的壓入，否則子查詢會變得很慢，這是一個問題。本質上，必須用不同的方法執行依賴于outer_expr值的子查詢。

優化器選擇SQL遵從速度，是以它說明了這種outer_expr可能是null的可能性：

1）如果outer_expr是NULL,為了評估下面的表達式，它必須去執行SELECT去決定是否生成任何行資料：

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

在這種情況下，執行原始SELECT是必須的，沒有任何等價條件可以提前壓入幫助查詢。

2）另一方面，當outer_expr是非null值，那麼這種比較是最基本的：

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

該條件可以被轉換為被使用的壓入條件：

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

沒有這個轉換，子查詢将會變慢。

為了解決是否可以壓入條件在子查詢的困境，條件被包裝在觸發器函數内。這樣，表達式如下這樣的形式：

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)都是一個特别函數用于評估下面的值：

>x 當“linked"外部表達式oe_i是非null值的情況。

>TRUE 當“linked"外部表達式oe_i是null值的情況。

  注意：

  觸發函數并不是你通過create Trigger生成的那種觸發函數。

相等的被包裝在trigcond()函數内部的并是不是第一類的斷言對于查詢優化器。大多數優化器不能處理斷言，可能開或和關查詢的執行時間，是以他們假設任何trigcond(x) 被看作是未知函數并且忽略它。被觸發 等價可以被用于這些優化：

>引用優化：trigcond(X=Y[OR Y IS NULL])能夠被用于轉換ref,eq_ref,或者是ref_or_null表查詢。

>基于索引子查詢的執行引擎：trigcond(x=y)能夠被用于轉換unique_subquery或者index_subquery查詢。

>表條件生成器：如果子查詢是一個連接配接多個表，觸發條件會盡快被檢查。

當優化器使用觸發條件去建立一些索引查詢方法(就像先前列出的第一二系列)，它必須有一個回退政策當調劑是關閉的情況下使用。這個準備政策總是一樣的:做一個全表掃描。在EXPLAIN輸出中，回退測展示為Full scan on Null key 在Extra列：

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但事實上不可能，這沒有性能壞處。

為了幫助查詢優化器更好的執行你的查詢，使用這些建議：

1）申明一列是NOT NULL的如果他不能為null的話。這也從其他反面幫助優化器對條件檢測的簡單性

2）如果你不需要差別null從False子查詢結果中，你很容易的能避免慢查詢計劃，像這樣替代對比：

outer_expr IN (SELECT inner_expr FROM ...)

使用這個表達式：

(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))

其中 NULL IN (SELECT ..)永遠不會被計算，因為mysql阻止評估了AND部分如果表達式結果是空的話。

另一個可能的重寫是：

EXISTS (SELECT inner_expr FROM ...

        WHERE inner_expr=outer_expr)

optimizer_switch系統變量的subquery_materialization_cost_based标記能夠控制上面的選擇是子查詢物化還是IN-TO-EXISTS子查詢轉換。具體請看8.9.3的優化開關。

到這裡關于通過EXISTS政策來優化子查詢就結束了。接下來我們将介紹關于8.3Optimization and Indexes 優化和索引