一、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種情況：
-
統(tǒng)計(jì)信息不準(zhǔn)確
-
索引列的值允許為NULL
-
謂詞使用了不等于（<>, !=）
-
LIKE前通配或全通配的查詢
-
索引列使用了函數(shù)、數(shù)學(xué)運(yùn)算、其它表達(dá)式等
-
使用了隱式類型轉(zhuǎn)換
-
查詢轉(zhuǎn)換失敗
-
其它語句邏輯原因
第一、二種情況在現(xiàn)實(shí)中比較常見，解決辦法也相對(duì)比較簡單，下面就不再作詳細(xì)展開了。
謂詞使用了不等于(<>,!=)，走不了索引
解決方法：
-
如果不等條件之外的值不多，而且是確定的，可以改為等值或IN查詢，比如status狀態(tài)字段一般值類別很少；
-
如果不等條件之外的值很多，可以改為“> OR <”的形式，當(dāng)然第2種方法包含了方法1。
舉個(gè)例子，先構(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ù)索引，否則對(duì)于全通配問題最好辦法就是全文索引。
創(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)部會(huì)按字節(jié)翻轉(zhuǎn)的，正確寫法如：
SELECT * FROM t WHERE REVERSE(t.name) LIKE REVERSE('數(shù)據(jù)')||'%';
否則查詢出來的數(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)算，還無法做很好的消除動(dòng)作，所以對(duì)于索引列應(yīng)該盡量保持純凈，否則可能無法用上正確的索引。
舉例：
把語句的條件改寫一下，將運(yù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ì)比來看，第一次查詢發(fā)生了類型轉(zhuǎn)換，可以通過執(zhí)行計(jì)劃中的謂詞信息獲知。通過分析發(fā)現(xiàn)，X因?yàn)槭荲ARCHAR2，優(yōu)先級(jí)比數(shù)值類型低，遇到數(shù)值類型，會(huì)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查詢組成的視圖，這個(gè)查詢?cè)?0.2.0.4上很正常，升級(jí)到11.2.0.4后執(zhí)行計(jì)劃顯示不走索引，性能非常差。
在10g中的執(zhí)行計(jì)劃：
在11g中的錯(cuò)誤執(zhí)行計(jì)劃：
通過收集統(tǒng)計(jì)信息都無效，將優(yōu)化器降級(jí)到10.2.0.4即有效。很顯然，這是引入了BUG或者新的限制。一旦遇到這種是BUG或限制導(dǎo)致的，可以通過10053跟蹤文件或者SQLT來進(jìn)行分析。對(duì)于這條語句無法走JPPD查詢轉(zhuǎn)換，在10053中就可以找到原因：
然后在MOS中查看得知是BUG:9380298，默認(rèn)開關(guān)關(guān)閉。
ORACLE針對(duì)這樣的查詢，為了防止遇到笛卡爾積，默認(rèn)把修復(fù)BUG的補(bǔ)丁關(guān)閉了。顯然通過設(shè)置_fix_control參數(shù)打開9380298 fix即可。
語句邏輯問題，導(dǎo)致優(yōu)化器選擇不了索引
舉一個(gè)典型的例子，先準(zhǔn)備測試表，并在其上創(chuàng)建一個(gè)組合索引：
查詢需求：查找創(chuàng)建時(shí)間是2013年的，并且最后ddl時(shí)間比創(chuàng)建時(shí)間大1天以上的對(duì)象。
這個(gè)索引是組合索引，上面的語句對(duì)前導(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)然，也可以手動(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)榉植疾痪?nbsp;
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è)都是范圍的查詢，這里只能通過一個(gè)列來輪詢索引，先做access，再做filter。
SQL語句的邏輯改寫很重要，往往通過邏輯改寫就能改變SQL的執(zhí)行計(jì)劃，從不好的計(jì)劃到好的計(jì)劃，比如semi join，anti join與or，往往走FILTER導(dǎo)致執(zhí)行計(jì)劃較差，這時(shí)候就需要通過邏輯等價(jià)改寫。邏輯等價(jià)改寫往往需要掌握一些集合的知識(shí)，比如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è)都有制定對(duì)應(yīng)的規(guī)范及要求，但說到落地執(zhí)行情況，這個(gè)就比較困難了。如果企業(yè)在意旨上是期望開發(fā)人員去學(xué)懂規(guī)范，然后學(xué)以致用，就有點(diǎn)太理想化了。于是，為了保證開發(fā)人員真的是按照數(shù)據(jù)庫開發(fā)規(guī)范來編寫代碼，很多企業(yè)就在應(yīng)用上線前增加了一道SQL上線審核的工序。
說到SQL上線審核，關(guān)鍵要解決三個(gè)問題：
1、如何在上線的應(yīng)用版本中發(fā)現(xiàn)新增的SQL語句；
2、新增SQL存在哪些問題，如何快速準(zhǔn)確的定位；
3、對(duì)于問題SQL，如何快速提供優(yōu)化方案。
這三個(gè)問題，是一環(huán)扣一環(huán)的，解決不了前面的問題，就無從解決后面的問題。然而，應(yīng)用系統(tǒng)SQL眾多，如果單靠人工，難度是很大的，專家資源投入就更不說了，顯然不能滿足當(dāng)今IT系統(tǒng)高速發(fā)展的需要。
這里跟大家分享我們?cè)谶@方面的一些實(shí)踐和成果。通過結(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上線審核，我們將開發(fā)規(guī)范規(guī)則化后落到SQL審核平臺(tái)，內(nèi)置了4個(gè)維度、200多種常見的審查規(guī)則，還支持靈活的按需添加規(guī)則。同時(shí)，審查的不只是SQL語句本身，還包括了對(duì)表的模型設(shè)計(jì)、索引的構(gòu)建。
在應(yīng)用新版本上線前，通過SQL審核平臺(tái)，自動(dòng)分析出版本的新增SQL，基于以上規(guī)則對(duì)新增SQL進(jìn)行審查，并自動(dòng)提供優(yōu)化建議，可生成可視化的報(bào)表和詳細(xì)報(bào)告。不管是DBA還是開發(fā)人員都可以基于此平臺(tái)，對(duì)問題進(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)籌帷幄，對(duì)癥下藥、藥到病除。另外，企業(yè)在IT建設(shè)中要重視開發(fā)規(guī)范的落地執(zhí)行，必要時(shí)使用合適的工具，在加速IT環(huán)境建設(shè)效率的同時(shí)，還能兼顧到IT系統(tǒng)的建設(shè)質(zhì)量，做到兩不誤。
