相克军老师16-基本索引原理

基本索引概念当从表中读取数据时，Oracle提供了两个选择： 从表中读取每一行（全表扫描） 通过ROWID一次读取一行当我们要访问大型表的少数行时，可能需要使用索引。因为如果没有索引，那么只能进行全表扫描。索引改进性能的程度取决于两个因素：1、数据的选择性2、表数据在数据块上的分布如果选择性很高（例如身份证号码），那么根据索引值返回的ROWID很少，如果选择性很低（例如国家）则返回的ROWID很多，那么索引的性能将会大大降低如果选择性很高，但是相关的行在表中的存储位置并不互相靠近，则会进一步减少索引的益处，如果匹配索引值的数据分散在表的多个数据块中，则必须从表中选择多个单独的块以满足查询，基于索引的读取是单块读取，如果使用全表扫描，使用的是多块读取以快速扫描表，因此全表扫描不见得比索引扫描速度慢。全表扫描和索引查找之间的平衡点1、分区2、并行DML3、并行查询4、使用db_file_multiblock_read_count进行更大的IO操作5、硬件更为快速6、磁盘上的缓存可以缓存更多的数据7、内存的廉价使得我们的内存进一步增大8、Oracle采用了增强的索引特性（例如跳跃式扫描索引）SELECT、UPDATE、DELETE+WHERE条件可以从索引中得到好处 （前提是：当访问的行数较少时）一般来说，增加索引会带来insert语句性能的下降如果根据未索引列update索引列，那么也会带来性能的降低大量的delete也会因为索引的存在而导致性能降低因此我们要分析具体的情况，判断索引和DML语句之间的关系我们如何去去判断一个表上的索引呢？如果发现对一个表的DML速度较慢，可以执行上面的命令，判断是否是因为增加了索引而引起的性能问题。组合索引当某个索引包含有多个列时，我们称这个索引为组合索引。在使用组合索引的时候，要谨慎选择索引列中的列顺序。一般来说，索引的第一列应该是最有可能在where子句中使用的列，并且也是在索引中最具有选择性的列。对于9i以前，查询只能在where子句中使用索引的第一列时使用索引。除非在where子句中给empno指定一个值，否则一般不会使用组合索引。从Oracle9i开始，引入了跳跃式索引扫描功能，即使在where子句中没有指定empno的数值，也会可能会使用索引。我们来举个例子，看一下跳跃式索引扫描。

createindexsex_emp_id on emp(sex,emp_id)

select* fromemp whereemp_id=123;在Oracle数据库的内部，生成了两个查询，然后对两个查询的ROWID进行了联合。当使用跳跃式索引扫描时，自动给SEX加上了数值，启用了两个查询。如果SEX有50个数值，那么需要启用50个查询才能完成查询，因此性能大大降低。因此是否适合使用跳跃式索引扫描，取决于第一个索引列的选择性。一般建议第一个列的可选性非常低。跳跃式索引扫描相对索引直接扫描速度要慢一些，但是相对表扫描速度还是要快很多。使用跳跃式索引的条件

1优化器认为是合适的.

2索引中的前导列的唯一值的数量能满足一定的条件.

3优化器要知道前导列的值分布(通过分析/统计表得到)

