《由索引未被使用，看SQL開(kāi)發(fā)規(guī)范落地》要點(diǎn)：
本文介紹了由索引未被使用，看SQL開(kāi)發(fā)規(guī)范落地，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

作者介紹

王科,新炬網(wǎng)絡(luò)架構(gòu)師.Oracle OCM,10年以上運(yùn)維管理經(jīng)驗(yàn),擅長(zhǎng)運(yùn)維服務(wù)與運(yùn)維工具的融合應(yīng)用,在數(shù)據(jù)資產(chǎn)管理、云計(jì)算、大數(shù)據(jù)相關(guān)領(lǐng)域也均有一定的研究及實(shí)踐.

上次丁俊大師在社群上做了CBO優(yōu)化器和坑爹案例的分享后,反響不是一般的強(qiáng)烈,但其中也有一部分同學(xué)表示太高大上了(我也是這樣覺(jué)得的),消化起來(lái)相當(dāng)有難度,于是便有了本文.繞開(kāi)復(fù)雜的CBO優(yōu)化器不說(shuō),本文將幫你理清那些因?yàn)镾QL語(yǔ)句編寫(xiě)規(guī)范問(wèn)題導(dǎo)致沒(méi)有充分利用索引來(lái)大幅提升效率的使用場(chǎng)景.

一、SQL無(wú)法走索引的情況及解決思路

因?yàn)閿?shù)據(jù)庫(kù)優(yōu)化器不夠智能,或者一些邏輯原因,導(dǎo)致SQL在比較適合走索引的情況下卻無(wú)法正確利用索引.這時(shí)候,除了給數(shù)據(jù)庫(kù)需要的統(tǒng)計(jì)信息之外,SQL語(yǔ)句本身還必須要給優(yōu)化器足夠多的額外有效信息,幫助優(yōu)化器能夠選擇更好的執(zhí)行計(jì)劃.要讓優(yōu)化器正確選擇需要的索引,要考慮兩點(diǎn)：

• 如何避免優(yōu)化器的限制
• 根據(jù)業(yè)務(wù)數(shù)據(jù)特點(diǎn)改寫(xiě)SQL語(yǔ)句

說(shuō)明：這里說(shuō)的走不了索引,是指走不了正常的RANGE SCAN,非(FAST) FULL INDEX SCAN.

SQL無(wú)法走索引常見(jiàn)的有如下8種情況：

1. 統(tǒng)計(jì)信息不準(zhǔn)確
2. 索引列的值允許為NULL
3. 謂詞使用了不等于(<>, !=)
4. LIKE前通配或全通配的查詢
5. 索引列使用了函數(shù)、數(shù)學(xué)運(yùn)算、其它表達(dá)式等
6. 使用了隱式類型轉(zhuǎn)換
7. 查詢轉(zhuǎn)換失敗
8. 其它語(yǔ)句邏輯原因

第一、二種情況在現(xiàn)實(shí)中比較常見(jiàn),解決辦法也相對(duì)比較簡(jiǎn)單,下面就不再作詳細(xì)展開(kāi)了.

謂詞使用了不等于(<>,!=),走不了索引

解決方法：

1. 如果不等條件之外的值不多,而且是確定的,可以改為等值或IN查詢,比如status狀態(tài)字段一般值類別很少；
2. 如果不等條件之外的值很多,可以改為“> OR <”的形式,當(dāng)然第2種方法包含了方法1.

舉個(gè)例子,先構(gòu)建測(cè)試場(chǎng)景：

謂詞使用<>,無(wú)法利用索引：

將<>改寫(xiě)為OR連接后,能夠正確使用索引,走OR擴(kuò)展：

如果業(yè)務(wù)允許,改寫(xiě)為下列語(yǔ)句也是走索引的,不再演示.

SELECT * FROM t?WHERE t.NAME IN (‘ORADB1′,’ORADB2′,’ORADB3’);

LIKE前通配或全通配的查詢,走不了索引

解決方法,有如下三種：

(1)根據(jù)業(yè)務(wù)需求,是否可以把前通配去掉

原來(lái)全通配,無(wú)法走索引：

把前通配去掉,改為后通配,可以正常使用索引：

