庖丁解牛-圖解MySQL 8.0優化器查詢解析篇

一  背景和架構

我們都知道，利用編寫程式來動态實作我們應用所需要的邏輯，進而程式執行時得到我們需要的結果。那麼資料庫就是一種通過輸入SQL字元串來快速擷取資料的應用。當然，假設沒有資料庫這種系統應用，用程式如何實作呢？我們可能會發現，即使不管資料如何存儲、資料是否并發通路，仍然需要不斷通過修改程式處理不同應用對資料的不同請求。比如大資料領域，我們通常通過非關系型資料庫的API，實作對資料的擷取。然而這種方式雖然入門簡單，但是維護極難，而且通用性不強，即使不斷進行軟體架構設計或者抽象重構，仍然需要不斷地變換應用，這也是為何非關系型資料庫回頭擁抱資料庫SQL優化器的原因。

SQL優化器本質上是一種高度抽象化的資料接口的實作，經過該設計，客戶可以使用更通用且易于了解的SQL語言，對資料進行操作和處理，而不需要關注和抽象自己的資料接口，極大地解放了客戶的應用程式。

本文就來通過圖形解說的方式介紹下MySQL 8.0 SQL優化器如何把一個簡單的字元串（SQL），變成資料庫執行器可以了解的執行序列，最終将資料返還給客戶。強大的優化器是不需要客戶關注SQL如何寫的更好來更快獲得需要的資料，是以優化器對原始SQL一定會做一些等價的變化。在

《MySQL 8.0 Server層最新架構詳解》

一文中我們重點介紹了MySQL最新版本關于Server層解析器、優化器和執行器的總體介紹，包括一些代碼結構和變化的詳細展示，并且通過simple_joins函數抛磚引玉展示了MySQL優化器在邏輯變換中如何簡化嵌套Join的優化。本文我們會一步一步帶你進入神奇的優化器細節，詳細了解優化器優化部分的每個步驟如何改變着一個SQL最終的執行。

本文基于最新MySQL8.0.25版本，因為優化器轉換部分篇幅比較長，我們分成兩篇文章來介紹，第一部分介紹基于基本結構的Setup和Resolve的解析轉換過程，第二部分介紹更為複雜的子查詢、分區表和連接配接的複雜轉換過程，大綱如下：

Setup and Resolve

• setup_tables : Set up table leaves in the query block based on list of tables.

• resolve_placeholder_tables/merge_derived/setup_table_function/setup_materialized_derived : Resolve derived table, view or table function references in query block.

• setup_natural_join_row_types : Compute and store the row types of the top-most NATURAL/USING joins.

• setup_wild : Expand all '*' in list of expressions with the matching column references.

• setup_base_ref_items : Set query_block's base_ref_items.

• setup_fields : Check that all given fields exists and fill struct with current data.

• setup_conds : Resolve WHERE condition and join conditions.

• setup_group : Resolve and set up the GROUP BY list.

• m_having_cond->fix_fields : Setup the HAVING clause.

• resolve_rollup : Resolve items in SELECT list and ORDER BY list for rollup processing.

• resolve_rollup_item : Resolve an item (and its tree) for rollup processing by replacing items matching grouped expressions with Item_rollup_group_items and updating properties (m_nullable, PROP_ROLLUP_FIELD). Also check any GROUPING function for incorrect column.

• setup_order : Set up the ORDER BY clause.

• resolve_limits : Resolve OFFSET and LIMIT clauses.

• Window::setup_windows1: Set up windows after setup_order() and before setup_order_final().

• setup_order_final: Do final setup of ORDER BY clause, after the query block is fully resolved.

• setup_ftfuncs : Setup full-text functions after resolving HAVING.

• resolve_rollup_wfs : Replace group by field references inside window functions with references in the presence of ROLLUP.

二  詳細轉換過程

轉換的整個架構是由Query_expression到Query_block調用prepare函數(sql/sql_resolver.cc)并且根據不同轉換規則的要求自頂向下或者自底向上的過程。

1  傳遞null到join的内表清單（propagate_nullability）

prepare開始先要處理nullable table，它指的是table可能包含全為null的row，根據JOIN關系（top_join_list）null row可以被傳播。如果能确定一個table為nullable會使得一些優化退化，比如access method不能為EQ_REF、outer join不能優化為inner join等。

2  解析設定查詢塊的leave_tables（setup_tables）

SELECT
  t1.c1
FROM t1,
     (SELECT
       t2.c1
     FROM t2,
          (SELECT
            t3.c1
          FROM t3
          UNION
          SELECT
            t4.c1
          FROM t4) AS t3a) AS t2a;      

