仓酷云

标题: MYSQL编程：Oracle Index 的三个成绩 [打印本页]

作者: 分手快乐 时间: 2015-1-16 22:44
标题: MYSQL编程：Oracle Index 的三个成绩
能够以较低的成本向客户提供IT所有权，当节约成本成为客户最高优先级时，解决方案提供商可以向更多的客户同时提供服务。虽然有许多来自RDBMS固有的局限性。oracle|成绩
索引(Index)是罕见的数据库工具，它的设置优劣、利用是不是妥当，极年夜地影响数据库使用程序和Database的功能。固然有很多材料讲索引的用法，DBA和Developer们也常常与它打交道，但笔者发明，仍是有很多的人对它存在曲解，因而针对利用中的罕见成绩，讲三个成绩。此文一切示例所用的数据库是Oracle8.1.7OPSonHPNseries,示例全体是实在数据，读者不必要注重详细的数据巨细，而应注重在利用分歧的办法后，数据的对照。本文所讲基础都是老生常谈，可是笔者试图经由过程实践的例子，来真正让您分明事变的关头。

第一讲、索引并不是老是最好选择

　　假如发明Oracle在有索引的情形下，没有利用索引，这并非Oracle的优化器堕落。在有些情形下，Oracle的确会选择全表扫描（FullTableScan）,而非索引扫描（IndexScan）。这些情形一般有：

1.表未做statistics,大概statistics陈腐，招致Oracle判别掉误。

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

　　对第1种情形，最多见的例子，是以下这句sql语句：

selectcount(*)frommytable;

在未作statistics之前，它利用全表扫描，必要读取6000多个数据块（一个数据块是8k）,做了statistics以后，利用的是INDEX(FASTFULLSCAN)，只必要读取450个数据块。可是，statistics做得欠好，也会招致Oracle不利用索引。

　　第2种情形就要庞大很多。一样平常观点上都以为索引比表快，对照难以了解甚么情形下全表扫描要比索引扫描快。为了讲分明这个成绩，这里先先容一下Oracle在评价利用索引的价值（cost）时两个主要的数据：CF(Clusteringfactor)和FF(Filteringfactor).

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*frommytableswheremyid>=1;和

Select*frommytableswheremyid>=400000

　　这两句看似差未几的sql语句，对Oracle而言，却有伟大的不同。由于前者的FF是100%，尔后者的FF大概只要1%。假如它的CF年夜于实践的数据块数，则Oracle大概会选择完整分歧的优化体例。而实践上，在我们的数据库上的测实验证了我们的展望.以下是在HP上实行时它们的explainplan:

　　第一句：

SQL>select*frommytableswheremyid>=1;

　　已选择325917行。

ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE(Cost=3132Card=318474Bytes=141402456)

10TABLEACCESS(FULL)OFMYTABLES(Cost=3132Card=318474Bytes=141402456)

Statistics

----------------------------------------------------------

7recursivecalls

89dbblockgets

41473consistentgets

19828physicalreads

0redosize

131489563bytessentviaSQL*Nettoclient

1760245bytesreceivedviaSQL*Netfromclient

21729SQL*Netroundtripsto/fromclient

1sorts(memory)

0sorts(disk)

325917rowsprocessed

第二句：

ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE(Cost=346Card=663Bytes=294372)

10TABLEACCESS(BYINDEXROWID)OFMYTABLES(Cost=346Card=663

Bytes=294372)

21INDEX(RANGESCAN)OFPK_MYTABLES(UNIQUE)(Cost=5Card=663)

Statistics

----------------------------------------------------------

1278recursivecalls

0dbblockgets

6647consistentgets

292physicalreads

0redosize

3544898bytessentviaSQL*Nettoclient

42640bytesreceivedviaSQL*Netfromclient

524SQL*Netroundtripsto/fromclient

1sorts(memory)

0sorts(disk)

7838rowsprocessed

　　不言而喻，第1句没有利用索引，第2句利用了主键索引pk_mytables.FF的伟大影响因而可知一斑。由此想到，我们在写sql语句时，假如事后估量一下FF,你就几近能够预感到Oracle会否利用索引。

第二讲、索引也有优劣

索引有Btree索引，Bitmap索引，Reversebtree索引，等。最经常使用的是Btree索引。B的全称是Balanced,其意义是，从tree的root就任何一个leaf，要经由一样多的level.索引能够只要一个字段（Singlecolumn）,也能够有多个字段（Composite）,最多32个字段，8I还撑持Function-basedindex.很多developer都偏向于利用单列B树索引。

除此以外呢？我们仍是来看一个例子吧：

　　在HP（Oracle8.1.7）上实行以下语句：

