SQL 优化之唯一和普通索引区别

作者简介:孙显鹏,Oracle 十年从业经验,拥有11G ocp认证,精通内部原理,擅长调优,解决疑难问题,致力于帮助客户解决生产过程过出现的性能问题,提高生产效率!爱好书法!

现象:

客户反映核心系统慢要求分析原因,微信发送了AWR报告。通过分析AWR发现系统负载很低平均值5%,各类IO平均等待5ms以下,无异常等待,每秒redo较小。但是相对逻辑读较高,物理读写较小,cursor使用强制,绑定变量自然很好,主要问题聚焦在TOP get SQL 的第一位,同时也是时间维度的第一位,经过和客户沟通确定是该SQL影响了业务使用。接下来我们分析该SQL,优化SQL,通过分析优化SQL学习唯一索引和普通索引在等值条件下的区别。

分析过程:

逻辑读TOP SQL 信息:

SQL 文本信息:

获取该SQL目前的执行计划:

ORACLE 采取了全表扫描和过滤条件的算法。

看看统计信息:

从统计信息看dr列我没有列出该列只有两个不同值,且dr列存在函数,但是vsrcrowid列的选择率很高,客户疑问为什么没有采取索引扫描呢?我们强制让oracle采取该索引扫描看看执行计划:

我们分析下采用索引扫描的执行计划:

首先使用IDX_53001范围扫描,扫描行数达到1955k,接下来回表取数据行数达到977k,cost值高达1505k比全表扫103k高了许多,这就是oracle为什么不采取索引扫描的原因了。从统计信息分析知道vsrcrowid选择率很高呀,其实你忽略了聚合因子也高呀,并且该索引是普通索引等值条件是需要进行索引范围扫描的,那么也就是需要从索引的最左端扫描到具体值,竟然扫描了1955k行,也就说明范围扫描效率不高,扫描了太多数据块,范围扫描完接下来需要对已经扫描的数据进行第二个条件的过滤也就是执行计划ID为1的操作。不幸的是索引的聚合因子很高导致效率更差。(回忆什么是聚合因子: 索引扫描时两个rowid指向不同数据块那么聚合因子加1,有的人叫做集群因子我觉得这个叫法不合理,这个指标反映的是行在块里的密集度,叫做聚合因子较合适),这也正是ORACLE 为什么不采取索引扫描的底层原因。

解决方案:

好了,既然我们找到的根因,如何处理问题那么方式较多了。我们可以重建该表,建表时以该字段排序,这样就可以降低聚合因子。但是我们发现该字段不同值基本上和行数相同,如果有可能将该索引修改为唯一索引那么定位很快效率将会极大提升,并且不会因为时间推移需要再次重建表以字段排序的麻烦,一次性解决问题。将处理方案发送给客户,客户的应用通过协商觉得可以保证该字段的唯一性,并且很给力的将3000多个null值立马修改。接下来就是删除原索引的建立唯一索引。看看建立唯一索引后的执行计划:

执行时间从原来的2s多变成了0.19s,cost值为4,ORACLE 自动选择了索引扫描,效率极大提高。

这里需要解析唯一索引和普通索引在等值条件下为什么差距如此大,这个是解决该问题的关键因素。唯一索引等值条件需要扫描的块为索引高度+1或者+2(如果存在行链接),可想而知这种索引是最高效的方式,我们优化SQL的最终目标就是尽可能降低扫描块的数量。那么普通索引的等值查询是需要索引范围扫描的,这种情况下扫描的块和指定的值有关系,最高效就是指定的值正是最小值,最差情况就是指定的值是最大值需要扫描所有叶块然后回表取数据。

说到这里我想说下,其实研究底层的原理是非常有必要的,只有你熟悉了每个原理比如我们上面提到的每个索引的扫描方式,这样在遇到问题时才能高效处理问题。接下来我给大家提供一个问题思考:

下面这张图是MOS 关于检查点介绍中的一张图:


我的问题是,DBWR写进程是只写CKPT链表的脏buffer,还是DBWR也需要写LURW 辅助链表上的脏buffer?通过上面这张图其实是可以看出来的。在以后的文章中我会分析该图,这里我们先讨论。

未经允许不得转载:Oracle一体机用户组 » SQL 优化之唯一和普通索引区别

相关推荐