Oracle培训之索引 PPT课件

ORACLE培训之INDEX,2020/5/6,1,困惑,只知道createindex怎么写，而不了解oracle的index内部机制？提供的索引选项太多，不知在给定的条件下使用何种index？如何合理使用资源，作出正确选择，防止死锁的发生，提升数据库性能？此应用的数据表，很少用来查询，可否不建立索引？,2020/5/6,2,基本的索引概念,从表中访问数据的时候，Oracle提供了两个选择：从表中顺序读取每一行（即全表扫描），或者通过rowid一次读取一行(此即为通过索引来检索数据)。访问大型表的少量行时，使用索引可以执行较少的IO，没有必要访问表中的所有的数据块索引改进性能的程度取决于：1）数据的选择性如果数据非常具有选择性，则表中将只有很少的行匹配索引值。Oracle将能够快速查询匹配索引值的Rowid的索引，并且可以快速查询少量的相关表块。如果数据选择性不高，则索引可能返回许多rowid，导致从表中查询许多单独的块，则其访问的数据块可能比全表扫描还高。,2020/5/6,3,基本的索引概念,（2）在表的块之间分布数据的方式如果数据非常具有选择性，但相关的数据的行在表中的存储位置并不互相靠近，则会进一步减少索引的益处。如果匹配索引值的数据分散在多个块中，则必须从表中选择多个单独的块以满足查询。在一些情况中，您会发现当数据分散在表的多个块中时，最好是不使用索引，而是执行全表扫描。执行全表扫描时，Oracle使用多块读取以满足快速扫描表。基于索引的读取是单块读取，因此在使用索引的时的目标是减少解决查询所需要的单个块的数量注：oracle中的一些可用选项，eg：分区、并行DML、并行操作、db_file_multiblock_Read_count进行更大的IO操作，,2020/5/6,4,索引对select语句DML语句的影响,索引通常能提高查询的性能，因此select，具有较高选择率的update和delete语句能够提高速度(因为在大部份情况下维护索引的成本比update,delete的时候定位需要处理的数据的代价低)索引降低Insert语句的性能(因为同时要对表和索引进行插入)索引列的update和delete同样会更新表和索引，故需要在数据操作性能和使用索引的查询性能中找到平衡点。开发人员和测试人员可以通过以下视图查询索引情况：(1)索引信息Selecttable_name,index_namefromuser_indexeswheretable_name=*;(2)索引列的信息Selecttable_name,index_name,column_name,column_positionfromuser_ind_columns;,2020/5/6,5,索引的类型,B树索引位图索引HASH索引反转键(reversekey)索引函数索引分区索引(本地和全局索引),2020/5/6,6,B树索引,B树索引在oracle中是一个通用索引，是默认的索引类型，可以为单列索引，也可以是组合/复合索引，最多32列。最顶端的数据块叫做根块(rootblock)每个查找都是从根块开始，根块指向分支块(branchblock)每个分支块再指向下一个分支块，或者指向叶子块(leafblock)叶子块存储键值和指向数据的rowid，对于一个给定的索引根块到任何节点的层数都是一样的，是平衡的，为了支持范围查找，叶子块是相互链接的。B*树的特点之一是：所有叶子块都应该在树的同一层上，这一层称之为索引的高度,它说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。由此可见B*树的B代表的是balanced，所谓的Heightbalanced。大多数B*树索引的高度都是2或3，即使索引中有数百万行记录也是如此，这说明，一般而言，在索引中找到一个键只需要2到3次I/O,这确实不错。,2020/5/6,7,B+树索引,2020/5/6,8,索引相关信息,查看索引统计信息Selectindex_nameasname,blevel,leaf_blocksasleaf_blks,distinct_keysasdst_keys,num_rows,clustering_factorasclust_fact,avg_leaf_blocks_per_keyasleaf_per_key,avg_data_blocks_per_keyasdata_per_keyfromuser_ind_statisticswheretable_name=*；Blevel为了访问叶子块而需要读取的分支块的数量，包括根块Leaf_blocks-索引中叶子块数Distinct_keys-索引中的唯一键值总数Num_rows-索引中的键值数，对于主键来说，等同于distinct_keysClustering_factor表明有多少临近的索引条目指到不同的数据块，如果表的数据和索引排序是相似的，聚簇因子(clusteringfactor)就小，最小值是表中非空的数据块总数最大值为索引中的键数,2020/5/6,9,聚簇因子(clustering_factor),2020/5/6,10,b*树子索引复合索引,b*树子索引复合索引复合索引也是一种B*树索引，它由多列组成。当我们拥有使用两列或超过两列的频繁查询时，就使用B*树复合索引，而其所使用的两列或多列在where子句中and逻辑操作符连接。因为复合索引中列的顺序很重要，所以确信以最有效的索引顺序排列他们，前导列应该是基数大的列（不同的取值多）。可以合理的运用索引键压缩。而且单独的where也会使用此索引，而后序列则必须与前导一起使用。收集索引的统计信息：SQLexecdbms_stats.set_param(cascade,false);-默认是级联收集索引信息，这句命令是关闭此功能SQLexecdbms_stats.gather_table_stats(SCOTT,DEPT);-收集dept表SQLEXECdbms_stats.gather_index_stats(SCOTT,PK_EMP,estimate_percent=dbms_stats.auto_sample_size);-只收集索引信息,2020/5/6,11,位图索引,位图索引非常合适决策支持系统(DSS)和数据仓库他们不适合OLTP，适用于较少或者中等基数(不同值的数量)的列访问的大表，索引列最多可达到30，因为位图对于低基数非常快。Eg：Selectcount(*)fromTwheregender=Mandlocationin(1,11,30)andage_group=41andover-非常适合bitmap索引注：只有B-树索引才能用于主键和唯一键由于索引的锁是内部设定，只有b树索引支持行级锁，因为单个位图索引可能索引数千行，DML语句容易产生死锁，在oltp中对于表中数据经常发生变化的会产生严重的性能问题，altertable修改位图索引列，会使索引失效。(3)Oracle10g以前索引空间管理问题，会导致过于频繁的索引rebuild,2020/5/6,13,反转键索引,Oracle推出它的主要目的就是为了降低在并行服务器（OracleParallelServer）环境下索引叶块的争用。当B*Tree索引中列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节，使得它们不会分到一个叶子块中，但是反向索引也因此不能应用于范围搜索，因为它的相邻叶子块是无顺序的。CREATEINDEXindex_nameontable_name(column_name)REVERSE;考虑一个限制条件wherenamelike%a和wherenamelikea%这两条的执行效率明显a%比%a的效率要高，但应用却是要查出以a结尾的，所以用a%没用这样反向键索引就有用处了？SQLcreateindexin_test5ontest(reverse(object_name);Indexcreated.,2020/5/6,14,降序索引是oracle8i引入的，用以扩展B*树索引的功能，它允许在索引中以降序(从大到小的顺序)存储一列。对于降序的orderby有效。,降序索引,2020/5/6,15,基于函数的索引,应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率Eg：UPPER，lower，initcap等函数如果优化器使用函数索引，必须Altersystemsetquery_rewrite_enabled=true;基于函数索引需考虑以下：(1)应用设置的函数能限制在此列上使用？能限制此列上的执行的所有函数是否有足够的空间来存储索引？每列上增加的索引数量会针对该表执行的DML语句带来性能问题,2020/5/6,16,建立索引规则,建立索引常用的规则如下：1、表的主键、外键必须有索引；2、数据量超过10000的表应该有索引(此标准根据数据库配置而定)；3、经常与其他表进行连接的表，在连接字段上应该建立索引；4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；5、索引应该建在选择性高的字段上(索引的选择性指的仅通过索引键条件筛选出来的记录跟总记录的比,这里最容易出问题的是大家认为最终出来的结果很少并且使用了索引但效率很低,这实际就是索引不合适选择率不高所致)；6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；,2020/5/6,17,建立索引规则,7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替A、正确选择复合索引中的主列字段，一般是选择性较好的字段；B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；,2020/5/6,18,建立索引规则,8、频繁进行数据操作的表，不要建立太多的索引；9、删除无用的索引，避免对执行计划造成负面影响；以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。,2020/5/6,19,为什么没有使用我创建的索引,Case1：已经建立了B树索引，但是谓词中没有使用索引的最前列，此时oracle会出现两种扫描方式：（1）索引跳跃式扫描（2）全表扫描Case2：由于b树索引不会存储null的行的相应索引条目，如果此列中有null的行，进行selectcount(*)fromT不会走indexCase3：对于一个索引的列使用了函数select*fromtwheref(indexed_column)=value;如需解决可以考虑使用函数索引,2020/5/6,20,为什么没有使用我创建的索引,Case4：比较不匹配的数据类型(数据隐式转换)，如果我们在account_number使用了varchar2类型但是我们执行了以下查询：select*frombankswhereaccount_number=990345等价于select*frombankswhereto_number(account_number)=990345Case5：使用!=，模糊查询使用like%*%使用isnull或者isnotnull可以使用createindexindex_nameonT(index_column,1);但是建议对相关设计改良，采用notnulldefault值处理Case6：表的索引统计信息不准确，导致扫描错误(DBA需要关注),2020/5/6,21,讨论选择率,最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更