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

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

> 索引设置为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一定会用索引，究其原因可能有很多种情况，下面列举出两个场景，对于这样的问题，尝试提供一些思路和方法。

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

执行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自己的选择。

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

我们执行comment表操作，让Oracle重新生成执行计划，但发现还是采用了全表扫描，

其实我们就可以看出问题，TABLE ACCESS FULL会扫描所有数据，但此处Rows值是1，说明Oracle认为表记录只有1条，自然TABLE ACCESS FULL是比较合适的选择，无可厚非。

接下来我们用一个11g推出的工具，STA(SQL Tuning Advisor)，来看看此时Oracle可以给我们什么建议，首先创建任务，其中sql_id是我们执行update语句对应的sqlid，

接着执行report_tuning_task输出建议结果，请注意要是不设置开始的set，则可能结果显示为空，

内容如下，表示Oracle对这条SQL有两个建议，

第一个建议是，手工采集表和索引的统计信息，并且给出了SQL语句，

第二个建议，则是使用SQL Profile，固定执行计划，

并且给出了按照原始SQL，以及使用了SQL Profile的SQL，各执行10次的统计信息平均值数据，原始SQL用的TABLE ACCESS FULL，

使用SQL Profile的SQL，用的索引扫描INDEX RANGE SCAN，

可以看出，通过SQL Tuning Advisor，可以让Oracle来提供一些优化建议，并且直接给出了一些方法SQL，能辅助我们进行优化工作。

接下来针对实验问题，我们采用手工收集统计信息，再次执行，就会发现SQL用了索引范围扫描，相应地可以看10053事件，就会发现索引的成本，此时就会低于TABLE ACCESS FULL，

总结：

1. CBO时代，并不是有了索引，就一定会用索引，能不能用上，需要看谁的成本更低，影响成本值计算的因素很多，本文的问题，只有1条记录的时候，不用索引是对的，因为多块读的全表扫描，成本低于单块读的索引扫描(需要回表)，但当有10001条记录的时候，不用索引就是错误的了，原因就是由于统计信息不准，造成Oracle计算成本值出现偏差，此时要么手工采集统计信息，要么使用SQL Profile固化执行计划，当然有索引但不用的场景，还有其他的因素，具体问题具体分析。

2. 像本文中，灌入大量数据，此时需要手工收集统计信息，才能保证Oracle估算成本值的正确，虽然Oracle有自动收集统计信息的job，但前提是要求这张表，当日的增删改数据量超过表总量的10%(参数可以调整)，或者执行过truncate操作，可以参考dbsnake的书，而且每晚定时才能执行，因此之前这段时间其实是有可能使用了错误的执行计划，这就会有一些副作用。

3. SQL Tuning Advisor工具，可以让Oracle为我们优化SQL提出一些建议，自动化指出一些方向，还是比较有用的一种方法。

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)