未在setup_table調用之前，每個Query_block的leaf_tables是為0的。

該函數的作用就是建構leaf_tables，包括base tables和derived tables清單，用于後續的優化。setup_tables并不會遞歸調用，而是隻解決本層的tables，并統計出本層derived table的個數。但是随後會調用resolve_placeholder_tables()->resolve_derived()->derived(Query_expression)::prepare->Query_block::prepare來專門遞歸處理derived table對應的Query_expression。

接下來我們根據prepare的調用順序，繼續看下針對于derived table處理的函數resolve_placeholder_tables。

3  解析查詢塊Derived Table、View、Table函數 （resolve_placeholder_tables）

這個函數用于對derived table、view和table function的處理，如果該table已經merged過了，或者是由于使用transform_grouped_to_derived()被調用到，已經決定使用materialized table方式，則直接忽略。

前面已經介紹過resolve_derived()的作用，我們重點介紹merge_derived()函數，merge_derived是改變Query_expression/Query_block架構結構，将derived table或者view合并到到query block中。

merge_derived 處理和合并Derived table

1）merge_derived transformation的先決條件

• 外層query block是否允許merge（allow_merge_derived）

• • 外層query block為nullptr

• • 外層query expression的子查詢為nullptr，derived table是第一層子查詢

• • 外層的外層query block可以allow_merge_derived=true，或者不包括外層的外層query block話是否為SELECT/SET

• 外層lex是否可以支援merge（lex->can_use_merged()+lex->can_no_use_merged()）

• derived table是否已經被标記為需要物化materialize，比如建立視圖的方法是CREATE ALGORITHM=TEMPTABLE VIEW（derived_table->algorithm == VIEW_ALGORITHM_TEMPTABLE）

• 整個dervived table所在的查詢表達式單元中，不能是（Query_expression::is_mergeable() ）：

• • Union查詢
  • 包含聚集、HAVING、DISTINCT、WINDOWS或者LIMIT
  • 沒有任何table list

• HINT或者optimizer_switch沒有禁止derived_merge；

• heuristic建議合并(derived_query_expressionmerge_heuristic())；

• • 如果derived table包含的子查詢SELECT list依賴于自己的列時，不支援；

• • 如果是dependant subquery需要多次執行時，不支援；

• derived table中如果查詢塊包含SEMI/ANTI-JOIN，并指定STRAIGHT_JOIN時，不支援；

• 如果合并的derived table和現有query block的leaf table count大約 MAX_TABLES時，不支援；

2）merge_derived transformation的轉換過程

• 利用derived_table->nested_join結構來輔助處理OUTER JOIN的情況。

• 把derived table中的表merge到NESTED_JOIN結構體（derived_table->merge_underlying_tables()）。

• 将derived table中的所有表連接配接到父查詢的table_list清單中，同時把derived table從父查詢中删除。

• 對父查詢的所有相關資料結構進行重新計算（leaf_table_count、derived_table_count、table_func_count、materialized_derived_table_count、has_sj_nests、has_aj_nests、partitioned_table_count、cond_count、between_count、select_n_having_items）。

• 傳播設定父查詢OPTION_SCHEMA_TABLE（add_base_options()）和如果是外查詢JOIN的内表，傳播設定nullable屬性（propagate_nullability()）。

• 合并derived table的where條件到外查詢中（merge_where()）。

• 建立對derived table需要擷取的列的引用（create_field_translation()）。

• 将Derived table的結構從父查詢中删除（exclude_level()）。

• 将derived table中的列或者表的重命名合并到父查詢（fix_tables_after_pullout()/repoint_contexts_of_join_nests()）。

• 因為已經把derived table中包含的表merge到了父查詢，是以需要對TABLE_LIST中的表所在的位置進行重新定位（remap_tables()）。

• 将derived table合并到父查詢之後，需要重新修改原來derived table中所有對derived table中所有列的引用（fix_tables_after_pullout()）。

• 如果derived table中包含ORDER BY語句，如果滿足下列條件，derived table将會保留ORDER BY并合并到父查詢中，其他情況ORDER BY将會被忽略掉：

• • 如果父查詢允許排序并且正好是隻有derived table
  • 不是一個UNION
  • 可以有WHERE條件，但是不能有group by或聚合函數
  • 本身并不是有序的

過程簡化為：

merge_derived 圖解過程

看起來官方的derived merge還是不夠完美，無法自底向上的遞歸merge包含的opt trace：

trace_derived.add_utf8_table(derived_table)
       .add("select#", derived_query_block->select_number)
       .add("merged", true);

