SQL優化三板斧：精簡之道、驅動為王、集合為本

8月3日，一同事轉來一個SQL，我打開文件，發現整個代碼多達347行。

在DB中執行，時耗達到了4分多鐘，再往下鉆取，如同蝸牛一般，根本鉆不動，14分鐘過去了，還只鉆取到了800行。

由此該SQL的性能表現為“兩慢”： 首條返回慢、下鉆提取慢。 大多數情況，我們只會遇其一，要么快速返回出現性能瓶頸，要么全部提取出現性能瓶頸。這回好了，都齊全了。透過窗戶，望著被“妮妲”肆意狂虐后葉顫枝亂的樹木，心里不禁在想： 服務器也被“妮妲”肆虐了？

此時，臺風“妮妲”瘋狂過后的溫馨涼意，也沒能讓我心如止水，畢竟這個優化任務看起來有些棘手。

人生若只如初見

因為來者不善，而時間寬限，我也計劃打持久戰。在展開分析前，我對SQL中的表對象和數據量做了初步統計。如下：

人生若只如初見，初見往往是美妙的，讓人心曠神怡的。而與該SQL的初次交流，畫面卻是暗潮涌動殺機四伏：

1. 動輒 千萬上億 的數據量， 近40次 對象訪問，還不包括VIEW中的表對象。

2. 從SQL代碼上看，出現了 聚合函數 ，因此可以斷定是 批量數據處理。

以上兩點，按經驗，能2分鐘跑出來就不錯了，現在是要求2~3S，看起來是一個不可完成的任務。

第一板斧：大刀闊斧

在初步分析中，ORDER_RELEASE和ORDER_RELEASE_REFNUM兩個表是最搶眼的，數據量分別是千萬級和億級，訪問次數更是驚人的達到了10次以上。好奇心我決定以這兩個表為切入口，探究下是如何被訪問的？

借助于NOTEPAD++編輯神器，很快定位到了這兩個表的訪問情況：

初步一看：

1. 這兩個表的訪問基本上都是在 子查詢 中，而且都是成對出現

2. 仔細對比了子查詢后，發現這些子查詢可分A、B兩類

3. A類子查詢共有 5個的代碼都是完全一樣的 ，如下：

4、B類子查詢共有 3個的代碼都是完全一樣的 ，如下：

深入子查詢內部，無論是A類子查詢還是B類子查詢，ORDER_RELEASE R和ORDER_RELEASE_REFNUM O_REF的關聯方式都是一樣的，關聯字段是ORDER_RELEASE_GID。此時，結合兩個表的命名，按多年的經驗，我猜想：

1. ORDER_RELEASE_GID為ORDER_RELEASE表的 主鍵字段

2. ORDER_RELEASE_REFNUM與ORDER_RELEASE表存在 主外鍵約束 ，字段就是ORDER_RELEASE_GID

為了驗證我的假設，我VIEW了ORDER_RELEASE_REFNUM的表結構，如下：

果真如此。那么問題來了，即便如此，我們又能做什么呢？答案很簡單，這兩類子查詢中， ORDER_RELEASE表可以被“砍掉” 。等價的SQL如下：

A類：

B類：

再看看這個子查詢的數據量：

只有 8千多條 ，相對于千萬上億，已經是非常少的數據量了。

結合上述分析結果，我對SQL做了如下調整：

1. 將A、B類子查詢用兩個 with子查詢 代替，這樣就能 減少大表的訪問次數；

2. 在A、B類子查詢中， 將ORDER_RELEASE表“砍掉” ，減少表關聯帶來的IO開銷；

3. 由于子查詢的數據量非常小，將之前的IN子查詢改寫為INNER JOIN，這樣就可以形成小結果集驅動大表的效果。

調整后的代碼如下：

對于這次的優化，我并沒有抱什么希望，因為這僅僅是常規性的精簡，還沒有深入到代碼內部。或者說， 這還僅僅是規范性改寫。

