MySQL派生表合并優(yōu)化的原理和實(shí)現(xiàn)過(guò)程

更新時(shí)間：2024年07月11日 09:42:47 作者：華為云開(kāi)發(fā)者聯(lián)盟

本文從一個(gè)案例出發(fā)梳理了MySQL派生表合并優(yōu)化的流程實(shí)現(xiàn)和優(yōu)化原理,并對(duì)優(yōu)化前后同一條SQL語(yǔ)句在代碼層面的類實(shí)例映射關(guān)系進(jìn)行了對(duì)比,這篇文章主要介紹了MySQL派生表合并優(yōu)化的原理和實(shí)現(xiàn),需要的朋友可以參考下

引言

MySQL是一種流行的開(kāi)源關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，廣泛應(yīng)用于各種Web應(yīng)用程序和企業(yè)系統(tǒng)中。隨著數(shù)據(jù)量的增加和查詢復(fù)雜度的提高，優(yōu)化SQL查詢性能變得至關(guān)重要。派生表（Derived Table）是SQL查詢中常用的一種技術(shù)，通過(guò)在主查詢中嵌套子查詢來(lái)實(shí)現(xiàn)更復(fù)雜的數(shù)據(jù)處理。然而，派生表的使用有時(shí)會(huì)導(dǎo)致系統(tǒng)的性能瓶頸。

為了解決這一問(wèn)題，MySQL引入了派生表合并優(yōu)化（Derived Table Merging Optimization）。本文將詳細(xì)介紹派生表合并優(yōu)化的原理及在MySQL中的實(shí)現(xiàn)。

何為派生表？

派生表是一個(gè)臨時(shí)表，它是由子查詢的結(jié)果集生成并在主查詢中使用。簡(jiǎn)單來(lái)講，就是將FROM子句中出現(xiàn)的檢索結(jié)果集當(dāng)成一張表，比如 FROM一個(gè)SELECT構(gòu)造的子查詢，這個(gè)子查詢就是一個(gè)派生表；SELECT一個(gè)視圖，這個(gè)視圖就是一個(gè)派生表；SELECT一個(gè)WITH構(gòu)造的臨時(shí)表（Common table expression，CTE），這個(gè)CTE表就是一個(gè)派生表。如下圖舉例所示：

圖1 子查詢語(yǔ)句樣例

MySQL優(yōu)化器處理派生表有兩種策略：

第一種，將派生表物化為一個(gè)臨時(shí)表；

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

派生表物化為一個(gè)臨時(shí)表，可能會(huì)引發(fā)性能問(wèn)題，如下情況：

大數(shù)據(jù)量子查詢：派生表的結(jié)果集可能非常大，導(dǎo)致內(nèi)存消耗和磁盤(pán)I/O增加。

復(fù)雜查詢：多層嵌套查詢或包含多個(gè)派生表的查詢，會(huì)使優(yōu)化器難以選擇最佳執(zhí)行計(jì)劃。

不可索引：派生表的結(jié)果集是臨時(shí)的，無(wú)法直接使用索引進(jìn)行優(yōu)化。

為了解決這些問(wèn)題，MySQL 引入了派生表合并優(yōu)化。

派生表合并優(yōu)化原理

派生表合并優(yōu)化的核心思想是將派生表的子查詢合并到主查詢中，從而避免生成臨時(shí)表。具體來(lái)說(shuō)就是，優(yōu)化器會(huì)嘗試將派生表的子查詢展開(kāi)，并直接嵌入到主查詢的執(zhí)行計(jì)劃中。這樣可以減少臨時(shí)表的使用，降低內(nèi)存和磁盤(pán)I/O的負(fù)擔(dān)，從而提高查詢性能。

下文將通過(guò)案例對(duì)派生表合并優(yōu)化進(jìn)行詳細(xì)說(shuō)明。

1．案例分析

創(chuàng)建如下兩張表：

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;

對(duì)于下述的查詢語(yǔ)句：

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;

關(guān)閉optimizer_switch（優(yōu)化器開(kāi)關(guān)）的derived_merge選項(xiàng)，對(duì)應(yīng)的執(zhí)行計(jì)劃如下：

+----+-------------+-------------+------------+------+---------------+-------------+---------+------------------+------+----------+-----------------+
| 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列出現(xiàn)兩行DERIVED類型, 說(shuō)明派生表沒(méi)有合并，派生表會(huì)物化為臨時(shí)表。

執(zhí)行EXPLAIN ANALYZE進(jìn)一步分析，可知兩個(gè)子查詢都是物化為臨時(shí)表后，再執(zhí)行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)

開(kāi)啟optimizer_switch（優(yōu)化器開(kāi)關(guān)）的derived_merge選項(xiàng)，對(duì)應(yīng)的執(zhí)行計(jì)劃如下：

