效能提升1400+倍,快來看MySQL Volcano模型迭代器的謂詞位置優化詳解

語言: CN / TW / HK
摘要:效能提升1400+倍,快來看MySQL Volcano模型迭代器的謂詞位置優化詳解。

本文分享自華為雲社群《華為雲資料庫核心專家為您揭祕MySQL Volcano模型迭代器效能提升千倍的祕密》,作者:GaussDB 資料庫 。

20年以上資料庫核心研發經驗。原IBM DB2資料庫核心專家,專長資料庫核心效能優化、SQL查詢優化、MPP分散式資料倉庫技術等。現就職於華為加拿大研究所,全程參與了RDS for MySQL以及GaussDB(for MySQL)的研發工作,熟悉GaussDB(for MySQL) 全棧技術。負責NDP的總體架構設計和實現,併成功落地上線。擁有多項技術發明專利,並co-author了SIGMOD 2020 Taurus( GaussDB(for MySQL)) Paper,目前專注於下一代雲資料庫智慧優化器的研究。

一、背景介紹

MySQL 8.0.18引入了一個新的SQL執行引擎,它遵循了Volcano模型。該模型的關鍵思想是將所有操作建模為“迭代器”。迭代器提供基本迭代元件:初始化、迭代和終止。所有迭代器都提供如以上相同的介面,因此迭代器可以任意組合堆疊在一起,形成執行計劃。

MySQL 8.0.18還包括一個新的連線方法:雜湊連線。雜湊連線有探測端和構建端。雜湊表是使用構建端的連線列作為雜湊鍵值構建的;然後使用探測端的連線列來查詢雜湊表中的匹配行。

關於Volcano模型和雜湊連線的細節不在本文的範圍內。本文重點討論一個問題,即雜湊連線的謂詞沒有附加到合適的雜湊連線迭代器,該問題可能會導致嚴重的效能下降。請注意:這不是一個功能問題,因為儘管有顯著的效能下降,但最終的查詢結果是正確的。

華為雲資料庫核心專家林舒向MySQL官方提交了此錯誤報告,以及對應的補丁,具體資訊請參考這裡:https://bugs.mysql.com/id=104760。在本文中,我們將通過一個示例查詢,來說明此問題,並比較應用補丁前後的效能差異。

本文中的查詢在MySQL 8.0.26上測試,使用100MB的TPC-H資料庫。為了說明問題,在查詢語句中使用了索引提示(Index Hint)來促使SQL優化器選擇雜湊連線。

二、問題描述

該問題使用的查詢及其執行計劃如下:

問題查詢:

explain format=tree
select avg(case ps_partkey when null then 1 else l_quantity end)
from lineitem left outer join 
( partsupp ignore index (primary) join part ignore index (primary) on ps_partkey = p_partkey and p_name like '%snow%')
on ps_suppkey = l_suppkey and ps_partkey = l_partkey

執行計劃:

-> Aggregate: avg((case partsupp.PS_PARTKEY when NULL then 1 else lineitem.L_QUANTITY end))  (cost=179829230844583.70 rows=899131908749360)
    -> Left hash join (part.P_PARTKEY = lineitem.L_PARTKEY), (partsupp.PS_PARTKEY = lineitem.L_PARTKEY), (partsupp.PS_SUPPKEY = lineitem.L_SUPPKEY)  (cost=89916039969647.67 rows=899131908749360)
        -> Table scan on lineitem  (cost=61043.00 rows=596410)
        -> Hash
            -> Filter: (part.P_NAME like '%snow%')  (cost=2849092735.78 rows=1507573496)
                -> Inner hash join (no condition)  (cost=2849092735.78 rows=1507573496)
                    -> Table scan on partsupp  (cost=0.23 rows=77726)
                    -> Hash
                        -> Table scan on part  (cost=0.01 rows=19396)

注意以上查詢計劃的內雜湊連線(Inner hash join)部分,partsupp和part之間的連線是“無條件”的,而謂詞(part.P_NAME like“%snow%”)是在內雜湊連線完成之後才被應用來過濾結果集的。觀察原始查詢語句,我們會注意到,partsupp和part之間存在一個連線謂詞(ps_partkey = p_partkey),這個謂詞去哪裡了呢?它隱含在外層的左雜湊連線(Left hash join)的連線謂詞中,即 (part.P_PARTKEY = lineitem.L_PARTKEY)和(partupp.PS_PartKEY=lineitem.L_PartKEY)這兩個謂詞描述裡。在內雜湊連線中缺少謂詞會導致效能問題,因為它使得有謂詞的連線操作被替換為笛卡爾積,由於缺少連線條件進行過濾,結果集會被放大。此外,本地謂詞(part.P_NAME like“%snow%”),可以在內雜湊連線之前被應用,提前過濾掉無效的行。

