生产环境慢查询统计中,发现表 STATUS 的 MILESTONE 字段条件查询时进行了全表扫描。表 STATUS 的 MILESTONE 字段定义如下:
针对上述问题创建索引:
create index STATUS_MILESTONE on STATUS("MILESTONE") tablespace DFS_INDEX2;
分析执行计划发现问题语句依然走的是全表扫描,新建的索引 STATUS_MILESTONE 没有用到。第一反应是索引是不是没有创建成功?
查看索引库发现该索引确实存在并且生效:
查看 STATUS 表 DDL 也可以看到该索引的定义:
看来我们的索引被 Oracle 忽视了。Oracle 无视现有索引有很多种原因,但一般来讲有这两种原因:
- 该 sql 的写法导致 Oracle 无法使用已有索引(最左前缀原则,请参考博客《B-Tree 索引和 Hash 索引的对比》)。
- Oracle 基于成本的优化器选择不使用该索引。
测试库中 STATUS 表记录数:
观察 STATUS 表 MILESTONE 字段相关数据特点,发现该字段的记录只有少数不同值的列:
现在我们以简单查询语句 select * from status S WHERE S.MILESTONE = ‘IFFDEL’ 来验证执行计划:
全表扫,cost 为 25。
强制优化器走索引 STATUS_MILESTONE 并分析其执行计划:
索引范围扫描,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