+----+-------------+-------------+------------+------+---------------+-------------+---------+---------------------+------+----------+-----------------+
| 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)

從執(zhí)行計(jì)劃可以看出，select_type列上只有一行為DERIVED類型，說(shuō)明發(fā)生了派生表合并。

執(zhí)行EXPLAIN ANALYZE進(jìn)一步分析，employees表上的子查詢?nèi)匀粫?huì)被物化為臨時(shí)表。departments表上的子查詢（派生表）進(jìn)行了合并優(yōu)化，departments表直接與臨時(shí)表t1進(jìn)行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)

對(duì)比derived_merge選項(xiàng)開(kāi)啟和關(guān)閉的兩個(gè)執(zhí)行計(jì)劃可知，開(kāi)啟派生表合并優(yōu)化特性后，departments表上的子查詢（派生表）不再物化為臨時(shí)表，而是合并到了父查詢，進(jìn)而簡(jiǎn)化了執(zhí)行計(jì)劃，并提高了執(zhí)行效率。

另外，也可以發(fā)現(xiàn)，并不是所有派生表都可以合并優(yōu)化，比如，案例中的employees表上的子查詢（派生表），因?yàn)楹芯酆虾瘮?shù)，就無(wú)法進(jìn)行合并優(yōu)化。

2．應(yīng)用場(chǎng)景限制

如下場(chǎng)景中派生表合并優(yōu)化是無(wú)效的：

1）派生表中含有聚合函數(shù)，或者含有DISTINCT、GROUP BY、HAVING這些分組子句。比如，案例中的派生表t1包含了聚合函數(shù)和GROUP BY分組就無(wú)法合并優(yōu)化。

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

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;

因?yàn)榕缮韇的select 列表中有標(biāo)量子查詢，無(wú)法合并，會(huì)被物化。

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;

上面這個(gè)例子使用用戶變量的形式給記錄加了行號(hào)，不能合并。

4）如果合并會(huì)導(dǎo)致外查詢塊中超過(guò)61張基表的連接訪問(wèn)，優(yōu)化器會(huì)選擇物化派生表。

5）UNION或UNION ALL。比如：

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

因?yàn)榕缮韉t有union操作，無(wú)法合并，會(huì)被物化。

6）對(duì)于視圖而言，創(chuàng)建視圖時(shí)如果指定了ALGORITHM=TEMPTABLE，它會(huì)阻止合并，這個(gè)屬性的優(yōu)先級(jí)比優(yōu)化器開(kāi)關(guān)的優(yōu)先級(jí)要高。

7）派生表中含LIMIT子句，因?yàn)楹喜?huì)導(dǎo)致結(jié)果集改變。比如：

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.背景知識(shí)

我們使用的MySQL代碼版本號(hào)為8.0.22。在介紹派生表代碼實(shí)現(xiàn)之前，先了解下MySQL描述一條查詢的邏輯語(yǔ)法樹(shù)結(jié)構(gòu)，有4個(gè)較為核心的類：

SELECT_LEX_UINT

對(duì)于一個(gè)query expression的描述結(jié)構(gòu)，其中可以包含union/union all等多個(gè)query block的集合運(yùn)算，同時(shí)SELECT_LEX_UNIT也根據(jù)query的結(jié)構(gòu)形成遞歸包含關(guān)系。

SELECT_LEX

對(duì)于一個(gè)query block的描述結(jié)構(gòu)，就是我們最為熟悉SPJ(選擇Selection、投影Projection、連接Join) + group by + order by + select list... 這樣的一個(gè)查詢塊，一個(gè)SELECT_LEX_UNIT中可能包含多個(gè)SELECT_LEX，而SELECT_LEX內(nèi)部則也可能嵌套包含其他SELECT_LEX_UNIT。

Item

對(duì)于expression的描述結(jié)構(gòu)，例如on條件、where條件、投影列等，都是用這個(gè)類來(lái)描述一個(gè)個(gè)表達(dá)式的，Item系統(tǒng)是MySQL SQL層代碼中最為復(fù)雜的子系統(tǒng)之一，其構(gòu)成了表達(dá)式樹(shù)。

TABLE_LIST

對(duì)于表信息的描述結(jié)構(gòu)。TABLE_LIST不僅僅針對(duì)SQL表達(dá)式中的物理表，也可以表示其他類型的表，例如視圖、臨時(shí)表、派生表等。此外，TABLE_LIST類還用于處理別名和連接等信息。

TABLE_LIST類是MySQL查詢處理的核心部分，涵蓋了SQL表達(dá)式中的各種表類型。以案例中的SQL查詢語(yǔ)句為例，在派生表合并優(yōu)化前，其對(duì)應(yīng)的類實(shí)例映射關(guān)系如下：

