《SQL優化三板斧：精簡之道、驅動為王、集合為本》要點：
本文介紹了SQL優化三板斧：精簡之道、驅動為王、集合為本，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

黃浩,現任職于中國惠普,從業十年,始終專注于SQL.在華為做項目的兩年多,做過大大小小的SQL多達1500個.閑暇之余,喜歡將部分案例寫成博客發表在華為內部數據庫官方社區,反響強烈,已連續四個月蟬聯該社區最佳博主.目前已開設專欄“優哉悠齋”,成為首個受邀社區“專家訪談”的外協人員.

公元2016年8月1日晚上,朋友圈流行著這樣一個段子：特想摸清臺風“妮妲”的威力有多大,一專業人士說：只須一句話就能讓你深刻理解.遂追問,答曰：“就連華為都通知放假了?”感謝“妮妲”,讓深圳這座高速運轉的城市在星期二這天暫停了；感謝華為,讓我這個來深10年,為生活奔波勞頓的人也能倚在窗前,眼觀疾風驟雨之變,心游驚濤駭浪之中.

妮妲走了,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++編輯神器,很快定位到了這兩個表的訪問情況：

初步一看：

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

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

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

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

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

ORDER_RELEASE_GID為ORDER_RELEASE表的主鍵字段

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

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

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

A類：

B類：

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

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

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

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

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

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

調整后的代碼如下：

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

果真,執行仍然需要耗時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\ORDERBY\GROUPBY、曲折的訪問路徑.雖然ORACLE優化器引擎也在努力識別并消除這些“多余的事”(可參見博客,然而,在面對復雜的SQL時,ORACLE也往往束手無策.因此,SQL優化的首要之事就是精簡SQL.

驅動為王

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

集合為本

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

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

文章出處：DBAplu社群(訂閱號ID:dbaplus)

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4389.html