(2)和此LIKE一樣的前通配或全通配的SQL有很多,此謂詞的LIKE變化不大?如果是,考慮建立函數(shù)索引,否則對(duì)于全通配問(wèn)題最好辦法就是全文索引.

創(chuàng)建instr函數(shù)索引：

(3)如果只是前通配,可以使用reverse函數(shù)索引(不是翻轉(zhuǎn)鍵索引)

原始語(yǔ)句：
SELECT ?* FROM t WHERE t.NAME LIKE ‘%ORADB1’;

創(chuàng)建reverse函數(shù)索引,并改寫(xiě)語(yǔ)句,注意查找值要倒序：

注意：如果通配查詢的是中文,要注意使用REVERSE翻轉(zhuǎn)條件值,因?yàn)镽EVERSE內(nèi)部會(huì)按字節(jié)翻轉(zhuǎn)的,正確寫(xiě)法如：

SELECT * FROM t WHERE REVERSE(t.name) LIKE REVERSE(‘數(shù)據(jù)’)||’%’;

否則查詢出來(lái)的數(shù)據(jù)不對(duì),將可能影響到業(yè)務(wù)的正常運(yùn)行.

索引列使用了函數(shù)、數(shù)學(xué)運(yùn)算、其他表達(dá)式等,走不了索引

解決方法：去掉對(duì)索引列的相關(guān)運(yùn)算,保持索引列純凈.

目前優(yōu)化器對(duì)一些數(shù)學(xué)運(yùn)算,還無(wú)法做很好的消除動(dòng)作,所以對(duì)于索引列應(yīng)該盡量保持純凈,否則可能無(wú)法用上正確的索引.

舉例：

把語(yǔ)句的條件改寫(xiě)一下,將運(yùn)算去掉：

以上例子只是簡(jiǎn)單的數(shù)學(xué)運(yùn)算,可能的運(yùn)算還有和其他列運(yùn)算,比如where ID+ext_col…

記住一個(gè)原則：盡量保持索引列純凈.

使用了隱式類型轉(zhuǎn)換,走不了索引

解決方法：必須避免隱式類型轉(zhuǎn)換,全部要求顯式類型轉(zhuǎn)換(非索引列),且避免對(duì)索引列進(jìn)行類型轉(zhuǎn)換(有函數(shù)索引除外).如果類型不一致,不管是否發(fā)生自動(dòng)類型轉(zhuǎn)換,謂詞的右值應(yīng)該顯式轉(zhuǎn)換為與索引列保持一致(對(duì)于非索引列的運(yùn)算也應(yīng)該如此).

舉例：

從以上兩次查詢對(duì)比來(lái)看,第一次查詢發(fā)生了類型轉(zhuǎn)換,可以通過(guò)執(zhí)行計(jì)劃中的謂詞信息獲知.通過(guò)分析發(fā)現(xiàn),X因?yàn)槭荲ARCHAR2,優(yōu)先級(jí)比數(shù)值類型低,遇到數(shù)值類型,會(huì)TO_NUMBER隱式轉(zhuǎn)換,所以索引失效.第二次查詢,通過(guò)傳入與索引列類型一致的字符串后,得以解決.

查詢轉(zhuǎn)換失敗,走不了索引

查詢轉(zhuǎn)換是非常復(fù)雜的過(guò)程,ORACLE CBO的查詢轉(zhuǎn)換有好幾十種,比如CVM ：complex view merging ,SU:subquery unnest, JPPD:JOIN PREDICATE PUSH DOWN等(在10053文件里都可以看到).如果查詢轉(zhuǎn)換失敗,那么必將影響后續(xù)優(yōu)化器的一些操作,比如JPPD中JOIN謂詞無(wú)法推入到視圖中,那么很可能視圖就無(wú)法走索引了.而且,查詢轉(zhuǎn)換有很多BUG,觸發(fā)BUG需要找到原因,比如設(shè)置隱含參數(shù)、fix control等,或者改寫(xiě)SQL繞過(guò)BUG.如下例所示：

其中AB_XRTOFFREC_201703是UNION ALL查詢組成的視圖,這個(gè)查詢?cè)?0.2.0.4上很正常,升級(jí)到11.2.0.4后執(zhí)行計(jì)劃顯示不走索引,性能非常差.

在10g中的執(zhí)行計(jì)劃：

