设为首页 - 加入收藏 核心网 (http://www.hxwgxz.com)- 云主机,资讯,互联网,人工智能,云计算,大数据,区块链,VR,站长网!
热搜: 连奕名 统一 2017 市场
当前位置: 主页 > 大数据 > 正文

我对Oracle索引的认知

发布时间:2021-07-24 16:52 所属栏目:[大数据] 来源:互联网
导读:本 文只讲最最平常最最简单的索引,就是以create index ix on tx(a,b,c);形式创建的索引,而不讲位图索引、反向键索引、倒序索引、基于函数的索引等等。其实呢,

本 文只讲最最平常最最简单的索引,就是以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

 

qc) select * from tx where a = :va and b >= :vb order by c

 

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

 

同样,为什么在索引字段上作了函数运算之后,索引不可用?

 

考虑下面这个语句:

 

select * from tx where f(a) = :vfa;

 

首先,在字段 a上作了函数运算之后,排序的规则是否仍旧一样? a < b 与 f(a) < f(b)是否等价?

 

其次,就算f(a)和a的排序规则一样,但是索引块中存的a, 但是你传给它的是经过了函数运算的值:vfa, 只有Oracle知道函数f的反函数inv_f,并在vfa上做inv_f(:vfa)计算之后,才能通过索引的B树结果进行查找。

 

当 然,现实中f可能不是显示的,而是隐式的,如传入参数和字段类型不匹配的情况下,Oracle可能在字段上作函数运算。从语句上可能看不出索引字段上被做 了函数运算,但Oracle内部已经在字段上运用了函数。这样也会导致索引不可用,这种情况下用hint强制使用索引也是没用的。

 

通过dbms_xplan.display_cursor可以或许可以查看到这种隐式类型转换。

 

通过v$sql_bind_metadata应当可以查看到每个绑定变量的类型,

 

通过 v$sql_bind_capture这个视图甚至可以看到每个绑定变量具体的值,不要把bind_capture和bind peek搞混哦,而且这里bind_cature也不会每绑定一次变量就capture一次,不然对执行量非常高,绑定频繁的语句,capture以同样 频率进行的话,开销可能还是有点大的。

 

上面 讲到了索引的有序性,下面来讲讲索引另外一个有趣的性质,其实,我们完全可以把索引看作一张表,这张表包含和主表一样多的记录(如果不考虑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)

 

这个公式相信很多地方可以找到(我是从cost base Oracle fundamentals这本书里copy出来的),简单说一下我自己对这个公式的理解:

 

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乘起来就可以了。

 

【免责声明】本站内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。

网友评论
推荐文章