本文翻译自Jonathan Lewis发表在DBAZine上的文章:Why Isn’t Oracle Using My Index?!,可以从此处下载本文的Word版本.

原文参见: 为什么Oracle不使用我的索引?!

为什么Oracle不使用我的索引?!
by Jonathan Lewis
标题的这个问题可能是在Metalink论坛与Usenet新闻组出现的最频繁的问题了.这篇文章使用一个测试用例(可以在你自己的系统来重现的)来演示基于成本的优化器的基本工作原理.在看完这篇文章之后,当再次遇到这个令人讨厌的问题时,你应该就可以自信的解答了.

由于在安装Oracle的时候存在大量的选项,因此当某人执行一条你口授的脚本时,通常很难精确的预测即将出现什么结果. 当时我想要尝试一下,希望你的数据库选择了一个相对普通的安装选项,并且最常用的关键的参数是取得默认值. 这个例子是在Oracle 8.1.7下创建并测试的,参数db_block_size被设置成最常用的值(8k),参数db_file_multiblock_read_count也设置了一个很常用的值(8).在Oracle 9.2下跑图-1中的这个脚本(创建了一组表,在表上添加索引并分些表与索引),结果可能出现部分差异.

create table t1 as
select 	trunc((rownum-1)/15)	n1,  trunc((rownum-1)/15)	n2, rpad('x', 215)		v1
from all_objects
where rownum <= 3000;
create table t2 as
select
    mod(rownum,200) n1,
    mod(rownum,200) n2,
    rpad('x',215) v1
    from all_objects
    where rownum <= 3000;
create index t1_i1 on t1(N1);
create index t2_i1 on t2(n1);
analyze table t1 compute
statistics;
analyze table t2 compute statistics;

图 1: 测试的数据集
在你准备好数据之后,你可能认为这两组数据是一样的,尤其是,在两个数据集中字段N1有的值范围相同(从0-199),并且每个值都出现了15次.你可能会这样检查数据:

select n1, count(*)
from t1
group by n1;

查询表T2也会证实你的上述观点.
如果你接着执行下面的查询语句:

select * from t1 where n1 = 45;
select * from t2 where n1 = 45;

你将发现每个查询语句都返回了15条记录.然而如果你执行

set autotrace traceonly explain

语句,你将发现这两个查询语句拥有两种不同的执行路径.针对表T1的查询使用了索引,而针对表T2的查询则做了一个全表扫描.
因此,在拥有完全相同的数据的情况下,同一个查询语句戏剧性地出现了两个不同的执行路径.

索引到底怎么了?

注意: 如果你曾经听到如下的关于使用索引的”魔法”准则,例如,”在数据少于23%/10%/2%(随机取一个数字)的时候,Oracle将使用索引,”那么,此时你将怀疑他们的准确性.例如,在这个例子中,Oracle对于一个在3000记录中取15条的查询使用了全表扫描,仅仅0.5%的数据.
要调查诸如此例的问题,只有一个手段(我经常首先尝试使用的):添加一些索引以使得Oracle选择我们认为它应该选择的执行路径,并检查这能否给我们部分提示.
在这个例子中,添加一个简单的提示:
/*+ index(t2, t2_i1) */
就足够让Oracle从选择全表扫描切换到选择使用索引访问.图-2展示了这三种路径的成本(简化为C=nnn).

select * from t1 where n1 = 45;
EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T1 (C=2)
  INDEX(RANGE SCAN) OF T1_I1 (C=1)

select * from t2 where n1 = 45;
EXECUTION PLAN
--------------
TABLE ACCESS FULL OF T2 (C=15)

select /*+ index(t2 t2_i1) */
    *
from t1
where n1 = 45;
EXECUTION PLAN
--------------
TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
  INDEX(RANGE SCAN) OF T2_I1 (C=1)

图 2: 不同的查询以及它们的成本
因此,为什么在T2相关的查询中Oracle没有选择索引作为默认的执行路径?很简单,正如执行计划所展示,选择执行全表扫描的成本要低于使用索引的成本.

为什么使用全表扫描成本更低?

当然,这仅仅是在重复问题.为什么全表扫描的成本会低于使用索引的成本呢?
通过深入调查这个问题,你将揭开了基于成本的优化器的关键机制(也是致命的错误假设).
让我们通过运行下面这个查询来开始我们的考查:

select
     table_name,
     blevel,
	   avg_data_blocks_per_key,
	   avg_leaf_blocks_per_key,
	   clustering_factor
from	user_indexes;

下表是对应的输出结果:

请特别关注”data block per key”的值. 如果你执行一个完全基于这个索引的键值的等值校验的查询语句,这个值就是Oracle认为必须访问的不同数据块的数量.
因此,我们的查询语句的成本是从哪儿来得呢?就Oracle而言,如果输入的键值为45,当从表T1获取数据时,我们就可以访问一个索引叶子块以及一个表块(两个块),因此成本为2.
如果对表T2执行同样的查询,我们就必须访问一个索引叶子块以及15个表块(总共16个块),因此成本为16.
很明显,根据这种观点,表T1的索引比表T2的索引更加理想.不过,这就带来了两个未解决的问题:
全表扫描的成本来自何处,为什么两张表之间的avg_data_blocks_per_key的数值差别如此大?
第二个问题的答案很简单.回头再看看表T1的定义,它使用trunc()函数来生成N1的值,使用”rownum -1″除以15并截成整数.

     Trunc(675/15) = 45
     Trunc(676/15) = 45
           …
     Trunc(689/15) = 45

所有值为45的记录实际上都是一条接着一条连续(可能所有的都能够保存到一个数据块中)的出现的.
表T2使用mod()函数来生成N1的值,使用对rownum取200的模的方式.

      mod(45,200) = 45
      mod(245,200) = 45
            …
      mod(2845,200) = 45

值为45的记录要隔200才会在表中出现一次(可能导致每一个相关数据块中都不会超过1条记录).
通过对表的分析,Oracle可以得到我们表中的数据分布的完美的描述.从而优化器才确切的明白,对于我们的查询,Oracle将需要访问多少个数据块,在这个简单的例子中,这个查询的成本就是需要访问的数据块的数量.

但是为什么是全表扫描呢?

我们看到,对于同样的执行路径,对表T2进行索引访问的成本要远远高于对表T1的索引访问成本,但是为什么会选择使用全表扫描呢?
这个问题将让我们发现Oracle做的两个过于简单甚至不恰当的假设.
第一个假设是,每个块访问都视为一次物理磁盘读,第二个假设是,多块读的速度与单块读一样.
因此,这些假设将对我们的这个实验产生什么影响?
如果使用下面的查询语句查询user_tables表:

select
      table_name,
      blocks
from user_tables;

你将发现这两张表每个都是占用96个数据块.
在文章开头,我说过这个测试用例运行在一个db_file_multiblock_read_count的值为8的版本为8的Oracle系统系统上.
粗略地讲,Oracle认为它可以通过12(96/8)次磁盘读请求来读出所有的96个数据块.
由于通过索引访问表需要16个块(等于物理读)请求, 从Oracle的可悲的受骗的视角看的话,选择全表扫描显得更清晰也更快捷.
瞧!如果你要访问的数据适当地散布在表上,即使只有很小比例的数据也会选择使用全表扫描,在数据块非常多(也就是表很大)而返回的记录数很少的时候,这个问题还会被放大.

校正

实际上,可能你已经发现,我计算出来的扫描读次数为12,而执行计划中报告的成本是15.一种轻微的简化版本认为表扫描(或者索引快速全扫描)的成本为
‘number of blocks’ /
db_file_multiblock_read_count.
Oracle使用一种”调整后”的多块读的值来做这种计算(然而,在扫描开始之后,它仍然尽力使用这个真实的请求值来扫描).
为了方面查询,下表对比了几个真实值与调整后的值:

你还将发现,当你为这个参数提供一个不切实际的很大的值后,Oracle可以为你提供保护来避免发生错误.
顺便提一下,Oracle 9中有一点小小的改变,表扫描的成本还会做进一步的调整(对相除之后的结果加1),这意味着Oracle 9中的表扫描的成本会比Oracle 8中大一点点,从而索引会变得更可能被使用一点点.

修正

我们已经看到,优化器有两个内置的假设,而这两个假设又不是很合理.

• 一次单块读的成本与一次多块读的成本一样-(实际上不大可能,特别是运行在没有顺序的文件系统上的时候)
• 一次块访问就是一次物理磁盘读-(那么Buffer Cache是干什么吃的?)

从Oracle 8.1刚发布开始,就有多个参数可供我们以一种相当切合实际的方式来修正这些假设.
Tim Gorman的文章为这些参数提供了一个切实的描述,下面是简单的描述:

