[[211727]]

一、案例

好天氣，壞SQL

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

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

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

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

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

我向開發人員咨詢了這5個EXISTS子查詢的業務功能，得到的信息是：

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

2、不同EXISTS子查詢代表不同類型的權限集合；

3、鑒權的對象粒度是DU，即每個EXISTS子查詢與EXISTS子查詢的關聯字段都是LINE.DU_ID

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

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

笨方法，好效果

當時就在想：是單個exists慢？還是5個放在一起慢呢？

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

通過反復注釋加測試，詭異的現象出現了：

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

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

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

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

神器不出，莫與爭鋒

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

因為已經明確了是在exists子查詢存在性能問題，我就重點關注了EXISTS的Monitor信息，希望能從中發現有價值的信息與啟示。

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

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

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

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

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

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

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

撥開云霧不等于立見天日

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

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

回歸本源，方得圓滿

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

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

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

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

不見兔子不撒鷹

我繼續著自己天馬行空的想法。

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

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

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

于是，我帶著這個方案又去“游說”權限模塊的開發人員。

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

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

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

二、心得

從此愛上SQL Monitor

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

感謝SQL Monitor！

頭疼醫頭，腳疼醫腳

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

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

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