OB小優系列文章 | 聚合類相關子查詢提升（上）

OB君：查詢優化器是關系資料庫系統的核心子產品，也是衡量整個資料庫系統成熟度的“試金石”。OceanBase的查詢優化器曆經了九年多時間的磨練，逐漸提煉出一套獨有的工程實踐哲學。本系列文章将重點介紹聚合類相關子查詢的改寫機制，歡迎探讨～

傳送門：

OB小優系列（一）：OceanBase查詢優化器的設計之道和工程實踐 OB小優系列（二）：OceanBase并行執行引擎實作 OB小優系列（三）：OceanBase查詢改寫的最佳實踐

引言

使用子查詢可以讓使用者簡潔明了地寫出含義清晰的複雜SQL語句。這個功能對使用者而言是非常友好的，但是對資料庫而言是很不友好的。從資料庫角度而言，處理子查詢是相對低效的。為了改進子查詢的處理，資料庫系統通常會嘗試改寫SQL，消除子查詢。業務中常見的子查詢包含以下幾種：

• 非相關的子查詢。這類子查詢的計算完全不依賴主查詢。它可以被獨立的計算。通常，這類查詢直接被提升為主查詢中的一個視圖。
• SPJ (SELECT-PROJECT-JOIN) 類相關子查詢。這類查詢通常被改寫為 SEMI / ANTI JOIN。

SPJ 相關子查詢的處理難度是高于非相關查詢的。而比前者更加複雜的一類查詢是：聚合類相關子查詢。本系列文章重點介紹這類子查詢的改寫機制（簡稱為 JA 改寫）。

場景介紹

S 君開了一家影院，生意紅火。某一天，S 君想進一步改善影院的業績，想要知道哪些場次票價相對偏低。

為此，S 君寫了下面這條查詢。他用一個子查詢統計了一部電影的平均售價，然後找出定價偏低的排片場次有哪些。PLAY表記錄所有電影的排片資訊；TICKETS 表記錄了所有的售票資訊。

Q1:
SELECT * FROM PLAY P WHERE price <
                    (SELECT AVG(price) FROM TICKETS T WHERE T.film = P.film);           

當 S 君執行這條查詢的時候，發現等了一分鐘都沒有獲得結果。萬分焦急的 S 君向經驗豐富的 OB 君求助：為什麼這麼簡單的查詢執行的這麼慢?

OB 君發現這條查詢是一個“聚合類相關子查詢”（簡稱為 JA 子查詢，Join Aggregation）。這類查詢的主要特征是：使用者使用一個相關的子查詢來計算一個統計值，然後利用該統計值來對主查詢的結果進行過濾。

OB 君分析了這兩張表的情況，不禁感歎這家影院業績真是不錯。他向 S 君解釋道：你的影院效益太好，PLAY 表裡排片有 10K+ ，每個排片都要算一次電影的平均售價，影院總共也就上映了100場電影，票卻售出了5M 張，平均每部電影就售出了 50 K 張票，那麼這條查詢邏輯上要通路 10K * 50K = 500 M 才能算出結果，一分鐘怎麼可能算得出結果。X 君請求 OB 君幫個忙改進一下這條查詢。OB 君祭出了一招：JA改寫第一式，寫下了查詢Q2。

Q2:
SELECT * FROM PLAY P,
            (SELECT film, AVG(price) as avg_price FROM TICKETS T GROUP BY film) V
              WHERE P.film = V.film AND P.price < V.avg_price;           

改寫理念

S 君仔細分析 Q1，發現這條查詢是針對PLAY中的每一行，都需要去執行一次TICKETS上的聚合查詢 Q3（其中 ？的取值由P.film決定）。

Q3:
SELECT AVG(price) FROM TICKETS T WHERE T.film = ?;           

在這個場景中， film 的取值數量并不多。根據 film 的取值不同，Q3 實際生成的不同查詢隻有 100 個。但這個 100 個參數不同的查詢卻會被反複執行 10K+ 次。OB 君給的優化方式是：用一個分組查詢提前算出所有影片的平均售價，之後主查詢需要使用不同的統計值時，可以直接從提前計算的結果中獲得。Q2 中的視圖 V 實作了這個效果，它隻需要掃描一遍 TICKETS 表就可以獲得所有電影的平均售價。

Q2 中的視圖 V: SELECT film, AVG(price) as avg_price FROM TICKETS T GROUP BY film;           

之後，Q2隻需要将PLAY和V按照film連接配接，就可以快速找出哪些排片的平均售價偏低了。從 Q1 -> Q2 的改寫是将一個聚合類的相關子查詢改寫成了一次分組（GROUP BY） + 一次連接配接（JOIN）。改寫後的查詢預期需要掃描PLAY表和TICKETS表各一次，總計 5M + 行的記錄；最後執行一次 100 : 10K 的内連接配接。相對于原始查詢 500 M+ 的預期資料通路量，執行效率會有巨大的提升。假如這兩張表上有film字段的索引，那麼還能利用索引加速聚合和連接配接的運算效率。

1. 如果 PLAY 和 TICKETS 在 film 上有索引，我們可以使用 merge aggregation來優化視圖 V 的計算，使用 merge join 來處理 P 與 V 的連接配接。
2. 如果 PLAY 上有 (film, price) 的索引，可以先計算 V 的結果，然後使用 nest loop join 将 P.film = V.film AND P.price < V.avg_price 轉換為 PLAY 上的過濾條件，利用 index scan 大大減少 PLAY 的掃描量。
3. 即便沒有合适的索引，我們依然可以使用 hash join 來計算 PLAY 與 V 的連接配接。

可以看到，改寫後的 SQL 在計劃選擇上有了更大空間。原始的 Q1 查詢中，我們隻能利用主查詢中的 PLAY 來驅動子查詢的計算，本質上是一個 NEST LOOP JOIN 的過程。在改寫後，我們可以采用更多的 JOIN 的算法，甚至可以利用子查詢提升産生的視圖來驅動主查詢中的表進行連接配接。

總結

JA 改寫第一式能夠很有效的提升聚合類子查詢的處理效率。但它并不是總是适用的。通常我們認為它有兩個主要的局限性：

1. 假如T.film = P.film對TICKETS T表有很強的過濾性，但是改寫後的查詢并不能利用這個條件來減少 T 表的掃描量；
2. 相關條件必須是等值條件，如果是T.film != P.film這樣的非等值條件，JA 改寫是不能處理的。

在下一篇文章中，我們會介紹JA改寫第二式，它能夠很好的處理以上這兩個問題。

We are Hiring!

OceanBase 九年如一日，不忘初心，砥砺前行，緻力于實作一個中國人完全自主設計的分布式通用資料庫系統，打破西方大廠在商業資料庫領域的絕對壟斷地位。時至今日，OceanBase 已經成功應用于螞蟻金服的交易、支付、賬務等核心系統和網商銀行、印度Paytm等業務系統。

非常歡迎有志于讓中國的政府和企業用上中國人自己的通用商業資料庫的同學加入我們，一起為實作這一目标而共同努力！發送履歷到 [email protected]，我們等的就是你！

OceanBase 技術交流群

— 想了解更多OceanBase背後的技術秘密？

— 想與螞蟻金服OceanBase的技術專家深入交流？

— 加入OceanBase釘釘互動群：搜尋群号21949783