《Mysql必讀SQL效率之where子句中的子查詢和函數》要點：
本文介紹了Mysql必讀SQL效率之where子句中的子查詢和函數，希望對您有用。如果有疑問，可以聯系我們。

工作中,曾有同事問我以下sql的效率如何,這里擴展一下這個問題并進行分析,主要說明where子句中的子查詢和函數執行次數及索引使用情況.
select * from trd_fundjour a??
where oc_date = (select collect_date from hscon.sys_arg);
首先來看一下表的數據分布情況：

表trd_fundjour是分區表,?里面是按月分區的,以oc_date為索引.

先看一下201605分區全掃描產生多少邏輯讀.

這里產生了88個一致讀,接下來執行前面的sql.


這里比前一次多出了7個一致讀,也多出了對sys_arg表的掃描,單獨查詢sys_arg表看看.


一致讀剛好是7個,從這里可以看出,子查詢只執行了一次,而不是針對分區中的每條記錄比較一次,因為這個分區中有3279條記錄.

下面來看能否用上索引,這里強制走索引看看.

顯然,在用hint強制的情況下索引是可以走的.就本例而言,走索引比全表掃描效率會更高,但在不用hint的情況下Oracle選的卻是全表掃描.雖然從表面上看是選擇了較差的執行計劃,其實是正常的,因為在生成執行計劃的時候Oracle得不到子查詢的值,無法判斷全表掃描和索引掃描哪個更優,因此選擇全表掃描就無可厚非了.所以,在可能的情況下應該將這些非相關子查詢執行結果賦給變量,把上面的sql拆成兩個語句來執行.

下面來看一下函數的表現,hscon.f_get_collectdate的實現邏輯就是封裝前面sql中的子查詢,返回查詢結果.

將子查詢改成函數,可以看到遞歸調用和一致讀都大了很多,是哪里產生的呢?我們來單獨查一下函數看看.


從這里可以看出,函數調用一次就產生1個遞歸調用和7個一致讀.3286*7(函數執行一次7個一致讀)加上88(分區全掃描產生的一致讀)剛好是23090,也就是說在前面的語句中函數執行了3286次!上文這一數字其實可以被拆分成3279+7兩部分來看,3279是分區的記錄數,7應該是和分區消除相關.我們可以得出這樣的結論：針對分區中的每一條記錄,函數都會調用一次進行比較.

既然前面的寫法會導致函數頻繁調用,我們修改一下sql寫法,先把函數生成一個結果集.

從上圖看,情況更加糟糕,但其實并不是我們的想法錯了,而是Oracle太“聰明”了.從執行計劃的filter中可以看出,Oracle重寫了我們的sql,合并了子查詢,基本上相當于又給改回了原樣.下面加個hint來防止Oracle的這種自作聰明.


情況有了很大的改善.雖說與直接使用子查詢方式相比,使用函數在一致讀上還是大了一些,這個差別懷疑是和分區表有關(非分區表應該沒有差別,未做驗證),并且實際上可以通過調整執行計劃來達到無差別,這里不做詳細說明.

下面看一下索引的使用情況.

同樣地,也是可以走索引的,但Oracle不會主動使用,需要用hint強制,理由前面已經提過了.需要注意的是,這里遞歸調用只有8次,和分區全掃描時的表現完全不同.
再看下面的語句.


這應該是比較合理的執行計劃了.

綜上所述,通常使用子查詢的效率比用函數都要來得好一些,個別情況下用函數的效率很糟糕.

最高效的方法就是盡可能拆分成兩個語句,用變量來代替子查詢和函數.

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/6158.html