selectcount(1)frommytabswherecoid>=130000andissuedate>=to_date(2001-07-20,yyyy-mm-dd)。

　　一入手下手，我们有两个单列索引：I_mytabs1(coid),I_mytabs2(issuedate),上面是实行情形：

COUNT(1)

----------

6427

ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE(Cost=384Card=1Bytes=11)

10SORT(AGGREGATE)

21TABLEACCESS(BYINDEXROWID)OFT_MYTABS(Cost=384Card

=126Bytes=1386)

32INDEX(RANGESCAN)OFI_MYTABS2(NON-UNIQUE)(Cost=11

Card=126)

Statistics

----------------------------------------------------------

172recursivecalls

1dbblockgets

5054consistentgets

2206physicalreads

0redosize

293bytessentviaSQL*Nettoclient

359bytesreceivedviaSQL*Netfromclient

2SQL*Netroundtripsto/fromclient

5sorts(memory)

0sorts(disk)

1rowsprocessed

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

　　如今，往失落这两个单列索引，增添一个复合索引I_mytabs_test(coid,issuedate),从头实行，了局以下：

COUNT(1)

----------

6436

ExecutionPlan

----------------------------------------------------------

0SELECTSTATEMENTOptimizer=CHOOSE(Cost=3Card=1Bytes=11)

10SORT(AGGREGATE)

21INDEX(RANGESCAN)OFI_MYTABS_TEST(NON-UNIQUE)(Cost=3Card=126Bytes=1386)

Statistics

----------------------------------------------------------

806recursivecalls

5dbblockgets

283consistentgets

76physicalreads

0redosize

293bytessentviaSQL*Nettoclient

359bytesreceivedviaSQL*Netfromclient

2SQL*Netroundtripsto/fromclient

3sorts(memory)

0sorts(disk)

1rowsprocessed

　　能够看到，此次只读取了300个数据块。

7000块对300块，这就是在这个例子中，单列索引与复合索引的价值之比。这个例子提醒我们，在很多情形下，单列索引不如复合索引无效率。

　　能够说，在索引的设置成绩上，实在有很多事情能够做。准确地设置索引，必要对使用举行整体的剖析。

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

抛开后面所说的，假定你设置了一个十分好的索引，任何傻瓜都晓得应当利用它，可是Oracle却恰恰不必，那末，必要做的第一件事变，是审阅你的sql语句。

Oracle要利用一个索引，有一些最基础的前提：

1，where子句中的这个字段，必需是复合索引的第一个字段；

2，where子句中的这个字段，不该该介入任何情势的盘算

　　详细来说，假定一个索引是按f1,f2,f3的序次创建的，如今有一个sql语句,where子句是f2=:var2,则由于f2不是索引的第1个字段，没法利用该索引。

　　第2个成绩，则在我们当中十分严峻。以下是从实践体系下面抓到的几个例子：

SelectjobidfrommytabswhereisReq=0andto_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，f1isnull,f1isnotnull,f1notin,f1!=,f1like‘%pattern%;

3，Notexist

4，某些情形下，f1in也会不必索引；

关于这些操纵，别无举措，只要只管制止。好比，假如发明你的sql中的in操纵没有利用索引，大概能够将in操纵改成对照操纵+unionall。笔者在理论中发明良多时分这很无效。

可是，Oracle是不是真正利用索引，利用索引是不是真正无效，仍是必需举行实地的检验。公道的做法是，对所写的庞大的sql,在将它写进使用程序之前，先在产物数据库上做一次explain.explain会取得Oracle对该sql的剖析（plan）,能够明白地看到Oracle是怎样优化该sql的。

假如常常做explain,就会发明，喜好写庞大的sql并非个好习气，由于太过庞大的sql其剖析企图常常不尽善尽美。现实上，将庞大的sql拆开，偶然候会极年夜地进步效力，由于能取得很好的优化。固然这已是题外话了。
与其他数据库相比，MySQL易学易用。

作者: 灵魂腐蚀 时间: 2015-1-19 21:53
对于微软系列的东西除了一遍遍尝试还真没有太好的办法

作者: 小妖女 时间: 2015-1-28 12:46
如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

作者: 精灵巫婆 时间: 2015-2-5 14:46
where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

作者: 小魔女 时间: 2015-2-12 08:34
having子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。

作者: 愤怒的大鸟 时间: 2015-3-3 01:12
无法深入到数据库系统层面去了解和探究

作者: 小女巫 时间: 2015-3-11 08:14
连做梦都在想页面结构是怎么样的，绝非虚言

作者: 因胸联盟 时间: 2015-3-17 23:51
呵呵，这就是偶想说的

欢迎光临仓酷云 (http://ckuyun.com/)