• 周四. 10 月 3rd, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

警惕 Oracle 索引优化时陷阱之无效的索引范围扫描(INDEX RANGE SCAN)导致的全表扫描

King Wang

1 月 3, 2022

生产环境慢查询统计中,发现表 STATUS 的 MILESTONE 字段条件查询时进行了全表扫描。表 STATUS 的 MILESTONE 字段定义如下:
表 STATUS 的 MILESTONE 字段定义.png
针对上述问题创建索引:

create index STATUS_MILESTONE on STATUS("MILESTONE") tablespace DFS_INDEX2;

分析执行计划发现问题语句依然走的是全表扫描,新建的索引 STATUS_MILESTONE 没有用到。第一反应是索引是不是没有创建成功?
查看索引库发现该索引确实存在并且生效:
查看索引库发现该索引确实存在.png
查看 STATUS 表 DDL 也可以看到该索引的定义:
查看 STATUS 表 DDL 也可以看到该索引的定义.png
看来我们的索引被 Oracle 忽视了。Oracle 无视现有索引有很多种原因,但一般来讲有这两种原因:

  • 该 sql 的写法导致 Oracle 无法使用已有索引(最左前缀原则,请参考博客《B-Tree 索引和 Hash 索引的对比》)。
  • Oracle 基于成本的优化器选择不使用该索引。

测试库中 STATUS 表记录数:
测试库中 STATUS 表记录数.png
观察 STATUS 表 MILESTONE 字段相关数据特点,发现该字段的记录只有少数不同值的列:
该字段的记录只有少数不同值的列.png
现在我们以简单查询语句 select * from status S WHERE S.MILESTONE = ‘IFFDEL’ 来验证执行计划:
简单查询语句来验证执行计划.png
全表扫,cost 为 25。
强制优化器走索引 STATUS_MILESTONE 并分析其执行计划:
强制优化器走索引 STATUS_MILESTONE 并分析其执行计划.png
索引范围扫描,cost 61,比全表扫还昂贵。很明显本文描述问题命中上述不走索引的两大主流原因之“Oracle 基于成本的优化器选择不使用该索引”的情况。
如果创建索引时不进行显式说明,Oracle 默认创建的都是 B-tree 索引。也就是说上文中我们创建的索引 STATUS_MILESTONE 是一个 B-tree 索引。
B-tree 的优势在于大量不同的记录,而位图索引的优势在于相对于表中行总数而言只有少数不同值的列,所以本文 select * from status S WHERE S.MILESTONE = ‘IFFDEL’ 示例用位图索引效果会更好。重新创建索引如下:

drop index STATUS_MILESTONE;
create bitmap index STATUS_MILESTONE_BITMAP on STATUS("MILESTONE") tablespace DFS_INDEX2;

当然,在多字段检索复杂查询中,可能还是联合 B-tree 索引优势更明显。
总结:
造成无效的范围索引扫描有很多原因,除了本文中的少数不同值的情况之外,还可能存在以下可能:

  • 使用了范围谓词,如 <、>、LIKE 和 BETWEEN。
  • 只使用到了组合索引的部分列。

参考资料

  • The index is being ignored
  • Index Range Scans

发表回复