在11g中的錯(cuò)誤執(zhí)行計(jì)劃：

通過(guò)收集統(tǒng)計(jì)信息都無(wú)效,將優(yōu)化器降級(jí)到10.2.0.4即有效.很顯然,這是引入了BUG或者新的限制.一旦遇到這種是BUG或限制導(dǎo)致的,可以通過(guò)10053跟蹤文件或者SQLT來(lái)進(jìn)行分析.對(duì)于這條語(yǔ)句無(wú)法走JPPD查詢轉(zhuǎn)換,在10053中就可以找到原因：

然后在MOS中查看得知是BUG:9380298,默認(rèn)開(kāi)關(guān)關(guān)閉.

 

ORACLE針對(duì)這樣的查詢,為了防止遇到笛卡爾積,默認(rèn)把修復(fù)BUG的補(bǔ)丁關(guān)閉了.顯然通過(guò)設(shè)置_fix_control參數(shù)打開(kāi)9380298 fix即可.

語(yǔ)句邏輯問(wèn)題,導(dǎo)致優(yōu)化器選擇不了索引

舉一個(gè)典型的例子,先準(zhǔn)備測(cè)試表,并在其上創(chuàng)建一個(gè)組合索引：

查詢需求：查找創(chuàng)建時(shí)間是2013年的,并且最后ddl時(shí)間比創(chuàng)建時(shí)間大1天以上的對(duì)象.

這個(gè)索引是組合索引,上面的語(yǔ)句對(duì)前導(dǎo)列進(jìn)行了運(yùn)行,也不符合走index skip scan的條件,所以,走FULL TABLE SCAN.那么是否可以通過(guò)邏輯改寫(xiě)走索引呢,基于保持索引列純凈的原則,將create_date移到右邊,語(yǔ)句如下：

改寫(xiě)后發(fā)現(xiàn),還是沒(méi)有走索引,因?yàn)镺racle認(rèn)為前導(dǎo)列右邊的created不固定,無(wú)法從指定索引處查找.通過(guò)分析得知,Oracle謂詞傳遞有一定限制,create_date+1無(wú)法做謂詞傳遞給last_ddl_time.再次改寫(xiě)：

此時(shí)Oracle知道將謂詞傳遞給last_ddl_time了,T.LAST_DDL_TIME>=TO_DATE(‘ 2013-01-01 00:00:00’, ‘syyyy-mm-dd?hh24:mi:ss’).當(dāng)然,也可以手動(dòng)謂詞傳遞,last_ddl_time肯定大于等于DATE’2013-1-2′：

還未完,我們繼續(xù)往下看：

如果查詢條件中無(wú)t.created>=DATE’2013-1-1’,即如下面語(yǔ)句：

–由此兩數(shù)據(jù)比較可知,應(yīng)該走索引更佳.因?yàn)闆](méi)有其他過(guò)濾條件,可以考慮建立函數(shù)索引：

SQL> CREATE INDEX idx1_t_object ON t_objects(last_ddl_time-created);

–注意收集直方圖,因?yàn)榉植疾痪?/p>

SQL> exec dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t_objects’,estimate_percent => 100,method_opt => ‘for all indexed columns’,cascade => TRUE);

當(dāng)然,對(duì)于兩個(gè)都是范圍的查詢,這里只能通過(guò)一個(gè)列來(lái)輪詢索引,先做access,再做filter.

SQL語(yǔ)句的邏輯改寫(xiě)很重要,往往通過(guò)邏輯改寫(xiě)就能改變SQL的執(zhí)行計(jì)劃,從不好的計(jì)劃到好的計(jì)劃,比如semi join,anti join與or,往往走FILTER導(dǎo)致執(zhí)行計(jì)劃較差,這時(shí)候就需要通過(guò)邏輯等價(jià)改寫(xiě).邏輯等價(jià)改寫(xiě)往往需要掌握一些集合的知識(shí),比如NOT (A AND B)==NOT A OR NOT B,NOT (A OR B)==NOT A AND NOT B等.

總結(jié)

SQL有索引而不走索引的情況還有很多,比如在DBLINK查詢中,可能走不了索引,這時(shí)候需要通過(guò)driving_site hint或者遠(yuǎn)程庫(kù)建立視圖等方式解決等,需要綜合從語(yǔ)法語(yǔ)義、索引選擇性、索引訪問(wèn)特點(diǎn)等多方面進(jìn)行分析.

