Oracle Index 的三个问题ITeye - 乐橙lc8

Oracle Index 的三个问题ITeye

2019年03月24日13时00分32秒 | 作者: 炫明 | 标签: 索引,运用,数据 | 浏览: 1124

       索引 ( index ) 是常见的数据库目标,它的设置好坏、运用是否妥当,极大地影响数据库运用程序和 database 的功能。虽然有许多材料讲索引的用法, dba 和 developer 们也常常与它打交道,但笔者发现,仍是有不少的人对它存在误解,因而针对运用中的常见问题,讲三个问题。此文一切示例所用的数据库是 oracle 8.1.7 ops on hp n series , 示例全部是实在数据,读者不需求注意详细的数据巨细,而应注意在运用不同的方法后,数据的比较。本文所讲根本都是老生常谈,可是笔者企图通过实践的比方, 来真实让您了解工作的要害。

榜首讲、索引并非总是最佳挑选

假如发现 oracle 在有索引的状况下,没有运用索引,这并不是 oracle 的优化器犯错。在有些状况下, oracle 的确会挑选全表扫描( full table scan ) , 而非索引扫描( index scan )。这些状况一般有:

1. 表未做 statistics, 或许 statistics 陈腐,导致 oracle 判别失误。

2. 依据该表具有的记载数和数据块数,实践上全表扫描要比索引扫描更快。

对第 1 种状况,最常见的比方,是以下这句 sql 句子:

select count(*) from mytable;

在 未作 statistics 之前,它运用全表扫描,需求读取 6000 多个数据块(一个数据块是 8k ) , 做了 statistics 之后,运用的是 index (fast full scan) ,只需求读取 450 个数据块。可是, statistics 做得欠好,也会导致 oracle 不运用索引。

第 2 种状况就要杂乱得多。一般概念上都以为索引比表快,比较难以了解什么状况下全表扫描要比索引扫描快。为了讲清楚这个问题,这儿先介绍一下 oracle 在评价运用索引的价值( cost )时两个重要的数据: cf(clustering factor) 和 ff(filtering factor).

cf: 所谓 cf, 浅显地讲,便是每读入一个索引块,要对应读入多少个数据块。

ff: 所谓 ff, 便是该 sql 句子所挑选的成果集,占总的数据量的百分比。

大约的核算公式是: ff * (cf + 索引块个数 ) ,由此估量出,一个查询, 假如运用某个索引,会需求读入的数据块块数。需求读入的数据块越多,则 cost 越大, oracle 也就越或许不挑选运用 index. (全表扫描需求读入的数据块数等于该表的实践数据块数)

其中心便是, cf 或许会比实践的数据块数量大。 cf 遭到索引中数据的摆放方法影响,一般在索引刚树立时,索引中的记载与表中的记载有杰出的对应联系, cf 都很小;在表通过许多的刺进、修改后,这种对应联系越来越乱, cf 也越来越大。此刻需求 dba 从头树立或许安排该索引。

假如某个 sql 句子曾经一向运用某索引,较长时刻后不再运用,一种或许便是 cf 现已变得太大,需求从头整理该索引了。

ff 则是 oracle 依据 statistics 所做的估量。比方 , mytables 表有 32 万行,其主键 myid 的最小值是 1 ,最大值是 409654 ,考虑以下 sql 句子:

select * from mytables where myid 和

select * from mytables where myid =400000

这两句看似差不多的 sql 句子,对 oracle 而言,却有巨大的不同。由于前者的 ff 是 100% , 而后者的 ff 或许只要 1% 。假如它的 cf 大于实践的数据块数,则 oracle 或许会挑选彻底不同的优化方法。而实践上,在咱们的数据库上的检验验证了咱们的猜测 . 以下是在 hp 上履行时它们的 explain plan:

榜首句:

sql select * from mytables where myid

已挑选 325917 行。

execution plan



0 select statement optimizer=choose (cost=3132 card=318474 byt es=141402456)

1 0 table access (full) of mytables (cost=3132 card=318474 byt es=141402456)

statistics



7 recursive calls

89 db block gets

41473 consistent gets

19828 physical reads

0 redo size

131489563 bytes sent via sql*net to client

1760245 bytes received via sql*net from client

21729 sql*net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

325917 rows processed

第二句:

execution plan



0 select statement optimizer=choose (cost=346 card=663 bytes=2 94372)

1 0 table access (by index rowid) of mytables (cost=346 card=663

bytes=294372)

2 1 index (range scan) of pk_mytables (unique) (cost=5 card=663)

statistics



1278 recursive calls

0 db block gets

6647 consistent gets

292 physical reads

0 redo size

3544898 bytes sent via sql*net to client

42640 bytes received via sql*net from client

524 sql*net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

7838 rows processed

清楚明了,第 1 句没有运用索引,第 2 句运用了主键索引 pk_mytables. ff 的巨大影响由此可见一斑。由此想到,咱们在写 sql 句子时,假如预先估量一下 ff, 你就简直能够预见到 oracle 会否运用索引。



第二讲、索引也有好坏

