導讀
數據庫的查詢優化器是整個系統的"大腦"，一條SQL語句執行是否高效在不同的優化決策下可能會產生幾個數量級的性能差異，因此優化器也是數據庫系統中最為核心的組件和競爭力之一。阿里雲瑤池旗下的雲原生數據庫PolarDB MySQL版作為領先的雲原生數據庫，希望能夠應對廣泛用户場景、承接各類用户負載，助力企業數據業務持續在線、數據價值不斷放大，因此對優化器能力的打磨是必須要做的工作之一。
本系列將從PolarDB for MySQL的查詢變換能力開始，介紹我嗎，們在這個優化器方向上逐步積累的一些工作。
本篇為「PolarDB優化器查詢變換」系列第四篇，前三篇內容分別解讀了：
1.Join消除
2.窗口函數
3.Join條件下推
引言
PolarDB MySQL作為一款HTAP數據庫，在複雜SQL查詢優化能力上做了很多深入工作。早期用户SQL都非常簡單，MySQL單機能力也有限。隨着業務數據越來越多，業務場景越來越複雜，迫切需要越來越強大的數據庫來滿足統計、報表需求。
PolarDB在並行能力、查詢變換能力、優化器等方面都做了非常深入的工作，這些工作有一個總目標：讓用户的複雜查詢執行得越來越快。本篇文章將對PolarDB的IN-List變換進行深入闡述，從而讓我們對PolarDB的查詢改寫能力有更感性的認知。下面是一個常見的慢SQL：in函數運算，裏面的常量比較多。
select sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem
where
l_partkey in (
9628136,19958441,10528766,.......); #in list裏面有上千個常量值。SQL語句是常見的單表過濾查詢，然後進行agg彙總，實際執行耗時比較長，執行比較慢的原因是IN-List裏面有上千個常量值。
原生MySQL
原生的MySQL執行計劃如下：
+---------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------+
| -> Aggregate: sum(lineitem.L_EXTENDEDPRICE)
-> Filter: (lineitem.L_PARTKEY in (9628136,19958441,10528766,....) (cost=60858714.81 rows=297355930)
-> Table scan on lineitem (cost=60858714.81 rows=594711859)
|
+---------------------------------------------------------------------------------------------------+執行過程是線性scan lineitem 5.9億條數據，逐條去判斷是不是在IN-List裏面，這個算子是Item_func_in，in集合元素個數比較多，我們使用10W常量值進行測試，這個算子做求值運算耗時較長，整體完成需要 375s。
具體看下Item_func_in代碼執行邏輯：
- 判斷是否可以二分查找，如可以二分查找，將IN-List轉成有序數組；
- 如果產生了有序數組，則執行時優先嚐試二分查找；
- 否則，線性scan，逐一判斷左表達式是否等於IN-List裏面的item。
可以看到求值邏輯已經是教優的了，這個算子基本沒有優化空間了。主要是外層循環次數太多，如果能減少外層的大loop，那麼就能降低延時。
PolarDB
PolarDB解決問題的思路是對該SQL做查詢變換， 把IN-List轉變成一張物化表，加入join list，具體變換過程如下：
Step 1：轉成in子查詢，上述SQL改寫為
select ... from lineitem where l_partkey in (...)
====>
select ... from lineitem where l_partkey in
(select dt._col_1 from (values (9628136),(19958441),...) dt)Step 2：SubQuery Unnest-消除子查詢子查詢
已經是非相關的，通過SU技術，可以消除子查詢，轉化為semi-join。物化表經過去重，並且Join列非空，進而可以轉化為inner-join。
SQL將繼續改寫為：
====>
select ... from lineitem, (values (9628136),(19958441),...) dt) where l_partkey = dt._col_1通過這種變換能到得如下好處：
- 不用逐條去做filter，因為MySQL執行器是火山模型，增加了一個filter算子就增加了一層虛函數調用；
- Join有join buffer，可以一個batch一個batch參與Join，這是轉成join list的一個好處；
- 轉成join list，join的優化非常多，如join order&access path，總能選到更優plan。
最後執行的plan如下：
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: sum(lineitem.L_EXTENDEDPRICE)
-> Nested loop inner join
-> Table scan on dt
-> Materialize with deduplication
-> scan on in-list: 100000 rows
-> Index lookup on lineitem using LINEITEM_FK2 (L_PARTKEY=dt._col_1), with index condition: (lineitem.L_PARTKEY = dt._col_1) (cost=7.34 rows=29)物化表數據量少，作為外表，inner-join成功使用lineitem索引，只要掃10萬條物化表記錄，然後再使用LINEITEM_FK2索引進行連接，整條SQL執行下來只需要32s。
測試效果
PolarDB IN-List優化後在 TPCH 100G 數據集上比原生方式提升11.5倍，又因為PolarDB支持並行查詢，32並行度模式下提升上百倍。
總結
原理上，PolarDB做完IN-List轉換為Join-List後，能得到如下兩方面的提升：
- IN-List裏面的常量都經過物化去重，基數可能會有不小的下降，這取決於重複值；
- IN-List消去，變成了一張物化表，參與Join-List後，有更多access path選擇，比如選擇更好的index，更多的Join方式：hash join還是nest loop join。
細微之處見真功夫，做IN-List轉換還要完成其他工作，如需要適配prepare statement協議、適配並行查詢協議等，PolarDB在雲數據庫市場能做到特性遙遙領先，離不開背後工程師們堅持客户價值第一的初心，後續我們將介紹更多查詢改寫相關內容，敬請期待。
