2022年索引详解 .pdf
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_1.gif)
![资源得分’ title=](/images/score_05.gif)
《2022年索引详解 .pdf》由会员分享,可在线阅读,更多相关《2022年索引详解 .pdf(16页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、一索引介绍 1.1 索引的创建语法:CREATE UNIUQE | BITMAP INDEX . ON . ( | ASC | DESC, | ASC | DESC,.) TABLESPACE STORAGE LOGGING | NOLOGGING COMPUTE STATISTICS NOCOMPRESS | COMPRESS NOSORT | REVERSE PARTITION | GLOBAL PARTITION 相关说明1) UNIQUE | BITMAP : 指定 UNIQUE 为唯一值索引, BITMAP为位图索引, 省略为 B-Tree索引。2) | ASC | DESC:可以对
2、多列进行联合索引,当为expression时即“基于函数的索引”3)TABLESPACE:指定存放索引的表空间( 索引和原表不在一个表空间时效率更高) 4)STORAGE:可进一步设置表空间的存储参数5)LOGGING | NOLOGGING:是否对索引产生重做日志( 对大表尽量使用NOLOGGING来减少占用空间并提高效率) 6)COMPUTE STATISTICS:创建新索引时收集统计信息7)NOCOMPRESS | COMPRESS:是否使用“键压缩”( 使用键压缩可以删除一个键列中出现的重复值) 8)NOSORT | REVERSE:NOSORT 表示与表中相同的顺序创建索引,REVE
3、RSE 表示相反顺序存储索引值9)PARTITION | NOPARTITION:可以在分区表和未分区表上对创建的索引进行分区1.2 索引特点:第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页,共 16 页 - - - - - - - - - 第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。第四,在使用分组和排序子句进行数据
4、检索时,同样可以显著减少查询中分组和排序的时间。第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。1.3 索引不足:第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。1.4 应该建索引列的特点:1)在经常需要搜索的列上,可以加快搜索的速度;2)在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;3)在经常用在连接的列上,这些列
5、主要是一些外键,可以加快连接的速度;4)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;5)在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;6)在经常使用在WHERE 子句中的列上面创建索引,加快条件的判断速度。1.5 不应该建索引列的特点:第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,
6、例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页,共 16 页 - - - - - - - - - 行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。第三, 对于那些定义为blob 数据类型的列不应该增加索引。这是因为, 这些列的数据量要么相当大,要么取值很少。第四,当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,
7、但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。1.6 限制索引限制索引是一些没有经验的开发人员经常犯的错误之一。在SQL中有很多陷阱会使一些索引无法使用。下面讨论一些常见的问题: 1.6.1 使用不等于操作符(、 != )下面的查询即使在cust_rating列有一个索引,查询语句仍然执行一次全表扫描。 select cust_Id,cust_name from customers where cust_rating aa; 把上面的语句改成如下的查询语句,这样,在采用基于规则的优化器而不是基于代价的优化器(更智能)时,将会
8、使用索引。 select cust_Id,cust_name from customers where cust_rating aa; 特别注意: 通过把不等于操作符改成OR条件,就可以使用索引, 以避免全表扫描。 1.6.2 使用 IS NULL 或 IS NOT NULL 使用 IS NULL 或 IS NOT NULL同样会限制索引的使用。因为NULL值并没有被定义。在 SQL语句中使用NULL会有很多的麻烦。因此建议开发人员在建表时,把需要索引的列设成NOT NULL 。如果被索引的列在某些行中存在NULL值,就不会使用这个索引(除非索引是一个位图索引,关于位图索引在稍后在详细讨论)。
9、 1.6.3 使用函数如果不使用基于函数的索引,那么在 SQL语句的 WHERE 子句中对存在索引的列使用函数时, 会使优化器忽略掉这些索引。下面的查询不会使用索引(只要它不是基于函数的索引) select empno,ename,deptno from emp where trunc(hiredate)=01-MAY-81; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页,共 16 页 - - - - - - - - - 把上面的语句改成下面的语句,这样就可以通过索引进行查
10、找。select empno,ename,deptno from emp where hiredate set timing on SQL create index TT_index on TT(teamid,areacode); 索引已创建。已用时间 : 00: 02: 03.93 SQL select count(areacode) from tt; COUNT(AREACODE) - 7230369 已用时间 : 00: 00: 08.31 SQL select /*+ index(tt TT_index )*/ count(areacode) from tt; COUNT(AREACO
11、DE) - 7230369 已用时间 : 00: 00: 07.37 1.15 索引的类型B-树索引位图索引 HASH 索引索引编排表反转键索引基于函数的索引分区索引本地和全局索引二索引分类Oracle 提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资源,使那些已经运行了几个小时甚至几天的进程在几分钟得以完成,这样会使您立刻成为一位英雄。下面就将简单的讨论每个索引选项。下面讨论的索引类型:B树索引 ( 默认类型 ) 位图索引HASH 索引名师资料总结 -
12、- -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页,共 16 页 - - - - - - - - - 索引组织表索引反转键 (reverse key)索引基于函数的索引分区索引 (本地和全局索引) 位图连接索引2.1 B树索引 ( 默认类型 ) B 树索引在Oracle 中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的( 简单 ) 索引,也可以是组合/ 复合 ( 多个列 ) 的索引。 B树索引最多可以包括32 列。在下图的例子中, B树索引位于雇员表的last_name
13、列上。 这个索引的二元高度为3;接下来, Oracle 会穿过两个树枝块(branch block),到达包含有ROWID 的树叶块。在每个树枝块中,树枝行包含链中下一个块的ID 号。树叶块包含了索引值、ROWID ,以及指向前一个和后一个树叶块的指针。Oracle 可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID 值。Oracle 不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引,而其中列上包含NULL值,这一行就会处于包含NULL值的索引列中,且将被处理为空 ( 视为 NULL)。技巧:索引列的值都存储在索引中。因此,可以建立一个组合(
14、 复合 ) 索引,这些索引可以直接满足查询,而不用访问表。这就不用从表中检索数据,从而减少了I/O量。B-tree 特点:适合与大量的增、删、改(OLTP )不能用包含OR操作符的查询;适合高基数的列(唯一值多)典型的树状结构;每个结点都是数据块;大多都是物理上一层、两层或三层不定,逻辑上三层;叶子块数据是排序的,从左向右递增;在分支块和根块中放的是索引的范围;名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页,共 16 页 - - - - - - - - - 2.2 位图索引位
15、图索引非常适合于决策支持系统(Decision Support System,DSS)和数据仓库,它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数( 不同值的数量 ) 的列访问非常大的表。尽管位图索引最多可达30 个列,但通常它们都只用于少量的列。例如,您的表可能包含一个称为Sex 的列,它有两个可能值:男和女。这个基数只为 2,如果用户频繁地根据Sex 列的值查询该表,这就是位图索引的基列。当一个表内包含了多个位图索引时,您可以体会到位图索引的真正威力。如果有多个可用的位图索引, Oracle 就可以合并从每个位图索引得到的结果集,快速删除不必要的数据。Bitmapt
16、特点:适合与决策支持系统;做 UPDATE 代价非常高;非常适合OR操作符的查询;基数比较少的时候才能建位图索引;技巧:对于有较低基数的列需要使用位图索引。性别列就是这样一个例子,它有两个可能值:男或女( 基数仅为2) 。位图对于低基数( 少量的不同值) 列来说非常快,这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引,所以非常小, 因此您可以经常检索表中超过半数的行,并且仍使用位图索引。当大多数条目不会向位图添加新的值时,位图索引在批处理( 单用户 ) 操作中加载表( 插入操作 ) 方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引,在大多数事务
17、处理应用程序中都会发生这种情况。示例下面来看一个示例表PARTICIPANT , 该表包含了来自个人的调查数据。列 Age_Code、Income_Level 、Education_Level和 Marital_Status都包括了各自的位图索引。下图显示了每个直方图中的数据平衡情况,以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并,可以看出性能得到了显著的提高。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页,共 16 页 - - - - -
18、 - - - - 如上图图所示,优化器依次使用4 个单独的位图索引,这些索引的列在WHERE 子句中被引用。每个位图记录指针( 例如 0 或 1) ,用于指示表中的哪些行包含位图中的已知值。有了这些信息后,Oracle 就执行 BITMAP AND操作以查找将从所有4 个位图中返回哪些行。 该值然后被转换为ROWID 值, 并且查询继续完成剩余的处理工作。注意,所有4 个列都有非常低的基数,使用索引可以非常快速地返回匹配的行。技巧:在一个查询中合并多个位图索引后,可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。下面的查
19、询可显示索引类型。SQL select index_name, index_type from user_indexes; INDEX_NAME INDEX_TYPE - - TT_INDEX NORMAL IX_CUSTADDR_TP NORMAL B树索引作为NORMAL 列出;而位图索引的类型值为BITMAP 。技巧:如果要查询位图索引列表,可以在USER _INDEXES 视图中查询index_type列。建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含 ROWID ,这样 Oracle 就可以在行级别上锁定索引。位图索引存储为压缩的索引值,其中包含
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 2022年索引详解 2022 索引 详解
![提示](https://www.taowenge.com/images/bang_tan.gif)
限制150内