果真，執行仍然需要 耗時4分多鐘 ，但是，這次的精簡并不是沒有任何收益。因為當往下鉆取時，速度非常快， 鉆取完6625條記錄不到10S。

不知不覺中，已到了下班時間。臺風過后，殘陽西下，不再燥熱，難違暖意，別有一番韻味。

第二板斧：披荊斬棘

第二天一上班，就開始接著昨天的節奏繼續優化。

SQL的精簡并沒有為快速返回帶來任何收益，我決定看下執行計劃，嘗試著從執行計劃中得到更多的信息。果真，F5后看到的執行計劃中，一個 VIEW的COST猶如“鶴立雞群” ，特別的扎眼：

從執行計劃看，Oracle對這個視圖做了傳統的處理，沒有合并，也沒有謂詞推入。所以視圖中的表基本上都是table access full。此時，突然想起在當時統計表對象的時候，記得只有一個視圖，而在昨天在精簡B類子查詢的時候，也出現過一個視圖。那這兩個視圖應該是同一個了。而昨天B類子查詢的速度是非常快的。

我趕緊將執行計劃定位到了B類子查詢，如下：

原來如此，在B類子查詢中，該視圖被 merge 了。

受此啟發，我也計劃將主查詢中的VIEW通過HINT進行MERGE，但是 HINT似乎并不生效 ，始終都無法改變現有的執行計劃。無奈之際，只有深入SQL，實地窺探這個VIEW到底“何德何能”，會讓ORACLE優化器如此“死心塌地”的“維持原判”。

從上圖中可以看出，該視圖與A類子查詢進行了關聯，而事實上，B類子查詢就是該視圖與A類子查詢關聯的結果呀。怎么在這里又要 臨時關聯 呢？難道昨天做精簡的時候還存在 漏網之魚 ？

再看代碼：

原來這里需要獲取該視圖的兩個字段，而在B類子查詢中，我們只獲取了SHIPMENT_GID一個字段。那 是否可以直接在B類子查詢中加一個字段 呢？

我們再來看看B類子查詢的代碼邏輯：

在這里，我們獲取了SHIPMENT_GID字段，并對該字段通過DISTINCT去除了 重復值 。這樣做的目的在于，在后面調用該子查詢時，以該子查詢為驅動表，驅動關聯其他表對象。因為子查詢的結果集很小，而被關聯的表對象都是千萬上億級別的。

很顯然，如果我們在B類子查詢中增加ORDER_RELEASE_GID字段，就會影響到SHIPMENT_GID的 唯一性 ，這樣，在后續的關聯查詢中，就不能直接用B類子查詢驅動關聯。這會直接破壞掉已經建立好的驅動關系。

既然增加字段之路行不通，那就嘗試著再 增加一個WITH子查詢 ，代碼如下：

與此同時，對訪問該視圖的代碼也進行了適應性的修改，修改后的腳本如下：

再次執行，耗時2:28，雖然與秒級的性能要求相距甚遠，但是至少性能提升了近50%，其意義并在于提升的效果，而在于證明了優化方向是正確的，即在 大表 林立群狼環視虎視眈眈的環境中，要快速準確的 定位出驅動表，需要明確將驅動表數據準備好。

第三板斧：神工鬼斧

性能尚未達標，優化仍需繼續。

先看看執行計劃：

從COST列，并沒有看到成本特別高的操作。所以，我放棄了繼續在執行計劃上做文章，轉而 深入分析SQL代碼邏輯。

經過一番抽絲剝繭起承轉合后，SQL的整體代碼邏輯也呼之欲出，發現頂層的邏輯設計非常簡單明了，就是三個 子查詢的結果集內連接 ，如下圖所示：

接下來，我做了一件被人“鄙視”的小兒科的事，就是 分別執行了這三個子查詢。 原本想著總會有一個慢的，我就重點優化慢的那個子查詢。而結果卻出人意表， 三個子查詢都是在2S左右 就能完成執行，而且數據量都在1萬以內。那為何三個子查詢 關聯 在一起， 性能會如此受影響呢？ 要知道，如果是三個1萬以內的表關聯，即便是無任何索引，那也是秒出呀。