• Optimizer_index_cost_adj的值范围为1到10000,默认为100.实际上,这个参数描述的是,相对于一次多块读来讲,一次单块读有多么便宜.如果它的值为30,也就是高速Oracle一次单块读的成本是一次多块读的成本的30%. 从而Oracle就会因此这个参数的值很小而更多的倾向于选择使用索引访问.
• Optimizer_index_caching的值访问为0到100,默认为0.这个参数告诉Oracle,假定索引块将在Buffer Cache中存在百分比.在这个例子中,将这个值设置为接近100的值将助长使用索引而不是表扫描.

关于这些参数的真正美好的事情是,可以将他们设置成”符合实际”的值.
将optimizer_index_caching设置成”buffer cache hit ratio”范围内的一个值(你需要自己决定具体是按照default pool,keep pool还是这两个的某种组合来得到这个数值).
Optimizer_index_cost_adj的值的设置要更加复杂一点.检查v$system_event视图中等待事件”db file scattered read”(多块读取)与”db file sequential read”(单块读取)的有代表性的等待时间.用后者(单块读的等待时间)除以前者(多块读的等待时间)并乘以100.

改进

不要忘了,这两个参数可能需要一天(周)的不同时段进行调整以反映终端用户的工作负载.仅仅取得一组数字,就一直使用下去,是不可行的.
很高兴,在Oracle 9中,情况得到了改善.你可以收集系统统计信息,通常就包含以下四个统计数据:

• 单块读的平均读取时间
• 多块读的平均读取时间
• 实际发生的多块读的平均读取块数
• CPU的理论可用速度

要详细介绍这个特性足够配得上一整篇文章,但是这里我特别强调一点,前三个统计值使得Oracle可以明白对多块读的真实成本(相对单块读来讲).实际上,CPU速度使得Oracle可以得出不适宜的访问机制的CPU成本,比如,读取一个数据块中的每条记录以找出特定的数据值,以及与此相似的行为.
当你将系统升级到Oracle 9时,你首先需要检查的事情就是是否正确使用系统统计信息.单单这个特性就可能大大降低你尝试”优化”的糟糕的SQL的时间.
顺便提一下,尽管系统统计信息带来了惊人的效果,这两个优化器调整参数仍然有效,虽然使用它们的确切地公式在Oracle 8与Oracle 9之间发生了变化.

主题的变种

当然,我选择了一个非常特殊的例子,一个单列非唯一索引上的等值查询,并且表中没有空值,这种情况非常容易处理.(我甚至都没有提及索引的blevel与clustering_factor.)Oracle还有多个不同的方法来处理更加一般的例子.
考虑如下这些我为了方便而忽视的情况:

• 多列索引
• 使用多列索引中的部分列
• 范围扫描
• 唯一索引
• 由非唯一索引代表的唯一约束
• 索引跳跃扫描
• 只查询索引的语句
• 位图索引
• 空值的影响

这个列表还可以不停地列下去.并没有一个简单的公式来告诉你Oracle是如何计算它的成本,只存在一个通用准则,通过它你可以了解这个方法的梗概,以及一组可以应用到不同情形下的不同计算公式.
不管怎样,本文的目的是让你知道有这个通用准则,以及优化器策略中内嵌的两个基本假设.我希望,这篇文章可以帮助你更加深入的理解那些优化器做出来的众所周知的怪事.

进一步阅读

• Tim Gorman: www.evdbt.com. “The Search for Intelligent Life in the Cost Based Optimiser.”
• Wolfgang Breitling: www.centrexcc.com. “Looking under the hood of the CBO.”

建议继续学习：

1. 由浅入深探究mysql索引结构原理、性能分析与优化    (阅读：15007)
2. 浅谈MySQL索引背后的数据结构及算法    (阅读：9892)
3. 由浅入深理解索引的实现(2)    (阅读：6348)
4. HBase二级索引与Join    (阅读：5798)
5. 如何建立合适的索引？    (阅读：5374)
6. InnODB和MyISAM索引统计集合    (阅读：5198)
7. Innodb 表和索引结构    (阅读：4799)
8. mysql查询中利用索引的机制    (阅读：4723)
9. MySQL索引背后的数据结构及算法原理    (阅读：4423)
10. mysql索引浅析    (阅读：4084)