642
將SQL優化做到極致 - 子查詢優化
編輯手記：子查詢是SQL中比較重要的一種語法，恰當地應用會很大程度上提高SQL的性能，若用的不得當，也可能會帶來很多問題。因此子查詢也是SQL比較難優化的部分。今天一起來學習最常見的幾種優化子查詢到方式。
作者簡介：
韓鋒
精通包括Oracle、MySQL、informix等多種關係型數據庫，有豐富的數據庫架構設計開發經驗。就職於宜信。
針對子查詢，優化器支持了多種優化策略。Oracle查詢轉換功能主要有啟發式（基於規則）查詢轉換以及基於Cost的查詢轉換兩種，針對子查詢主要有Subquery Unnest、Push Subquery等。查詢轉換的目的是轉化為Join（包括Semi、Anti Join等），充分利用索引、Join技術等高效訪問方式提高效率。如果子查詢不能unnest（啟發式），可以選擇把子查詢轉換為Inline View（基於Cost）；如果都不可以，那麼子查詢就會最後執行，可能會看到類似Filter的操作。
1.子查詢轉換
下麵先通過一個示例看看。
//上麵代碼準備了必要的數據環境，並收集相關對象的統計信息
//默認情況下，是將上麵的操作轉換為表間關聯方式執行
/*通過提示no_unnest，禁止了子查詢解嵌套。一次采用了原始的方式執行，子查詢部分的作用就是"FILTER"*/
2.子查詢合並
子查詢合並是指優化器不再單獨為子查詢生成執行計劃，而是將子查詢合並到主查詢中，最終為合並後的結果生成一個最優的執行計劃。可以通過參數_simple_view_merging或者提示MERGE/NO_MERGE來控製是否開啟、關閉子查詢合並。
根據子查詢的複雜程度，子查詢可分為簡單子查詢、複雜子查詢。
所謂簡單子查詢，是指可以簡單將子查詢字段投影到外部的情況。對於這種情況，優化器采取的是啟發式策略，即滿足條件下就行合並。
複雜子查詢是指存在分組行數的情況。針對這種情況，優化器采取的是基於代價的策略，最終是否轉換取決於成本。當然還有一些子查詢是無法進行合並的。
下麵通過幾個示例看一下。
//這種方式下，並沒有進行子查詢合並。
下麵強製看看效果
/*這裏可以看到，沒有再生成內聯視圖，子查詢被合並了。那為什麼默認沒有進行子查詢合並呢？從成本可見，顯然不合並的成本更低*/
3.解嵌套子查詢
解嵌套子查詢是指在對存在嵌套子查詢的複雜語句進行優化時，查詢轉換器會嚐試將子查詢展開，使得其中的表能與主查詢中的表關聯，從而獲得更優的執行計劃。部分子查詢反嵌套屬於啟發式查詢轉換，部分屬於基於代價的轉換。
係統中存在一個參數來控製解嵌套子查詢—_unnest_subquery。參數_unnest_subquery在8i中的默認設置是false，從9i開始其默認設置是true。然而9i在非嵌套時不考慮成本。隻有在10g中才開始考慮兩種不同選擇的成本，並選取成本較低的方式。當從8i升級到9i時，可能想阻塞某些查詢的非嵌套。利用子查詢中的no_unnest提示可以完成這一點。在8i和9i中，如果star_transformation_enabled=true，則非嵌套時被禁用（即使用了提示）。在11g環境下還受優化器參數_optimizer_unnest_all_subqueries控製。此外，提示UNNEST/NO_UNNEST可以控製是否進行解嵌套。
下麵我們通過幾個示例看看解嵌套子查詢。
1）IN/EXISTS轉換為SEMI JOIN：
/*示例中的子查詢引用表DEPT，最終轉換為兩個表的哈希半連接。也就是說，exists子句中的子查詢被展開，其中的對象與主查詢中的對象直接進行半關聯操作*/
// IN的情況類似，如下：
2）IN/EXISTS轉換為ANTI JOIN：
/*優化器將NOT EXISTS後的子查詢做解嵌套，然後選擇了哈希的反連接。這種轉換屬於基於代價的查詢轉換。*/
//下麵看看NOT IN的情況
/*和NOT EXISTS類似，也選擇了哈希連接，隻不過是HASH JOIN ANTI NA。這裏的NA，實際表示Null-Aware的意思，在11g及以後的版本中，Oracle增加了對空值敏感的反關聯的支持*/
3）關聯子查詢的解嵌套
在對於關聯子查詢的解嵌套過程中，會將子查詢構造出一個內聯視圖，並將內聯視圖與主查詢的表進行關聯。這個操作可以通過參數_unnest_subquery來控製。這種轉換屬於啟發式查詢轉換。
/*在ID=2的步驟中生成了內聯視圖，然後跟外部表進行的哈希連接。下麵嚐試修改參數，看優化器如何處理*/
//這裏轉換成了嵌套循環的一種特列FILTER
4.子查詢推進
子查詢推進是一項對未能合並或者反嵌套的子查詢優化的補充優化技術。這一技術是在9.2版本引入的。通常情況下，未能合並或者反嵌套的子查詢的子計劃會被放置在整個查詢計劃的最後執行，而子查詢推進使得子查詢能夠提前被評估，使之可以出現在整體執行計劃較早的步驟中，從而獲得更優的執行計劃。可以通過PUSH_SUBQ/NO_PUSH_SUBQ來控製。
//默認情況下，就是用子查詢推進技術。
對比一下，我們看看強製不使用的情況
/*對比上麵，對了一步FILTER。這裏使用了嵌套循環，每一個EMP表的記錄，都對應一次子查詢的查詢，獲得MAX值*/
5.子查詢分解
所謂子查詢分解，是指由WITH創建的複雜查詢語句存儲在臨時表中，按照與一般表相同的方式使用該臨時表的功能。從概念上來看它與嵌套視圖比較類似，但各自有其優缺點。優點在於子查詢如果被多次引用，使用嵌套視圖就需要被執行多次，尤其在海量數據中滿足條件的結果非常少得情況下，兩者差別很明顯。
使用WITH子查詢的優點就在於其複雜查詢語句隻需要執行一次，但結果可以在同一個查詢語句中被多次使用。
缺點是使用WITH子查詢，由於不允許執行查詢語句變形，所以無效的情況也比較多。尤其是WITH中的查詢語句所創建的臨時表無法擁有索引，當其查詢結果的數據量比較大的時候，很可能會影響執行效率。
下麵通過一個是示例看看。
/*從上麵可以看出，在WITH中有兩個子查詢語句，但隻創建了一個臨時表，這是因為WITH中的第二個子查詢使用的是第一個子查詢的執行結果。在這種情況下，邏輯上隻允許創建一個臨時表，沒有必要再次創建。在處理WITH臨時表時，如果臨時表可以被優先執行而且可以縮減連接之前的數據量，就可以采用嵌套循環連接，否則必須使用哈希連接*/
6.子查詢緩存
針對某些子查詢操作，優化器可以將子查詢的結果進行緩存，避免重複讀取。這一特性在FILTER型的子查詢或標量子查詢中都能觀察到。看一個示例。
/*注意Id=3步驟的Start=3(emp表中的deptno有3個不同的值，這裏就重複執行3次)。這體現了Cache技術，標量子查詢中也有類似的Cache技術。*/
子查詢相關文章：
感謝韓鋒老師授權！Oracle社區提供自由開放的技術交流平台和多樣的分享途徑，在分享中成就別人提高自己。請有意願分享的朋友聯係Eygle（微信eyygle）。新的一年，祝大家天天進步！
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-17 17:33:06