4合适的SQL语句如果oracle没有选择使用跳跃式索引扫描，那么可能选择使用索引快速全局扫描或全表扫描。我们花点时间来研究一下Oracle中扫描数据的方法：1、全表扫描（FullTableScanFTS）Oracle读取表中所有的行、多块读操作可以大大的减少IO的次数、利用多块读可以大大的提高全表扫描的速度、只有在全表扫描的情况下才能使用多块读。在较大的表上不建议使用全表扫描、如果读取表的数据总量超过5%—10%，那么通常进行全表扫描。并行查询可能会使得我们的路径选择采用全表扫描。2、通过ROWID对表进行读取（TableaccessbyROWID）ROWID指出了数据文件、块号、行号，通过ROWID是oracle数据库中读取单行数据最快速的方法。这种方法不会采用多块读、而是会采用单块读的方式。3、索引扫描或者索引查找（indexscanindexlookup）通过索引找到数据行的ROWID、然后通过ROWID直接到表中查找数据，这种方式称为索引查找或者索引扫描。因为一个ROWID对应一个数据行，因此这种方式采用的也是单块读。在索引中，除了存储每个索引值、还存储相应的ROWID，索引扫描分为两步：1、扫描索引得到相应的ROWID2、通过找到的ROWID从表中读取相应的数据每次采用的都是单块IO读因为索引小、而且经常使用，因此通常被cache到内存中，因此第一步通常是逻辑读（数据可以从内存中得到）因为表数据比较大、因此第二步读通常是物理读，因此性能较低访问路径走的是主键索引，因此是INDEXUNIQUESCAN首先是索引扫描、然后是根据索引查找到的ROWID进行表的访问。这个查询中，因为访问的列都在索引中，因此省略了访问的第二步。因为访问路径走的是非唯一索引，因此是INDEXRANGESCAN索引本身已经是排序的，因此只需要读取数据、不需要对数据进行排序。根据索引的类型和where条件的限制不同，有四种索引扫描类型：1、索引唯一扫描（INDEXUNIQUESCAN）2、索引范围扫描（INDEXRANGESCAN）3、索引全扫描（INDEXFULLSCAN）4、索引快速扫描（INDEXFASTFULLSCAN）索引唯一扫描通过唯一键、主键，oracle通常返回一个数据行，因此采用的是索引唯一扫描索引范围扫描1、在唯一键上使用range操作符（>、<、<>、>=、<=、between）2、在组合索引上，只使用部分列进行查询、导致查询出多行3、对非唯一索引列上进行的查询索引全扫描查询出的数据必须全部从索引中得到索引快速扫描扫描索引块中的所有数据块，这点与fullindexscan相似，但是索引快速扫描不进行数据的排序，在这种方式下，可以使用多块读功能、也可以使用并行读功能，最大化数据的吞吐量。限制索引这个是一些没有经验的开发人员经常犯的错误之一。在SQL中，有很多陷阱让索引没有办法使用。很多情况使用索引1、where子句中使用到了索引列2、没有where子句，但是也可能使用到索引 查询索引列的MIN或者MAX

对索引列执行count等等我们下面来研究一下使用where但是阻止oracle使用索引的几种情况。1、使用不等于运算符（<>、!=）在where中使用不等于条件，将会使索引失效。

当分析表的时候，oracle收集表中数据分布的相关统计信息，通过使用这种分析，基于成本的优化器可以决定在where子句中对一些值使用索引，而对其他的值不使用索引。 因此不是说在一个列上建立了索引就一直会使用索引。 根据不同值，优化器都会确定是否使用索引。使用索引的条件就是：根据索引树定位索引页节点、2、使用ISNULL或ISNOTNULL在where子句中使用ISNULL或者ISNOTNULL同样会限制索引的使用。如果被索引的列在某些行中存在NULL值，在索引列中就不会有相应的条目。（例外：位图索引对于NULL列也会进行记录，因此位图索引对于NULL搜索通常较为快速）。因此建议对列加上NOTNULL或者DEFAULT。3、使用函数如果不使用基于函数的索引（后面会讲到），那么在SQL语句的where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。一些常见的函数：trunc、substr、to_date、to_char、instr等，都可能会使索引失效。解决方案：1、使用基于函数的索引2、灵活书写SQL、避免在索引列上使用SQL函数通过灵活一变，SQL语句的访问路径变成了走索引。为了走索引，就不要在where子句中的索引列上使用函数。4、比较不匹配的数据类型这个是比较难于发现的问题。Oracle不会对不匹配的数据类型报错，Oracle会隐式地把VARCHAR2列的数据类型转换成要被比较的数值型数据类型（这是一个例子，还存在其他的数据类型转换）。自动加了一个函数to_number(zip)

