826
一次馬失前蹄的SQL優化：遞歸查詢引發的血案
作者介紹
黃浩：從業十年，始終專注於SQL。十年一劍，十年磨礪。3年通信行業，寫就近3萬條SQL；5年製造行業，遨遊在ETL的浪潮；2年性能優化，厚積薄發自成一家。
在上個案例分享時，有讀者表示“很想知道，作者失敗的時候是怎麼辦？”，並且看熱鬧不嫌事大，要求“來一篇文章唄”。好吧，正所謂，常在河邊走，哪有不濕鞋。本人在SQL優化領域摸爬滾打多年，“接客”無數，難免會遇到些難以伺候的“官人”，本文就跟大家分享一次不成功的優化經曆與教訓。
一個由正則表達式引發的“血案”
話說某天一大早，我收到了一封來自社區的郵件，居然有人在社區提問時@我，在邀請的一眾專家中，居然有我！瞬間，我受寵若驚並誠惶誠恐，要知道，這是我第一次受邀解決問題。
我戰戰兢兢點開郵件中的鏈接，提問的標題是《Oracle正則表達式作為條件執行計劃會改變》。看標題，似乎是正則表達式幹擾了執行計劃，我的第一反應是：是不是因為使用了正則表達式，導致了索引優化器不能走索引掃描？我心存僥幸：如果是這樣，該多好呀。再往下看正文內容：
SQL如下：
SELECT TA.*
FROM TAB_T1 TA
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY L.HDID_C ORDER BY L.LLID_C DESC) RN,
L.HDID_C,
L.URDID_C
FROM ESF.TAB_T2 L) R
ON (R.HDID_C = TA.APLTNN_C AND R.RN = 1)
WHERE TA.ATPY_C =
在實際應用中，會對TA.CACSS_C做過濾，當條件值為常量時，如：AND TA.CACSS_C IN ('10001')，其執行計劃如下：
此時，TAB_T2的訪問方式是通過INDEX_N1 INDEX RANGE SCAN的。
而當過濾值變成一個正則表達式的子查詢時：
SELECT TA.*
FROM TAB_T1 TA
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY L.HDID_C ORDER BY L.LLID_C DESC) RN,
L.HDID_C,
L.URDID_C
FROM ESF.TAB_T2 L) R
ON (R.HDID_C = TA.APLTNN_C AND R.RN = 1)
WHERE TA.ATPY_C = 1
AND TA.CACSS_C IN
(SELECT REGEXP_SUBSTR(CONT, '[^,]+', 1, LEVEL)
FROM (SELECT '10001' CONT FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(CONT, '[^,]+'))
執行計劃如下：
此時，TAB_T2的訪問方式是TABLE ACESS FULL。
要說明的是：在該例中，無論是寫成常量還是正則表達式，其值是一樣的，都是1001。
請教各位專家：為何有了正則表達式，執行計劃就變了呢？
果然是正則表達式幹擾了優化器對索引的選擇。但是仔細一看，又不是那麼回事：
-
我們常見的正則表達式幹擾索引掃描的場景是這樣的：regexp_like(column_name, 'a')。如果我們在column_name字段上創建了索引，該條件自然無法選擇index range scan的訪問方式。
-
而在該提問中，正則表達式REGEXP_SUBSTR的參數並非表的字段，而是一個常量。
-
在沒有正則表達式的時候，INDEX RANGE SCAN的INDEX是INDEX_N1對應的字段是HDID_C，該字段來自TAB_T2，是關聯字段，而非過濾字段。
-
正則表達式是出現在IN子查詢中，而IN表達式的字段為CACSS_C，一方麵，沒有正則表達式情況下使用的索引也非CACSS_C字段對應的索引，另一方麵，該字段並沒有創建索引。
來者不善，善者不來
看來，得要重新審視這個提問，我開始解讀這個SQL的邏輯。該SQL的邏輯示意如下：
因為TAB_T1在經過ATPY_C = 1 AND CACSS_C = 10001過濾後，數據量並不大，而R子查詢存在如下特性：
-
表TAB_T2的數據量非常大；
-
子查詢內部沒有過濾條件；
-
子查詢存在ROW_NUMBER分析函數；
-
TAB_T2是通過HDID_C字段與外部表關聯的，而HDID_C字段上是創建了索引（INDEX_N1）的。
因此，通過HDID_C上的索引INDEX_N1，對子查詢進行謂詞推入（PUSH PRED）是比較理想的。此種場景也是非常適用謂詞推入的（PUSH PRED）。因此，原始的提問應該轉換成：過濾條件變成正則表達式時，Oracle優化器不再選擇謂詞推入（PUSH PRED）。
此時此刻，自然而然，我給提問者提供了如下優化建議：增加SQL HINT/*+ push_pred(r)*/。很快，提問者又回複了我：增加SQL HINT也無濟於事。我有些納悶，同時也見怪不怪，因為push_pred不生效的案例也屢見不鮮（詳見《SQL Hint都無法解救DB性能時，如何通過視圖曲線救國？》）。
但是，我仍然對HINT抱有一絲僥幸心理。我在拿到Oracle數據庫環境信息後，嚐試著其他HINT，比如/*+ index(l, index_n1)*/,/*+ use_nl(ta,l)*/。但是Oracle優化器依舊無動於衷。
當時的我，有如“鬼壓床”，無論自己怎樣用力，就是使不上力來，想大叫也叫不出聲，想睜開眼或翻身起床，卻一動也不能動。拚命掙紮數分鍾後，才終於醒來：HINT之法已然成為了死胡同。
按照慣例，當無法通過SQL HINT之輕撬動性能之重時，我會退一步，通過SQL等價改寫，換取一片海闊天空。
既然子查詢無法謂詞推入，那麼是否可以去子查詢呢？這是一個值得嚐試的想法，因為在過往的案例中，我多次通過去子查詢來實現性能提升。去子查詢R的等價改寫SQL如下：
SELECT *
FROM (SELECT TA.*,
ROW_NUMBER() OVER(PARTITION BY L.HDID_C ORDER BY L.LLID_C DESC) RN
FROM TAB_T1 TA, TAB_T2 L
WHERE HDID_C = TA.APLTNN_C
AND TA.ATPY_C = 1
AND TA.CACSS_C IN
(SELECT REGEXP_SUBSTR(CONT, '[^,]+', 1, LEVEL)
FROM (SELECT '10001' CONT FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(CONT, '[^,]+')))
WHERE RN = 1;
再看執行計劃，果然是“想什麼來什麼”。
我又讀了一遍SQL，心裏在想：
-
TAB_T1在通過ATPY_C = 1 AND CACSS_C = 10001條件過濾後，數據量不會大；
-
然後再與TAB_T2表關聯，此時，由於TAB_T2在關聯條件HDID_C上創建了索引，因此可以采用USE_NL的關聯方式，以提升性能；
-
由於TAB_T2過濾後的數據量小，而TAB_T2的數據量大，這就滿足小表關聯大表的場景要求，此種場景下，USE_NL的性能是可觀的。
風平浪靜之時，卻暗潮湧動
一切似乎都是美好的，突然，我意識到這個方案有一個致命的缺陷，那就是過程數據量。
在SQL中有個ROW_NUMBER的分析函數，要知道，分析函數的性能對數據量是非常敏感的。在原SQL中，ROW_NUMBER分析函數的數據量是TAB_T2表，而在去子查詢的改寫SQL中，ROW_NUMBER分析函數的數據量則是TAB_T1和TAB_T2關聯後的結果集。如果關聯關係是多對多的，那麼關聯產生的過程數據量將是不可預估的，同樣對性能的影響也是災難性的，雖然TAB_T1和TAB_T2的關聯方式為NESTED LOOP。
這樣一想，真是被嚇出了一身冷汗。我立馬找到TAB_T1和TAB_T2的關聯條件：HDID_C = TA.APLTNN_C。這裏，需要確定APLTNN_C的唯一性，如果TAB_T1表的APLTNN_C是唯一的，就不會產生多餘的過程數據量，否則必然會“泛濫成災”。
我查看了TAB_T1的表結構，很遺憾，在APLTNN_C上沒有創建唯一性的約束。那麼就意味著這個去子查詢的等價改寫就此被宣告“流產”。
但是，我的等價改寫之心還沒有死掉，我還要繼續嚐試等價改寫。
SQL等價改寫的基礎是要徹底讀懂SQL代碼背後蘊含的邏輯功能。那麼這個SQL的邏輯功能是什麼呢？
再次審視這個SQL，一行一行代碼看下去，發現這是一個很特別的SQL：
-
SELECT的字段全部來自TAB_T1表，沒有從子查詢R中獲取字段；
-
R子查詢與外部表TAB_T1表的通過INNER JOIN關聯。
從上可知，子查詢R隻具有過濾功能。那麼通過INNER JOIN過濾了什麼數據呢？
子查詢R與外部表TAB_T1的關聯條件是：R.HDID_C = TA.APLTNN_C AND R.RN = 1。我們根據謂詞推入法推演一下邏輯功能：
-
如果外部表TAB_T2的某條APLTNN_C，其對應的HDID_C在TAB_T2中沒有記錄，那麼通過INNER JOIN，該條記錄就會被過濾掉，因為它不滿足R.HDID_C = TA.APLTNN_C這個關聯條件；
-
如果外部表TAB_T2的某條APLTNN_C，其對應的HDID_C在TAB_T2中存在記錄，可能是一條，也有可能為多條。這就滿足了第一個關聯條件：R.HDID_C = TA.APLTNN_C。此時，子查詢中ROW_NUMBER分析函數得到的結果肯定是大於或等於1的，這也就是說滿足了第二個條件RN=1。
我們可以舉例說明下，比如TAB_T1表中，APLTNN_C字段有三個值，分別是100、101、102，而在TAB_T2表中HDID_C隻有兩個值，其中100對應三條記錄，而101對應一條記錄，如下：
那麼TAB_T1和TAB_T2關聯後的結果，就隻剩下100和101，因為隻有100和101的RN有1的值。
山重水複之後，應柳暗花明
這樣一分析發現，原來這個看起來非常古怪的SQL，其本質是非常單純的，其功能就是查詢TAB_T1的數據，其條件是除了WHERE TA.ATPY_C = 1 And TA.CACSS_C = 10001外，還需要滿足其APLTNN_C必須在要TAB_T2中存在，對應的字段為HDID_C。
我想，看到這裏，如果要你來寫這個SQL，我相信十之八九的人會寫成這樣的：
SELECT TA.*
FROM TAB_T1 TA
WHERE TA.ATPY_C = 1
AND TA.CACSS_C IN
(SELECT REGEXP_SUBSTR(CONT, '[^,]+', 1, LEVEL)
FROM (SELECT '10001' CONT FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(CONT, '[^,]+'))
AND EXISTS
(SELECT 1 FROM ESF.TAB_T2 L WHERE L.HDID_C = TA.APLTNN_C)
沒錯，子查詢R完全可以由EXISTS來等價改寫。再看執行計劃，果然對TAB_T2的訪問又回到了INDEX RANGE。執行計劃如下：
我將上述方案提交給提問者，我想這次總該滿足要求了。但是，我得到的答複卻是：不行。原因是他在提問中貼出來的SQL僅僅是個片段，整段SQL是非常複雜的，而且隨著查詢條件的變化，整段SQL也會發生相應的變化，因此，最佳的方案就是在不改變SQL的情況下提升性能。
千折百繞，死馬當成活馬醫
看來，這次真的是遇到麻煩了，而且是大大的麻煩了。
當下，我想著，要弄清楚為何使用了IN子查詢的時候，謂詞推入會失效？即便是增加了HINT/*+ PUSH_PRED(R)*/。
再次回到提問的內容，說是正則表達式導致了不走索引掃描。通過分析，發現不走索引的直接原因是執行計劃沒有采用謂詞推入的方式。也就是說，問題可以轉換成：正則表達式導致了謂詞推入失效。當時，我就想，正則表達式僅僅是一個運算符，應該不至於幹擾到謂詞推入。於是，我將目光移到了子查詢最後一行代碼：CONNECT BY LEVEL <= REGEXP_COUNT(CONT, '[^,]+')。會不會是這個遞歸查詢惹的禍呢？
我將CONNECT BY遞歸查詢注釋掉，再看執行計劃：
果然，注釋掉CONNECT BY遞歸子查詢後，又回到了INDEX REANGE SCAN。由此來看，其罪魁禍首應該是CONNECT BY遞歸子查詢才對。
峰回路轉，能挽狂瀾於既倒否？
因一個不經意間的注釋，似乎於黑暗中又看到了絲絲光明。
分析到這裏，我還是比較興奮的，找到了問題的根本點。接下來，我就要正麵直視這個包含了遞歸查詢的IN子查詢，看看這個子查詢到底是實現了什麼樣的邏輯功能？
SELECT REGEXP_SUBSTR(CONT, '[^,]+', 1, LEVEL)
FROM (SELECT '10001' CONT FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(CONT, '[^,]+');
這個SQL的寫法是有一定講究的，它其實是為了實現行列轉換：
也就是說，這個正則表達式的目的就是將一串由逗號分割的字符串轉換成一個結果集。也就是說，如下的IN子查詢：
TA.CACSS_C IN
(SELECT REGEXP_SUBSTR(CONT, '[^,]+', 1, LEVEL)
FROM (SELECT '10001,10002,10003,10004' CONT FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(CONT, '[^,]+'))
完全等價於：TA.CACSS_C IN(10001,10002,10003,10004)
這種寫法，我也曾遇到過，諸如'10001,10002,10003,10004'常量是由前端應用傳入的，由於傳入的是一個字符串，如果直接寫成TA.CACSS_C IN('10001,10002,10003,10004')，顯然是不能實現過濾功能的。所以就隻能通過這個子查詢來實現過濾。其實，在執行SQL前，完全可以由前端應用java程序來分解'10001,10002,10003,10004'這串字符串。一個簡單的for each就可以實現了，但是為了少些代碼，開發人員便將這種分解推給了Oracle。
我想，如果真的是為了性能，做這樣的改動應該不致於被拒絕吧。於是，我將該方案提交給提問者。同樣的，也被提問者否定了。給的理由是：這種參數傳遞是框架內置的，框架的代碼不是你想改就能改的。
好吧，我承認，以我的能力，還不能推動框架性代碼修改。
此時，我並沒有因為提問者的否定而火急火燎抓頭搔耳，反而變得異常冷靜，因為至此，我能做的，就是繼續探究：為何一個遞歸子查詢會導致謂詞推入失效？希望能從中找出問題的根源，從而製定一個可行的優化方案。
為了便於分析，我在本地環境模擬了問題，附件《數據準備腳本.sql》為本地環境數據準備腳本（點擊文末“閱讀原文”即可查看）。
我們先來模擬index rangescan的場景：
SELECT *
FROM EMP E
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY CREATE_DTIM DESC) RN,
JOB
FROM JOBS J) R
ON E.JOB = R.JOB
AND R.RN = 1
WHERE E.DEPTNO = 20
AND SAL IN (SELECT '800' CONT FROM DUAL);
再模擬IN子查詢包含CONNECT BY遞歸查詢的場景：
SELECT /*+ push_pred(r)*/
*
FROM EMP E
INNER JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY CREATE_DTIM DESC) RN,
JOB
FROM JOBS J) R
ON E.JOB = R.JOB
AND R.RN = 1
WHERE E.DEPTNO = 20
AND SAL IN (SELECT REGEXP_SUBSTR(CONT, '[^,]+', 1, LEVEL)
FROM (SELECT '800' CONT FROM DUAL)
CONNECT BY LEVEL <= REGEXP_COUNT(CONT, '[^,]+'));
我們發現hint/*+ push_pred(r)*/並沒有生效。
在確保模擬的可用性後，我首先收集了10046事件（點擊文末“閱讀原文”詳見附件“10046.txt”），但是，從內容上，並不能看出所以然來。與此同時，我也向社區其他專家發起了求助。借助社區其他專家，我得到了如下信息：
向Oracle技術支持求助，其建議利用SQLT收集全部執行計劃，但在結果中，並沒有發現有PUSH PRED的執行計劃；
於是，我轉而收集了10053事件（點擊文末“閱讀原文”詳見附件“10053.trc”）。我采用對比分析法，即對比有謂詞推進和無謂詞推進兩個SQL的10053事件。我發現：
1、在謂詞推進的SQL中，SQL最終被等價轉換成如下：
2、在無謂詞推進的SQL中，SQL最終被等價轉換成如下：
3、可見，在等價轉換時，兩個SQL的轉換邏輯都是一樣的，即將IN子查詢的半連接轉換成了全連接；
4、能推入謂詞的SQL，在PM的時候，內容為：
5、不能推入謂詞的SQL，在PM的時候，內容為：
看來，謂詞推入果真是受START WITH/CONNECT BY的影響。至此，我也是絕了優化之心，徹底放棄了，心不甘情不願地接受了這個無情的事實：第一次受邀解決問題，就這樣無疾而終。
此時，我灰頭土臉地跟提問者說明了我了解的一切。我當時並不清楚我是否已經表達清楚了，也不知道提問者是否真的明白了我所說的。總之，在提問者一頭霧水的臉及“哦”“哦”“哦”的應答中，我就像一個心虛的答辯者，希望盡早結束這一切。
寫此文紀念這次不成功的優化經曆，目的在於：
1、優化並不是絕對的，優化的結果會受很多因素的影響，而其中最大的因素便是人，不同的人，優化的方法和手段會不一樣，優化的深度也會有差異。此案例，我相信，不成功，是因為我的道行還不夠高，對Oracle的認知還不夠深。換而言之，我相信，該案例肯定是有優化之道的。
2、再次驗證了性能優化空間是線性的，通常性能優化收益圖例如下：
優化空間包括：優化手段、優化方法、性能收益等。
3、SQL往往扮演了“背鍋”的角色。從業務用戶感知應用響應遲鈍，到確定SQL出現性能瓶頸，這中間，不知道經過了多少層代碼結構。但，最終，被人們記住的總是那條出現性能瓶頸的SQL，而忽略了導致這條SQL性能瓶頸的大環境大因素，這其中的原因很多很深很玄妙。
原文發布時間為：2017-02-06
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-15 18:02:14