trace_derived.add_alnum("transformations_to_derived_table", "removed_ordering");      

該優化可以通過set optimizer_switch="derived_merge=on/off"來控制。

setup_materialized_derived 設定物化Derived Table

對于剩下不能采用 merge 算法的 derived table ，會轉為materialize 物化方式去處理。但此時隻是做一些變量設定等預處理，實際的物化執行是在executor階段執行。

• setup_materialized_derived_tmp_table(): 設定一個臨時表包含物化Derived Table的所有行資料。

• check_materialized_derived_query_blocks(): 設定屬于目前Derived Table所在的查詢塊結構。

trace_derived.add_utf8_table(this)
       .add("select#", derived->first_query_block()->select_number)
       .add("materialized", true);      

setup_table_function 處理表函數

如果 query block 中有 table function，整個過程會處理兩遍。第一遍會跳過 table function 的 table ，第二遍才專門再對table function 的 table 執行一遍上述邏輯。這裡的考慮應該是先 resolve 了外部環境（相對于table function），因為有可能函數參數會有依賴外部的 derived table。

trace_derived.add_utf8_table(this)
       .add_utf8("function_name", func_name, func_name_len)
       .add("materialized", true);      

4  将SELECT *的通配符展開成具體的fields（setup_wild）

5  建立Query_block級别的base_ref_items（setup_base_ref_items)

base_ref_items記錄了所有Item的位置，友善查詢塊的其他Item可以進行引用，或者通過Item_ref及其Item_ref子類進行直接引用，例如子查詢的引用（Item_view_ref）、聚合函數引用（Item_aggregate_ref）、外查詢列的引用（Item_outer_ref）、subquery 子查詢産生NULL value的引用輔助（Item_ref_null_helper）。

舉例說明比較複雜的Item_outer_ref：

6  對select_fields進行fix_fields()和列權限檢查（setup_fields）

下圖是比較複雜的帶子查詢的fixed field過程。有些field和表關聯，有的要添加相應的Item_xxx_ref引用。

7  解析和fixed_fields WHERE條件和Join條件（setup_conds）

setup_join_cond如果有nested_join會遞歸調用setup_join_cond進行解析和設定。這裡也順帶介紹下simplify_const_condition函數的作用，如果發現可以删除的const Item，則會用Item_func_true/Item_func_false來替代整個的條件，如圖。

8  解析和設定ROLLUP語句（resolve_rollup）

在資料庫查詢語句中，在 GROUP BY 表達式之後加上 WITH ROLLUP 語句，可以使得通過單個查詢語句來實作對資料進行不同層級上的分析與統計。

SELECT YEAR,
       country,
       product,
       SUM(profit) AS profit
FROM sales
GROUP BY YEAR,
         country,
         product WITH ROLLUP;

+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

相當于做了下面的查詢：

SELECT *
FROM
  (SELECT YEAR,
          country,
          product,
          SUM(profit) AS profit
   FROM sales
   GROUP BY YEAR,
            country,
            product
   UNION ALL SELECT YEAR,
                    country,
                    NULL,
                    SUM(profit) AS profit
   FROM sales
   GROUP BY YEAR,
            country
   UNION ALL SELECT YEAR,
                    NULL,
                    NULL,
                    SUM(profit) AS profit
   FROM sales
   GROUP BY YEAR
   UNION ALL SELECT NULL,
                    NULL,
                    NULL,
                    SUM(profit) AS profit
   FROM sales) AS sum_table
ORDER BY YEAR, country, product;

+------+---------+------------+--------+
| YEAR | country | product    | profit |
+------+---------+------------+--------+
| NULL | NULL    | NULL       |   7535 |
| 2000 | NULL    | NULL       |   4525 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | NULL       |   1350 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | NULL    | NULL       |   3010 |
| 2001 | Finland | NULL       |     10 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+      

排序由于有NULL的問題，是以分級彙總的效果非常難弄，而且group 列不同改變，SQL複雜度來回變化，而ROLLUP很簡單就可以實作效果，下面看下rollup在解析過程做了什麼樣的轉換達到了意想不到的效果。

9  解析和設定GROUP BY/ORDER BY語句（setup_group/setup_order）

其中一個函數find_order_in_list(): 嘗試在select fields裡去尋找可以映射的列，否則就得在最後投影的all fields裡加上目前列，同時也做fix_fields。

• m_having_cond->fix_fields : 對having條件進行fixed_fields。

• resolve_limits : 處理OFFSET和LIMIT子句（offset_limit和select_limit的Items）。

