453      阿裏雲  技術社區[雲棲]

從SQL改寫到SQL重寫，什麼樣的SQL才是好SQL？

作者介紹

黃浩，現任職於中國惠普，從業十年，始終專注於SQL。十年一劍，十年磨礪。3年通信行業，寫就近3萬條SQL；5年製造行業，遨遊在ETL的浪潮；2年性能優化，厚積薄發自成一家。

在生活中，很多時候我們會有這樣的體悟：問題要麼不出，一旦出現，會像多諾米骨牌一樣，會連鎖引發諸多相關問題，讓我們疲於應付。SQL優化也是如此，那廂，因一個視圖代碼變更引發的性能事件（參見案例：《SQL Hint都無法解救DB性能時，如何通過視圖曲線救國？》）還恍若昨日，餘塵未落；這廂，與該視圖相關的首頁加載的性能問題又甚囂塵上殺伐四起。

就在距離上次視圖優化一個星期的時間，一封“紅色”的郵件中的“SQL優化”項格外醒目：

唯一值得慶幸的是，該性能問題被放在8月版本計劃裏麵：時間還是蠻充裕的。於是我按照自己的節奏展開分析。

我找到開發責任人，拿到了SQL：

初一看，122行，不算長，也不短。但是我對SQL中出現的視圖對象BAS_PROJECT_ALL_V產生了很大的興趣。這個視圖剛剛完成優化，怎麼就又出現了性能問題了呢？看了下執行計劃：

自上而下，體型雖然婀娜妙曼，但是“妙曼”得有些讓人眩暈。這都要歸功於視圖對象BAS_PROJECT_ALL_V，該視圖有5個union all，而在該SQL中，又被訪問了3次。記得上次案例是由於謂詞沒有被推入引發的，而看執行計劃，視圖被訪問三次，都沒有做謂詞推入，我就試著強製謂詞推入看看效果，但是即便是謂詞推入，問題依舊。因臨近下班，也就沒有深入分析，計劃第二天再看看，反正離8月版本還有3周的時間。

第二天一過來，因為有來自其他同事的性能問題，我暫時將昨天的性能問題擱在一旁。大概在11點鍾的時候，托盤上的espace彈出了消息，發消息的是昨天提供SQL的同事，內容是：昨天的那個性能問題必須要在今天內完成優化。這個消息著實把我“震”住了，不是說好的是8月版本嗎？友誼的小船咋說翻就翻呢？

原來，同樣的性能問題在生產環境也出現了，而且生產用戶直接提了一個BUG單，用戶很生氣，後果很嚴重。從測試人員到生產用戶，從UAT到生產，從郵件到BUG單，事態的嚴重程度已完全超出了當下深圳高溫天氣的黃色預警級別，以至於我立馬放下手頭的工作，頂著烈烈炎日，大汗淋淋的趕往“事發現場”—開發責任人所在ODC。

因為越是時間緊迫，溝通就越顯得緊要，何況昨天初步“目測”，一方麵執行計劃過於複雜，而代碼邏輯似乎又並不簡單，因此更需要當麵溝通。

根據開發人員的描述，這個功能的業務需求很簡單，如下：

根據登錄用戶ID，獲取該用戶對應的所有項目列表，用戶的項目列表包含兩部分：其一是分配至該用戶下的項目列表，其二是該用戶所在區域的所有初始化過的項目，如下圖所示：

在了解到業務需求後，我開始解讀SQL，在解讀的過程中不時的向開發人員請教、交流。約莫半小時後，SQL的邏輯框架也逐漸明朗起來，如下：

從邏輯示意圖中，我們至少有如下兩個疑問：

1. 子查詢route及T在配置項目列表和區域項目列表中都出現了，是否可以進行合並？

2. 在“用戶區域初始化項目列表”中，表SUROT_T與其他結果集沒有任何關聯條件，隻有UID_C的過濾條件，這意味著會發生笛卡爾積。

