MySQL派生表合并優(yōu)化的原理和實現(xiàn)過程
引言
MySQL是一種流行的開源關系型數(shù)據(jù)庫管理系統(tǒng)，廣泛應用于各種Web應用程序和企業(yè)系統(tǒng)中。隨著數(shù)據(jù)量的增加和查詢復雜度的提高，優(yōu)化SQL查詢性能變得至關重要。派生表（Derived Table）是SQL查詢中常用的一種技術，通過在主查詢中嵌套子查詢來實現(xiàn)更復雜的數(shù)據(jù)處理。然而，派生表的使用有時會導致系統(tǒng)的性能瓶頸。
為了解決這一問題，MySQL引入了派生表合并優(yōu)化（Derived Table Merging Optimization）。本文將詳細介紹派生表合并優(yōu)化的原理及在MySQL中的實現(xiàn)。
何為派生表？
派生表是一個臨時表，它是由子查詢的結果集生成并在主查詢中使用。簡單來講，就是將FROM子句中出現(xiàn)的檢索結果集當成一張表，比如 FROM一個SELECT構造的子查詢，這個子查詢就是一個派生表；SELECT一個視圖，這個視圖就是一個派生表；SELECT一個WITH構造的臨時表（Common table expression，CTE），這個CTE表就是一個派生表。如下圖舉例所示：
圖1 子查詢語句樣例
MySQL優(yōu)化器處理派生表有兩種策略：
第一種，將派生表物化為一個臨時表；
第二種，將派生表合并到外查詢塊中。
派生表物化為一個臨時表，可能會引發(fā)性能問題，如下情況：
大數(shù)據(jù)量子查詢：派生表的結果集可能非常大，導致內(nèi)存消耗和磁盤I/O增加。
復雜查詢：多層嵌套查詢或包含多個派生表的查詢，會使優(yōu)化器難以選擇最佳執(zhí)行計劃。
不可索引：派生表的結果集是臨時的，無法直接使用索引進行優(yōu)化。
為了解決這些問題，MySQL 引入了派生表合并優(yōu)化。
派生表合并優(yōu)化原理
派生表合并優(yōu)化的核心思想是將派生表的子查詢合并到主查詢中，從而避免生成臨時表。具體來說就是，優(yōu)化器會嘗試將派生表的子查詢展開，并直接嵌入到主查詢的執(zhí)行計劃中。這樣可以減少臨時表的使用，降低內(nèi)存和磁盤I/O的負擔，從而提高查詢性能。
下文將通過案例對派生表合并優(yōu)化進行詳細說明。
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;
對于下述的查詢語句：
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（優(yōu)化器開關）的derived_merge選項，對應的執(zhí)行計劃如下：
+----+-------------+-------------+------------+------+---------------+-------------+---------+------------------+------+----------+-----------------+ | 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類型, 說明派生表沒有合并，派生表會物化為臨時表。
執(zhí)行EXPLAIN ANALYZE進一步分析，可知兩個子查詢都是物化為臨時表后，再執(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)
開啟optimizer_switch（優(yōu)化器開關）的derived_merge選項，對應的執(zhí)行計劃如下：
+----+-------------+-------------+------------+------+---------------+-------------+---------+---------------------+------+----------+-----------------+ | 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í)行計劃可以看出，select_type列上只有一行為DERIVED類型，說明發(fā)生了派生表合并。
執(zhí)行EXPLAIN ANALYZE進一步分析，employees表上的子查詢?nèi)匀粫晃锘癁榕R時表。departments表上的子查詢（派生表）進行了合并優(yōu)化，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選項開啟和關閉的兩個執(zhí)行計劃可知，開啟派生表合并優(yōu)化特性后，departments表上的子查詢（派生表）不再物化為臨時表，而是合并到了父查詢，進而簡化了執(zhí)行計劃，并提高了執(zhí)行效率。
另外，也可以發(fā)現(xiàn)，并不是所有派生表都可以合并優(yōu)化，比如，案例中的employees表上的子查詢（派生表），因為含有聚合函數(shù)，就無法進行合并優(yōu)化。
2．應用場景限制
如下場景中派生表合并優(yōu)化是無效的：
1）派生表中含有聚合函數(shù)，或者含有DISTINCT、GROUP BY、HAVING這些分組子句。比如，案例中的派生表t1包含了聚合函數(shù)和GROUP BY分組就無法合并優(yōu)化。
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張基表的連接訪問，優(yōu)化器會選擇物化派生表。
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）對于視圖而言，創(chuàng)建視圖時如果指定了ALGORITHM=TEMPTABLE，它會阻止合并，這個屬性的優(yōu)先級比優(yōu)化器開關的優(yōu)先級要高。
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。在介紹派生表代碼實現(xiàn)之前，先了解下MySQL描述一條查詢的邏輯語法樹結構，有4個較為核心的類：
SELECT_LEX_UINT
對于一個query expression的描述結構，其中可以包含union/union all等多個query block的集合運算，同時SELECT_LEX_UNIT也根據(jù)query的結構形成遞歸包含關系。
SELECT_LEX
對于一個query block的描述結構，就是我們最為熟悉SPJ(選擇Selection、投影Projection、連接Join) + group by + order by + select list... 這樣的一個查詢塊，一個SELECT_LEX_UNIT中可能包含多個SELECT_LEX，而SELECT_LEX內(nèi)部則也可能嵌套包含其他SELECT_LEX_UNIT。
Item
對于expression的描述結構，例如on條件、where條件、投影列等，都是用這個類來描述一個個表達式的，Item系統(tǒng)是MySQL SQL層代碼中最為復雜的子系統(tǒng)之一，其構成了表達式樹。
TABLE_LIST
對于表信息的描述結構。TABLE_LIST不僅僅針對SQL表達式中的物理表，也可以表示其他類型的表，例如視圖、臨時表、派生表等。此外，TABLE_LIST類還用于處理別名和連接等信息。
TABLE_LIST類是MySQL查詢處理的核心部分，涵蓋了SQL表達式中的各種表類型。以案例中的SQL查詢語句為例，在派生表合并優(yōu)化前，其對應的類實例映射關系如下：
圖2 派生表合并優(yōu)化前的SQL語句
圖3 派生表合并優(yōu)化前的邏輯語法樹
圖2為SQL表達式，圖3為MySQL處理后對應的邏輯語法樹。圖2顏色涵蓋的SQL語句范圍與圖3相同顏色的類實例一一對應。比如，圖2米黃色涵蓋了整條SELECT語句（query block），也就對應著圖3的SELECT_LEX1實例；圖2最外層的淺灰色包含了米黃色區(qū)域，代表整條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描述，子查詢中可以是任意查詢形態(tài)，再包含多個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. 代碼實現(xiàn)
MySQL主要在prepare階段處理派生表的合并優(yōu)化，詳細的函數(shù)調(diào)用和處理過程如下：
-> 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
-> 創(chuàng)建Query_result_union對象，在執(zhí)行derived子查詢時，用來向臨時表里寫入結果數(shù)據(jù)
-> 調(diào)用內(nèi)層嵌套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這個子查詢內(nèi)部的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. 對分區(qū)表做靜態(tài)剪枝
-> 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 沒有聚合/窗口函數(shù)+group by + 沒有having + 沒有distinct + 有table + 沒有window + 沒有l(wèi)imit
-> SELECT_LEX::merge_derived，確定可以展開到外層后，執(zhí)行 merge_derived 動作
-> 再做一系列的檢查看是否可以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ù)量達到MySQL能處理的最大值
-> 通過檢查后，開始merge
1. 把內(nèi)層join列表合并到外層中
2. 把where條件與外層的where條件做AND組合
3. 把投影列合并到外層投影列中
-> 對于不能展開的，采用物化方式執(zhí)行，setup_materialized_derived
處理query block中的其它 derived table，...
-> resolve_placeholder_tables 處理完成
頂層 query block 的其它處理 ...案例中的SQL語句經(jīng)過上面的派生表的合并優(yōu)化處理后，其對應的映射關系如下：
圖4 派生表合并優(yōu)化后的SQL語句
圖5 派生表合并優(yōu)化后的邏輯語法樹
對比合并優(yōu)化前，有如下變化：（圖4的SQL語句已基于圖5的邏輯語法樹等價變換）
1）派生表t2所指向的內(nèi)部 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派生表合并優(yōu)化的具體實現(xiàn)，那么，如何從原理上證明該優(yōu)化方法的正確性呢？可以嘗試根據(jù)關系代數(shù)定理對其進行論證。
先簡化場景，假設有兩個表，一個是主查詢（主表）R，一個是派生表D。在沒有合并優(yōu)化之前，查詢可能是這樣的形式：
1）外層查詢從派生表中選擇數(shù)據(jù)：σ條件1(D)
2）派生表D是從另一個或多個表導出的結果，通過一定的操作如選擇σ條件2、投影π屬性或連接?等得到。
不考慮具體實現(xiàn)的復雜性，讓我們通過一個簡單查詢的例子來說明外層查詢和派生表合并的效果。假設派生表D是從主表R通過選擇操作產(chǎn)生的：D = σ條件2(R)，而外層查詢又對D進行選擇：σ條件1(D)。
根據(jù)關系代數(shù)的選擇的疊加律（σa(σb(R)) = σa ∧ b(R)），可以合并這兩個選擇操作為一個操作，直接作用在主表R上：σ條件1 ∧ 條件2(R)。
這樣，外層查詢和派生表D就被合并成了一個直接對原始表R進行操作的查詢，省去了創(chuàng)建和訪問派生表D的開銷。
對于更復雜的派生表，它們可能通過多個操作，如連接、投影和選擇，從一個或多個表導出。針對這樣的情況，基于關系代數(shù)的性質(zhì)，比如選擇的疊加律和交換律、投影的結合律等，通過相應的關系代數(shù)變換，所有這些操作的組合都可以被重寫為直接作用于原始表上的一系列操作，也就證明了MySQL的這一優(yōu)化方式是有效的。
總結
本文從一個案例出發(fā)梳理了MySQL派生表合并優(yōu)化的流程實現(xiàn)和優(yōu)化原理，并對優(yōu)化前后同一條SQL語句在代碼層面的類實例映射關系進行了對比。MySQL派生表合并的代碼實現(xiàn)細節(jié)比較多，篇幅有限，不再贅述，希望本文能夠作為一個參考，幫助感興趣的讀者進一步研究這部分源碼。
到此這篇關于MySQL派生表合并優(yōu)化的原理和實現(xiàn)的文章就介紹到這了,更多相關MySQL派生表合并內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了mysql5.6.8源碼安裝過程，非常不錯，具有參考借鑒價值,需要的朋友可以參考下2018-01-01
MySQL分表是將原始表中的數(shù)據(jù)按照一定規(guī)則分散到多個表中,以減輕單表數(shù)據(jù)量過大的壓力,本文主要介紹了MySQL分表策略與實踐小結,具有一定的參考價值,感興趣的可以了解一下2024-03-03
這篇文章主要介紹了MySQL lru鏈表的相關資料，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-11-11
mysql中group by語句用于分組查詢，可以根據(jù)給定數(shù)據(jù)列的每個成員對查詢結果進行分組統(tǒng)計，最終得到一個分組匯總表， 經(jīng)常和having一起使用,需要的朋友可以參考下2016-11-11
SELinux導致PHP連接MySQL異常Can''t connect to MySQL server的解決方法
這篇文章主要介紹了SELinux導致PHP連接MySQL異常Can't connect to MySQL server的解決方法,有2種,一是設置允許,二是關閉SELinux,需要的朋友可以參考下2014-07-07
這篇文章主要介紹了MySQL5綠色版windows下安裝總結,需要的朋友可以參考下2017-03-03
MySQL優(yōu)化案例系列-mysql分頁優(yōu)化
這篇文章主要介紹了MySQL優(yōu)化案例系列-mysql分頁優(yōu)化,需要的朋友可以參考下2016-08-08
MySQL數(shù)據(jù)庫的InnoDB和MyISAM存儲引擎的區(qū)別及說明
InnoDB是MySQL的默認存儲引擎,它支持事務、外鍵和行級鎖定,具有更好的并發(fā)控制性能和崩潰恢復能力,而MyISAM不支持事務和外鍵,使用表級鎖定,適合讀操作頻繁的場景2024-12-12
探究MySQL中索引和提交頻率對InnoDB表寫入速度的影響
這篇文章主要介紹了MySQL中索引和提交頻率對InnoDB表寫入速度的影響,作者通過實際測試運行時間的對比來驗證,需要的朋友可以參考下2015-05-05
在SpringBoot中實現(xiàn)WebSocket會話管理的方案
在構建實時通信應用時,WebSocket 無疑是一個強大的工具,SpringBoot提供了對WebSocket的支持,本文旨在探討如何在 Spring Boot 應用中實現(xiàn) WebSocket 會話管理,我們將通過一個模擬的場景一步步展開討論,需要的朋友可以參考下2023-11-11