ITPub博客

探索索引的奥秘 - 有索引就一定会用么?

原创 Oracle 作者:bisal 时间:2017-12-19 15:18:00 1 删除 编辑

上一篇文章《探索索引的奥秘 - 索引的属性》,我们了解了索引的属性,回顾一下,

> 索引设置为unusable,会有以下特点,

    1. 索引设置为unusable,此时会删除索引段。

    2. 索引处于unusable期间,对表数据做DML操作,此时不维护索引。

    3. 索引处于unusable期间,优化器会忽略此索引。

    4. 索引处于unusable期间,由于不需要维护索引,因此可以提升批量导入性能。

    5. 索引unusable变为usable,有两种方法,一种是删除-重建索引,一种是使用alter index ... rebuild,两种方法,都相当于重新构建了索引。

> 索引设置为invisible,会有以下特点,


    1. 索引设置为invisible,不会删除索引段。

    2. 索引处于invisible期间,对表数据做DML操作,此时会维护索引。

    3. 索引处于invisible期间,优化器会忽略此索引。

    4. 索引invisible变为visible,直接使用alter index ... visible。
> unusable比invisible优先级要高,同时设置,起作用的是unusable。

> 只有函数索引可以设置disable和enable,涉及函数索引维护的操作,会被禁止,且执行计划,不会用这索引。


关于索引,还有一些存在模糊的知识点,这篇文章我们关注的是,是否有索引,就一定会用索引?


我们时常会碰见这种问题,

我们创建了索引,但为什么SQL未使用这个索引?


Oracle 10g之前默认的优化器模式,是RBO,数据的访问效率,会参考一些规则,说白了就是一些硬编码,定义了优先级,优先级高的,认为效率就高,例如索引就比全表扫描效率高,如下是优先级1-15的列表,最快的是基于ROWID的访问,最慢的则是全表扫描,


但Oracle 10g开始,优化器默认模式就是CBO了,C表示的就是Cost,即以成本为依据,结合对象的统计信息,谁的成本值低,谁的效率就高,相比RBO,这样更科学些,当然有些前提,例如统计信息要准确。


我们对一张表建立了索引,但并不代表SQL一定会用索引,究其原因可能有很多种情况,下面列举出两个场景,对于这样的问题,尝试提供一些思路和方法。



场景一:正确的有索引却不用


创建测试表,插入一条数据,创建索引,采集表和索引的统计信息,USER_TABLES视图显示有1条记录,平均行长为14字节。


执行update语句,条件是索引字段id,执行计划显示,对表的扫描,用全表扫描而不是索引扫描,


如果各位对索引的结构,比较了解的话,就比较容易理解其原因了,我们此处用的是BTree索引,即平衡二叉树索引,他的结构类似一棵树形,有根节点、分支节点,以及叶子结点,唯一索引和非唯一索引,叶子结点存储的信息会略有不同,我们此处建立的是非唯一索引,因此叶子结点中存储的,则是索引字段键值,以及对应的rowid,rowid是一个伪列,通过他可以快速定位,一条记录对应的物理位置,因为他的信息包括了,这条记录对应的文件号、块号、行号等信息,rowid的访问CBO时代他的优先级是最高的,关于rowid,内容其实还是很丰富的,有机会我们再聊。


再说索引结构,为什么说索引快,主要就是因为索引的查找,就是以这棵树的根节点开始,找分支节点,如果等值查询,则可以直接定位到具体的叶子结点,如果是范围查询,因为叶子结点是排序的,因此只要找出起始节点,按照叶子结点的指针,就可以找出对应结果集,无论何种用法,我们可以看出,他的执行路径都是有限的,根节点-分支节点-叶子结点,而且即使表的数据量再增加,只要索引数层级不变,其消耗的代价就是稳定的,而全表扫描,则会随着表数据量的增加,高水位不断上升,导致增加的成本消耗。


但一些情况下,索引扫描效率未必高,比如上面实验,因为要是SQL语句需要的数据,除了索引字段外,还有其他字段,则首先使用索引扫描,定位叶子结点,根据其中存储的rowid,回表找出对应的其他字段信息,而且若是INDEX RANGE SCAN这种索引范围扫描,会是单块读,而全表扫描则是多块读,相比之下,1次IO读的数据块数量就不同,对应的数据量就不同,效率就会不同,如果使用全表扫描,由于只有1条记录,则可以1次IO就完成数据读取。如果使用索引扫描,则先要消耗IO扫描索引,再回表消耗IO读取数据,成本高于全表。


虽然此处用了1条记录测试,有些极端,但即使有很多记录,还是需要综合考虑多块读、单块读、表的记录数、平均行长、回表等各种因素,只要TABLE ACCESS FULL的成本值低,无论是否有索引,都会选择TABLE ACCESS FULL。如果要用科学的数据,则可以做一个10053事件,就可以看出全表扫描和索引扫描两种方法对应的成本计算过程和结果,了解Oracle自己的选择。




场景二:错误的有索引却不用


我们接着插入10000条记录,但不执行统计信息更新,USER_TABLES视图显示表只有1条记录,可实际此时应该有10001条记录了。