三、原因分析

這個問題發生的場景是,一個語句使用了外連線,而這個外連線選用雜湊連線來完成,並且這個外連線的一部分涉及到了多於一張表。當這些條件同時具備,就可能會引發此問題。

在雜湊連線中,其構建端是無法訪問探測端上的任何列,反之亦然。一個引用了雙端的謂詞只能放置在雜湊連線迭代器上。當MySQL優化器為表分配謂詞時,它假定謂詞可以引用表前面出現的所有表,然而這顯然不適用於雜湊連線。因此,當優化器的計劃轉換為迭代器計劃時,優化器到迭代器的轉換程式碼需要做出額外的補救,現在MySQL的轉換程式碼中缺少相關的處理,導致了本文的問題。

四、如何修復

針對這個問題的修復主要專注在優化器結構到迭代器轉換程式碼中的主函式:ConnectJoins()。基本的思路是讓該函式知道哪些表在當前不是可用的,因為這些表位於雜湊連線的另一邊。當函式將謂詞放置在迭代器上時,尚未應用的謂詞將沿著迭代器向上推,並在所需的表可用後立即被應用。

以下是在MySQL 8.0.26之上應用修復後的執行計劃。partupp和part之間的內雜湊連線現在有一個連線謂詞:partupp.PS_PARTKEY=part.P_KEY。另外 ,本地謂詞(part.P_NAME like“%snow%”)現在出現在內部連線下面,也就是先於內雜湊連線而被應用。

-> Aggregate: avg((case partsupp.PS_PARTKEY when NULL then 1 else lineitem.L_QUANTITY end))  (cost=179829230844583.70 rows=899131908749360)
    -> Left hash join (part.P_PARTKEY = lineitem.L_PARTKEY), (partsupp.PS_SUPPKEY = lineitem.L_SUPPKEY)  (cost=89916039969647.67 rows=899131908749360)
        -> Table scan on lineitem  (cost=61043.00 rows=596410)
        -> Hash
            -> Inner hash join (partsupp.PS_PARTKEY = part.P_PARTKEY)  (cost=2849092735.78 rows=1507573496)
                -> Table scan on partsupp  (cost=0.23 rows=77726)
                -> Hash
                    -> Filter: (part.P_NAME like '%snow%')  (cost=0.01 rows=19396)
                        -> Table scan on part  (cost=0.01 rows=19396)

下面是應用補丁前後的查詢時間比較:打補丁前,查詢耗時需要11分37秒

打補丁後,查詢耗時僅需0.49秒

11分37秒 vs 0.49秒,修改前後的效能差距有1400多倍,區別是巨大的。希望這個問題可以在下一個MySQL版本中得到解決。

我們知道,MySQL社群的發展離不開每個資料庫領域從業人員的努力,華為雲GaussDB也一直重視開源社群的發展,積極對社群版本進行優化和改進,為社群做貢獻。本次MySQL Volcano模型迭代器的謂詞位置優化,助力MySQL查詢效能提升千倍,正是華為雲GaussDB 對社群發展的積極反饋。

另外,告訴大家一個好訊息,華為雲資料庫專場活動正在進行,雲資料庫MySQL包年19.9元起,助力企業無憂上雲,更多活動詳情戳:https://activity.huaweicloud.com/dbs_Promotion/index.html

參考資料:

[1] G. Graefe, "Volcano— An Extensible and Parallel Query Evaluation System," IEEE Transactions on Knowledge and Data Engineering, pp. 120-135, 1994.

[2] "WL#11785: Volcano iterator design," [Online]. Available: https://dev.mysql.com/worklog/task/?id=11785.

[3] "WL#12074: Volcano iterator executor base," [Online]. Available: https://dev.mysql.com/worklog/task/?id=12074.

[4] "WL#12470: Volcano iterator semijoin," [Online]. Available: https://dev.mysql.com/worklog/task/?id=12470.

[5] "Hash Join Optimization," [Online]. Available: https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html.

[6] "WL#2241: Hash join," [Online]. Available: https://dev.mysql.com/worklog/task/?id=2241.

"TPC-H Homepage," [Online]. Available: http://www.tpc.org/tpch/.

 

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