MySQL派生表合併最佳化的原理和實現

华为云开发者联盟發表於2024-07-11

本文分享自華為雲社群《【華為雲MySQL技術專欄】MySQL 派生表合併最佳化的原理和實現》，作者：GaussDB 資料庫。

引言

MySQL是一種流行的開源關係型資料庫管理系統，廣泛應用於各種Web應用程式和企業系統中。隨著資料量的增加和查詢複雜度的提高，最佳化SQL查詢效能變得至關重要。派生表（Derived Table）是SQL查詢中常用的一種技術，透過在主查詢中巢狀子查詢來實現更復雜的資料處理。然而，派生表的使用有時會導致系統的效能瓶頸。

為了解決這一問題，MySQL引入了派生表合併最佳化（Derived Table Merging Optimization）。本文將詳細介紹派生表合併最佳化的原理及在MySQL中的實現。

何為派生表？

派生表是一個臨時表，它是由子查詢的結果集生成並在主查詢中使用。簡單來講，就是將FROM子句中出現的檢索結果集當成一張表，比如 FROM一個SELECT構造的子查詢，這個子查詢就是一個派生表；SELECT一個檢視，這個檢視就是一個派生表；SELECT一個WITH構造的臨時表（Common table expression，CTE），這個CTE表就是一個派生表。如下圖舉例所示：

圖1 子查詢語句樣例

MySQL最佳化器處理派生表有兩種策略：

第一種，將派生表物化為一個臨時表；

第二種，將派生表合併到外查詢塊中。

派生表物化為一個臨時表，可能會引發效能問題，如下情況：

大資料量子查詢：派生表的結果集可能非常大，導致記憶體消耗和磁碟I/O增加。
複雜查詢：多層巢狀查詢或包含多個派生表的查詢，會使最佳化器難以選擇最佳執行計劃。
不可索引：派生表的結果集是臨時的，無法直接使用索引進行最佳化。

為了解決這些問題，MySQL 引入了派生表合併最佳化。

派生表合併最佳化原理

派生表合併最佳化的核心思想是將派生表的子查詢合併到主查詢中，從而避免生成臨時表。具體來說就是，最佳化器會嘗試將派生表的子查詢展開，並直接嵌入到主查詢的執行計劃中。這樣可以減少臨時表的使用，降低記憶體和磁碟I/O的負擔，從而提高查詢效能。

下文將透過案例對派生表合併最佳化進行詳細說明。

1．案例分析

建立如下兩張表：

CREATE TABLE `departments` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `employees` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
`department_id` int DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

對於下述的查詢語句：

SELECT t1.department_id, t2.name, t1.total_salary
FROM
(SELECT department_id, SUM(salary) total_salary
FROM employees GROUP BY department_id) t1
JOIN
(SELECT id, name
FROM departments
WHERE name='Human Resources') t2
ON t1.department_id = t2.id;

關閉optimizer_switch（最佳化器開關）的derived_merge選項，對應的執行計劃如下：

+----+-------------+-------------+------------+------+---------------+-------------+---------+------------------+------+----------+-----------------+
| id | select_type | table       | partitions | type | possible_keys | key         | key_len | ref              | rows | filtered | Extra           
|+----+-------------+-------------+------------+------+---------------+-------------+---------+------------------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2>  | NULL       | ALL  | NULL          | NULL        | NULL    | NULL             |    2 |   100.00 | Using where     |
|  1 | PRIMARY     | <derived3>  | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | t1.department_id |    2 |   100.00 | NULL            |
|  3 | DERIVED     | departments | NULL       | ALL  | NULL          | NULL        | NULL    | NULL             |    1 |   100.00 | Using where     |
|  2 | DERIVED     | employees   | NULL       | ALL  | NULL          | NULL        | NULL    | NULL             |    1 |   100.00 | Using temporary |
+----+-------------+-------------+------------+------+---------------+-------------+---------+------------------+------+----------+-----------------+
4 rows in set, 1 warning (0.01 sec)

select_type列出現兩行DERIVED型別, 說明派生表沒有合併，派生表會物化為臨時表。

執行EXPLAIN ANALYZE進一步分析，可知兩個子查詢都是物化為臨時表後，再執行JOIN。

EXPLAIN: -> Nested loop inner join  (actual time=0.304..0.308 rows=1 loops=1)
-> Table scan on t2  (cost=2.73 rows=2) (actual time=0.003..0.003 rows=1 loops=1)
-> Materialize  (cost=0.55 rows=1) (actual time=0.163..0.164 rows=1 loops=1)
-> Filter: (departments.`name`='Human Resources') (cost=0.55 rows=1) (actual time=0.103..0.125 rows=1 loops=1)
-> Table scan on departments (cost=0.55 rows=3) (actual time=0.095..0.114 rows=3 loops=1)
-> Index lookup on t1 using <auto_key0> (department_id=t2.id) (actual time=0.004..0.006 rows=1 loops=1)
-> Materialize (actual time=0.137..0.139 rows=1 loops=1)
-> Table scan on <temporary> (actual time=0.001..0.003 rows=3 loops=1)
-> Aggregate using temporary table (actual time=0.102..0.104 rows=3 loops=1)
-> Table scan on employees (cost=0.65 rows=4) (actual time=0.040..0.056 rows=4 loops=1)
 1 row in set (0.00 sec)

開啟optimizer_switch（最佳化器開關）的derived_merge選項，對應的執行計劃如下：

+----+-------------+-------------+------------+------+---------------+-------------+---------+---------------------+------+----------+-----------------+
| id | select_type | table       | partitions | type | possible_keys | key         | key_len | ref                 | rows | filtered | Extra           |
+----+-------------+-------------+------------+------+---------------+-------------+---------+---------------------+------+----------+-----------------+
|  1 | PRIMARY     | departments | NULL       | ALL  | PRIMARY       | NULL        | NULL    | NULL                |    1 |   100.00 | Using where     |
|  1 | PRIMARY     | <derived2>  | NULL       | ref  | <auto_key0>   | <auto_key0> | 5       | test.departments.id |    2 |   100.00 | NULL            |
|  2 | DERIVED     | employees   | NULL       | ALL  | NULL          | NULL        | NULL    | NULL                |    1 |   100.00 | Using temporary |
+----+-------------+-------------+------------+------+---------------+-------------+---------+---------------------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

從執行計劃可以看出，select_type列上只有一行為DERIVED型別，說明發生了派生表合併。

執行EXPLAIN ANALYZE進一步分析，employees表上的子查詢仍然會被物化為臨時表。departments表上的子查詢（派生表）進行了合併最佳化，departments表直接與臨時表t1進行JOIN。

EXPLAIN: -> Nested loop inner join (actual time=0.271..0.295 rows=1 loops=1)
-> Filter: (departments.`name` = 'Human Resources') (cost=0.55 rows=1) (actual time=0.103..0.122 rows=1 loops=1)
-> Table scan on departments (cost=0.55 rows=3) (actual time=0.095..0.112 rows=3 loops=1)
-> Index lookup on t1 using <auto_key0> (department_id=departments.id) (actual time=0.005..0.007 rows=1 loops=1)
-> Materialize (actual time=0.164..0.166 rows=1 loops=1)
-> Table scan on <temporary> (actual time=0.002..0.004 rows=3 loops=1)
-> Aggregate using temporary table (actual time=0.114..0.117 rows=3 loops=1)
-> Table scan on employees (cost=0.65 rows=4) (actual time=0.044..0.065 rows=4 loops=1) 
1 row in set (0.00 sec)

對比derived_merge選項開啟和關閉的兩個執行計劃可知，開啟派生表合併最佳化特性後，departments表上的子查詢（派生表）不再物化為臨時表，而是合併到了父查詢，進而簡化了執行計劃，並提高了執行效率。

另外，也可以發現，並不是所有派生表都可以合併最佳化，比如，案例中的employees表上的子查詢（派生表），因為含有聚合函式，就無法進行合併最佳化。

2．應用場景限制

如下場景中派生表合併最佳化是無效的：

1）派生表中含有聚合函式，或者含有DISTINCT、GROUP BY、HAVING這些分組子句。比如，案例中的派生表t1包含了聚合函式和GROUP BY分組就無法合併最佳化。

2）派生表的SELECT列表中有子查詢，也就是標量子查詢。比如：

select *
from (select stuno,
course_no,
(select course_name
from course c
where c.course_no = a.course_no) as course_name,
score
from score a) b
where b.stuno = 1;

因為派生表b的select 列表中有標量子查詢，無法合併，會被物化。

3）分配了使用者變數。比如：