圖2 派生表合并優(yōu)化前的SQL語(yǔ)句

圖3 派生表合并優(yōu)化前的邏輯語(yǔ)法樹(shù)

圖2為SQL表達(dá)式，圖3為MySQL處理后對(duì)應(yīng)的邏輯語(yǔ)法樹(shù)。圖2顏色涵蓋的SQL語(yǔ)句范圍與圖3相同顏色的類實(shí)例一一對(duì)應(yīng)。比如，圖2米黃色涵蓋了整條SELECT語(yǔ)句（query block），也就對(duì)應(yīng)著圖3的SELECT_LEX1實(shí)例；圖2最外層的淺灰色包含了米黃色區(qū)域，代表整條SQL語(yǔ)句（query expression），對(duì)應(yīng)著圖3的SELECT_LEX_UINT1實(shí)例（不涉及UNION操作，SELECT_LEX_UINT1只包含SELECT_LEX1，即一個(gè)SELECT_LEX實(shí)例）。

圖2中用括號(hào)圈起來(lái)的部分，就是一個(gè)SELECT_LEX_UNIT，而每個(gè)SELECT toke開(kāi)始的一個(gè)query block，就是一個(gè)SELECT_LEX，而在外層的SELECT_LEX中，會(huì)嵌套子查詢，用一個(gè)SELECT_LEX_UNIT描述，子查詢中可以是任意查詢形態(tài)，再包含多個(gè)SELECT_LEX，從而形成SELECT_LEX_UNIT -> SELECT_LEX -> SELECT_LEX_UNIT -> SELECT_LEX ... 這種相互嵌套的結(jié)構(gòu)。

最外層的 query block（SELECT_LEX1）有兩個(gè)派生表（t1、t2）。t1 和 t2 通過(guò) derived 指針?lè)謩e指子查詢 query expression（SELECT_LEX_UINT3、SELECT_LEX_UINT2）。

2. 代碼實(shí)現(xiàn)

MySQL主要在prepare階段處理派生表的合并優(yōu)化，詳細(xì)的函數(shù)調(diào)用和處理過(guò)程如下：

-> Sql_cmd_dml::prepare
  -> Sql_cmd_select::prepare_inner
    -> SELECT_LEX::prepare
      頂層 query block 的處理，全局入口
      -> SELECT_LEX::resolve_placeholder_tables
        處理query block中的第一個(gè) derived table
        -> TABLE_LIST::resolve_derived
          -> 創(chuàng)建Query_result_union對(duì)象，在執(zhí)行derived子查詢時(shí)，用來(lái)向臨時(shí)表里寫(xiě)入結(jié)果數(shù)據(jù)
          -> 調(diào)用內(nèi)層嵌套SELECT_LEX_UNIT::prepare，對(duì)derived table對(duì)應(yīng)的子查詢做遞歸處理
            -> SELECT_LEX::prepare
              -> 判斷derived中的子查詢是否允許merge到外層，當(dāng)滿足如下任一條件時(shí)，“有可能”可以merge到外層：
                 1. derived table屬于最外層查詢
                 2. 屬于最外層的子查詢之中，且query是一個(gè)SELECT查詢
              -> SELECT_LEX::resolve_placeholder_tables 嵌套處理derived table這個(gè)子查詢內(nèi)部的derived table...
              ... 處理query block中其他的各個(gè)組件，包括condition/group by/rollup/distinct/order by...
              -> SELECT_LEX::transform_scalar_subqueries_to_join_with_derived
              ... 一系列對(duì)query block（Item中的）處理，略過(guò)
              -> SELECT_LEX::apply_local_transforms 做最后的一些查詢變換（針對(duì)最外層query block）
                 1. 簡(jiǎn)化join，把嵌套的join表序列盡可能展開(kāi)，去掉無(wú)用的join，outer join轉(zhuǎn)inner join等
                 2. 對(duì)分區(qū)表做靜態(tài)剪枝
              -> SELECT_LEX::push_conditions_to_derived_tables（針對(duì)最外層query block）
                 外 query block 中與 derived table 相關(guān)的條件會(huì)推入到派生表中
          -> 至此derived table對(duì)應(yīng)的子查詢部分resolve完成
        -> TABLE_LIST::is_mergeable
          -> SELECT_LEX_UNIT::is_mergeable
             判斷當(dāng)前 derived table 是否可以merge到外層，要同時(shí)滿足如下的要求：（只支持最簡(jiǎn)單的SPJ查詢）
             1. derived table query expression 沒(méi)有union
             2. derived table query block 沒(méi)有聚合/窗口函數(shù)+group by + 沒(méi)有having + 沒(méi)有distinct + 有table + 沒(méi)有window + 沒(méi)有l(wèi)imit
        -> SELECT_LEX::merge_derived，確定可以展開(kāi)到外層后，執(zhí)行 merge_derived 動(dòng)作
          -> 再做一系列的檢查看是否可以merge
             1. 外層query block是否允許merge，例如CREATE VIEW/SHOW CREATE這樣的命令，不允許做merge
             2. 基于啟發(fā)式，檢查derived子查詢的投影列是否有子查詢，有則不做merge
             3. 如果外層有straight_join，而derived子查詢中有semi-join/anti-join，則不允許merge
             4. 外層表的數(shù)量達(dá)到MySQL能處理的最大值
          -> 通過(guò)檢查后，開(kāi)始merge
             1. 把內(nèi)層join列表合并到外層中
             2. 把where條件與外層的where條件做AND組合
             3. 把投影列合并到外層投影列中
        -> 對(duì)于不能展開(kāi)的，采用物化方式執(zhí)行，setup_materialized_derived
        處理query block中的其它 derived table，...
      -> resolve_placeholder_tables 處理完成
        頂層 query block 的其它處理  ...

