導(dǎo)讀

數(shù)據(jù)庫(kù)的查詢優(yōu)化器是整個(gè)系統(tǒng)的"大腦"，一條SQL語(yǔ)句執(zhí)行是否高效在不同的優(yōu)化決策下可能會(huì)產(chǎn)生幾個(gè)數(shù)量級(jí)的性能差異，因此優(yōu)化器也是數(shù)據(jù)庫(kù)系統(tǒng)中最為核心的組件和競(jìng)爭(zhēng)力之一。阿里云瑤池旗下的云原生數(shù)據(jù)庫(kù)PolarDB MySQL版作為領(lǐng)先的云原生數(shù)據(jù)庫(kù)，希望能夠應(yīng)對(duì)廣泛用戶場(chǎng)景、承接各類用戶負(fù)載，助力企業(yè)數(shù)據(jù)業(yè)務(wù)持續(xù)在線、數(shù)據(jù)價(jià)值不斷放大，因此對(duì)優(yōu)化器能力的打磨是必須要做的工作之一。

本系列將從PolarDB for MySQL的查詢變換能力開始，介紹我們?cè)谶@個(gè)優(yōu)化器方向上逐步積累的一些工作。

引言

PolarDB MySQL作為一款HTAP數(shù)據(jù)庫(kù)，在復(fù)雜SQL查詢優(yōu)化能力上做了很多深入工作。早期用戶SQL都非常簡(jiǎn)單，MySQL單機(jī)能力也有限。隨著業(yè)務(wù)數(shù)據(jù)越來(lái)越多，業(yè)務(wù)場(chǎng)景越來(lái)越復(fù)雜，迫切需要越來(lái)越強(qiáng)大的數(shù)據(jù)庫(kù)來(lái)滿足統(tǒng)計(jì)、報(bào)表需求。

PolarDB在并行能力、查詢變換能力、優(yōu)化器等方面都做了非常深入的工作，這些工作有一個(gè)總目標(biāo)：讓用戶的復(fù)雜查詢執(zhí)行得越來(lái)越快。本篇文章將對(duì)PolarDB的IN-List變換進(jìn)行深入闡述，從而讓我們對(duì)PolarDB的查詢改寫能力有更感性的認(rèn)知。下面是一個(gè)常見的慢SQL：in函數(shù)運(yùn)算，里面的常量比較多。

SQL語(yǔ)句是常見的單表過濾查詢，然后進(jìn)行agg匯總，實(shí)際執(zhí)行耗時(shí)比較長(zhǎng)，執(zhí)行比較慢的原因是IN-List里面有上千個(gè)常量值。

原生MySQL

原生的MySQL執(zhí)行計(jì)劃如下：

執(zhí)行過程是線性scan lineitem 5.9億條數(shù)據(jù)，逐條去判斷是不是在IN-List里面，這個(gè)算子是Item_func_in，in集合元素個(gè)數(shù)比較多，我們使用10W常量值進(jìn)行測(cè)試，這個(gè)算子做求值運(yùn)算耗時(shí)較長(zhǎng)，整體完成需要 375s。

具體看下Item_func_in代碼執(zhí)行邏輯：

●?判斷是否可以二分查找，如可以二分查找，將IN-List轉(zhuǎn)成有序數(shù)組；

●?如果產(chǎn)生了有序數(shù)組，則執(zhí)行時(shí)優(yōu)先嘗試二分查找；

●?否則，線性scan，逐一判斷左表達(dá)式是否等于IN-List里面的item。

可以看到求值邏輯已經(jīng)是教優(yōu)的了，這個(gè)算子基本沒有優(yōu)化空間了。主要是外層循環(huán)次數(shù)太多，如果能減少外層的大loop，那么就能降低延時(shí)。

PolarDB

PolarDB解決問題的思路是對(duì)該SQL做查詢變換， 把IN-List轉(zhuǎn)變成一張物化表，加入join list，具體變換過程如下：

Step 1：轉(zhuǎn)成in子查詢，上述SQL改寫為

Step 2：SubQuery Unnest-消除子查詢

子查詢已經(jīng)是非相關(guān)的，通過SU技術(shù)，可以消除子查詢，轉(zhuǎn)化為semi-join。物化表經(jīng)過去重，并且Join列非空，進(jìn)而可以轉(zhuǎn)化為inner-join。

SQL將繼續(xù)改寫為：

通過這種變換能到得如下好處：

●?不用逐條去做filter，因?yàn)镸ySQL執(zhí)行器是火山模型，增加了一個(gè)filter算子就增加了一層虛函數(shù)調(diào)用；

●?Join有join buffer，可以一個(gè)batch一個(gè)batch參與Join，這是轉(zhuǎn)成join list的一個(gè)好處；

●?轉(zhuǎn)成join list，join的優(yōu)化非常多，如join order&access path，總能選到更優(yōu)plan。

最后執(zhí)行的plan如下：

物化表數(shù)據(jù)量少，作為外表，inner-join成功使用lineitem索引，只要掃10萬(wàn)條物化表記錄，然后再使用LINEITEM_FK2索引進(jìn)行連接，整條SQL執(zhí)行下來(lái)只需要32s。

測(cè)試效果

PolarDB IN-List優(yōu)化后在 TPCH 100G 數(shù)據(jù)集上比原生方式提升11.5倍，又因?yàn)镻olarDB支持并行查詢，32并行度模式下提升上百倍。

總結(jié)

原理上，PolarDB做完IN-List轉(zhuǎn)換為Join-List后，能得到如下兩方面的提升：

●?IN-List里面的常量都經(jīng)過物化去重，基數(shù)可能會(huì)有不小的下降，這取決于重復(fù)值；

●?IN-List消去，變成了一張物化表，參與Join-List后，有更多access path選擇，比如選擇更好的index，更多的Join方式：hash join還是nest loop join。

細(xì)微之處見真功夫，做IN-List轉(zhuǎn)換還要完成其他工作，如需要適配prepare statement協(xié)議、適配并行查詢協(xié)議等，PolarDB在云數(shù)據(jù)庫(kù)市場(chǎng)能做到特性遙遙領(lǐng)先，離不開背后工程師們堅(jiān)持客戶價(jià)值第一的初心，后續(xù)我們將介紹更多查詢改寫相關(guān)內(nèi)容，敬請(qǐng)期待。