select (@i := @i + 1) as rownum, stuno, course_no, course_name, score
from ((select a.stuno, a.course_no, b.course_name, a.score
from score a
left join course b
on a.course_no = b.course_no) dt, (select (@i := 0) num) c)
where stuno = 1;

上面這個例子使用使用者變數的形式給記錄加了行號，不能合併。

4）如果合併會導致外查詢塊中超過61張基表的連線訪問，最佳化器會選擇物化派生表。

5）UNION或UNION ALL。比如：

select id, c1from (select id, c1 
from t1
union
select id, c1 from t2) dt
where dt.id = 1;

因為派生表dt有union操作，無法合併，會被物化。

6）對於檢視而言，建立檢視時如果指定了ALGORITHM=TEMPTABLE，它會阻止合併，這個屬性的優先順序比最佳化器開關的優先順序要高。

7）派生表中含LIMIT子句，因為合併會導致結果集改變。比如：

select * from (select id,c1 from t1 limit 10) a where a. id=1;

8）只引用了字面量值。比如：

select * from (select '1' as c1, 2 as c2 ) a;

原始碼分析

1.背景知識

我們使用的MySQL程式碼版本號為8.0.22。在介紹派生表程式碼實現之前，先了解下MySQL描述一條查詢的邏輯語法樹結構，有4個較為核心的類：

SELECT_LEX_UINT

對於一個query expression的描述結構，其中可以包含union/union all等多個query block的集合運算，同時SELECT_LEX_UNIT也根據query的結構形成遞迴包含關係。

SELECT_LEX

對於一個query block的描述結構，就是我們最為熟悉SPJ(選擇Selection、投影Projection、連線Join) + group by + order by + select list... 這樣的一個查詢塊，一個SELECT_LEX_UNIT中可能包含多個SELECT_LEX，而SELECT_LEX內部則也可能巢狀包含其他SELECT_LEX_UNIT。

Item

對於expression的描述結構，例如on條件、where條件、投影列等，都是用這個類來描述一個個表示式的，Item系統是MySQL SQL層程式碼中最為複雜的子系統之一，其構成了表示式樹。

TABLE_LIST

對於表資訊的描述結構。TABLE_LIST不僅僅針對SQL表示式中的物理表，也可以表示其他型別的表，例如檢視、臨時表、派生表等。此外，TABLE_LIST類還用於處理別名和連線等資訊。

TABLE_LIST類是MySQL查詢處理的核心部分，涵蓋了SQL表示式中的各種表型別。以案例中的SQL查詢語句為例，在派生表合併最佳化前，其對應的類例項對映關係如下：

圖2 派生表合併最佳化前的SQL語句

圖3 派生表合併最佳化前的邏輯語法樹

圖2為SQL表示式，圖3為MySQL處理後對應的邏輯語法樹。圖2顏色涵蓋的SQL語句範圍與圖3相同顏色的類例項一一對應。比如，圖2米黃色涵蓋了整條SELECT語句（query block），也就對應著圖3的SELECT_LEX1例項；圖2最外層的淺灰色包含了米黃色區域，代表整條SQL語句（query expression），對應著圖3的SELECT_LEX_UINT1例項（不涉及UNION操作，SELECT_LEX_UINT1只包含SELECT_LEX1，即一個SELECT_LEX例項）。

圖2中用括號圈起來的部分，就是一個SELECT_LEX_UNIT，而每個SELECT toke開始的一個query block，就是一個SELECT_LEX，而在外層的SELECT_LEX中，會巢狀子查詢，用一個SELECT_LEX_UNIT描述，子查詢中可以是任意查詢形態，再包含多個SELECT_LEX，從而形成SELECT_LEX_UNIT -> SELECT_LEX -> SELECT_LEX_UNIT -> SELECT_LEX ... 這種相互巢狀的結構。

最外層的 query block（SELECT_LEX1）有兩個派生表（t1、t2）。t1 和 t2 透過 derived 指標分別指子查詢 query expression（SELECT_LEX_UINT3、SELECT_LEX_UINT2）。

2. 程式碼實現

MySQL主要在prepare階段處理派生表的合併最佳化，詳細的函式呼叫和處理過程如下：