二、如何將SQL開(kāi)發(fā)規(guī)范落地

上面說(shuō)到的問(wèn)題,說(shuō)到底都是不遵守?cái)?shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范的問(wèn)題.說(shuō)到數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范,估計(jì)很多企業(yè)都有制定對(duì)應(yīng)的規(guī)范及要求,但說(shuō)到落地執(zhí)行情況,這個(gè)就比較困難了.如果企業(yè)在意旨上是期望開(kāi)發(fā)人員去學(xué)懂規(guī)范,然后學(xué)以致用,就有點(diǎn)太理想化了.于是,為了保證開(kāi)發(fā)人員真的是按照數(shù)據(jù)庫(kù)開(kāi)發(fā)規(guī)范來(lái)編寫(xiě)代碼,很多企業(yè)就在應(yīng)用上線前增加了一道SQL上線審核的工序.

說(shuō)到SQL上線審核,關(guān)鍵要解決三個(gè)問(wèn)題：

1、如何在上線的應(yīng)用版本中發(fā)現(xiàn)新增的SQL語(yǔ)句；

2、新增SQL存在哪些問(wèn)題,如何快速準(zhǔn)確的定位；

3、對(duì)于問(wèn)題SQL,如何快速提供優(yōu)化方案.

這三個(gè)問(wèn)題,是一環(huán)扣一環(huán)的,解決不了前面的問(wèn)題,就無(wú)從解決后面的問(wèn)題.然而,應(yīng)用系統(tǒng)SQL眾多,如果單靠人工,難度是很大的,專家資源投入就更不說(shuō)了,顯然不能滿足當(dāng)今IT系統(tǒng)高速發(fā)展的需要.

這里跟大家分享我們?cè)谶@方面的一些實(shí)踐和成果.通過(guò)結(jié)合多年的運(yùn)維和優(yōu)化經(jīng)驗(yàn),我們自主研發(fā)了SQL審核工具,不僅可以自動(dòng)化完成SQL上線審核,還可以做到SQL的性能監(jiān)控和自動(dòng)優(yōu)化,達(dá)到SQL全生命周期管理的效果.對(duì)于SQL上線審核,我們將開(kāi)發(fā)規(guī)范規(guī)則化后落到SQL審核平臺(tái),內(nèi)置了4個(gè)維度、200多種常見(jiàn)的審查規(guī)則,還支持靈活的按需添加規(guī)則.同時(shí),審查的不只是SQL語(yǔ)句本身,還包括了對(duì)表的模型設(shè)計(jì)、索引的構(gòu)建.

 

在應(yīng)用新版本上線前,通過(guò)SQL審核平臺(tái),自動(dòng)分析出版本的新增SQL,基于以上規(guī)則對(duì)新增SQL進(jìn)行審查,并自動(dòng)提供優(yōu)化建議,可生成可視化的報(bào)表和詳細(xì)報(bào)告.不管是DBA還是開(kāi)發(fā)人員都可以基于此平臺(tái),對(duì)問(wèn)題進(jìn)行確認(rèn)和解決,實(shí)現(xiàn)系統(tǒng)優(yōu)化前移、提升應(yīng)用版本質(zhì)量的目標(biāo).

 

三、總結(jié)

本文主要和大家分享了SQL無(wú)法走索引的一些常見(jiàn)情景及解決方法,當(dāng)然,SQL的規(guī)范化使用是十分重要的,SQL的優(yōu)化也不僅僅局限于索引的優(yōu)化.所以,只有平時(shí)多積累,結(jié)合理論多實(shí)踐,遇到問(wèn)題時(shí)才能運(yùn)籌帷幄,對(duì)癥下藥、藥到病除.另外,企業(yè)在IT建設(shè)中要重視開(kāi)發(fā)規(guī)范的落地執(zhí)行,必要時(shí)使用合適的工具,在加速I(mǎi)T環(huán)境建設(shè)效率的同時(shí),還能兼顧到IT系統(tǒng)的建設(shè)質(zhì)量,做到兩不誤.

文章來(lái)自微信公眾號(hào)：DBAplus社群

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