MySQL · 性能優化 · 條件下推到物化表 · 數據庫內核月報

## 背景 MySQL引入了Materialization（物化）這一關鍵特性用于子查詢（比如在IN/NOT IN子查詢以及 FROM 子查詢）優化。具體實現方式是：在SQL執行過程中，第一次需要子查詢結果時執行子查詢并將子查詢的結果保存為臨時表，后續對子查詢結果集的訪問將直接通過臨時表獲得。與此同時，優化器還具有延遲物化子查詢的能力，先通過其它條件判斷子查詢是否真的需要執行。物化子查詢優化SQL執行的關鍵點在于對子查詢只需要執行一次。與之相對的執行方式是對外表的每一行都對子查詢進行調用，其執行計劃中的查詢類型為“DEPENDENT SUBQUERY”。在使用Materialization（物化）能提高SQL性能的同時，也有必要留意相關SQL是否存在進一步優化空間的可能性。比如下面描述的場景： ~~~ mysql>explain extended Select * from (select * from score where score >= 60) derived1 where class_id = 10; +----+-------------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+ | 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 4 | const | 0 | 100 | | | 2 | DERIVED | score | index | idx_score | idx_score | 4 | | 1 | 100 | Using where; Using index | +----+-------------+------------+-------+---------------+-------------+---------+-------+------+----------+--------------------------+ ~~~ 從執行計劃可看出，MySQL首先物化了子查詢（select_type=DERIVED，或者以format=json格式查看執行計劃），然后再通過class_id字段對結果集進行過濾。這個SQL從語義上，也可以寫成如下形式，若索引合理執行效率會更高。 ~~~ select * from score where score >= 60 and class_id=10 ~~~ 從這個例子可以看出子查詢物化時的一個潛在問題：當子查詢本身比較耗費資源或結果集較大時，往往存在較高的優化空間，特別是在外層條件可作用于子查詢的情況下。通過條件下推，在執行過程中盡早減少數據訪問量，能顯著提高性能。本文重點描述將條件下推到物化子查詢的場景。 ## 分析事實上前面提到的查詢在5.7版本可以自動重寫。打開優化器選項?derived_merge=on?后，查看重寫后的語句如下： ~~~ select `remall`.`score`.`class_id` AS `class_id`,`remall`.`score`.`student_id` AS `student_id`,`remall`.`score`.`score` AS `score` from `remall`.`score` where ((`remall`.`score`.`class_id` = 10) and (`remall`.`score`.`score` >= 60)) ~~~ 另一方面，并不是所有子查詢可以做到自動條件下推。比如下面這個語句： ~~~ select * from (select class_id, avg(score) from score group by class_id) derived1 where class_id = 10; ~~~ 出現這種現象的原因是MySQL優化器目前只能對Mergable的視圖或子查詢進行重寫。理解這一概念可以先從視圖的兩種算法入手：merge 和 temptable。一般較為復雜的視圖或子查詢會使用temptable算法類型，包括： 1\. 聚合子查詢； 2\. 含有LIMIT的子查詢； 3\. UNION 或UNION ALL子查詢； 4\. 輸出字段中的子查詢；我們也可以顯示的通過創建視圖來判斷子查詢是否使用了merge算法。比如： ~~~ mysql>create algorithm=merge view v as select class_id, avg(score) from score group by class_id; 執行成功,花費 2.46 ms. mysql>show warnings; +---------+------+-------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------+ | Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) | +---------+------+-------------------------------------------------------------------------------+ ~~~ 我們創建視圖時指定使用merge，但是數據庫判定該算法不適合因此使用默認的undefined（實際執行過程中使用temptable算法）。 ~~~ /** Strategy for how to process a view or derived table (merge or materialization) */ enum enum_view_algorithm { VIEW_ALGORITHM_UNDEFINED = 0, VIEW_ALGORITHM_TEMPTABLE = 1, VIEW_ALGORITHM_MERGE = 2 }; ~~~ 使用merge算法的視圖或子查詢能夠將查詢條件下推到視圖或子查詢內部；而temptable算法子查詢或視圖不能將條件下推，只能在結果集上做進一步過濾。優化器對對這一判斷標準為： ~~~ bool merge_derived(THD *thd, TABLE_LIST *derived_table) { ... // Check whether derived table is mergeable, and directives allow merging if (!derived_unit->is_mergeable() || derived_table->algorithm == VIEW_ALGORITHM_TEMPTABLE || (!thd->optimizer_switch_flag(OPTIMIZER_SWITCH_DERIVED_MERGE) && derived_table->algorithm != VIEW_ALGORITHM_MERGE)) DBUG_RETURN(false); ... } ~~~ ## 條件下推原則不是所有數據庫引擎都完美實現條件下推下推到子查詢的功能。對MySQL中使用聚合查詢的視圖或者from子查詢，建議的條件下推原則是： ? ? ? ?查詢中只依賴于視圖或者from子查詢輸出字段的where 條件能夠安全的下推。同時需要注意條件下推到視圖或derived table子查詢后所存放的恰當位置： 1. 從語義上看，下推到聚合子查詢的條件可以放在?HAVING?子句里。下推后的?HAVING字句可以是：?HAVING xxx and NEW_CONDITION operation VALUE; 2. 若條件是子查詢的group 字段，且該條件上有索引，那么將該條件放在子查詢的where字句中，性能會更好（HAVING條件中不含聚合函數時，將該條件下推到where字句中過濾整個group）。對于其他類型的視圖或from子查詢，也可以通過語義檢查的方式進行人工條件下推。 ## 總結任何數據庫的優化器都不是萬能的。了解優化器的特性后并規避其短處，才能寫出最優SQL語句。