-> Sql_cmd_dml::prepare
  -> Sql_cmd_select::prepare_inner
    -> SELECT_LEX::prepare
      頂層 query block 的處理，全域性入口
      -> SELECT_LEX::resolve_placeholder_tables
        處理query block中的第一個 derived table
        -> TABLE_LIST::resolve_derived
          -> 建立Query_result_union物件，在執行derived子查詢時，用來向臨時表裡寫入結果資料
          -> 呼叫內層巢狀SELECT_LEX_UNIT::prepare，對derived table對應的子查詢做遞迴處理
            -> SELECT_LEX::prepare
              -> 判斷derived中的子查詢是否允許merge到外層，當滿足如下任一條件時，“有可能”可以merge到外層：
                 1. derived table屬於最外層查詢
                 2. 屬於最外層的子查詢之中，且query是一個SELECT查詢
              -> SELECT_LEX::resolve_placeholder_tables 巢狀處理derived table這個子查詢內部的derived table...
              ... 處理query block中其他的各個元件，包括condition/group by/rollup/distinct/order by...
              -> SELECT_LEX::transform_scalar_subqueries_to_join_with_derived
              ... 一系列對query block（Item中的）處理，略過
              -> SELECT_LEX::apply_local_transforms 做最後的一些查詢變換（針對最外層query block）
                 1. 簡化join，把巢狀的join表序列儘可能展開，去掉無用的join，outer join轉inner join等
                 2. 對分割槽表做靜態剪枝
              -> SELECT_LEX::push_conditions_to_derived_tables（針對最外層query block）
                 外 query block 中與 derived table 相關的條件會推入到派生表中
          -> 至此derived table對應的子查詢部分resolve完成
        -> TABLE_LIST::is_mergeable
          -> SELECT_LEX_UNIT::is_mergeable
             判斷當前 derived table 是否可以merge到外層，要同時滿足如下的要求：（只支援最簡單的SPJ查詢）
             1. derived table query expression 沒有union
             2. derived table query block 沒有聚合/視窗函式+group by + 沒有having + 沒有distinct + 有table + 沒有window + 沒有limit
        -> SELECT_LEX::merge_derived，確定可以展開到外層後，執行 merge_derived 動作
          -> 再做一系列的檢檢視是否可以merge
             1. 外層query block是否允許merge，例如CREATE VIEW/SHOW CREATE這樣的命令，不允許做merge
             2. 基於啟發式，檢查derived子查詢的投影列是否有子查詢，有則不做merge
             3. 如果外層有straight_join，而derived子查詢中有semi-join/anti-join，則不允許merge
             4. 外層表的數量達到MySQL能處理的最大值
          -> 透過檢查後，開始merge
             1. 把內層join列表合併到外層中
             2. 把where條件與外層的where條件做AND組合
             3. 把投影列合併到外層投影列中
        -> 對於不能展開的，採用物化方式執行，setup_materialized_derived
        處理query block中的其它 derived table，...
      -> resolve_placeholder_tables 處理完成
        頂層 query block 的其它處理  ...

案例中的SQL語句經過上面的派生表的合併最佳化處理後，其對應的對映關係如下：

圖4 派生表合併最佳化後的SQL語句

圖5 派生表合併最佳化後的邏輯語法樹

對比合並最佳化前，有如下變化：（圖4的SQL語句已基於圖5的邏輯語法樹等價變換）

1）派生表t2所指向的內部 query expression（SELECT_LEX_UINT2/SELECT_LEX2）已消除。

2）SELECT_LEX2中的物理表departments上移至外部query block（SELECT_LEX1）的JOIN運算中。

3）SELECT_LEX2中的WHERE條件合併到SELECT_LEX1。

4）SELECT_LEX1中針對派生表t2的投影，替換為物理表departments。

原理證明

前文描述了MySQL派生表合併最佳化的具體實現，那麼，如何從原理上證明該最佳化方法的正確性呢？可以嘗試根據關係代數定理對其進行論證。

先簡化場景，假設有兩個表，一個是主查詢（主表）R，一個是派生表D。在沒有合併最佳化之前，查詢可能是這樣的形式：

1）外層查詢從派生表中選擇資料：σ條件1(D)

2）派生表D是從另一個或多個表匯出的結果，透過一定的操作如選擇σ條件2、投影π屬性或連線⋈等得到。

不考慮具體實現的複雜性，讓我們透過一個簡單查詢的例子來說明外層查詢和派生表合併的效果。假設派生表D是從主表R透過選擇操作產生的：D = σ條件2(R)，而外層查詢又對D進行選擇：σ條件1(D)。

