Oracle Index 索引无效原因

电玩女神 2022-12-27 09:25 181阅读 0赞

## 索引无效原因 ##

> 最近遇到一个SQL语句的性能问题，修改功能之前的运行时间平均为0.3s，可是添加新功能后，时间达到了4~5s。虽然几张表的数据量都比较大（都在百万级以上），但是也都有正确创建索引，不知道到底慢在了哪里，下面展开调查。

经过几次排除，把问题范围缩小在索引上，首先在确定索引本身没有问题的前提下，考虑索引有没有被使用到，那么新的问题来了，怎么知道指定索引是否被启用。

### 判断索引是否被执行 ###

1. 分析索引

即将索引至于监控状态下，对索引进行分析。如下对`ID_TT_SHOHOU_HIST_002` 索引进行分析

alter index ID_TT_SHOHOU_HIST_002 monitoring usage;

2. 查看v$object\_usage视图中记录的信息

select * from v$object_usage;

![详细信息][92abed46af2497858084f69901a0760d.png]  
字段依次为：

*  INDEX\_NAME --索引名
 *  TABLE\_NAME --表名
 *  MONITORING --是否被监控
 *  **USED** --是否被启用
 *  START\_MONITORING --监控开始时间
 *  END\_MONITORING --监控结束时间

> 如上图，虽然索引已经被引用，但是速度依旧很慢，莫非是虽然启用了索引，但是又被其他的一些原因拖慢了速度，继续调查。  
> 调查途中，收集到一些Oracle 数据库不走索引的原因分享给大家

### 不走索引的原因 ###

1. 在索引列上使用函数时不会使用索引

例如常见的，`TO_CHAR`、`TO_DATE`、`TO_NUMBER`、`TRUNC` ...等等。  
此时的解决办法可以使用**函数索引**，顾名思义就是把使用函数后的字段整体当成索引中的字段。  
如下图中的`TO_CHAR(SHOHOU_DATE, 'YYYYMMDD')`就是一个函数索引，因为日期字段中含有时分秒，进行日期比较的时候，必须转化成固定的格式。

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

2. 索引的列进行隐式的类型转换

SELECT * FROM TABLE WHERE INDEX_COLUM = 5

上面语句中的`INDEX_COLUM`字段类型为`VARCHAR2`，这时就会发生隐式类型转换，类似于

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

替代方式：函数索引  
通过`nvl(b,c)`将为空的字段转为不为空的c值，再在函数nvl(b,c)上建立函数索引  
转换前

SELECT * FROM A WHERE B = NULL

转换后

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

5. 组合索引

组合索引：由多个列构成的索引。如

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

`INDEX_EMP`则为复合索引，`COL1`为引导列。进行查询时，可以使用`WHERE COL1 = ?`，也可以使用`WHERE COL1 = ? AND COL2 = ?`，这样的限制条件都会使用索引，但是`WHERE COL2 = ?`，不会使用索引，所以限制条件中包含引导列时，该限制条件才会使用组合索引。

> 经过一番调查，我使用的SQL语句检索条件中对时间列进行`TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD')`格式化日期，去除掉时分秒。再建立函数索引后仍然没有起到优化加速的效果，仔细观察发现在使用TO\_CHAR格式化时间之后，又进行TO\_DATE转为时间格式和其他子查询的字段进行比较。然后很快想到，建立一个`TO_DATE(TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD'), 'YYYYMMDD')`这样的函数索引，结果缺失提高了不少的运行速度，从4~5s缩短到了0.5s左右。

> 但是这只是在PL/SQL软件中运行SQL提高了速度，实际项目运行仍然是4~5s，使用语句查看索引的使用状况时，发现并没有使用索引，但是在PL/SQL软件中确实调用了索引，这至今都是未解之谜，如果有大神知道原因希望能帮我解答一下这个疑问。

> 既然不能自动调用，只能强制让SQL走指定索引了，强制的方法如下

在`SELECT`语句后加入`/*+INDEX(TTSH ID_TT_SHOHOU_HIST_002)*/`，其中`TTSH`是表的别名（当表有别名的时候，必须在索引前加入表的别名）

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的效率问题已经解决了，但是这不是最好的解决方案。  
> 首先，目前的索引中已经存在包含`TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD')`的函数索引，又再创建一个`TO_DATE(TO_CHAR(TTSH.SHOHOU_DATE, 'YYYYMMDD'), 'YYYYMMDD')`，看着就很难受  
> 其次，强制使用索引的方法需要在SQL中指定索引名，假如数据库中的索引名发生变更，还需去更改SQL。  
> 最好的方法是把索引字段的TO\_DATE去掉，统一使用TO\_CHAR的索引。

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

上面的部分语句因为`CALENDER`字段是DATE类型，所以比较时使用了TO\_DATE，其实只要把`CALENDER`转化成CHAR类型就行了，虽然看起来要改动的地方很多，其实解决了更大的问题。

### 参考博客地址： ###

[https://blog.csdn.net/u010662668/article/details/61920199][https_blog.csdn.net_u010662668_article_details_61920199]  
[https://blog.csdn.net/chenjian98306/article/details/50331803][https_blog.csdn.net_chenjian98306_article_details_50331803]  
[https://www.cnblogs.com/jianggc/articles/2029854.html][https_www.cnblogs.com_jianggc_articles_2029854.html]

[92abed46af2497858084f69901a0760d.png]: /images/20221120/33d0294099184ec391fb3e3c79cce5b8.png
[https_blog.csdn.net_u010662668_article_details_61920199]: https://blog.csdn.net/u010662668/article/details/61920199
[https_blog.csdn.net_chenjian98306_article_details_50331803]: https://blog.csdn.net/chenjian98306/article/details/50331803
[https_www.cnblogs.com_jianggc_articles_2029854.html]: https://www.cnblogs.com/jianggc/articles/2029854.html