[[211727]]

一、案例

好天氣，壞SQL

金秋10月，如同陽春三月般，是一個令人難以忘懷與期待的季節(jié)。而在這個美好的季節(jié)了，我拿到了一個不怎么令人愉悅的SQL。

優(yōu)化小組的測試MM給我發(fā)了封關(guān)于性能問題的郵件，在郵件里面，問題描述是這樣的：權(quán)限配置越少，性能越差，當(dāng)配置了全部（2萬）du的權(quán)限時，只需要2s，當(dāng)只配置了120個DU的權(quán)限時，需要半小時以上。

看到這個描述，我心里也咯噔了一下：這是違背了常理的。一般來說，只有越大越慢，現(xiàn)在反而是越小越慢。

按照習(xí)慣，我還是想先見識見識這個一反常理的SQL，看看到底是何方神圣。我找開發(fā)人員拿到了SQL，打開代碼如下：     

從體量上看，這個SQL并不大，總共才130多行。這在I項目組中是比較常見的。而從體型上看，似乎不怎么協(xié)調(diào)：尾巴太大。在WHERE條件子句，拖著5個EXISTS條件，并且都是OR的關(guān)系。這已經(jīng)很不尋常了，會不會就是問題中描述的問題所在呢？

我向開發(fā)人員咨詢了這5個EXISTS子查詢的業(yè)務(wù)功能，得到的信息是：

1、這5個EXISTS子查詢的功能是鑒權(quán)，即權(quán)限鑒別；

2、不同EXISTS子查詢代表不同類型的權(quán)限集合；

3、鑒權(quán)的對象粒度是DU，即每個EXISTS子查詢與EXISTS子查詢的關(guān)聯(lián)字段都是LINE.DU_ID

從SQL自身看，找不到明顯的“破綻”，我就嘗試著看看執(zhí)行計劃，在SQL DEV中按下了F5，顯示的執(zhí)行計劃如下：

執(zhí)行計劃比較長，我們可以只看exists部分，發(fā)現(xiàn)基本上都是索引掃描，cost值也非常低，也就是說執(zhí)行計劃中也看不出問題。那問題到底出在哪里呢?

笨方法，好效果

當(dāng)時就在想：是單個exists慢？還是5個放在一起慢呢？

為了弄清楚這個原因，我就逐個注釋掉EXISTS，并觀察注釋后的性能。雖然這個辦法有些笨拙，甚至很多人都不齒于該方法，但有些時候這確實也是定位問題的有效的手段和途徑。

通過反復(fù)注釋加測試，詭異的現(xiàn)象出現(xiàn)了：

1、5個exists條件單獨作用時，沒有性能問題；

2、***個和第三個exists條件聯(lián)合作用時，也沒有性能問題；

3、第三個、第四個和第五個exists條件聯(lián)合作用時，性能問題就凸顯了。

由此看來，問題越來越復(fù)雜了。Oracle在執(zhí)行這條SQL時到底發(fā)生了什么呢？千頭萬緒理還亂，一籌莫展想不通。萬般無奈之下，只能祭出必殺神器：SQL Monitor。

神器不出，莫與爭鋒

在拿到SQL Monitor的結(jié)果后，似乎一切都明朗起來了，SQL Monitor的截圖如下（由于當(dāng)時的原始數(shù)據(jù)丟失，以下僅給出模擬數(shù)據(jù)）：

因為已經(jīng)明確了是在exists子查詢存在性能問題，我就重點關(guān)注了EXISTS的Monitor信息，希望能從中發(fā)現(xiàn)有價值的信息與啟示。

在對比了5個exists的執(zhí)行計劃后，“執(zhí)行次數(shù)”引起了我的注意：5個EXISTS的執(zhí)行次數(shù)及實際行數(shù)竟然不一樣！

（1）這組數(shù)字之間也有著巧妙的聯(lián)系：***個的執(zhí)行次數(shù)為20000，及恰好是總的DU數(shù)量，第二個的執(zhí)行次數(shù)等于***個的執(zhí)行次數(shù)-***個實際行數(shù)，即滿足如下算法：

其中f(n)為第n個exists的執(zhí)行次數(shù)，e(n)為第n個exists的實際返回行數(shù)，并且n>1。

（2）***個和第二個exists的實際返回行數(shù)的和是120，恰恰是郵件中提及的權(quán)限配置數(shù)量；而第三個19880加上120正好等于2萬，又恰恰是全部DU數(shù)。難道這一切僅僅是巧合而已？還是另有隱情呢？

基于以上兩點信息，我豁然開朗恍然大悟，個中緣由了然于胸。我們可以大致推斷出Oracle的執(zhí)行原理如下圖所示：

按照上面的流程與算法，就很容易理解上述那組數(shù)字了。同樣的，也明白了為何權(quán)限配置越少的時候性能越差，配置越多的時候性能反而越好。

為了更好地理解，這里可以舉兩個極端的例子。如果有沒有配置任何權(quán)限，那么每個DU都需要遍歷5個exists子查詢，就意味著總共要執(zhí)行10萬次（2萬DU，每個DU執(zhí)行5次）exists子查詢。反過來，如果我們將2萬DU都配置了權(quán)限，而且是***類權(quán)限（即***個exists的權(quán)限），那么每個DU只需要執(zhí)行***個exists，后面4個exists子查詢不需要執(zhí)行。因此只需要執(zhí)行2萬次。2萬次與10萬次的差別（另外還需要考慮不同exists之間本身性能也是有差異的），對性能的影響還是非常明顯的。

撥開云霧不等于立見天日

籠罩在詭異性能問題上的云霧終于被揭開了，但我卻絲毫沒有欣喜之感。問題的原因雖然已經(jīng)“大白于天下”，但解決方案讓我一籌莫展。

