注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

lazydba

hello

 
 
 

日志

 
 

甲骨文索引基础 Oracle Index Fundamentals  

2010-01-02 17:30:50|  分类: all about databa |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
在这篇文章里,给大家简单介绍一下本人对Oracle索引的理解,如有不妥的地方,请不吝指教。如果不幸受到误导引起任何损失,本人概不负责:D

本文只讲最最平常最最简单的索引,就是以create index ix on tx(a,b,c);形式创建的索引,而不讲位图索引、反向键索引、倒序索引、基于函数的索引等等。其实呢,只要是基于B树的索引,不管是在Oracle, Mysql,还是其它数据库中,原理应当都是一样的。

索引最重要的一个性质应该就是有序,索引中的每一项,是从左到右,从小到大,以严格的顺序排列好的。

下面的讨论都以上面的索引ix(a,b,c)为例。

把这棵索引的叶子节点画到纸上,大概是这样的:
a1 a2 a3 ...... an
b1 b2 b3 ...... bn
c1 c2 c3 ...... cn

上面这个3×n的矩阵,每一列代表了一行记录,同时这一列记录,也对应了表里的唯一一条记录。当然,在Oracle里,对于non-unique索引,需要补上rowid,才是真正唯一的。上面的索引相当于create unique index ix on tx(a,b,c,rowid);我们先把这个细节忽略掉。

把每一列看作一个向量,vi = (ai, bi, ci),
有序的含义就是:
vi < vj iff i < j;

vi < vj这么定义:
(ai < aj) or (ai = aj and bi < bj) or (ai = aj and bi = bj and ci < cj)

从这个基本性质,我们可以得到一些其它性质(为了打字方便,ai+k表示a(i+k),而不是a(i)+k):
1) 如果ai, ai+1, ......, ai+k 都是相等的,那么,
?? bi <= bi+1 <= ...... <= bi+k
2) 如果ai, ai+1, ......, ai+k是相等的,而且bi,bi+1, ......, bi+k也是相等的,那么
?? ci < ci+1 < ...... < ci+k

但是从 ai, ai+1, ......, ai+k相等,我们得不到
?ci < ci+1 < ...... < ci+k这个结论。

索引相关的很多问题,都和上面提到的这几个性质有关系。

下面来看几个常见的查询:
q1) select * from tx where a = :va and b = :vb;
q2) select * from tx where b = :vb and c = :vc;
q3) select * from tx where a = :va and c = :vc;
q4) select * from tx where a = :va order by b;
q5) select * from tx where a = :va order by b, c;
q6) select * from tx where a = :va order by c;
q7) select * from tx where a = :va order by b, c desc;
q8) select * from tx where a = :va order by b desc, c desc;
q9) select * from tx where a = :va and b <= :vb1
qa) select * from tx where a = :va and b >= :vb
qb) select * from tx where a = :va and c >= :vc

大家可以考虑一下这些查询各自会以怎样的方式执行,不同查询之间有什么区别?

上面讲到了索引的有序性,下面来讲讲索引另外一个有趣的性质,其实,我们完全可以把索引看作一张表,这张表包含和主表一样多的记录(如果不考虑null),只不过每条记录只有主表的部分字段,开个玩笑,我们是不是可以把索引叫做有序视图呢?或者精确一点,有序物化视图:)。

那么,我在执行一些查询的时候,如果所有字段都包含在索引中,是不是只要访问索引就可以了呢?

这些字段可以出现在select列表中,where条件中,order by字段中,也可以出现在两个表连接时的连接条件中。

那么,根据业务的需求,我们是不是可以设计或调整索引以减少对主表的访问呢?或者,是不是可以适当的调整应用的设计来满足索引呢?

同时,考虑到索引的有序性,是不是可以利用索引来避免排序呢?

当然,我们不能忽略null的存在。如果一条记录在索引中的所有字段上都是null的,那么oracle是不会索引这条记录的。比如如果记录ri的ai, bi, ci字段都是null的,索引中是找不到这条记录的。这会有什么问题呢?首先表中的记录和索引中的记录从数量上来说就不一样了。

考虑一下Oracle会怎样执行下面这个查询:

select count(*) from tx;

这个呢,hint起作用了吗?

select /*+ parallel(tx, 4) */ count(*) from tx;

大家可以测试一下,怎样把count(*)这个操作并行化,从这里或许可以得到一些Oracle怎么处理hint的提示。

最后,讲一下Oracle CBO计算索引访问成本的公式:

cost =
? blevel +
? ceiling(leaf_blocks * effective index selectivity) +
? ceiling(clustering_factor * effective table selectivity)

这个公式相信很多地方可以找到,简单说一下我自己对这个公式的理解:
blevel是索引树的高度,
leaf_blocks是索引的页子节点的个数,
effective index selectivity (eis)怎么算呢?
还是举几个例子,
1. where a = :va and b = :vb c = :vc
这里eis是 (selectivity a) * (selectivity b) * (selectivity c)
2. where a = :va and c = :vc
这里eis是 selectivity a
3. where b = :vb and c = :vc
这里eis是 1
4. where a = :va and b >= :vb and c = :vc
这里eis是 (selectivity a) * (selectivity range b)

就是说按索引字段的顺序,第一个不在where条件中出现的字段,或者第一个做了范围运算的字段,之后出现的字段的selectivity是不能乘到effective index selectivity里去的。

简单的说,ceiling(leaf_blocks * effective index selectivity)表示的是Oracle需要访问的索引叶子节点的个数。
?
clustering_factor表示的是按索引的顺序,从头走到尾,需要访问多少次数据块。这里需要考虑到Oracle的一个优化:如果连续n条记录在同一个表块中,那么oracle认为只需要访问一次数据块。
那么clustering_factor的值的范围就很容易确定了:cf >= table blocks and cf <= rows in index

effective table selectivity,这个计算就容易了,把索引中所有字段的selectivity乘起来就可以了。

如果查询中还有其它条件, 比如 d = :vd and e = :ve ....,但是d,e这些字段又不在索引中,那么在这些列上的过滤条件,需要回表后把这些值取出来才能判断,所以d,e这些列的selectivity是不能乘到effective table selectivity里去的。

ceiling(clustering_factor * effective table selectivity)表示需要回表的次数。

所以上面索引访问的cost就是走某个索引,需要访问的数据块的个数。

当然,前面的讨论忽略了index skip scan这种情况,因为本人对index skip scan也不是很明白。

什么情况下会走skip scan?

select * from tx where a = :va and c = :vc 是不是会在c这个字段上也作一个skip scan呢?

  评论这张
 
阅读(170)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018