SQL審核：OR展開與子查詢優化案例詳解

黃廷忠（網名：認真就輸）

雲和恩墨技術專家

個人博客：https://www.htz.pw/

本篇整理內容是黃廷忠在“雲和恩墨大講堂”微信分享中的講解案例，SQL 優化及 SQL審核，是從源頭解決性能問題的根本手段，無論是開發人員還是 DBA，都應當持續深入的學習 SQL 開發技能，從而為解決性能問題打下根基。

第一篇為：性能為王：SQL標量子查詢的優化案例分析

本篇為係列案例之二：OR展開與子查詢優化案例詳解。

本案例 SQL 是15年給一個省電信係統做優化時遇到的。

SQL性能問題診斷

下麵來看看一條 SQL，主查詢使用 OR 與子查詢聯合一起使用，導致子查詢不能展開，隻能走 FILTER，子查詢被輪詢很多次，消耗大量邏輯讀。

這裏需要注意紅色框部分，可以看到括號中存在主查詢列過濾，並且在後麵跟一條子查詢做OR運算。

 

下麵來查看此 SQL 的執行計劃：

執行計劃中，可以看到在謂詞信息部分有多個 FILTER，在執行計劃中有3個 FILTER，但是在 SQL*PLUS 中，隻有兩個 OR，所以需要弄清楚哪些 FILTER 是過濾，哪些 FILTER 是子查詢沒有展開導致的。

 

兩個紅色框的部分就是子查詢沒有展開導致的，箭頭部分隻是用於列過濾的。這裏有一個判斷準則：

1. 當 FILTER 下麵有兩個兒子表（結果集）的時候，此時FILTER就是子查詢沒有展開導致，此時的 FILTER 可以看成是執行完子查詢後的過濾；

2. 當 FILTER 下麵隻有一個兒子表（結果集），此時的 FILTER 是做行過濾的；

 

FIlTER 的原理跟 NL 的原理類型，當驅動表換回一行時，被驅動表執行一次。但是 FILTER 還與 NL 有2點不同的是:

1. 當驅動表返回有重複值時，被驅動不會執行

2. 當被驅動表找到匹配的行時，立即終止本次循環

 

在 SQL 中見到出現 FILTER 時，不能直接說性能不好。那到底怎麼判斷有 FILTER 時，SQL 是的性能是否好呢？其實覺得可以根據主查詢返回行數很少時，使用 FILTER 性能可能很好，主查詢返回的行數很多時，走 FILTER 性能肯定不好。 

基礎信息分析

下麵來看看 v$sql 中的統計信息：

每個字段的值在上麵案列中已經提過，這裏就不再說明了。

 

可以看到平均返回一行，消耗的邏輯讀是2076324：

通過 SQL 每次執行返回的邏輯讀與 SQL 關聯的表占用的大小，可以間接的推斷此 SQL 由於不停的輪詢消耗大量的邏輯讀，可以知道主表返回的行數肯定很多的(這裏還有一種特殊情況就是消耗 TEMP 的情況)。

 

下麵查看一下主表返回的行數：

這裏隻簡單的查詢表 GROUP BY 的值，這裏根據上麵的值估計主表返回的結果集很多，如果要準確的值，可以關聯上麵2張表查詢。

SQL 改寫

現在知道原因了，那麼這個 SQL 優化基本完成70%的工作了，現在就是想辦法來怎麼處理了：讓 SQL 不走 FILTER。

 

由於原來 SQL 就使用 DISTINCT ，所以這裏不需要考慮重複值的情況。根據本 SQL 的特征，將 SQL 修改成如下的:

這裏將 OR 修改成 UNION 的方式。

SQL性能優化效果

修改後的 SQL 的執行計劃：

從執行計劃中，我們已經沒有看到 FILTER 信息了。

 

下麵查看 SQL 執行的統計信息

這裏看到 SQL 執行的邏輯都從原來的2,076,324降到現在的11了。效果很明顯。

總結

本條 SQL 優化是通過改寫 SQL 來完成的，意味著業務需要修改 SQL，可能會出現業務修改完 SQL 再上線，這中間可能會消耗大量的時間，並且如果 SQL 後期出現性能問題，需要再次修改 SQL 的成本也會更高。建議在優化的 SQL 時候，可以通過提示+SQL PROFILE 來固定執行，達到不修改 SQL 的前提下，優化 SQL。這樣業務可以不需要修改代碼，並且後期隨時可以通過修改 SQL PROFILE 中的提示信息來修改 SQL 的執行計劃。

良好的應用性能，必須依賴高度優化的SQL性能，我們推薦用戶通過SQL審核在更前的時段發現和修正問題，從而防患於未然。

文章轉自數據和雲公眾號，原文鏈接