根據關係代數的選擇的疊加律（σa(σb(R)) = σa ∧ b(R)），可以合併這兩個選擇操作為一個操作，直接作用在主表R上：σ條件1 ∧ 條件2(R)。

這樣，外層查詢和派生表D就被合併成了一個直接對原始表R進行操作的查詢，省去了建立和訪問派生表D的開銷。

對於更復雜的派生表，它們可能透過多個操作，如連線、投影和選擇，從一個或多個表匯出。針對這樣的情況，基於關係代數的性質，比如選擇的疊加律和交換律、投影的結合律等，透過相應的關係代數變換，所有這些操作的組合都可以被重寫為直接作用於原始表上的一系列操作，也就證明了MySQL的這一最佳化方式是有效的。

總結

本文從一個案例出發梳理了MySQL派生表合併最佳化的流程實現和最佳化原理，並對最佳化前後同一條SQL語句在程式碼層面的類例項對映關係進行了對比。MySQL派生表合併的程式碼實現細節比較多，篇幅有限，不再贅述，希望本文能夠作為一個參考，幫助感興趣的讀者進一步研究這部分原始碼。

點選關注，第一時間瞭解華為雲新鮮技術~

Hbase-原理-region合併和hfile的合併（大合併、小合併）
2020-11-27
MySQL的索引型別和實現原理
2019-09-07
MySql索引型別
值得收藏，揭秘 MySQL 多版本併發控制實現原理
2022-12-01
MySql
MySQL 合併查詢union 查詢出的行合併到一個表中
2023-02-01
MySql
MySQL 合併查詢join 查詢出的不同列合併到一個表中
2023-02-02
MySql
ForkJoin和氣泡排序組合實現的歸併排序
2020-11-02
排序
MySQL-17-mysql alter 語句如何實現？如何合併為一個
2024-07-07
MySql
PostgreSQL、Oracle/MySQL和SQL Server的MVCC實現原理方式
2019-05-14
OracleMySqlServerMVC
Mysql資料庫大表最佳化方案和Mysql大表最佳化步驟
2019-03-07
MySql資料庫
MySQL——索引實現原理
2018-10-30
MySql索引
MySQL MVCC實現原理
2023-03-28
MySqlMVC
Nginx 實現高併發的原理分析
2022-09-20
Nginx
併發容器之ArrayBlockingQueue和LinkedBlockingQueue實現原理詳解
2018-05-06
BloC
mysql 索引合併
2021-09-10
MySql索引
基於檔案的表合併及行轉列實現參考
2021-06-29
Aspose.Words實現郵件合併功能和列印
2020-10-23
MySQL中InnoDB儲存引擎的實現和執行原理
2021-06-01
MySql儲存引擎
MySQL Join的底層實現原理
2018-11-13
MySql
MySQL: kill 會話的實現原理
2019-01-30
MySql會話
併發——深入分析ThreadLocal的實現原理
2020-04-16
thread
MySQL事務實現原理
2021-09-09
MySql
併發Lock之ReentrantLock實現原理
2019-03-03
ReentrantLock
java併發之SynchronousQueue實現原理
2018-08-23
Java
python字典合併運算子的原理
2021-09-11
Python
Mysql事務原理與最佳化最佳實踐
2024-04-28
MySql
效率爆表！EasyExcel合併單元格這樣實現才是yyds
2024-08-19
Excel
類的繼承和派生
2024-05-06
繼承
SAP Marketing Cloud的Contact匯入配置和資料合併原理
2019-07-03
Cloud
前端路由的原理和實現
2019-04-28
前端路由
MySQL索引底層實現原理
2018-11-20
MySql索引
MySQL 高併發配置最佳化
2019-04-18
MySql
Mysql表分割槽實現
2020-10-25
MySql
報表合計需求的實現方法
2019-11-21
Nginx實現高速併發處理的原理詳解
2021-01-13
Nginx
SAP Marketing Cloud Contact 模型的匯入配置和資料合併原理
2022-05-15
Cloud模型
【MySQL】MySQL如何合併多行資料，行轉列，group_concat 多行合併
2020-11-26
MySql
《java併發程式設計的藝術》併發底層實現原理
2018-07-17
Java程式設計
Excel快速合併多張Excel工作表教程 Excel工作表怎麼合併？
2018-04-15
Excel