《基本索引原理》PPT课件.ppt
《《基本索引原理》PPT课件.ppt》由会员分享,可在线阅读,更多相关《《基本索引原理》PPT课件.ppt(71页珍藏版)》请在三一办公上搜索。
1、基本索引概念当从表中读取数据时,Oracle提供了两个选择:从表中读取每一行(全表扫描)通过ROWID一次读取一行当我们要访问大型表的少数行时,可能需要使用索引。因为如果没有索引,那么只能进行全表扫描。,索引改进性能的程度取决于两个因素:1、数据的选择性2、表数据在数据块上的分布如果选择性很高(例如身份证号码),那么根据索引值返回的ROWID很少,如果选择性很低(例如国家)则返回的ROWID很多,那么索引的性能将会大大降低如果选择性很高,但是相关的行在表中的存储位置并不互相靠近,则会进一步减少索引的益处,如果匹配索引值的数据分散在表的多个数据块中,则必须从表中选择多个单独的块以满足查询,基于索
2、引的读取是单块读取,如果使用全表扫描,使用的是多块读取以快速扫描表,因此全表扫描不见得比索引扫描速度慢。,全表扫描和索引查找之间的平衡点1、分区2、并行DML3、并行查询4、使用db_file_multiblock_read_count进行更大的IO操作5、硬件更为快速6、磁盘上的缓存可以缓存更多的数据7、内存的廉价使得我们的内存进一步增大8、Oracle采用了增强的索引特性(例如跳跃式扫描索引),SELECT、UPDATE、DELETE+WHERE条件可以从索引中得到好处(前提是:当访问的行数较少时)一般来说,增加索引会带来insert语句性能的下降如果根据未索引列update索引列,那么也
3、会带来性能的降低大量的delete也会因为索引的存在而导致性能降低因此我们要分析具体的情况,判断索引和DML语句之间的关系,我们如何去去判断一个表上的索引呢?,如果发现对一个表的DML速度较慢,可以执行上面的命令,判断是否是因为增加了索引而引起的性能问题。,组合索引当某个索引包含有多个列时,我们称这个索引为组合索引。在使用组合索引的时候,要谨慎选择索引列中的列顺序。一般来说,索引的第一列应该是最有可能在where子句中使用的列,并且也是在索引中最具有选择性的列。对于9i以前,查询只能在where子句中使用索引的第一列时使用索引。,除非在where子句中给empno指定一个值,否则一般不会使用组
4、合索引。,从Oracle 9i开始,引入了跳跃式索引扫描功能,即使在where子句中没有指定empno的数值,也会可能会使用索引。我们来举个例子,看一下跳跃式索引扫描。create index sex_emp_idonemp(sex,emp_id)select*from empwhere emp_id=123;,在Oracle数据库的内部,生成了两个查询,然后对两个查询的ROWID进行了联合。当使用跳跃式索引扫描时,自动给SEX加上了数值,启用了两个查询。如果SEX有50个数值,那么需要启用50个查询才能完成查询,因此性能大大降低。因此是否适合使用跳跃式索引扫描,取决于第一个索引列的选择性。一
5、般建议第一个列的可选性非常低。跳跃式索引扫描相对索引直接扫描速度要慢一些,但是相对表扫描速度还是要快很多。使用跳跃式索引的条件1 优化器认为是合适的.2 索引中的前导列的唯一值的数量能满足一定的条件.3 优化器要知道前导列的值分布(通过分析/统计表得到)4 合适的SQL语句,如果oracle没有选择使用跳跃式索引扫描,那么可能选择使用索引快速全局扫描或全表扫描。我们花点时间来研究一下Oracle中扫描数据的方法:1、全表扫描(Full Table Scan FTS)Oracle读取表中所有的行、多块读操作可以大大的减少IO的次数、利用多块读可以大大的提高全表扫描的速度、只有在全表扫描的情况下才
6、能使用多块读。在较大的表上不建议使用全表扫描、如果读取表的数据总量超过5%10%,那么通常进行全表扫描。并行查询可能会使得我们的路径选择采用全表扫描。,2、通过ROWID对表进行读取(Table access by ROWID)ROWID指出了数据文件、块号、行号,通过ROWID是oracle数据库中读取单行数据最快速的方法。这种方法不会采用多块读、而是会采用单块读的方式。,3、索引扫描或者索引查找(index scan index lookup)通过索引找到数据行的ROWID、然后通过ROWID直接到表中查找数据,这种方式称为索引查找或者索引扫描。因为一个ROWID对应一个数据行,因此这种方
7、式采用的也是单块读。在索引中,除了存储每个索引值、还存储相应的ROWID,索引扫描分为两步:1、扫描索引得到相应的ROWID2、通过找到的ROWID从表中读取相应的数据每次采用的都是单块IO读因为索引小、而且经常使用,因此通常被cache到内存中,因此第一步通常是逻辑读(数据可以从内存中得到)因为表数据比较大、因此第二步读通常是物理读,因此性能较低,访问路径走的是主键索引,因此是INDEX UNIQUE SCAN首先是索引扫描、然后是根据索引查找到的ROWID进行表的访问。,这个查询中,因为访问的列都在索引中,因此省略了访问的第二步。,因为访问路径走的是非唯一索引,因此是INDEX RANGE
8、 SCAN,索引本身已经是排序的,因此只需要读取数据、不需要对数据进行排序。,根据索引的类型和where条件的限制不同,有四种索引扫描类型:1、索引唯一扫描(INDEX UNIQUE SCAN)2、索引范围扫描(INDEX RANGE SCAN)3、索引全扫描(INDEX FULL SCAN)4、索引快速扫描(INDEX FAST FULL SCAN),索引唯一扫描通过唯一键、主键,oracle通常返回一个数据行,因此采用的是索引唯一扫描,索引范围扫描1、在唯一键上使用range操作符(、=、=、between)2、在组合索引上,只使用部分列进行查询、导致查询出多行3、对非唯一索引列上进行的查
9、询,索引全扫描查询出的数据必须全部从索引中得到,索引快速扫描扫描索引块中的所有数据块,这点与full index scan相似,但是索引快速扫描不进行数据的排序,在这种方式下,可以使用多块读功能、也可以使用并行读功能,最大化数据的吞吐量。,限制索引这个是一些没有经验的开发人员经常犯的错误之一。在SQL中,有很多陷阱让索引没有办法使用。很多情况使用索引1、where子句中使用到了索引列2、没有where子句,但是也可能使用到索引查询索引列的MIN或者MAX对索引列执行count等等,我们下面来研究一下使用where但是阻止oracle使用索引的几种情况。1、使用不等于运算符(、!=)在where
10、中使用不等于条件,将会使索引失效。,当分析表的时候,oracle收集表中数据分布的相关统计信息,通过使用这种分析,基于成本的优化器可以决定在where子句中对一些值使用索引,而对其他的值不使用索引。因此不是说在一个列上建立了索引就一直会使用索引。根据不同值,优化器都会确定是否使用索引。,使用索引的条件就是:根据索引树定位索引页节点、,2、使用 IS NULL或IS NOT NULL在where子句中使用IS NULL或者IS NOT NULL同样会限制索引的使用。如果被索引的列在某些行中存在NULL值,在索引列中就不会有相应的条目。(例外:位图索引对于NULL列也会进行记录,因此位图索引对于N
11、ULL搜索通常较为快速)。,因此建议对列加上NOT NULL或者DEFAULT。,3、使用函数如果不使用基于函数的索引(后面会讲到),那么在SQL语句的where子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。一些常见的函数:trunc、substr、to_date、to_char、instr等,都可能会使索引失效。解决方案:1、使用基于函数的索引2、灵活书写SQL、避免在索引列上使用SQL函数,通过灵活一变,SQL语句的访问路径变成了走索引。为了走索引,就不要在where子句中的索引列上使用函数。,4、比较不匹配的数据类型这个是比较难于发现的问题。Oracle不会对不匹配的数据类型
12、报错,Oracle会隐式地把VARCHAR2列的数据类型转换成要被比较的数值型数据类型(这是一个例子,还存在其他的数据类型转换)。,自动加了一个函数to_number(zip),对于不匹配的数据类型,Oracle隐式的加上一个转换函数。为什么不加载到100043上面,因为这是一个常量,常量是不能改变的。,灵活的使用,在常量上面加上一个,表示这是一个字符常量,这样字符常量就和ZIP VARCHAR2一致了。索引列的数据类型和常量类型要求一致。,索引的选择性Oracle通过多种方法来判断使用索引的价值,第一个方法就是判断索引中的唯一值或不同键的数量。唯一键的数量/表的行数,判断索引的选择性。选择性
13、越高、索引返回的行数越少,该索引就越好。,通过这个来判断索引的选择性。,这个表的这个索引的可选择性很高(100%)。,集群因子(cluster factor)集群因子是索引与他所基于的表相比较得出的有序性度量,它用于检查在索引访问之后执行的表查找的成本(将集群因子与选择性相乘即可得到该操作的成本)。索引扫描以后,根据从索引中取出的ROWID顺序进行表数据的查找。,什么是Clustering Factor 呢?Clustering Factor是的含义是如果通过一个索引扫描一张表,需要访问的表的数据块的数量。Clustering Factor计算的方法如下:1、扫描一个索引2、比较某行的rowi
14、d和前一行的rowid,如果这两个rowid不属于同一个数据块,那么cluster factor增加13、整个索引扫描完毕后,就得到了该索引的cluster factor。如果Clustering Factor接近于表存储的块数,说明这张表是按照索引字段的顺序存储的。如果Clustering Factor接近于行的数量,那说明这张表不是按索引字段顺序存储的。在计算索引访问成本的时候,这个值十分有用。Clustering Factor乘以选择性参数(selectivity)就是访问索引的开销。如果这个统计数据不能真实反映出索引的真实情况,那么可能会造成优化器错误的选择执行计划。另外如果某张表上的
15、大多数访问是按照某个索引做索引扫描,那么将该表的数据按照索引字段的顺序重新组织,可以提高该表的访问性能。,这个段分配了一个extent,因此这里面显示的是8blocks,不是实际的占用块数。,因此这个表占用的blocks是1个。,聚集因子的数量和表的块数一致,因此表的数据排序和索引一致,因此在进行索引范围扫描的时候,对表的访问成本还是比较低的。聚集因子主要影响的是索引范围扫描。如果聚集因子很高,那么建议对表进行重新整理,降低聚集因子。,二元高度(binary height)索引查找分为两个过程:1、根据树进行定位、找出ROWID(索引查找)2、根据ROWID找出表中的数据行(表数据查找)进行索
16、引查找的时候,首先从树根开始读数据,通过中间节点,最后定位到叶节点,整个过程只能进行单数据块的读取。,索引,表,二元高度是3,读取一行数据经过了四次数据块的读取,其中三次是索引,一次是表数据。,查找每个索引的二元高度,这个索引只占用了一个数据块,因此二元高度是0,二元高度主要随着表中索引列的非NULL值以及索引列中值的宽度而变化。如果索引列上大量的行被删除,那么他的二元高度不会降低,重建索引会降低二元高度,如果一个索引中被删除的行接近2025%,重建索引会减低二元高度。二元高度对索引的性能影响不是很大,但是在可能的情况下,降低二元高度还是有必要的。,使用直方图在分析表和索引时,直方图用于记录数
17、据的分布。通过获取该信息,基于成本的优化器就可以决定使用将返回少量行的索引,而避免使用基于限制条件返回许多行的索引。直方图的使用不受索引的限制,我们可以在表的任何列上构建直方图(一般是在表的索引列上构建直方图)。构建直方图最主要的原因就是:帮助优化器在表中数据严重倾斜时做出更好的规划。如果一个表中的列上(通常是索引列)数据发生严重的倾斜,那么在这个列上建立直方图将非常的有意义。这样优化器就知道什么时候该使用索引、什么时候不该使用索引。,在hr用户下面的employees表的job_id列上建立了一个直方图。这个直方图有10个存储桶。,行数,10,10,10,10,10,10,10,10,10,
18、10,1000-1300,1301-1400,1401-1430,1430-1500,1430,1430,1430,1430,1430,1430,对整个列的100行数据进行均分成10个存储桶,每个桶中存储10个数据行。然后写出每个桶中的数据范围。我们发现1430这个数值对应的行号数非常多,大约73行,占到了73%。因此当我们使用where id=1403的时候,优化器不会走索引。默认情况下,oracle的直方图会产生75个存储桶,可以把存储桶的size限制在1254之间。,快速全局索引扫描在索引的快速全局扫描期间,Oracle读取B树索引上的所有树叶块,这个索引可以按照顺序读取,这样可以一次读
19、取多个块,初始化文件中的DB_FILE_MULTIBLOCK_READ_COUN T可以控制同时被读取的块数。相对全表扫描,快速全局索引扫描通常需要较少的物理IO。如果表查询的所有列都被包含在索引中,而索引的前置列并不在where条件中,就可以使用快速全局索引扫描。select empno,ename,deptnofrom empwhere deptno=30;empno,ename,deptno上有一个组合索引。作为一个选择,针对上面的情况,Oracle可能采取跳跃式索引扫描。,跳跃式索引扫描组合索引、where条件中没有出现前置列(通常是组合索引的第二列)。相对全索引扫描,跳跃式索引扫描读
20、取的数据块更少、速度更快。,读取的块数明显减少,索引类型1、B树索引2、位图索引3、HASH索引4、索引组织表IOT5、反转键(reverse keys)索引6、基于函数的索引7、分区索引(本地索引、全局索引)8、位图连接索引,B树索引B树索引在Oracle中是一个通用索引,创建索引的时候默认就是B树索引。单列索引组合索引(最多可以多达32个列)对于B树索引,我们需要关注他的二元高度(blevel)。B树索引保存了在索引列上有值的每个数据行的ROWID。Oracle不会对索引列上包含NULL值的行进行索引,如果索引是一个组合索引,而其中列上包含NULL值,这一行会于索引列中。,B树索引图释。,
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 基本索引原理 基本 索引 原理 PPT 课件
链接地址:https://www.31ppt.com/p-5487139.html