一開始，我嘗試著基于現(xiàn)有SQL通過SQL Hint干預(yù)執(zhí)行計劃，但是性能毫無起色。我又嘗試著改寫這個SQL，將OR EXISTS子查詢改寫成LEFT JOIN，性能問題卻變本加厲。我還嘗試著創(chuàng)建基于該SQL的特定索引，仍舊無濟于事。

回歸本源，方得圓滿

多次嘗試無果，在萬般無奈之下，我又回到了問題的本原。

這個SQL，在本場景中，除了***個exists子查詢執(zhí)行了100次，第二個exists子查詢執(zhí)行了20次，其它四個exists子查詢執(zhí)行的19880*4次都是沒有意義的。既然沒有意義，那是否可以省略掉呢？我很為自己這個天馬行空不著邊際的想法振奮。

因為就如開始測試時，將后面三個exists注釋掉后，性能非常好。也就是說如果能成功避開無用的EXISTS子查詢，也是可以達(dá)到性能優(yōu)化之目標(biāo)的。

但很顯然，Oracle在執(zhí)行SQL前，是無法識別哪些EXISTS子查詢是必須執(zhí)行的？哪些EXISTS子查詢是無須執(zhí)行的？難道自己的這個想法就這樣夭折了嗎？

不見兔子不撒鷹

我繼續(xù)著自己天馬行空的想法。

既然Oracle在執(zhí)行SQL的時候未卜先知，那么我們在寫這個SQL時，是否可以先卜上一卦，如果某類權(quán)限沒有配置，就不在SQL中拼湊對應(yīng)的EXISTS子查詢。這樣，本案例SQL就會只剩下兩個EXISTS子查詢了。性能也自然能得到滿足。

以上想法僅僅是我一廂情愿的理想主義，其在實際應(yīng)用中是否可行還是未知之?dāng)?shù)：這個SQL在Java代碼中是固定的還是拼湊的？某類權(quán)限是否配置的判斷是否復(fù)雜？是否也會存在性能問題？如此等等，不寒而栗。但就如小馬過河，不去嘗試又怎么知道是否真實可行呢？

于是，我?guī)е@個不太正經(jīng)的方案與開發(fā)人員溝通。開發(fā)人員的表現(xiàn)讓人喜憂參半。喜的是，他并不反對這個方案，如果真的能解決性能問題，他也是樂于接受該方案；憂的是，這段5個exists子查詢的SQL并不是他控制的。原來該案例的SQL所在的系統(tǒng)模塊是任務(wù)管理，而5個EXISTS子查詢是鑒權(quán)功能，隸屬于權(quán)限模塊。這些EXISTS子查詢都是由權(quán)限模塊來開發(fā)和維護的。用任務(wù)管理模塊開發(fā)人員的話說就是“這5個EXISTS是通過調(diào)用權(quán)限模塊的服務(wù)獲取的，如果權(quán)限模塊給我們3個EXISTS，我們就拼湊三個EXISTS子查詢，如果他們不給我們EXISTS，我就不拼湊EXISTS子查詢。”

于是，我?guī)е@個方案又去“游說”權(quán)限模塊的開發(fā)人員。

當(dāng)我找到權(quán)限模塊的開發(fā)人員時，我們并沒有直接拖出我的方案，而是把性能問題表述了一邊。意想不到的是，這位開發(fā)人員很是淡定，好像這一切早就知道了；卻也滿臉的無奈，他說：“這個性能問題還是暴露出來了，沒有辦法，當(dāng)初權(quán)限這塊的設(shè)置就是這么復(fù)雜，我們也不想如此復(fù)雜。”

見時機成熟，我就把我的方案全盤托出。沒想到，這位開發(fā)人員聽完后，兩眼大放異彩，一臉容光煥發(fā)，說到：“這很好，非常不錯，我現(xiàn)在就按照你的方案改寫。這不單單是你的這個SQL有問題，其它所有涉及到鑒權(quán)的SQL都會有這個問題。”

接下來，一切都水到渠成了。

二、心得

從此愛上SQL Monitor

該案例的優(yōu)化過程甚為曲折，幾近山窮水盡半途而廢。在為幾個exists弄得焦頭爛額一籌莫展之際，幸得SQL Monitor之助，方能撥開云霧，終見青天。從explain plan中，我們能得知Oracle優(yōu)化器的意圖，而通過SQL Monitor，我們能獲取到運行時的很多信息，比如本案例中涉及到的“實際返回行數(shù)”、“執(zhí)行次數(shù)”。這一些對我們定位問題及原因分析非常有用。

感謝SQL Monitor！

頭疼醫(yī)頭，腳疼醫(yī)腳

該案例對應(yīng)的BUG單很快被關(guān)閉，但作為優(yōu)化方案的設(shè)計者，我非常清楚這個方案的局限性和漏洞。沒錯，針對該案例，“不見兔子不撒鷹”式的方案的確能***，但也僅僅是適用于該案例的業(yè)務(wù)場景。該方案還存在一個致命的缺陷：隨著配置的權(quán)限類型越多，其對整個SQL的性能影響越大。我們將權(quán)限配置對SQL的性能影響設(shè)為P，則P的計算公式為：

由公式可見，當(dāng)N=0時，是沒有影響的，而當(dāng)N=5時，影響是***的。

事后，我將這種隱患口頭上與組長交流過，但組長也是無奈：“我也認(rèn)真研究過I項目的權(quán)限機制，發(fā)現(xiàn)存在一定的不合理的地方，要不然也不至于寫出如此復(fù)雜的鑒權(quán)語句。但是，目前來看，不可能將權(quán)限機制推倒重來。先就這樣子吧。”