那么問題出在哪里呢？沒的說，肯定是執行計劃并沒有按我們預想的去執行這個SQL。此時，我也沒有心思去仔細分析執行計劃，而是直接祭出了第三板斧 通過with子查詢的方式將ORDER_REL、SHP、REL三個子查詢封裝成結果集 ，改寫后的SQL如下：

再看執行計劃：

看起來與我們預期的效果一致了，而關鍵還是要看執行的效率。

3.5S，再往下鉆取，也不到10s皇天不負有心人，終于可以畫“句號”了。此時，已經是第三天上午，距離拿到原始SQL將近2天的時間了。臺風“妮妲”早已銷聲匿跡，來也匆匆去也匆匆。你方唱罷我登場，立秋前的燒烤模式再次以勝利者的姿態，歇斯底里的“蒸烤”著這片大地。而躲在空調房的人類，也在盡情的透支著地球賜予的有限資源，最終會如同這個SQL一樣，終有一天會引發災難；而再去治理，再去挽救，需要花費更多的資源與精力。

后記

從4分鐘到3.5S，從鉆取卡頓到一瀉千里，整整經歷了近2天時間，耗時之長在以往的優化案例中實屬少見。事實上，當一開始拿到這個SQL時，尤其是在了解到這個SQL及背后的數據環境時，我心里面是直打鼓的。可以說，是硬著頭皮拿下了這個SQL，現在回想起來仍然后怕。然而，除了后怕，更多的是該案例優化過程中所體現出的SQL（優化）精髓： 精簡之道、驅動為王、集合為本。

精簡之道

大道至簡、簡單即高效、復雜的事情簡單化等等這些我們喜聞樂見的生活常識，同樣適用于SQL（優化）。記得SQL優化大師曾說過： 不要讓ORACLE做多余的事 。而對于ORACLE而言，多余的事情是什么呢？多余的表關聯、重復的表訪問、冗余的關聯（過濾）條件、不必要的DISTINCT\ORDER BY\GROUP BY、曲折的訪問路徑。雖然ORACLE優化器引擎也在努力識別并消除這些“多余的事 ”（可參見博客，然而，在面對復雜的SQL時，ORACLE也往往束手無策。因此， SQL優化的首要之事就是精簡SQL。

驅動為王

有這樣一句話：一頭獅子領著一群羊，要勝過一頭羊領著一群獅子。這就道出了“領頭”的重要性，在ORACLE優化器中，就是 “驅動表” 。驅動表的意義有如木楔子，只有薄如紙片銳如刀刃的楔子，才能輕而易舉的插入堅硬木樁中。如果給你一個圓頭的木頭，任憑你力氣再大，也不能插入。這就要求驅動表的數據量要足夠的少。盡管ORACLE優化器也在努力尋找合適的“領頭”，而有的時候，ORACLE優化器 會被腰里別了桿槍的老鼠給騙了 。比如本案例中的A類子查詢，起初是通過IN子查詢進行過濾的，這就存在很大的性能風險。關于驅動表的優化案例有很多，后續會專題分享。

集合為本

集合操作是二維關系數據庫引擎在數據處理時的根本 ，單表是一個集合，多表關聯后的結果也是一個集合，視圖、子查詢的返回結果還是一個集合，整個SQL執行完后的結果仍然是一個集合。

因此， 一個高效的SQL一定有一個合理的集合運算結構。 根據業務需求，結合代碼邏輯，有的時候需要將代碼片通過子查詢封裝；而有的時候又需要將子查詢合并到主查詢中；有的時候需要將大集合根據業務邏輯切片成多個小的集合；有的時候又需要將若干個小的集合預先合并成大集合。總之，在進行SQL（優化）時，一定要有集合的概念，用集合的思維指導SQL（優化）。

 

來自：http://dbaplus.cn/news-21-805-1.html