了解完業務邏輯及SQL代碼邏輯結構後，我們不能繞過視圖BAS_PROJECT_ALL_V，且看該視圖的代碼邏輯：

單從視圖的數據邏輯看，存在以下問題：

1. 表重複訪問，圖中黃色底紋和藍色底紋的表對象都是重複訪問；

2. 除了T2結果集外，其他結果集都都訪問了三個以上的表對象，模式都是一樣的：以主表LEFT JOIN從表。眾所周知，LEFT JOIN在SQL中的功能是獲取字段，並沒有過濾數據的作用。從LEFT JOIN的這些表獲取的字段是否被外層的SQL訪問了呢？如果沒有被訪問，那就意味著，就本案例的SQL而言，這些LEFT JOIN是多餘的。

帶著上述分析後的問題，我深入分析了代碼，確認了如下信息：

1. SQL中的部分代碼是可以精簡的，比如子查詢ROUTE及T隻是為了獲取屬性字段，完全可以在得到了所需的PN_C清單後，再與子查詢ROUTE及T關聯獲取。

2. 視圖的代碼也可以精簡，比如在視圖T5結果集中訪問BPOPT_T表對象獲取的字段，在SQL中根本沒有訪問，也就是說在T5結果集中完全可以不訪問該表對象。

那麼還有個問題，那就是笛卡爾積。而通過分析代碼，發現並非沒有關聯條件，而是將關聯條件寫到了where過濾位置了，如下：

基於該邏輯的複雜性，我決定將該過濾條件改寫成EXISTS子查詢。

業務需求了解了，大致的邏輯框架也清楚了，病症病因也定位了，接下來就是該大刀闊斧的進行SQL改寫了，改寫的過程就很簡單了，改寫後的SQL：

改動點如下：

1. 取消視圖BAS_PROJECT_ALL_V，用WITH 子查詢替代視圖。在with子查詢中，根據需求關聯表對象，砍掉了無關的表對象；

2. 在獲取區域項目列表的代碼裏，將LEFT JOIN WHERE改成EXISTS；

3. 在獲取了所有的項目列表後，再關聯子查詢ROUTE和T，獲取項目相關屬性信息。

修改後的執行計劃如下：

在PL SQL DEV中執行，2.5S左右，看來SQL改寫還是收到了成效，我也長長的舒了口氣，看窗外，已是夕陽西下，一抹餘暉透過玻璃窗投射進來，此刻在唿唿空調室內，絲絲暖意，卻全然忘記早上發布的高溫黃色預警，雖然已近黃昏，窗外依舊炙熱。

第二天，正當我準備整理本次優化案例時，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的提升在動輒幾秒鍾、幾分鍾的優化空間中，如滄海一粟，但此刻卻顯得彌足珍貴。

該案例的優化過程其實就是一個SQL改寫的過程，而最終演變成了SQL重寫。這回到了一個最古老的問題：什麼樣的SQL才是好SQL？這個問題很難回答，因為這個問題跟哲學問題“什麼樣的人才是好人”是一樣的。然而，我在長期與SQL為伴的過程中，從開發到優化，發現一個準則：簡單即高效。這也合乎現在流行的返璞歸真、大道至簡的追求。

簡單，並不是表現在代碼量，而更在於SQL代碼結構的簡明、邏輯處理的簡練。所在，在優化過程中，我首先考慮的是SQL自身的優化，也就是通常說的等價改寫。我堅信，索引、Hint等技術的應用，應該是基於SQL已經極致化的假設。因為無論是索引，還是hint，在紛繁蕪雜、不確定性的環境中，其負麵影響也是巨大的。在風起雲湧大浪淘沙時還能浪遏飛舟的，唯有“簡單”的SQL。

原文發布時間為：2016-12-14

本文來自雲棲社區合作夥伴DBAplus

最後更新：2017-05-11 14:55:01

  上一篇：  一篇文全麵讀懂緩存與隊列

  下一篇：  與MySQL傳統複製相比，GTID有哪些獨特的複製姿勢?