案例中的SQL語(yǔ)句經(jīng)過(guò)上面的派生表的合并優(yōu)化處理后，其對(duì)應(yīng)的映射關(guān)系如下：

圖4 派生表合并優(yōu)化后的SQL語(yǔ)句

圖5 派生表合并優(yōu)化后的邏輯語(yǔ)法樹(shù)

對(duì)比合并優(yōu)化前，有如下變化：（圖4的SQL語(yǔ)句已基于圖5的邏輯語(yǔ)法樹(shù)等價(jià)變換）

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

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

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

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

原理證明

前文描述了MySQL派生表合并優(yōu)化的具體實(shí)現(xiàn)，那么，如何從原理上證明該優(yōu)化方法的正確性呢？可以嘗試根據(jù)關(guān)系代數(shù)定理對(duì)其進(jìn)行論證。

先簡(jiǎn)化場(chǎng)景，假設(shè)有兩個(gè)表，一個(gè)是主查詢（主表）R，一個(gè)是派生表D。在沒(méi)有合并優(yōu)化之前，查詢可能是這樣的形式：

1）外層查詢從派生表中選擇數(shù)據(jù)：σ條件1(D)

2）派生表D是從另一個(gè)或多個(gè)表導(dǎo)出的結(jié)果，通過(guò)一定的操作如選擇σ條件2、投影π屬性或連接?等得到。

不考慮具體實(shí)現(xiàn)的復(fù)雜性，讓我們通過(guò)一個(gè)簡(jiǎn)單查詢的例子來(lái)說(shuō)明外層查詢和派生表合并的效果。假設(shè)派生表D是從主表R通過(guò)選擇操作產(chǎn)生的：D = σ條件2(R)，而外層查詢又對(duì)D進(jìn)行選擇：σ條件1(D)。

根據(jù)關(guān)系代數(shù)的選擇的疊加律（σa(σb(R)) = σa ∧ b(R)），可以合并這兩個(gè)選擇操作為一個(gè)操作，直接作用在主表R上：σ條件1 ∧ 條件2(R)。

這樣，外層查詢和派生表D就被合并成了一個(gè)直接對(duì)原始表R進(jìn)行操作的查詢，省去了創(chuàng)建和訪問(wèn)派生表D的開(kāi)銷。

對(duì)于更復(fù)雜的派生表，它們可能通過(guò)多個(gè)操作，如連接、投影和選擇，從一個(gè)或多個(gè)表導(dǎo)出。針對(duì)這樣的情況，基于關(guān)系代數(shù)的性質(zhì)，比如選擇的疊加律和交換律、投影的結(jié)合律等，通過(guò)相應(yīng)的關(guān)系代數(shù)變換，所有這些操作的組合都可以被重寫(xiě)為直接作用于原始表上的一系列操作，也就證明了MySQL的這一優(yōu)化方式是有效的。

總結(jié)

本文從一個(gè)案例出發(fā)梳理了MySQL派生表合并優(yōu)化的流程實(shí)現(xiàn)和優(yōu)化原理，并對(duì)優(yōu)化前后同一條SQL語(yǔ)句在代碼層面的類實(shí)例映射關(guān)系進(jìn)行了對(duì)比。MySQL派生表合并的代碼實(shí)現(xiàn)細(xì)節(jié)比較多，篇幅有限，不再贅述，希望本文能夠作為一個(gè)參考，幫助感興趣的讀者進(jìn)一步研究這部分源碼。

到此這篇關(guān)于MySQL派生表合并優(yōu)化的原理和實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL派生表合并內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: