一、SQL無法走索引的情況及解決思路
因為數(shù)據(jù)庫優(yōu)化器不夠智能，或者一些邏輯原因，導(dǎo)致SQL在比較適合走索引的情況下卻無法正確利用索引。這時候，除了給數(shù)據(jù)庫需要的統(tǒng)計信息之外，SQL語句本身還必須要給優(yōu)化器足夠多的額外有效信息，幫助優(yōu)化器能夠選擇更好的執(zhí)行計劃。要讓優(yōu)化器正確選擇需要的索引，要考慮兩點：
-
如何避免優(yōu)化器的限制
-
根據(jù)業(yè)務(wù)數(shù)據(jù)特點改寫SQL語句
說明：這里說的走不了索引，是指走不了正常的RANGE SCAN，非(FAST) FULL INDEX SCAN。
SQL無法走索引常見的有如下8種情況：
-
統(tǒng)計信息不準確
-
索引列的值允許為NULL
-
謂詞使用了不等于（<>, !=）
-
LIKE前通配或全通配的查詢
-
索引列使用了函數(shù)、數(shù)學(xué)運算、其它表達式等
-
使用了隱式類型轉(zhuǎn)換
-
查詢轉(zhuǎn)換失敗
-
其它語句邏輯原因
第一、二種情況在現(xiàn)實中比較常見，解決辦法也相對比較簡單，下面就不再作詳細展開了。
謂詞使用了不等于(<>,!=)，走不了索引
解決方法：
-
如果不等條件之外的值不多，而且是確定的，可以改為等值或IN查詢，比如status狀態(tài)字段一般值類別很少；
-
如果不等條件之外的值很多，可以改為“> OR <”的形式，當(dāng)然第2種方法包含了方法1。
舉個例子，先構(gòu)建測試場景：
謂詞使用<>，無法利用索引：
將<>改寫為OR連接后，能夠正確使用索引，走OR擴展：
如果業(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)條件值，因為REVERSE內(nèi)部會按字節(jié)翻轉(zhuǎn)的，正確寫法如：
SELECT * FROM t WHERE REVERSE(t.name) LIKE REVERSE('數(shù)據(jù)')||'%';
否則查詢出來的數(shù)據(jù)不對，將可能影響到業(yè)務(wù)的正常運行。
索引列使用了函數(shù)、數(shù)學(xué)運算、其他表達式等，走不了索引
解決方法：去掉對索引列的相關(guān)運算，保持索引列純凈。
目前優(yōu)化器對一些數(shù)學(xué)運算，還無法做很好的消除動作，所以對于索引列應(yīng)該盡量保持純凈，否則可能無法用上正確的索引。
舉例：
把語句的條件改寫一下，將運算去掉：
以上例子只是簡單的數(shù)學(xué)運算，可能的運算還有和其他列運算，比如where ID+ext_col...
記住一個原則：盡量保持索引列純凈。
使用了隱式類型轉(zhuǎn)換，走不了索引
解決方法：必須避免隱式類型轉(zhuǎn)換，全部要求顯式類型轉(zhuǎn)換（非索引列），且避免對索引列進行類型轉(zhuǎn)換（有函數(shù)索引除外）。如果類型不一致，不管是否發(fā)生自動類型轉(zhuǎn)換，謂詞的右值應(yīng)該顯式轉(zhuǎn)換為與索引列保持一致（對于非索引列的運算也應(yīng)該如此）。
舉例：
從以上兩次查詢對比來看，第一次查詢發(fā)生了類型轉(zhuǎn)換，可以通過執(zhí)行計劃中的謂詞信息獲知。通過分析發(fā)現(xiàn)，X因為是VARCHAR2，優(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í)行計劃顯示不走索引，性能非常差。
在10g中的執(zhí)行計劃：
在11g中的錯誤執(zhí)行計劃：
通過收集統(tǒng)計信息都無效，將優(yōu)化器降級到10.2.0.4即有效。很顯然，這是引入了BUG或者新的限制。一旦遇到這種是BUG或限制導(dǎo)致的，可以通過10053跟蹤文件或者SQLT來進行分析。對于這條語句無法走JPPD查詢轉(zhuǎn)換，在10053中就可以找到原因：
然后在MOS中查看得知是BUG:9380298，默認開關(guān)關(guān)閉。
ORACLE針對這樣的查詢，為了防止遇到笛卡爾積，默認把修復(fù)BUG的補丁關(guān)閉了。顯然通過設(shè)置_fix_control參數(shù)打開9380298 fix即可。
語句邏輯問題，導(dǎo)致優(yōu)化器選擇不了索引
舉一個典型的例子，先準備測試表，并在其上創(chuàng)建一個組合索引：
查詢需求：查找創(chuàng)建時間是2013年的，并且最后ddl時間比創(chuàng)建時間大1天以上的對象。
這個索引是組合索引，上面的語句對前導(dǎo)列進行了運行，也不符合走index skip scan的條件，所以，走FULL TABLE SCAN。那么是否可以通過邏輯改寫走索引呢，基于保持索引列純凈的原則，將create_date移到右邊，語句如下：
改寫后發(fā)現(xiàn)，還是沒有走索引，因為Oracle認為前導(dǎo)列右邊的created不固定，無法從指定索引處查找。通過分析得知，Oracle謂詞傳遞有一定限制，create_date+1無法做謂詞傳遞給last_ddl_time。再次改寫：
此時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)該走索引更佳。因為沒有其他過濾條件，可以考慮建立函數(shù)索引：
SQL> CREATE INDEX idx1_t_object ON t_objects(last_ddl_time-created);
--注意收集直方圖，因為分布不均
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í)行計劃，從不好的計劃到好的計劃，比如semi join，anti join與or，往往走FILTER導(dǎo)致執(zhí)行計劃較差，這時候就需要通過邏輯等價改寫。邏輯等價改寫往往需要掌握一些集合的知識，比如NOT (A AND B)==NOT A OR NOT B，NOT (A OR B)==NOT A AND NOT B等。
總結(jié)
SQL有索引而不走索引的情況還有很多，比如在DBLINK查詢中，可能走不了索引，這時候需要通過driving_site hint或者遠程庫建立視圖等方式解決等，需要綜合從語法語義、索引選擇性、索引訪問特點等多方面進行分析。
二、如何將SQL開發(fā)規(guī)范落地
上面說到的問題，說到底都是不遵守數(shù)據(jù)庫開發(fā)規(guī)范的問題。說到數(shù)據(jù)庫開發(fā)規(guī)范，估計很多企業(yè)都有制定對應(yīng)的規(guī)范及要求，但說到落地執(zhí)行情況，這個就比較困難了。如果企業(yè)在意旨上是期望開發(fā)人員去學(xué)懂規(guī)范，然后學(xué)以致用，就有點太理想化了。于是，為了保證開發(fā)人員真的是按照數(shù)據(jù)庫開發(fā)規(guī)范來編寫代碼，很多企業(yè)就在應(yīng)用上線前增加了一道SQL上線審核的工序。
說到SQL上線審核，關(guān)鍵要解決三個問題：
1、如何在上線的應(yīng)用版本中發(fā)現(xiàn)新增的SQL語句；
2、新增SQL存在哪些問題，如何快速準確的定位；
3、對于問題SQL，如何快速提供優(yōu)化方案。
這三個問題，是一環(huán)扣一環(huán)的，解決不了前面的問題，就無從解決后面的問題。然而，應(yīng)用系統(tǒng)SQL眾多，如果單靠人工，難度是很大的，專家資源投入就更不說了，顯然不能滿足當(dāng)今IT系統(tǒng)高速發(fā)展的需要。
這里跟大家分享我們在這方面的一些實踐和成果。通過結(jié)合多年的運維和優(yōu)化經(jīng)驗，我們自主研發(fā)了SQL審核工具，不僅可以自動化完成SQL上線審核，還可以做到SQL的性能監(jiān)控和自動優(yōu)化，達到SQL全生命周期管理的效果。對于SQL上線審核，我們將開發(fā)規(guī)范規(guī)則化后落到SQL審核平臺，內(nèi)置了4個維度、200多種常見的審查規(guī)則，還支持靈活的按需添加規(guī)則。同時，審查的不只是SQL語句本身，還包括了對表的模型設(shè)計、索引的構(gòu)建。
在應(yīng)用新版本上線前，通過SQL審核平臺，自動分析出版本的新增SQL，基于以上規(guī)則對新增SQL進行審查，并自動提供優(yōu)化建議，可生成可視化的報表和詳細報告。不管是DBA還是開發(fā)人員都可以基于此平臺，對問題進行確認和解決，實現(xiàn)系統(tǒng)優(yōu)化前移、提升應(yīng)用版本質(zhì)量的目標。
三、總結(jié)
本文主要和大家分享了SQL無法走索引的一些常見情景及解決方法，當(dāng)然，SQL的規(guī)范化使用是十分重要的，SQL的優(yōu)化也不僅僅局限于索引的優(yōu)化。所以，只有平時多積累，結(jié)合理論多實踐，遇到問題時才能運籌帷幄，對癥下藥、藥到病除。另外，企業(yè)在IT建設(shè)中要重視開發(fā)規(guī)范的落地執(zhí)行，必要時使用合適的工具，在加速IT環(huán)境建設(shè)效率的同時，還能兼顧到IT系統(tǒng)的建設(shè)質(zhì)量，做到兩不誤。
