《Mysql必讀SQL效率之where子句中的子查詢(xún)和函數(shù)》要點(diǎn)：
本文介紹了Mysql必讀SQL效率之where子句中的子查詢(xún)和函數(shù)，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

工作中,曾有同事問(wèn)我以下sql的效率如何,這里擴(kuò)展一下這個(gè)問(wèn)題并進(jìn)行分析,主要說(shuō)明where子句中的子查詢(xún)和函數(shù)執(zhí)行次數(shù)及索引使用情況.
select * from trd_fundjour a??
where oc_date = (select collect_date from hscon.sys_arg);
首先來(lái)看一下表的數(shù)據(jù)分布情況：

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

先看一下201605分區(qū)全掃描產(chǎn)生多少邏輯讀.

這里產(chǎn)生了88個(gè)一致讀,接下來(lái)執(zhí)行前面的sql.


這里比前一次多出了7個(gè)一致讀,也多出了對(duì)sys_arg表的掃描,單獨(dú)查詢(xún)sys_arg表看看.


一致讀剛好是7個(gè),從這里可以看出,子查詢(xún)只執(zhí)行了一次,而不是針對(duì)分區(qū)中的每條記錄比較一次,因?yàn)檫@個(gè)分區(qū)中有3279條記錄.

下面來(lái)看能否用上索引,這里強(qiáng)制走索引看看.

顯然,在用hint強(qiáng)制的情況下索引是可以走的.就本例而言,走索引比全表掃描效率會(huì)更高,但在不用hint的情況下Oracle選的卻是全表掃描.雖然從表面上看是選擇了較差的執(zhí)行計(jì)劃,其實(shí)是正常的,因?yàn)樵谏蓤?zhí)行計(jì)劃的時(shí)候Oracle得不到子查詢(xún)的值,無(wú)法判斷全表掃描和索引掃描哪個(gè)更優(yōu),因此選擇全表掃描就無(wú)可厚非了.所以,在可能的情況下應(yīng)該將這些非相關(guān)子查詢(xún)執(zhí)行結(jié)果賦給變量,把上面的sql拆成兩個(gè)語(yǔ)句來(lái)執(zhí)行.

下面來(lái)看一下函數(shù)的表現(xiàn),hscon.f_get_collectdate的實(shí)現(xiàn)邏輯就是封裝前面sql中的子查詢(xún),返回查詢(xún)結(jié)果.

將子查詢(xún)改成函數(shù),可以看到遞歸調(diào)用和一致讀都大了很多,是哪里產(chǎn)生的呢?我們來(lái)單獨(dú)查一下函數(shù)看看.


從這里可以看出,函數(shù)調(diào)用一次就產(chǎn)生1個(gè)遞歸調(diào)用和7個(gè)一致讀.3286*7(函數(shù)執(zhí)行一次7個(gè)一致讀)加上88(分區(qū)全掃描產(chǎn)生的一致讀)剛好是23090,也就是說(shuō)在前面的語(yǔ)句中函數(shù)執(zhí)行了3286次!上文這一數(shù)字其實(shí)可以被拆分成3279+7兩部分來(lái)看,3279是分區(qū)的記錄數(shù),7應(yīng)該是和分區(qū)消除相關(guān).我們可以得出這樣的結(jié)論：針對(duì)分區(qū)中的每一條記錄,函數(shù)都會(huì)調(diào)用一次進(jìn)行比較.

既然前面的寫(xiě)法會(huì)導(dǎo)致函數(shù)頻繁調(diào)用,我們修改一下sql寫(xiě)法,先把函數(shù)生成一個(gè)結(jié)果集.

從上圖看,情況更加糟糕,但其實(shí)并不是我們的想法錯(cuò)了,而是Oracle太“聰明”了.從執(zhí)行計(jì)劃的filter中可以看出,Oracle重寫(xiě)了我們的sql,合并了子查詢(xún),基本上相當(dāng)于又給改回了原樣.下面加個(gè)hint來(lái)防止Oracle的這種自作聰明.


情況有了很大的改善.雖說(shuō)與直接使用子查詢(xún)方式相比,使用函數(shù)在一致讀上還是大了一些,這個(gè)差別懷疑是和分區(qū)表有關(guān)(非分區(qū)表應(yīng)該沒(méi)有差別,未做驗(yàn)證),并且實(shí)際上可以通過(guò)調(diào)整執(zhí)行計(jì)劃來(lái)達(dá)到無(wú)差別,這里不做詳細(xì)說(shuō)明.

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

同樣地,也是可以走索引的,但Oracle不會(huì)主動(dòng)使用,需要用hint強(qiáng)制,理由前面已經(jīng)提過(guò)了.需要注意的是,這里遞歸調(diào)用只有8次,和分區(qū)全掃描時(shí)的表現(xiàn)完全不同.
再看下面的語(yǔ)句.


這應(yīng)該是比較合理的執(zhí)行計(jì)劃了.

綜上所述,通常使用子查詢(xún)的效率比用函數(shù)都要來(lái)得好一些,個(gè)別情況下用函數(shù)的效率很糟糕.

最高效的方法就是盡可能拆分成兩個(gè)語(yǔ)句,用變量來(lái)代替子查詢(xún)和函數(shù).

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/6158.html