掀開SQL的神秘面紗，將優(yōu)化進(jìn)行到底

有這樣一條奇怪的SQL，返回結(jié)果不足10行，邏輯讀達(dá)到1.2w,存在索引卻走多次全表掃描，如何揭開它神秘的面紗拯救系統(tǒng)性能，答案在這里，你不可錯過！

本文來自上周四大講堂課程分享。

在某運營商的優(yōu)化經(jīng)歷中曾經(jīng)遇到了一條比較有意思的SQL。

該最開始的sql執(zhí)行情況如下：

SQL語句：

執(zhí)行計劃如下：

統(tǒng)計信息如下：

針對以上信息我們分析如下：

1) 該sql每天執(zhí)行上千次，平均每次執(zhí)行返回不到10行數(shù)據(jù)，但是平均邏輯讀達(dá)到1.2W，可能存在性能問題。

2）ID為4,5的執(zhí)行計劃路徑中出現(xiàn)了兩個全表掃描，看到這兒我們可以想到可能是沒有合適的索引導(dǎo)致走了全表掃描從而執(zhí)行效率低下。

3）ID為2的執(zhí)行計劃路徑出現(xiàn)了FILTER，且3，和6為其子路徑，如果FILTER有兩個及兩個以上的子路徑，那么他的執(zhí)行原理將類似于嵌套循環(huán)，id號最小的子路徑如果返回行數(shù)較多，可能會導(dǎo)致多次執(zhí)行id號更小的子路徑，導(dǎo)致性能低下。一般存在“OR EXISTS”的時候會出現(xiàn)此情況，可以根據(jù)情況避免。

4）存在條件“rownum<500”，但是從歷史的執(zhí)行情況來看，返回行數(shù)都遠(yuǎn)小于500行，此處我們先予以忽略。

處理過程：

1、進(jìn)過探查，發(fā)現(xiàn)存在兩個表都有可用的索引，且兩個表都只有幾十M的大小。

2、去掉“OR EXISTS”子句查看執(zhí)行效率。

執(zhí)行計劃和統(tǒng)計信息如下：

此處可用看到，去掉“OR EXISTS”之后兩個表走了合適的索引，并且執(zhí)行效率極高。

3、去掉“OR EXISTS”中的子句查看執(zhí)行效率。

SQL> SELECT A.OFFER_SPEC_GRP_ID

     FROM OFFER_SPEC_GRP_RELA A

     WHERE A.SUB_OFFER_SPEC_ID = 109910000618;

OFFER_SPEC_GRP_ID

-----------------

        100000048

        109090086

Elapsed: 00:00:00.01

執(zhí)行計劃和統(tǒng)計信息如下：

此處可用看到“OR EXISTS”中的子句單獨執(zhí)行返回行數(shù)并不多，且效率依舊很快。

4、我們把該條sql語句分為“OR EXISTS”的子句和其他部分兩塊，到此我們可以看到，兩塊的執(zhí)行效率都很高，但是合在一起就低了很多。在這種情況下，幾乎可以確認(rèn)，將該存在“OR EXISTS”的子句改寫為union必將提升效率。

執(zhí)行計劃如下：

統(tǒng)計信息：

此處我們可以看到，改寫之后邏輯讀僅僅11，較優(yōu)化前提升了上千倍。到了此處，我們已經(jīng)將sql優(yōu)化到幾乎最快的效率了。

第二次分析，確實改寫能夠提升效率，但是如果改寫sql會涉及到修改代碼，當(dāng)前能否在不修改代碼的情況下對其進(jìn)行優(yōu)化。

1）我們再來回顧一下最開始的執(zhí)行計劃路徑。

我們可以看到“OR EXISTS”中的子句是在ID為6的路徑才開始執(zhí)行的，這兒有一個知識點即為一個sql中的子句，一般情況下默認(rèn)會將其放到最后執(zhí)行。

2）ID為4,5的執(zhí)行計劃路徑中在有高效索引的情況下卻出現(xiàn)了兩個全表掃描，可以推斷CBO可能沒有正常評估執(zhí)行的cost。

3）“OR EXISTS”中的子句執(zhí)行效率很快，返回行數(shù)并不多，我們可以考慮提升CBO將其提前執(zhí)行，看能否影響CBO選擇出更高效的執(zhí)行計劃。

執(zhí)行計劃如下：

統(tǒng)計信息如下：

來看另外一種情況：

執(zhí)行計劃和統(tǒng)計信息如下：

此處我們在子句中加了一個HINT /*+ push_subq */，該HINT的作用即使提醒CBO將子句提前執(zhí)行。

我們可以看到，執(zhí)行效率較之前也得到了顯著提升，邏輯讀降低了7倍作用，雖然相對于改寫效率還是高很多，但是在急需處理的情況下該方案還是更加可取的，此時對執(zhí)行計劃進(jìn)行綁定即可，無需修改代碼。

4）最后執(zhí)行計劃中還是存在全表掃描，我使用hint使其強制走索引查看情況：

執(zhí)行計劃和統(tǒng)計信息如下：

此時雖然走了索引，但是卻是”INDEX FULL SCAN“,邏輯讀也增加了很多，所以此時可以保持之前全表掃描的執(zhí)行計劃。

索引雖好，但不是萬能的，只有結(jié)合系統(tǒng)的具體 情況，才能選擇性能最佳的SQL。

案例總結(jié)：

1）當(dāng)我們看到總的邏輯讀除以最后的返回的行數(shù)過大時可以認(rèn)為sql確實是存在性能瓶頸的（有些時候rownum限制除外），但是這沒有一個清晰的值來判斷過大還是過小，需要憑借優(yōu)化的經(jīng)驗去評估。但是1200左右邏輯讀/條這么夸張的比值還是完全可以判斷的，畢竟在某些情況下兩三個邏輯讀就能獲取上百條結(jié)果。

2）很多設(shè)計不合理的業(yè)務(wù)，沒有添加合適的索引，可能會導(dǎo)致全表掃描，在某些情況下走全表和走索引產(chǎn)生性能的消耗根本不是一個數(shù)量級的。

3）filter和嵌套循環(huán)類似，可以看做是升級版的嵌套循環(huán)。而嵌套循環(huán)，oracle從較小結(jié)果集中讀取一行，然后和較大結(jié)果集中所有數(shù)據(jù)逐條進(jìn)行比較，如果符合規(guī)則，就放入結(jié)果集中，然后去較小結(jié)果集的下一條數(shù)據(jù)繼續(xù)進(jìn)行循環(huán)，直到結(jié)束。嵌套循環(huán)只適合輸出較少結(jié)果集或者用于快速輸出結(jié)果集。

4）某些時候符合我們限制條件的條數(shù)過多，但是我們用rownum進(jìn)行條數(shù)限制之后可能會改變其選擇執(zhí)行計劃。假如一個sql本來能夠返回10000W，但是我用rownum來限制只需要500行。那么優(yōu)化器會選擇能夠優(yōu)先返回500行的執(zhí)行計劃。