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

作者介紹

王科,新炬網(wǎng)絡(luò)架構(gòu)師.Oracle OCM,10年以上運(yùn)維管理經(jīng)驗(yàn),擅長運(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é)表示太高大上了(我也是這樣覺得的),消化起來相當(dāng)有難度,于是便有了本文.繞開復(fù)雜的CBO優(yōu)化器不說,本文將幫你理清那些因?yàn)镾QL語句編寫規(guī)范問題導(dǎo)致沒有充分利用索引來大幅提升效率的使用場景.

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

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

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

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

SQL無法走索引常見的有如下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. 其它語句邏輯原因

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

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

解決方法：

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

舉個例子,先構(gòu)建測試場景：

謂詞使用<>,無法利用索引：

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

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

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

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

解決方法,有如下三種：

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

原來全通配,無法走索引：

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

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

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

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

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

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

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

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

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

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

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

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

舉例：

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

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

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

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

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

舉例：

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

此時(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)然,也可以手動謂詞傳遞,last_ddl_time肯定大于等于DATE’2013-1-2′：

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

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

–由此兩數(shù)據(jù)比較可知,應(yīng)該走索引更佳.因?yàn)闆]有其他過濾條件,可以考慮建立函數(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)然,對于兩個都是范圍的查詢,這里只能通過一個列來輪詢索引,先做access,再做filter.

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

總結(jié)

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

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

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

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

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

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

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

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

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

 

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

 

三、總結(jié)

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

文章來自微信公眾號：DBAplus社群

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