Oracle Index索引無效的原因與解決方法

索引無效原因

最近遇到一個(gè)Oracle SQL語句的性能問題，修改功能之前的運(yùn)行時(shí)間平均為0.3s，可是添加新功能后，時(shí)間達(dá)到了4~5s。雖然幾張表的數(shù)據(jù)量都比較大（都在百萬級(jí)以上），但是也都有正確創(chuàng)建索引，不知道到底慢在了哪里，下面展開調(diào)查。

經(jīng)過幾次排除，把問題范圍縮小在索引上，首先在確定索引本身沒有問題的前提下，考慮索引有沒有被使用到，那么新的問題來了，怎么知道指定索引是否被啟用。

判斷索引是否被執(zhí)行

1. 分析索引

即將索引至于監(jiān)控狀態(tài)下，對(duì)索引進(jìn)行分析。如下對(duì) ID_TT_SHOHOU_HIST_002 索引進(jìn)行分析

alter index ID_TT_SHOHOU_HIST_002 monitoring usage;

2. 查看v$object_usage視圖中記錄的信息

select * from v$object_usage;

字段依次為：

•INDEX_NAME --索引名

•TABLE_NAME --表名

•MONITORING --是否被監(jiān)控

• USED --是否被啟用

•START_MONITORING --監(jiān)控開始時(shí)間

•END_MONITORING --監(jiān)控結(jié)束時(shí)間

如上圖，雖然索引已經(jīng)被引用，但是速度依舊很慢，莫非是雖然啟用了索引，但是又被其他的一些原因拖慢了速度，繼續(xù)調(diào)查。

調(diào)查途中，收集到一些Oracle 數(shù)據(jù)庫不走索引的原因分享給大家

不走索引的原因

1. 在索引列上使用函數(shù)時(shí)不會(huì)使用索引

例如常見的， TO_CHAR 、 TO_DATE 、 TO_NUMBER 、 TRUNC ...等等。

此時(shí)的解決辦法可以使用 函數(shù)索引 ，顧名思義就是把使用函數(shù)后的字段整體當(dāng)成索引中的字段。

如下圖中的 TO_CHAR(SHOHOU_DATE, 'YYYYMMDD') 就是一個(gè)函數(shù)索引，因?yàn)槿掌谧侄沃泻袝r(shí)分秒，進(jìn)行日期比較的時(shí)候，必須轉(zhuǎn)化成固定的格式。

CREATE INDEX ID_TT_SHOHOU_HIST_003
ON TT_SHOHOU_HIST
(DEL_FLG,TO_CHAR(SHOHOU_DATE, 'YYYYMMDD'), SHOHOU_ID)
TABLESPACE SALESPA_INDEX

2. 索引的列進(jìn)行隱式的類型轉(zhuǎn)換

SELECT * FROM TABLE WHERE INDEX_COLUM = 5

上面語句中的 INDEX_COLUM 字段類型為 VARCHAR2 ，這時(shí)就會(huì)發(fā)生隱式類型轉(zhuǎn)換，類似于

SELECT * FROM TABLE WHERE TO_NUMBER(INDEX_COLUM) = 5

3. WHERE 子句中使用不等于操作

不等于操作包括： > , != , NOT colum >= ? , NOT colum = ?

替代方式可以使用OR， colum > 0 =====> colum > 0 or colum 0;

4. 使用 IS NULL 和 IS NOT NULL

替代方式：函數(shù)索引

通過 nvl(b,c) 將為空的字段轉(zhuǎn)為不為空的c值，再在函數(shù)nvl(b,c)上建立函數(shù)索引

轉(zhuǎn)換前

SELECT * FROM A WHERE B = NULL

轉(zhuǎn)換后

SELECT * FROM A WHERE NVL(B,C) = C

5. 組合索引

組合索引：由多個(gè)列構(gòu)成的索引。如

CREATE INDEX INDEX_EMP ON EMP (COL1,COL2,COL3,...)

INDEX_EMP 則為復(fù)合索引， COL1 為引導(dǎo)列。進(jìn)行查詢時(shí)，可以使用 WHERE COL1 = ? ，也可以使用 WHERE COL1 = ? AND COL2 = ? ，這樣的限制條件都會(huì)使用索引，但是 WHERE COL2 = ? ，不會(huì)使用索引，所以限制條件中包含引導(dǎo)列時(shí)，該限制條件才會(huì)使用組合索引。

經(jīng)過一番調(diào)查，我使用的SQL語句檢索條件中對(duì)時(shí)間列進(jìn)行 TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD') 格式化日期，去除掉時(shí)分秒。再建立函數(shù)索引后仍然沒有起到優(yōu)化加速的效果，仔細(xì)觀察發(fā)現(xiàn)在使用TO_CHAR格式化時(shí)間之后，又進(jìn)行TO_DATE轉(zhuǎn)為時(shí)間格式和其他子查詢的字段進(jìn)行比較。然后很快想到，建立一個(gè) TO_DATE(TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD'), 'YYYYMMDD') 這樣的函數(shù)索引，結(jié)果缺失提高了不少的運(yùn)行速度，從4~5s縮短到了0.5s左右。

但是這只是在PL/SQL軟件中運(yùn)行SQL提高了速度，實(shí)際項(xiàng)目運(yùn)行仍然是4~5s，使用語句查看索引的使用狀況時(shí)，發(fā)現(xiàn)并沒有使用索引，但是在PL/SQL軟件中確實(shí)調(diào)用了索引，這至今都是未解之謎，如果有大神知道原因希望能幫我解答一下這個(gè)疑問。

既然不能自動(dòng)調(diào)用，只能強(qiáng)制讓SQL走指定索引了，強(qiáng)制的方法如下

在 SELECT 語句后加入 /*+INDEX(TTSH ID_TT_SHOHOU_HIST_002)*/ ，其中 TTSH 是表的別名（當(dāng)表有別名的時(shí)候，必須在索引前加入表的別名）

SELECT /*+INDEX(TTSH ID_TT_SHOHOU_HIST_002)*/ 
TO_DATE(TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD'), 'YYYYMMDD') AS SHOHOU_DATE 
FROM TT_SHOHOU_HIST TTSH
WHERE ...

至此，SQL的效率問題已經(jīng)解決了，但是這不是最好的解決方案。

首先，目前的索引中已經(jīng)存在包含 TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD') 的函數(shù)索引，又再創(chuàng)建一個(gè) TO_DATE(TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD'), 'YYYYMMDD') ，看著就很難受

其次，強(qiáng)制使用索引的方法需要在SQL中指定索引名，假如數(shù)據(jù)庫中的索引名發(fā)生變更，還需去更改SQL。

最好的方法是把索引字段的TO_DATE去掉，統(tǒng)一使用TO_CHAR的索引。

AND CAL.CALENDER = TO_DATE(TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD'), 'YYYYMMDD')

上面的部分語句因?yàn)?CALENDER 字段是DATE類型，所以比較時(shí)使用了TO_DATE，其實(shí)只要把 CALENDER 轉(zhuǎn)化成CHAR類型就行了，雖然看起來要改動(dòng)的地方很多，其實(shí)解決了更大的問題。

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問大家可以留言交流，謝謝大家對(duì)腳本之家的支持。