对于不匹配的数据类型，Oracle隐式的加上一个转换函数。为什么不加载到100043上面，因为这是一个常量，常量是不能改变的。灵活的使用，在常量上面加上一个’’，表示这是一个字符常量，这样字符常量就和ZIPVARCHAR2一致了。索引列的数据类型和常量类型要求一致。索引的选择性Oracle通过多种方法来判断使用索引的价值，第一个方法就是判断索引中的唯一值或不同键的数量。唯一键的数量/表的行数，判断索引的选择性。选择性越高、索引返回的行数越少，该索引就越好。通过这个来判断索引的选择性。这个表的这个索引的可选择性很高（100%）。集群因子（clusterfactor）集群因子是索引与他所基于的表相比较得出的有序性度量，它用于检查在索引访问之后执行的表查找的成本（将集群因子与选择性相乘即可得到该操作的成本）。索引扫描以后，根据从索引中取出的ROWID顺序进行表数据的查找。什么是ClusteringFactor呢？ClusteringFactor是的含义是如果通过一个索引扫描一张表，需要访问的表的数据块的数量。ClusteringFactor计算的方法如下：1、扫描一个索引2、比较某行的rowid和前一行的rowid，如果这两个rowid不属于同一个数据块，那么clusterfactor增加13、整个索引扫描完毕后，就得到了该索引的clusterfactor。 如果ClusteringFactor接近于表存储的块数，说明这张表是按照索引字段的顺序存储的。如果ClusteringFactor接近于行的数量，那说明这张表不是按索引字段顺序存储的。在计算索引访问成本的时候，这个值十分有用。ClusteringFactor乘以选择性参数（selectivity）就是访问索引的开销。如果这个统计数据不能真实反映出索引的真实情况，那么可能会造成优化器错误的选择执行计划。另外如果某张表上的大多数访问是按照某个索引做索引扫描，那么将该表的数据按照索引字段的顺序重新组织，可以提高该表的访问性能。这个段分配了一个extent，因此这里面显示的是8blocks，不是实际的占用块数。因此这个表占用的blocks是1个。聚集因子的数量和表的块数一致，因此表的数据排序和索引一致，因此在进行索引范围扫描的时候，对表的访问成本还是比较低的。聚集因子主要影响的是索引范围扫描。如果聚集因子很高，那么建议对表进行重新整理，降低聚集因子。二元高度（binaryheight）索引查找分为两个过程：1、根据树进行定位、找出ROWID（索引查找）2、根据ROWID找出表中的数据行（表数据查找）进行索引查找的时候，首先从树根开始读数据，通过中间节点，最后定位到叶节点，整个过程只能进行单数据块的读取。索引表二元高度是3，读取一行数据经过了四次数据块的读取，其中三次是索引，一次是表数据。查找每个索引的二元高度，这个索引只占用了一个数据块，因此二元高度是0二元高度主要随着表中索引列的非NULL值以及索引列中值的宽度而变化。如果索引列上大量的行被删除，那么他的二元高度不会降低，重建索引会降低二元高度，如果一个索引中被删除的行接近20～25%，重建索引会减低二元高度。二元高度对索引的性能影响不是很大，但是在可能的情况下，降低二元高度还是有必要的。使用直方图在分析表和索引时，直方图用于记录数据的分布。通过获取该信息，基于成本的优化器就可以决定使用将返回少量行的索引，而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制，我们可以在表的任何列上构建直方图（一般是在表的索引列上构建直方图）。构建直方图最主要的原因就是：帮助优化器在表中数据严重倾斜时做出更好的规划。如果一个表中的列上（通常是索引列）数据发生严重的倾斜，那么在这个列上建立直方图将非常的有意义。这样优化器就知道什么时候该使用索引、什么时候不该使用索引。在hr用户下面的employees表的job_id列上建立了一个直方图。这个直方图有10个存储桶。行数101010101010101010101000-13001301-14001401-14301430-1500143014301430143014301430对整个列的100行数据进行均分成10个存储桶，每个桶中存储10个数据行。然后写出每个桶中的数据范围。我们发现1430这个数值对应的行号数非常多，大约73行，占到了73%。因此当我们使用whereid=1403的时候，优化器不会走索引。默认情况下，oracle的直方图会产生75个存储桶，可以把存储桶的size限制在1～254之间。快速全局索引扫描在索引的快速全局扫描期间，Oracle读取B树索引上的所有树叶块，这个索引可以按照顺序读取，这样可以一次读取多个块，初始化文件中的DB_FILE_MULTIBLOCK_READ_COUNT可以控制同时被读取的块数。相对全表扫描，快速全局索引扫描通常需要较少的物理IO。如果表查询的所有列都被包含在索引中，而索引的前置列并不在where条件中，就可以使用快速全局索引扫描。

