Oracle培训之索引-.ppt

ORACLE ORACLE 培训培训 之之INDEXINDEX 2024/5/22 周三1困惑困惑 只知道createindex怎么写，而不了解oracle的index内部机制？提供的索引选项太多，不知在给定的条件下使用何种index？如何合理使用资源，作出正确选择，防止死锁的发生，提升数据库性能？此应用的数据表，很少用来查询，可否不建立索引？2024/5/22 周三2基本的索引概念基本的索引概念 从表中访问数据的时候，从表中访问数据的时候，OracleOracle提供了两个选择：从表中顺序读取每一行（即全表提供了两个选择：从表中顺序读取每一行（即全表扫描），或者通过扫描），或者通过rowidrowid一次读取一行一次读取一行(此即为通过索引来检索数据此即为通过索引来检索数据)。访问大型表的访问大型表的少量行时，使用索引可以执行较少的少量行时，使用索引可以执行较少的IOIO，没有必要访问表中的所有的数据块，没有必要访问表中的所有的数据块索引改进性能的程度取决于：索引改进性能的程度取决于：1 1）数据的选择性）数据的选择性如果数据非常具有选择性，则表中将只有很少的行匹配索引值。如果数据非常具有选择性，则表中将只有很少的行匹配索引值。OracleOracle将能够快将能够快速查询匹配索引值的速查询匹配索引值的RowidRowid的索引，并且可以快速查询少量的相关表块。如果数据的索引，并且可以快速查询少量的相关表块。如果数据选择性不高，则索引可能返回许多选择性不高，则索引可能返回许多rowidrowid，导致从表中查询许多单独的块，则其访问，导致从表中查询许多单独的块，则其访问的数据块可能比全表扫描还高。的数据块可能比全表扫描还高。2024/5/22 周三3基本的索引概念基本的索引概念（2 2）在表的块之间分布数据的方式）在表的块之间分布数据的方式如果数据非常具有选择性，但相关的数据的行在表中的存储位置并不互相靠近，如果数据非常具有选择性，但相关的数据的行在表中的存储位置并不互相靠近，则会进一步减少索引的益处。如果匹配索引值的数据分散在多个块中，则必须从表则会进一步减少索引的益处。如果匹配索引值的数据分散在多个块中，则必须从表中选择多个单独的块以满足查询。在一些情况中，您会发现当数据分散在表的多个中选择多个单独的块以满足查询。在一些情况中，您会发现当数据分散在表的多个块中时，最好是不使用索引，而是执行全表扫描。执行全表扫描时，块中时，最好是不使用索引，而是执行全表扫描。执行全表扫描时，OracleOracle使用多使用多块读取以满足快速扫描表。基于索引的读取是单块读取，因此在使用索引的时的目块读取以满足快速扫描表。基于索引的读取是单块读取，因此在使用索引的时的目标是减少解决查询所需要的单个块的数量标是减少解决查询所需要的单个块的数量注：注：oracleoracle中的一些可用选项，中的一些可用选项，egeg：分区、并行：分区、并行DMLDML、并行操作、并行操作、db_file_multiblock_db_file_multiblock_Read_countRead_count进行更大的进行更大的IOIO操作操作，2024/5/22 周三4索引索引 对对 select 语句语句 DML语句的影响语句的影响索引通常能提高查询的性能，因此select，具有较高选择率的update和delete语句能够提高速度(因为在大部份情况下维护索引的成本比因为在大部份情况下维护索引的成本比因为在大部份情况下维护索引的成本比因为在大部份情况下维护索引的成本比update,deleteupdate,delete的时候定位的时候定位的时候定位的时候定位需要处理的数据的代价低需要处理的数据的代价低需要处理的数据的代价低需要处理的数据的代价低)索引降低索引降低Insert 语句的性能语句的性能(因为同时要对表和索引进行插入因为同时要对表和索引进行插入)索引列的索引列的update 和和delete 同样会更新表和索引，故需要在数据操作性能和使用索同样会更新表和索引，故需要在数据操作性能和使用索引的引的 查询性能查询性能 中找到平衡点。中找到平衡点。开发人员和测试人员可以通过以下视图查询索引情况：开发人员和测试人员可以通过以下视图查询索引情况：(1)索引信息索引信息Select table_name,index_name from user_indexes where table_name=*;(2)索引列的信息索引列的信息Select table_name,index_name,column_name,column_position from user_ind_columns;2024/5/22 周三5索引的类型索引的类型B树索引位图索引HASH索引反转键(reversekey)索引函数索引分区索引(本地和全局索引)2024/5/22 周三6B树索引树索引B树索引在oracle中是一个通用索引，是默认的索引类型，可以为单列索引，也可以是组合/复合索引，最多32列。最顶端的数据块叫做根块(rootblock)每个查找都是从根块开始，根块指向分支块(branchblock)每个分支块再指向下一个分支块，或者指向叶子块(leafblock)叶子块存储键值和指向数据的rowid，对于一个给定的索引根块到任何节点的层数都是一样的，是平衡的，为了支持范围查找，叶子块是相互链接的。B*B*树的特点之一是：所有叶子块都应该在树的同一层上，这一层称之为索引的高度树的特点之一是：所有叶子块都应该在树的同一层上，这一层称之为索引的高度,它说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。由此可见它说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。由此可见B*B*树的树的B B代表的是代表的是balancedbalanced，所谓的，所谓的HeightbalancedHeightbalanced。大多数。大多数B*B*树索引的高度都是树索引的高度都是2 2或或3 3，即使索引中有数百万行记录也是如此，这说明，一般而言，即使索引中有数百万行记录也是如此，这说明，一般而言，在索引中找到一个键在索引中找到一个键在索引中找到一个键在索引中找到一个键只需要只需要只需要只需要2 2到到到到3 3次次次次I/O,I/O,这确实不错。这确实不错。这确实不错。这确实不错。2024/5/22 周三7B+树索引树索引2024/5/22 周三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)就小，最小值是表中非空的数据块总数最大值为索引中的键数2024/5/22 周三9聚簇因子聚簇因子(clustering_factor)2024/5/22 周三10b*树子索引树子索引复合索引复合索引b*b*树子索引树子索引复合索引复合索引复合索引也是一种复合索引也是一种B*B*树索引，它由多列组成。当我们拥有使用两列或超过两列树索引，它由多列组成。当我们拥有使用两列或超过两列的频繁查询时，就使用的频繁查询时，就使用B*B*树复合索引，而其所使用的两列或多列在树复合索引，而其所使用的两列或多列在wherewhere子句中子句中andand逻辑操作符连接。因为复合索引中列的顺序很重要，所以确信以最有效的索引顺序逻辑操作符连接。因为复合索引中列的顺序很重要，所以确信以最有效的索引顺序排列他们，前导列应该是基数大的列（不同的取值多）。可以合理的运用索引键压排列他们，前导列应该是基数大的列（不同的取值多）。可以合理的运用索引键压缩。而且单独的缩。而且单独的wherewhere也会使用此索引，而后序列则必须与前导一起使用。也会使用此索引，而后序列则必须与前导一起使用。收集索引的统计信息：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);-只收集索引信息2024/5/22 周三11位图索引位图索引位图索引非常合适决策支持系统(DSS)和数据仓库他们不适合OLTP，适用于较少或者中等基数(不同值的数量)的列访问的大表，索引列最多可达到30，因为位图对于低基数非常快。Eg：Selectcount(*)fromTwheregender=Mandlocationin(1,11,30)andage_group=41andover-非常适合bitmap索引注：(1)只有B-树索引才能用于主键和唯一键(2)由于索引的锁是内部设定，只有b树索引支持行级锁，因为单个位图索引可能索引数千行，DML语句容易产生死锁，在在在在oltpoltp中对于表中数据经常发生变化的会产生严中对于表中数据经常发生变化的会产生严中对于表中数据经常发生变化的会产生严中对于表中数据经常发生变化的会产生严重的性能问题，重的性能问题，重的性能问题，重的性能问题，(3)(3)alter table alter table 修改位图索引列，修改位图索引列，修改位图索引列，修改位图索引列，会使索引失效。会使索引失效。会使索引失效。会使索引失效。(3)Oracle10g以前索引空间管理问题，会导致过于频繁的索引rebuild2024/5/22 周三13反转键索引反转键索引 OracleOracle推出它的主要目的就是为了降低在并行服务器（推出它的主要目的就是为了降低在并行服务器（OracleParallelServerOracleParallelServer）环）环境下索引叶块的争用。当境下索引叶块的争用。当B*TreeB*Tree索引中列是由递增的序列号产生的话，那么这些索索引中列是由递增的序列号产生的话，那么这些索引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产引信息基本上分布在同一个叶块，当用户修改或访问相似的列时，索引块很容易产生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引生争用。反向索引中的索引码将会被分布到各个索引块中，减少了争用。反向索引反转了索引码中每列的字节，使得它们不会分到一个叶子块中，但是反向索引也因反转了索引码中每列的字节，使得它们不会分到一个叶子块中，但是反向索引也因此不能应用于范围搜索，因为它的相邻叶子块是无顺序的。此不能应用于范围搜索，因为它的相邻叶子块是无顺序的。CREATEINDEXindex_nameontable_name(column_name)REVERSE;CREATEINDEXindex_nameontable_name(column_name)REVERSE;考虑一个限制条件考虑一个限制条件wherenamelike%awherenamelike%a和和wherenamelikea%wherenamelikea%这两条的执行效率这两条的执行效率明显明显a%a%比比%a%a的效率要高的效率要高，但应用却是要查出以，但应用却是要查出以a a结尾的，所以用结尾的，所以用a%a%没用没用这样反向键索引就有用处了？这样反向键索引就有用处了？SQLcreateindexin_test5ontest(reverse(object_name);SQLcreateindexin_test5ontest(reverse(object_name);Indexcreated.Indexcreated.2024/5/22 周三14 降序索引是降序索引是oracle8ioracle8i引入的，用以扩展引入的，用以扩展B*B*树索引的功树索引的功能，它允许在索引中以降序能，它允许在索引中以降序(从大到小的顺序从大到小的顺序)存储一存储一列。对于降序的列。对于降序的orderbyorderby有效。有效。降序索引降序索引2024/5/22 周三15基于函数的索引基于函数的索引 应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。应用于查询语句条件列上包含函数的情况，索引中储存了经过函数计算的索引码值。可以在不修改应用程序的基础上能提高查询效率可以在不修改应用程序的基础上能提高查询效率 EgEg：UPPERUPPER，lowerlower，initcapinitcap等函数等函数如果优化器使用函数索引，必须如果优化器使用函数索引，必须 Altersystemsetquery_rewrite_enabled=true;Altersystemsetquery_rewrite_enabled=true;基于函数索引需考虑以下：基于函数索引需考虑以下：(1)(1)应用设置的函数能限制在此列上使用？应用设置的函数能限制在此列上使用？能限制此列上的执行的所有函数能限制此列上的执行的所有函数(2)(2)是否有足够的空间来存储索引？是否有足够的空间来存储索引？(3)(3)每列上增加的索引数量会针对该表执行的每列上增加的索引数量会针对该表执行的DMLDML语句带来性能问题语句带来性能问题2024/5/22 周三16建立索引规则建立索引规则 建立索引常用的规则如下：建立索引常用的规则如下：1 1、表的主键、外键必须有索引；、表的主键、外键必须有索引；2 2、数据量超过、数据量超过1000010000的表应该有索引的表应该有索引(此标准根据数据库配置而定此标准根据数据库配置而定)；3 3、经常与其他表进行连接的表，在连接字段上应该建立索引；、经常与其他表进行连接的表，在连接字段上应该建立索引；4 4、经常出现在、经常出现在WhereWhere子句中的字段，特别是大表的字段，应该建立索引；子句中的字段，特别是大表的字段，应该建立索引；5 5、索引应该建在选择性高的字段上索引应该建在选择性高的字段上(索引的选择性指的仅通过索引键条件筛选出来索引的选择性指的仅通过索引键条件筛选出来的记录跟总记录的比的记录跟总记录的比,这里最容易出问题的是大家认为最终出来的结果很少并且使用这里最容易出问题的是大家认为最终出来的结果很少并且使用了索引但效率很低了索引但效率很低,这实际就是索引不合适选择率不高所致这实际就是索引不合适选择率不高所致)；6 6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；2024/5/22 周三17建立索引规则建立索引规则 7 7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替 A A、正确选择复合索引中的主列字段，一般是选择性较好的字段；、正确选择复合索引中的主列字段，一般是选择性较好的字段；B B、复合索引的几个字段是否经常同时以、复合索引的几个字段是否经常同时以ANDAND方式出现在方式出现在WhereWhere子句子句 中？单字段中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；C C、如果复合索引中包含的字段经常单独出现在、如果复合索引中包含的字段经常单独出现在WhereWhere子句中，则分解为多个单子句中，则分解为多个单字段索引；字段索引；D D、如果复合索引所包含的字段超过、如果复合索引所包含的字段超过3 3个，那么仔细考虑其必要性，考虑减少复合个，那么仔细考虑其必要性，考虑减少复合的字段；的字段；E E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；引；2024/5/22 周三18建立索引规则建立索引规则 88、频繁进行数据操作的表，不要建立太多的索引；、频繁进行数据操作的表，不要建立太多的索引；9 9、删除无用的索引，避免对执行计划造成负面影响；、删除无用的索引，避免对执行计划造成负面影响；以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。2024/5/22 周三19为什么没有使用我创建的索引为什么没有使用我创建的索引Case1：已经建立了B树索引，但是谓词中没有使用索引的最前列，此时oracle会出现两种扫描方式：（1）索引跳跃式扫描（2）全表扫描Case2：由于b树索引不会存储null的行的相应索引条目，如果此列中有null的行，进行selectcount(*)fromT不会走indexCase3：对于一个索引的列使用了函数select*fromtwheref(indexed_column)=value;如需解决可以考虑使用函数索引2024/5/22 周三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需要关注)2024/5/22 周三21讨论选择率讨论选择率 最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高全表扫描可能会导致更多的物理磁盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于数小于1010的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于高很多。但是如果表非常有顺序，那么如果查询的记录数大于4040时，可能使用全时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：表扫描更快。因此，有一个索引范围扫描的总体原则是：1 1）对于原始排序的表）对于原始排序的表 仅读取少于表记录数仅读取少于表记录数4040的查询应该使用索引范围扫描。反之，的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的读取记录数目多于表记录数的4040的查询应该使用全表扫描。的查询应该使用全表扫描。2 2）对于未排序的表）对于未排序的表仅读取少于表记录数仅读取少于表记录数5 5的查询应该使用索引范围扫描。反之，的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的读取记录数目多于表记录数的5 5的查询应该使用全表扫描。的查询应该使用全表扫描。2024/5/22 周三22谢谢 谢谢2024/5/22 周三23