[[211727]]

一、案例

好天氣，壞SQL

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

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

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

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

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

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

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

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

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

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

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

笨方法，好效果

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

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

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

1、5個(gè)exists條件單獨(dú)作用時(shí)，沒有性能問題；

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

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

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

神器不出，莫與爭鋒

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

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

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

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

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

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

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

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

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

撥開云霧不等于立見天日

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

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

回歸本源，方得圓滿

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

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

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

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

不見兔子不撒鷹

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

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

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

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

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

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

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

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

二、心得

從此愛上SQL Monitor

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

感謝SQL Monitor！

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

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

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

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