selectempno,ename,deptno fromemp wheredeptno=30;empno,ename,deptno上有一个组合索引。作为一个选择，针对上面的情况，Oracle可能采取跳跃式索引扫描。跳跃式索引扫描组合索引、where条件中没有出现前置列（通常是组合索引的第二列）。相对全索引扫描，跳跃式索引扫描读取的数据块更少、速度更快。读取的块数明显减少索引类型1、B树索引2、位图索引3、HASH索引4、索引组织表IOT5、反转键（reversekeys）索引6、基于函数的索引7、分区索引（本地索引、全局索引）8、位图连接索引B树索引B树索引在Oracle中是一个通用索引，创建索引的时候默认就是B树索引。 单列索引 组合索引（最多可以多达32个列）对于B树索引，我们需要关注他的集群因子（二元高度（blevel）。B树索引保存了在索引列上有值的每个数据行的ROWID。Oracle不会对索引列上包含NULL值的行进行索引，如果索引是一个组合索引，而其中列上包含NULL值，这一行会于索引列中。B树索引图释。位图索引位图索引非常适合DSS和数据仓库。在OLTP系统中，尽量不要使用位图索引。Bitmap索引<Blue,10.0.3,12.8.3,0><Green,10.0.3,12.8.3,0001010000100100000><Red,10.0.3,12.8.3,0><Yellow,10.0.3,12.8.3,0010001000001000010>KeyStartROWIDEndROWID

BitmapTableIndexBlock10Block11Block12File3Block13对于位图索引的列，列值的数量要求较少或者中等（索引列基数较小）。 前面的例子中，列的基数是4Oracle为每个唯一键值创建一个位图，然后把与键值所关联的ROWID保存为位图。最多可以包括30列。位图的经典案例之一：位图发挥最大威力的场合是：当一个表中包含了多个位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。对于较低基数的位图索引来说，位图索引的尺寸远远的小于B树索引，因此可以大大的减少IO的数量。对于非常大的表来说，在多个低基数列上建立位图索引是一个很好的选择。对于位图索引来说，即使从表中读取很多行，也会使用位图索引。 例如在一个sex列上建立索引，每次从表中读取半数的数据行，但是还是会使用位图索引。位图索引的插入1、位图索引在批处理（单用户）操作中加载表（插入操作）方面通常比B树做得好。2、当有多个会话同时向表中插入数据行时不应该使用位图索引3、当每条记录都增加一个新值时，B-树索引要比位图索引快3倍。四个低基数列分别建立了位图索引。Oracle会使用这四个位图索引对数据进行筛选，计算出需要读取的数据行和数据块，然后进行读取。在这个过程中会涉及到位图的计算。

在B树索引中，可以实现行级锁定，但是在位图索引中，因为对ROWDI进行压缩存放（一个ROWID范围+位图），因此每次锁定的都是整个的ROWID范围，因此对表中的位图索引列进行更新的时候，并发性很差，容易导致死锁。SELECT语句(Cr块)不会受到这种锁定问题的影响。位图索引有很多的限制：1、基于规则的优化器（RBO）不会考虑位图索引2、当执行altertable语句并修改包含位图索引列时，会使得位图索引失效3、位图索引不包含任何列数据，不能用于任何类型的完整性检查 例如主键、唯一键约束4、位图索引不能被声明为唯一索引5、位图索引的最大长度为30注意：不要在繁忙的OLTP系统中使用位图索引HASH索引使用hash索引必须要使用hashcluster。我们首先来看一下cluster表的结构。簇表--ClustersClusteredordersandorder_itemtablesClusterKey(ORD_NO)101 ORD_DT CUST_CD 05-JAN-97 R01

