作者介紹
黃浩，現任職于中國惠普，從業十年，始終專注于sql。十年一劍，十年磨砺。3年通信行業，寫就近3萬條sql；5年制造行業，遨遊在etl的浪潮；2年性能優化，厚積薄發自成一家。
1 濤聲依舊，心有餘悸
就在距離上次視圖優化一個星期的時間，一封“紅色”的郵件中的“sql優化”項格外醒目：
唯一值得慶幸的是，該性能問題被放在8月版本計劃裡面：時間還是蠻充裕的。于是我按照自己的節奏展開分析。
我找到開發責任人，拿到了sql：
初一看，122行，不算長，也不短。但是我對sql中出現的視圖對象bas_project_all_v産生了很大的興趣。這個視圖剛剛完成優化，怎麼就又出現了性能問題了呢？看了下執行計劃：
自上而下，體型雖然婀娜妙曼，但是“妙曼”得有些讓人眩暈。這都要歸功于視圖對象bas_project_all_v，該視圖有5個union all，而在該sql中，又被通路了3次。記得上次案例是由于謂詞沒有被推入引發的，而看執行計劃，視圖被通路三次，都沒有做謂詞推入，我就試着強制謂詞推入看看效果，但是即便是謂詞推入，問題依舊。因臨近下班，也就沒有深入分析，計劃第二天再看看，反正離8月版本還有3周的時間。
2 友誼小船，說翻就翻
第二天一過來，因為有來自其他同僚的性能問題，我暫時将昨天的性能問題擱在一旁。大概在11點鐘的時候，托盤上的espace彈出了消息，發消息的是昨天提供sql的同僚，内容是：昨天的那個性能問題必須要在今天内完成優化。這個消息着實把我“震”住了，不是說好的是8月版本嗎？友誼的小船咋說翻就翻呢？
原來，同樣的性能問題在生産環境也出現了，而且生産使用者直接提了一個bug單，使用者很生氣，後果很嚴重。從測試人員到生産使用者，從uat到生産，從郵件到bug單，事态的嚴重程度已完全超出了當下深圳高溫天氣的黃色預警級别，以至于我立馬放下手頭的工作，頂着烈烈炎日，大汗淋淋的趕往“事發現場”—開發責任人所在odc。
因為越是時間緊迫，溝通就越顯得緊要，何況昨天初步“目測”，一方面執行計劃過于複雜，而代碼邏輯似乎又并不簡單，是以更需要當面溝通。
根據開發人員的描述，這個功能的業務需求很簡單，如下：
根據登入使用者id，擷取該使用者對應的所有項目清單，使用者的項目清單包含兩部分：其一是配置設定至該使用者下的項目清單，其二是該使用者所在區域的所有初始化過的項目，如下圖所示：
在了解到業務需求後，我開始解讀sql，在解讀的過程中不時的向開發人員請教、交流。約莫半小時後，sql的邏輯架構也逐漸明朗起來，如下：
從邏輯示意圖中，我們至少有如下兩個疑問：
子查詢route及t在配置項目清單和區域項目清單中都出現了，是否可以進行合并？
在“使用者區域初始化項目清單”中，表surot_t與其他結果集沒有任何關聯條件，隻有uid_c的過濾條件，這意味着會發生笛卡爾積。
了解完業務邏輯及sql代碼邏輯結構後，我們不能繞過視圖bas_project_all_v，且看該視圖的代碼邏輯：
單從視圖的資料邏輯看，存在以下問題：
表重複通路，圖中黃色底紋和藍色底紋的表對象都是重複通路；
除了t2結果集外，其他結果集都都通路了三個以上的表對象，模式都是一樣的：以主表left join從表。衆所周知，left join在sql中的功能是擷取字段，并沒有過濾資料的作用。從left join的這些表擷取的字段是否被外層的sql通路了呢？如果沒有被通路，那就意味着，就本案例的sql而言，這些left join是多餘的。
3 深入“虎穴”
帶着上述分析後的問題，我深入分析了代碼，确認了如下資訊：
sql中的部分代碼是可以精簡的，比如子查詢route及t隻是為了擷取屬性字段，完全可以在得到了所需的pn_c清單後，再與子查詢route及t關聯擷取。
視圖的代碼也可以精簡，比如在視圖t5結果集中通路bpopt_t表對象擷取的字段，在sql中根本沒有通路，也就是說在t5結果集中完全可以不通路該表對象。
那麼還有個問題，那就是笛卡爾積。而通過分析代碼，發現并非沒有關聯條件，而是将關聯條件寫到了where過濾位置了，如下：
基于該邏輯的複雜性，我決定将該過濾條件改寫成exists子查詢。
4 大刀闊斧
業務需求了解了，大緻的邏輯架構也清楚了，病症病因也定位了，接下來就是該大刀闊斧的進行sql改寫了，改寫的過程就很簡單了，改寫後的sql：
改動點如下：
取消視圖bas_project_all_v，用with 子查詢替代視圖。在with子查詢中，根據需求關聯表對象，砍掉了無關的表對象；
在擷取區域項目清單的代碼裡，将left join where改成exists；
在擷取了所有的項目清單後，再關聯子查詢route和t，擷取項目相關屬性資訊。
修改後的執行計劃如下：
在pl sql dev中執行，2.5s左右，看來sql改寫還是收到了成效，我也長長的舒了口氣，看窗外，已是夕陽西下，一抹餘晖透過玻璃窗投射進來，此刻在呼呼空調室内，絲絲暖意，卻全然忘記早上釋出的高溫黃色預警，雖然已近黃昏，窗外依舊炙熱。
5 分而治之，争分奪秒
第二天，正當我準備整理本次優化案例時，espace彈出了消息，是開發人員發過來的。消息的内容讓我立馬停止了整理。因為開發人員告訴我，2.5s仍然不能滿足需求，需要控制在2s内。此時此刻我的心情有些忐忑：根據經驗，這0.5s的性能提升，其難度遠比從10s優化到3s要大。
為了這0.5s，我又重新審視了昨天的優化方案。可以說昨天的優化方案已經對之前的代碼結構做了很大程度的解構，但是更多的是“精簡”sql。在資料處理流程上還沒有變化，優化前後的資料流程都是先集合再過濾，邏輯圖如下：
再看下執行計劃：
發現在union all六個來源的with子查詢時，産生了31m的io寫操作，資料量達到了163k，而sql最終傳回的結果集不到1000條。此時，我看到了希望的曙光。我将資料處理流程方案做了優化，如下圖所示：
也就是說，将之前合而治之變更成了分而治之，在原理上顯然是等價的。
根據分而治之的方案，改寫後的sql見附件：
sql改寫後，懷着虔誠又激動的心情按下了f8，焦急得恍若停滞。1.8s，不容易呀。這0.7s的提升在動辄幾秒鐘、幾分鐘的優化空間中，如滄海一粟，但此刻卻顯得彌足珍貴。
6 後記
該案例的優化過程其實就是一個sql改寫的過程，而最終演變成了sql重寫。這回到了一個最古老的問題：什麼樣的sql才是好sql？這個問題很難回答，因為這個問題跟哲學問題“什麼樣的人才是好人”是一樣的。然而，我在長期與sql為伴的過程中，從開發到優化，發現一個準則：簡單即高效。這也合乎現在流行的返璞歸真、大道至簡的追求。
簡單，并不是表現在代碼量，而更在于sql代碼結構的簡明、邏輯處理的簡練。所在，在優化過程中，我首先考慮的是sql自身的優化，也就是通常說的等價改寫。我堅信，索引、hint等技術的應用，應該是基于sql已經極緻化的假設。因為無論是索引，還是hint，在紛繁蕪雜、不确定性的環境中，其負面影響也是巨大的。在風起雲湧大浪淘沙時還能浪遏飛舟的，唯有“簡單”的sql。
原文釋出時間為：2016-12-14
本文來自雲栖社群合作夥伴dbaplus