MySQL · 性能優化 · 條件下推到物化表

mysql引入了materialization（物化）這一關鍵特性用于子查詢（比如在in/not in子查詢以及 from 子查詢）優化。

具體實作方式是：在sql執行過程中，第一次需要子查詢結果時執行子查詢并将子查詢的結果儲存為臨時表 ，後續對子查詢結果集的通路将直接通過臨時表獲得。

與此同時，優化器還具有延遲物化子查詢的能力，先通過其它條件判斷子查詢是否真的需要執行。物化子查詢優化sql執行的關鍵點在于對子查詢隻需要執行一次。 與之相對的執行方式是對外表的每一行都對子查詢進行調用，其執行計劃中的查詢類型為“dependent subquery”。

在使用materialization（物化）能提高sql性能的同時，也有必要留意相關sql是否存在進一步優化空間的可能性。比如下面描述的場景：

從執行計劃可看出，mysql首先物化了子查詢（select_type=derived，或者以format=json格式檢視執行計劃），然後再通過class_id字段對結果集進行過濾。這個sql從語義上，也可以寫成如下形式，若索引合理執行效率會更高。

從這個例子可以看出子查詢物化時的一個潛在問題：當子查詢本身比較耗費資源或結果集較大時，往往存在較高的優化空間，特别是在外層條件可作用于子查詢的情況下。通過條件下推，在執行過程中盡早減少資料通路量，能顯著提高性能。本文重點描述将條件下推到物化子查詢的場景。

事實上前面提到的查詢在5.7版本可以自動重寫。打開優化器選項 derived_merge=on 後，檢視重寫後的語句如下：

另一方面，并不是所有子查詢可以做到自動條件下推。比如下面這個語句：

出現這種現象的原因是mysql優化器目前隻能對mergable的視圖或子查詢進行重寫。了解這一概念可以先從視圖的兩種算法入手：merge 和 temptable。

一般較為複雜的視圖或子查詢會使用temptable算法類型，包括：

1. 聚合子查詢；

2. 含有limit的子查詢；

3. union 或union all子查詢；

4. 輸出字段中的子查詢；

我們也可以顯示的通過建立視圖來判斷子查詢是否使用了merge算法。 比如：

我們建立視圖時指定使用merge，但是資料庫判定該算法不适合是以使用預設的undefined（實際執行過程中使用temptable算法）。

使用merge算法的視圖或子查詢能夠将查詢條件下推到視圖或子查詢内部；而temptable算法子查詢或視圖不能将條件下推，隻能在結果集上做進一步過濾。優化器對對這一判斷标準為：

不是所有資料庫引擎都完美實作條件下推下推到子查詢的功能。對mysql中使用聚合查詢的視圖或者from子查詢，建議的條件下推原則是：

       查詢中隻依賴于視圖或者from子查詢輸出字段的where 條件能夠安全的下推。

同時需要注意條件下推到視圖或derived table子查詢後所存放的恰當位置：

從語義上看，下推到聚合子查詢的條件可以放在 having 子句裡。下推後的 having字句可以是： having xxx and new_condition operation value;

若條件是子查詢的group 字段，且該條件上有索引，那麼将該條件放在子查詢的where字句中，性能會更好（having條件中不含聚合函數時，将該條件下推到where字句中過濾整個group）。

對于其他類型的視圖或from子查詢，也可以通過語義檢查的方式進行人工條件下推。

任何資料庫的優化器都不是萬能的。 了解優化器的特性後并規避其短處，才能寫出最優sql語句。