PROD QTY A4102 20 A5675 19 W0824 10102 ORD_DT CUST_CD07-JAN-97N45

PROD QTY A209111 G783020 N958726Unclusteredordersandorder_itemtables ORD_NO PROD QTY ...

----- ------ ------ 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10 ORD_NO ORD_DT CUST_CD

------ ------ ------ 101 05-JAN-97 R01 102 07-JAN-97 N45

集群键ClusterKey(ORD_NO)101 ORD_DT CUST_CD 05-JAN-97 R01

PROD QTY A4102 20 A5675 19 W0824 10102 ORD_DT CUST_CD07-JAN-97N45

PROD QTY A209111 G783020 N958726在存储数据时，所有与这个集群键相关的行都存储在一个数据块上。如果数据都存储在同一个数据块上，并且将hash索引作为where子句的确切匹配条件，oracle就可以通过执行一个hash函数和一个IO来访问数据。如果通过一个二元高度是3的B树索引来访问数据，则需要在检索数据时使用4个IO。Select…FromCustomerA,OrderBWhereA.ord_no=B.ord_noAndA.ord_no=3975;使用B树索引I/O树枝块I/O树枝块I/O树枝块数据块表HASH键集群键数据散列函数1、等价查询2、匹配HASH列和确切的值3、基于HASH函数确定行的物理存储位置createclustercredit_cluster(card_novarchar2(16),transdatedatesort)hashkeys10000hashisora_hash(card_no)size256;createtablecredit_orders(card_novarchar2(16),transdatedate,amountnumber)clustercredit_cluster(card_no,transdate);集群键列：card_no、transdateHash列：card_noHASH索引可能是访问数据库中数据的最快方法，但是有自身的缺点：1、集群键上不同值的数目必须在创建hash集群之前就需要确定，需要在创建hash集群的时候指定这个值，低估了集群键的不同值的数字可能会导致集群的冲突（两个集群键有相同的HASH值）2、一旦这个值设置过低，需要重建cluster3、hash集群还可能浪费空间，如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间浪费4、如果不能为集群的未来增长分配好附加的空间，hash集群可能就不是最好的选择5、如果应用程序经常在集群上进行全表扫描，hash集群可能不是最好的选择，由于需要为未来增长分配好集群的剩余空间，全表扫描可能非常的消耗资源HASH索引非常适合数据仓库（相对静态值）索引组织表（IOT）索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。对于频繁在主键列上进行精确查找、范围查找的表来说，可以考虑使用IOT。我们还可以在IOT上建立二级索引。规则表的访问ROWID索引组织表(IOTs)索引组织表的访问Non-keycolumnsKeycolumnRowheaderSQL>CREATETABLEcountries(country_idCHAR(2)CONSTRAINTcountry_id_nnNOTNULL,country_nameVARCHAR2(40),currency_nameVARCHAR2(25),currency_symbolVARCHAR2(3),mapBLOB,flagBLOB,CONSTRAINTcountry_c_id_pkPRIMARYKEY(country_id))ORGANIZATIONINDEXPCTTHRESHOLD20 INCLUDING(currency_symbol)OVERFLOWTABLESPACEUSERS;反转索引当载入一些有序的数据时，索引肯定会碰到与IO相关的一些瓶颈。在载入期间，某部分索引和磁盘肯定会比其他部分使用频繁的多。为了解决这个问题，我们可以使用反转索引，这样对于有序数据的载入，oracle会更新不同的索引块。1234、1235、1236，如果数据以反转索引存储，那么这些数据的存放形式为：4321、5321、6321.结果就是索引会为每次新插入的行更新不同的索引块。KEYROWID------------------------12570000000F.0002.000128770000000F.0006.000145670000000F.0004.000166570000000F.0003.000189670000000F.0005.000196370000000F.0001.000199470000000F.0000.0001... ...反转索引Indexonemployee_idcolumnEmployeestableEMPLOYEE_IDLAST_NAME...--------------------7499ALLEN7369SMITH7521WARD...7566JONES7654MARTIN7698BLAKE7782CLARK.........创建反转索引SQL>createuniqueindexi1_t1ONt1(c1)2REVERSEpctfree303storage(initial200knext200k4pctincrease0maxextents50)5tablespaceindx;SQL>createuniqueindexi2_t1ONt1(c2);SQL>alterindexi2_t1REBUILDREVERSE;基于函数的索引Function-BasedIndexesSQL>CREATEINDEXFBI_UPPER_LASTNAME2ONCUSTOMERS(upper(cust_last_name));SQL>SELECT*