索 引有 b tree 索引, bitmap 索引, reverse b tree 索引, 等。最常用的是 b tree 索引。 b 的全称是 balanced , 其含义是,从 tree 的 root 到任何一个 leaf ,要通过相同多的 level. 索引能够只要一个字段( single column ) , 也能够有多个字段( composite ) , 最多 32 个字段, 8i 还支撑 function-based index. 许多 developer 都倾向于运用单列 b 树索引。

除此之外呢?咱们仍是来看一个比方吧:

在 hp ( oracle 8.1.7 ) 上履行以下句子:

select count(1) from mytabs where coid =130000 and issuedate = to_date (2001-07-20, yyyy-mm-dd) 。

一开始,咱们有两个单列索引: i_mytabs1(coid), i_mytabs2(issuedate), 下面是履行状况:

count(1)



6427

execution plan



0 select statement optimizer=choose (cost=384 card=1 bytes=11)

1 0 sort (aggregate)

2 1 table access (by index rowid) of t_mytabs (cost=384 card

=126 bytes=1386)

3 2 index (range scan) of i_mytabs2 (non-unique) (cost=11

card=126)

statistics



172 recursive calls

1 db block gets

5054 consistent gets

2206 physical reads

0 redo size

293 bytes sent via sql*net to client

359 bytes received via sql*net from client

2 sql*net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

能够看到,它读取了 7000 个数据块来取得所查询的 6000 多行。

现在,去掉这两个单列索引,添加一个复合索引 i_mytabs_test ( coid, issuedate), 从头履行,成果如下:

count(1)



6436

execution plan



0 select statement optimizer=choose (cost=3 card=1 bytes=11)

1 0 sort (aggregate)

2 1 index (range scan) of i_mytabs_test (non-unique) (cost=3 card=126 bytes=1386)

statistics



806 recursive calls

5 db block gets

283 consistent gets

76 physical reads

0 redo size

293 bytes sent via sql*net to client

359 bytes received via sql*net from client

2 sql*net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

1 rows processed

能够看到,这次只读取了 300 个数据块。

7000 块对 300 块,这便是在这个比方中,单列索引与复合索引的价值之比。这个比方提示咱们, 在许多状况下,单列索引不如复合索引有用率。

能够说,在索引的设置问题上,其实有许多工作能够做。正确地设置索引,需求对运用进行整体的剖析。



第三讲、索引再好,不必也是白费

抛开前面所说的,假定你设置了一个十分好的索引,任何傻瓜都知道应该运用它,可是 oracle 却偏偏不必,那么,需求做的榜首件工作,是审视你的 sql 句子。

oracle 要运用一个索引,有一些最根本的条件:

1 , where 子句中的这个字段,有必要是复合索引的榜首个字段;

2 , where 子句中的这个字段,不应该参加任何方式的核算

详细来讲,假定一个索引是按 f1, f2, f3 的次第树立的,现在有一个 sql 句子 , where 子句是 f2 = : var2, 则由于 f2 不是索引的第 1 个字段,无法运用该索引。

第 2 个问题,则在咱们之中十分严峻。以下是从 实践体系上面抓到的几个比方:

select jobid from mytabs where isreq=0 and to_date (updatedate) = to_date ( 2001-7-18, yyyy-mm-dd) ;

………

以上的比方能很容易地进行改善。请注意这样的句子每天都在咱们的体系中运转,耗费咱们有限的 cpu 和 内存资源。

除了 1 , 2 这两个咱们有必要牢记于心的准则外,还应尽量了解各种操作符对 oracle 是否运用索引的影响。这儿我只讲哪些操作或许操作符会显式( explicitly )地阻挠 oracle 运用索引。以下是一些根本规矩:

1 , 假如 f1 和 f2 是同一个表的两个字段,则 f1 f2, f1 =f2, f1

2 , f1 is null, f1 is not null, f1 not in, f1 !=, f1 like ‘ %pattern% ;

3 , not exist

4 , 某些状况下, f1 in 也会不必索引;

关于这些操作,别无方法,只要尽量防止。比方,假如发现你的 sql 中的 in 操作没有运用索引,或许能够将 in 操作改成 比较操作 + union all 。笔者在实践中发现许多时分这很有用。

但 是, oracle 是否真实运用索引,运用索引是否真实有用,仍是有必要进行实地的检验。合理的做法是,对所写的杂乱的 sql, 在将它写入运用程序之前,先在产品数据库上做一次 explain . explain 会取得 oracle 对该 sql 的解析( plan ) , 能够明确地看到 oracle 是怎么优化该 sql 的。

假如常常做 explain, 就会发现,喜欢写杂乱的 sql 并不是个好习惯,由于过火杂乱的 sql 其解析方案往往不尽善尽美。事实上,将杂乱的 sql 拆开,有时分会极大地进步功率,由于能取得很好的优化。当然这现已是题外话了。
,欢迎拜访网页规划爱好者web开发。   

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表乐橙lc8立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    Oracle Index 的三个问题ITeye

    索引,运用,数据
  • 2

    检查sqlserver的端口号ITeye

    端口号,能够,看到
  • 3
  • 4

    oracle中userITeye

    字段,运用,数据
  • 5
  • 6
  • 7

    mysql一切指令ITeye

    指令,用户,数据库
  • 8
  • 9
  • 10