记一次排查索引失效的过程

最新推荐文章于 2025-09-04 11:31:15 发布

原创最新推荐文章于 2025-09-04 11:31:15 发布 · 226 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #oracle

文章讲述了通过分析SQL查询的执行计划，发现全表扫描导致查询缓慢。在联表查询中，由于使用了函数导致索引未被利用。通过强制使用索引、检查和重建索引、调整SQL语句，最终解决了问题，将查询时间从1.6秒降低到0.14秒。

原sql：

select * from (
select a.*, ROWNUM rn from (
select ez.*,pr.WHIR_DEALING_USERS,pr.WHIR_STATUS
from WHIR$T3589 ez
inner join ORG_EMPLOYEE oe on ez.WHIR$T3589_OWNER = oe.EMP_ID
order by  ez.WHIR$T3589_ID desc
) a where ROWNUM &lt;= #{pageSize}
) b where rn >= #{pageNo}

执行时间0.05秒

后联表查询，添加语句

left join EZ_FLOW_HI_PROCINST pr on ez.WHIR$T3589_ID = pr.BUSINESS_KEY_

全sql为：

select * from (
        select a.*, ROWNUM rn from (
        select ez.*,pr.WHIR_DEALING_USERS,pr.WHIR_STATUS
        from WHIR$T3589 ez
        inner join ORG_EMPLOYEE oe on ez.WHIR$T3589_OWNER = oe.EMP_ID
        inner join EZ_FLOW_HI_PROCINST pr on TO_CHAR(ez.WHIR$T3589_ID) = pr.BUSINESS_KEY_
        order by ez.WHIR$T3589_ID desc
        ) a where ROWNUM <= 10
        ) b where rn >= 1

执行时间1.6秒

排查过程：

首先使用explain plan for

EXPLAIN PLAN FOR
select * from (
        select a.*, ROWNUM rn from (
        select ez.*,pr.WHIR_DEALING_USERS,pr.WHIR_STATUS
        from WHIR$T3589 ez
        inner join ORG_EMPLOYEE oe on ez.WHIR$T3589_OWNER = oe.EMP_ID
        inner join EZ_FLOW_HI_PROCINST pr on TO_CHAR(ez.WHIR$T3589_ID) = pr.BUSINESS_KEY_
        order by ez.WHIR$T3589_ID desc
        ) a where ROWNUM <= 10
        ) b where rn >= 1;

               SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

结果如下图：分析出慢的原因为新联的表是一张60万数据的表，并且查询时没走索引，而是全表查询

首先，既然没用索引，我选择强制使用索引

由于强制使用索引需要表名和索引名，我先查询索引名

SELECT i.index_name, i.index_type,i.status,i.last_analyzed, leaf_blocks, distinct_keys, num_rows, c.column_name, c.column_position
FROM user_ind_columns c
JOIN user_indexes i ON c.index_name = i.index_name
WHERE c.table_name = 'EZ_FLOW_HI_PROCINST';

强制使用索引语法：/*+ index(表名索引名)*/

/*= index(EZ_FLOW_HI_PROCINST ACT_IDX_HI_PRO_I_BUSKEY) */

可以看到依然没用用索引，还是全表扫描

我怀疑索引失效了，查询失效索引，想着如果索引失效就重建索引或者重新编译索引

重新编译索引：在Oracle数据库中，可以使用ALTER INDEX...REBUILD语句来重建索引。这个操作会将索引中的所有数据都重新组织，以便提高查询性能。例如，可以使用以下语句重建名为index_name的索引：

ALTER INDEX index_name REBUILD;

查询失效索引语句：

status：状态为“INVALID”，则该索引已失效。状态为“VALID”，则该索引未失效。

现在发现索引未失效

怀疑是因为索引碎片导致索引失效，索引碎片：如果索引碎片严重，可能导致查询性能下降或者索引无法使用。

如果leaf_blocks的值很大，而distinct_keys和num_rows的值很小，可能存在索引碎片。

但是根据查询结果并没有索引碎片

怀疑是sql语句使用不当导致索引失效，检查sql：

在索引列上执行函数或计算操作的查询语句。例如：

sql

```
SELECT * FROM mytable WHERE UPPER(name) = 'JIM'; 
```
如果 name 列有一个索引，那么上述查询将导致该索引失效。
在索引列上使用 LIKE 操作符的查询语句，如果查询的字符串以通配符开头，则索引也可能失效。例如：

sql
SELECT * FROM mytable WHERE name LIKE '%Jim';

如果 name 列有一个索引，那么上述查询也将导致该索引失效。
使用 OR 操作符的查询语句，如果其中一个操作不在索引列上，则整个查询可能不使用索引。例如：
sql
```
SELECT * FROM mytable WHERE name = 'Jim' OR age = 30; 
```
如果 name 列有一个索引，但 age 列没有索引，则上述查询可能不使用索引。
在索引列上使用不等于 (<>) 操作符的查询语句。例如：

sql
```
SELECT * FROM mytable WHERE name <> 'Jim'; 
```
如果 name 列有一个索引，那么上述查询可能不使用索引。
在索引列上使用 NOT IN 或 NOT EXISTS 子查询的查询语句。例如：
```
SELECT * FROM mytable WHERE name NOT IN (SELECT name FROM other_table);
```

我检查了sql，没有以上问题，所以不是sql的原因

怀疑是字段类型的原因

在联表查询中，如果要使用索引，通常需要保证参与联表查询的字段的数据类型相同或兼容。如果参与联表查询的字段的数据类型不同，那么在查询时可能会发生数据类型转换，从而导致索引失效。

原因找到了，使用函数对其进行转化，联表语句改为：inner join EZ_FLOW_HI_PROCINST pr on TO_CHAR(ez.WHIR$T3589_ID) = pr.BUSINESS_KEY_

注：由于sql使用函数的列不能使用索引，所以必须将使用转换函数的列设定为小表，WHIR$T3589只有300多条数据，所以使用TO_CHAR(ez.WHIR$T3589_ID)转化小表的字段

最终结果：

走索引后，执行时间0.14秒，成功