2FROMcustomers 3WHEREupper(cust_last_name)='SMITH';基于函数的索引需要考虑的问题1、基于函数的索引只能针对一种函数，对于其余的函数不起作用2、控制索引的数量，因为对DML会产生影响基于函数的索引会带来极大的好处。分区索引分区索引就是简单的把一个索引分成多个片段，这样可以访问更小的片段，可以将片段分别放在不同的磁盘驱动器上。B树索引和位图索引都可以被分区，而hash索引不能被分区。几种分区方式：1、表被分区而索引未被分区2、表未分区而索引被分区3、表和索引都被分区无论哪种方式，都必须使用基于成本的优化器分区可以提高性能和可维护性两种类型的分区索引1、本地分区索引 有前缀索引、无前缀索引2、全局分区索引 有前缀索引、无前缀索引如果使用了位图索引，就必须使用本地索引索引分区最主要的原因就是减少所需读取的索引大小、另外把分区放在不同的表空间中可以提高分区的可用性和可靠性在使用分区后的表和索引时，oracle还支持并行查询和并行DML，这样可以同时执行多个进程，从而加速处理这条语句。本地分区索引（通常使用的索引）使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含它所关联的表分区的键和ROWDI。 也就是说：表分区和索引分区是相同的本地分区索引可以是B树索引、位图索引，如果是B树索引，可以是唯一或不唯一的索引。对于分区表来说，每一个分区表有很大的独立性，对于分区索引来说，每个分区索引有很大的独立性。Oracle会自动维护本地索引。本地索引可以被单独重建，而不影响其他分区。有前缀的索引：有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。Participant表进行范围分区：分区列（survey_id、survy_date），然后我们在survey_id列上建立了一个有前缀的本地索引。索引的分区都使用表的相同范围界限来创建。本地的前缀索引可以让oracle快速剔除一些不必要的分区，也就是说没有包含在where条件子句中任何值的分区将不会被访问，这样可以大大的提高访问性能。无前缀的索引无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键（survey_id、survey_date）的相同分区表，建立在survy_date上的索引就是一个本地的无前缀索引。只要是本地索引（有前缀、无前缀）索引分区和表分区是一一对应的。如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。上面的例子中，我们可以建立（survey、survey_date）无前缀本地唯一索引。全局分区索引全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时，必须定义分区键的范围和值。全局索引只能使用B树索引，Oracle在默认情况下不会维护全集索引分区，如果一个表分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定altertable…updateglobalindexes。有前缀的索引表索引每个分区索引都包含指向多个表分区中行的索引条目。Oracle不支持无前缀全局分区索引位图连接索引位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中经常使用这种索引。快速重建索引执行alterindexcust_idx1rebuildparallelTablespaceusersStorage（pctincrease0）；使用已有索引而不是表来快速重建索引。可以指定rebuildonline选项，这样在重建索引的时候，允许对表分区进行DML。不能对位图索引或那些强制参照完整性约束的索引指定online