華為云資料庫內核專家為您揭秘MySQL Volcano模型迭代器性能提升千倍的秘密-有解無憂

林舒，20年以上資料庫內核研發經驗，原IBMDB2資料庫內核專家，專長資料庫內核性能優化、SQL查詢優化、MPP分布式資料倉庫技術等，現就職于華為加拿大研究所，全程參與了RDS for MySQL以及GaussDB(for MySQL)的研發作業，熟悉GaussDB(for MySQL) 全堆疊技術，負責NDP的總體架構設計和實作，并成功落地上線，擁有多項技術發明專利，并co-author了SIGMOD 2020 Taurus( GaussDB(for MySQL)) Paper，目前專注于下一代云資料庫智能優化器的研究，

1. 背景介紹

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優化器選擇哈希連接，

2. 問題描述

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

問題查詢：

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%”)，可以在內哈希連接之前被應用，提前過濾掉無效的行，

3. 原因分析

這個問題發生的場景是，一個陳述句使用了外連接，而這個外連接選用哈希連接來完成，并且這個外連接的一部分涉及到了多于一張表，當這些條件同時具備，就可能會引發此問題，

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

4. 如何修復

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

以下是在MySQL 8.0.26之上應用修復后的執行計劃，partupp和part之間的內哈希連接現在有一個連接謂詞：partupp.PS_PARTKEY=part.P_KEY，另外，本地謂詞（part.P_NAMElike“%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也一直重視開源社區的發展，積極對社區版本進行優化和改進，為社區做貢獻，本次MySQLVolcano模型迭代器的謂詞位置優化，助力MySQL查詢性能提升千倍，正是華為云GaussDB 對社區發展的積極反饋，

另外，告訴大家一個好訊息，華為云資料庫專場活動正在進行，云資料庫MySQL包年19.9元起，助力企業無憂上云，更多活動詳情戳閱讀原文，

參考資料：

[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.
[7] "TPC-H Homepage," [Online]. Available: http://www.tpc.org/tpch/.

本文由華為云發布，

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/448147.html

標籤：其他

上一篇：WebGPU 計算管線、計算著色器（通用計算）入門案例：2D 物理模擬

下一篇：一起來打靶 02