• setup_ftfuncs : 如果有full-text的函數，對相關Item進行fix_fields。

• remove_redundant_subquery_clause : 對于Table Subquery的表達式，通常是IN/ANY/ALL/EXISTS/etc，如果沒有聚合函數和Having子句，通常可以考慮删除不必要的ORDER/DISTINCT/GROUP BY。該函數支援三種REMOVE_ORDER | REMOVE_DISTINCT | REMOVE_GROUP，如果是SINGLEROW_SUBS的子查詢，隻考慮删除REMOVE_ORDER。

select c1 from t1 where t1.c2 in (select distinct c1 from t2 group by c1, c2 order by c1);
轉化為 =>
select c1 from t1 where t1.c2 in (select c1 from t2);      

• 處理是否可以删除不必要的distinct語句，删除的條件就是GROUP BY的列都在SELECT清單中，并且沒有ROLLUP和Window函數。

is_grouped() && hidden_group_field_count == 0 && olap == UNSPECIFIED_OLAP_TYPE
      

例如場景：

SELECT DISTINCT c1, max(c2) from t1 group by c1;      

10  解析和設定Window函數（Window::setup_windows1）

SELECT id,
       release_year,
       rating,
       avg(rating) over(PARTITION BY release_year) AS year_avg
FROM tw;

+------+--------------+--------+-------------------+
| id   | release_year | rating | year_avg          |
+------+--------------+--------+-------------------+
|    1 |         2015 |      8 |               8.5 |
|    3 |         2015 |      9 |               8.5 |
|    2 |         2015 |    8.5 |               8.5 |
|    4 |         2016 |    8.2 |               8.3 |
|    5 |         2016 |    8.4 |               8.3 |
|    6 |         2017 |      7 |                 7 |
+------+--------------+--------+-------------------+      

執行的過程和結果類似于下圖：

我們看下它在開始Query_block::prepare解析過程做了哪些事情：

select_lex->m_windows 不為空，就調用 Window::setup_windows1

• 周遊window函數清單，調用resolve_window_ordering來解析m_partition_by和m_order_by

• 處理inter-window的引用關系（如WINDOW w1 AS (w2), w2 AS (), w3 AS (w1)），但必須是一個有向無環圖（DAG）

• 重新周遊檢查是否唯一名字check_unique_name、建立window partition by和window order by的引用items

• 檢查視窗函數特征（Window::check_window_functions1(THD *thd, _block *select)）

• • 首先判斷的是目前是靜态視窗還是動态視窗；靜态視窗即判斷了 frame 的定義是否有定義上下邊界。m_static_aggregates 為 true, 意味着是靜态視窗，同時對每一個分區都可以進行一次評估。如果 ma_static_aggregates 為 false, 則進一步判斷其滑動視窗使用的是基于範圍還是基于行。 m_row_optimizable 基于行 m_range_optimizable 基于範圍 

• • 擷取聚合函數作為視窗函數時候視窗的特殊規格要求wfs->check_wf_semantics1(thd, select, &reqs) 這個方法其實就是判斷是不是需要row_buffer作為評估，如果我們隻看目前分區的行無法進行正确的計算不需要，而需要看之後的或者之前的行，就需要使用row_buffer。

三  綜述

本文重點介紹了下優化器的基于規則的其中一部分優化，更多的偏重于SQL中的基本操作符，如表、列、函數、聚合、分組、排序等元素的解析和設定以及一些顯而易見的結構變化。下一篇文章我們将繼續介紹子查詢、分區表和JOIN操作的轉換部分，敬請期待。

四  參考資料

• 《Mysql derived_MySQL · 新特性分析 · 5.7中Derived table變形記》

• 《ROLLUP性能增強》

• 《WL#9236, WL#9603 and WL#9727 - Add SQL window functions to MySQL》

五  關于我們

PolarDB 是阿裡巴巴自主研發的雲原生分布式關系型資料庫，于2020年進入Gartner全球資料庫Leader象限，并獲得了2020年中國電子學會頒發的科技進步一等獎。PolarDB 基于雲原生分布式資料庫架構，提供大規模線上事務處理能力，兼具對複雜查詢的并行處理能力，在雲原生分布式資料庫領域整體達到了國際領先水準，并且得到了廣泛的市場認可。在阿裡巴巴集團内部的最佳實踐中，PolarDB還全面支撐了2020年天貓雙十一，并重新整理了資料庫處理峰值記錄，高達1.4億TPS。歡迎有志之士加入我們，履歷請投遞到[email protected]，期待與您共同打造世界一流的下一代雲原生分布式關系型資料庫。