高效ORACLE 之 索引(完整)

信贷及管理信息事业部高效ORACLE之索引高效ORACLE之索引关键词：ORACLE、Effective、高效、B*TreeIndex、索引倾斜、索引空间丢失、B*TreeClusterIndex、Reversekeyindex、反向键索引、DescendingIndex、降序索引、函数索引、FBI、位图索引、Bitmapindex、位图联接索引、BitmapJoinIndex、ApplicationDomainIndex、应用域索引、CBO、CaseInsensitive、选择性索引、选择惟一性、压缩键索引、CompressedKeyIndex、模式。测试环境：ORACLE：OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0操作系统：WindowsXPProfessionalSp2硬件：IBMR50笔记本（CPU1.4Ghz/Mem512M）作者信息：单位：信贷及管理信息事业部作者：李杰电话：7663参考文献：《Oracle10gDatabaseConceptsRelease2》…………………………...ORACLE《OracleDatabase10g,Release2NewFeatures》……………………...ORACLE《EffectiveOracleByDesign》…………………………………………ThomasKyte《Cost-BasedOracleFundamentals》…………………………………..JonathanLewis《2Day+PerformanceTuningGuide10gR2》………………………..ORACLE《Oracle.High.Performance.Tuning.for.9i.and.10g》……………………GavinPowell《ExpertOracleDatabaseArchitecture》……………………………….ThomasKyte《Administrator'sGuide10gR2》………………………………………..ORACLE信贷及管理信息事业部高效ORACLE之索引前言与表的数据结构相比，索引是更加复杂的数据结构，正是通过这种复杂的数据结构，关系数据库提供了各种高效的访问数据的途径。因此，表上索引太少或者缺少索引，会影响查询（包括更新和删除）的效率；但如果索引太多，DML的性能又会受到影响。对数据库模式设计人员来说，要找到一个合适的平衡点，这对于应用的性能至关重要。在实际项目开发中，我们经常犯的错误是：总是事后才想起了索引，即项目开发完成后，在测试阶段或者投产后出现了性能问题时，才想起了索引（难道不是这样吗？呵呵！）。这说明索引还没有引起设计和开发人员的足够重视。磨刀不误砍柴工，如果刚开始的时候多花些时间好好的考虑如何索引我们的数据，这肯定能在以后的‘调优’中节省更多的时间。设计和开发人员要清楚的认识到：不能总把索引摆到‘救火’的位置上，它更是一种防范手段，要充分利用好这种防范手段。当然这要求我们的数据库模式设计人员，从一开始就要清楚的了解我们的数据的结构以及这些数据将怎样被使用。索引是应用设计和开发的一个重要方面。正确的理解和使用索引将会使应用开发变得经济且高效（较低的成本取得较好的效率）。本文的主旨是对ORCLE的各种索引进行介绍，讨论在什么时候、在什么地点需要使用索引以及应该使用什么样的索引等内容。本文将主要从应用的角度来考虑索引，也就是从索引的实际使用来介绍索引，而不会从DBA的角度去介绍索引增长、索引存储空间的使用、监控等内容。索引类型简介按模式分类B*树索引（B*TreeIndex）B*树索引是我们常说‘传统’的索引，目前几乎所有关系型数据库都支持这种索引技术。B*树的构造类似于二叉树，但是这里的‘B’并不代表二叉（Binary）之意，而是代表平衡（Balanced）之意（文中会有详细解释）。B*树索引有如下这些子类型：„索引组织表（Index-OrganizedTable）：索引组织表是存贮在B*树索引结构中的表，即行数据和B*树索引是物理存贮在一起的。这样能够有效提高数据访问的效率。索引组织表的详细信息我已经在《高效ORACLE之模式》中阐述过，这里不再进行详细介绍。但是这里要再次重点强调一下索引组织表的应用场合：DataWarehouse/MIS/BI/OLAP应用、空间（Spatial）应用等。„B*树群集索引（B*TreeClusterIndex）：B*树群集索引就是为群集建立的B*树索引，这是传统B*树索引的一个变体。在传统B*树索引中，键都是指向一行，而B*树群集索引中索引指向一个包含群集键值的数据块（该块包含群集键值相等的多行数据）。关于B*树群集索引的详细信息，我也在《高效ORACLE之模式》中详细阐述过，这里也不再进行讲解。同样需要强调一下B*树群集索引的应用场合：DataWarehouse/MIS/BI/OLAP应用。因为这些应用场合你能够控制数据的加载方式，只有在这种情况下你才能把数据按照‘群集’的方式加载进群集中。而OLTP系统中，数据是随机进入数据库的，你无法控制数据到来的时间，所以无法使用群。信贷及管理信息事业部高效ORACLE之索引„降序索引（DescendingIndex）：降序索引是B*树索引。它允许数据在索引结构中按“从大到小”的顺序排序。本文下面的内容会说明为什么降序索引比较重要，并说明降序索引是如何工作的。„反向键索引（ReverseKeyIndex）：这也是一种B*树索引，只不过键中的字节是“反转”的。这对键中存在顺序的连续值（比如序列生成的键值）的索引很有用途。通过“反转”，本来连续的键值，比如987500、987501、987502，ORACLE会逻辑的对205789、105789、005789等建立索引。这样本来连续的数据就变得相距甚远，对这些索引键值的插入就会分散到多个块（Block）上，从而有效降低大批量数据插入时对同一数据块的‘争用’，避免‘热块’的产生，从而提高效率。位图索引（BitmapIndex）：索引的目的是提供一个指向所给键值对应数据行的指针（RowID）。在普通的索引中，这是通过存储每一个键值对应数据行的ROWID来实现的。通常索引条目和数据行之间存在着一种一对一的关系：一个索引条目就指向一行。而对于位图索引，它使用键值的位图来取代普通索引的ROWID列表，一个位图索引条目可能会指向多行数据。位图索引适用于高度重复（低基数）且通常只读的数据表。在OLTP系统中，由于存在着并发性相关的性能问题，所以不能考虑使用位图索引。而数据以‘只读’为主要特征的DataWarehouse/MIS/BI/OLAP应用是位图索引的首选。位图联接索引（BitmapJoinIndex）：位图联接索引提供了在表A上创建包含表B中字段的索引的能力。比如：CREATEBITMAPINDEXA_BM_IDXONA(B.FILED1_NAME)FROMA,BWHEREA.FILED1_CODE=B.FILED1_CODE;这个索引是指向表A的，而并不是指向表B。这是一个全新的概念：能从其它表对某个表属性建立索引。这中索引会改变我们在DataWarehouse/MIS/BI/OLAP应用中数据模型设计的方式：一方面保持规范化的数据结构不变，一方面还能得到逆规范化的诸多好处。基于函数的索引（Function-BasedIndex）：FBI是B*树索引或者位图索引，所不同的是，它将一个函数计算得到的结果存贮在索引中，而不是直接存储列数据本身。FBI可以加快形如SELECT*FROMAWHEREFUNCTION(T.COL1)＝SOME_VALUE的查询，因为值FUNCTION(T.COL1)已经提前计算并存储在索引中。应用域索引（ApplicationDomainIndex）：ADI是你自己构建和存储的索引。ORACLE通过ADI给应用设计人员提供了‘DIY’索引的能力。该索引可以存在ORACLE中，也可以存储在ORACLE外，你要告诉ORACLE索引的选择率如何以及执行的开销有多大。优化器（OPTIMEZER）会根据你提供的信息决定是否使用该索引。按应用类型分按照索引的应用类型划分，我们还有另外一种索引分类方式：高效读写索引（EfficientasRead-WriteIndexes）„B*树索引（B*TreeIndex）„基于函数的索引（Function-BasedIndexex）„反向键索引（ReverseKeyIndex）高效只读索引（EfficientasRead-OnlyIndexes）„位图索引（BitmapIndexes）信贷及管理信息事业部高效ORACLE之索引„位图联接索引（BitmapJoinIndexes）„索引组织表（Index-OrganizedTable）„B*树索引群集和哈希群集（B*TreeIndexCluster&HashCluster）应用域索引（ApplicationDomainIndex）：非常特别的一种索引，关于它的阐述超出了本文的范畴，在这里不做详细介绍，有兴趣的话可以参看ORACLE相关技术文档去了解和学习。性能调整有关的索引类型（Indexrelevanttotuning）：„升序或降序索引（AscendingordescendingIndexes）„唯一索引（UniqueIndexes）„复合索引（CompositeIndexes）„压缩索引（CompressionIndexes）„反向键索引（ReverseKeyIndexes）„其它（诸如NOSORT或者NULL空值等）与性能调整有关的这些索引的使用都具有极强的技巧性，一般来说，这些通常也是我们调优时经常使用的技术。由上面的描述可以看到，在ORACLE中可供选择的索引类型非常丰富，本文的余下部分将通过提供一些技术细节来分别对每种索引深入进行阐述，这包括：如何（How）、何时（When）、何地（Where）使用这些索引。B*Tree索引B*Tree索引概述B*Tree索引是昀‘传统’的索引，也是使用昀多的一类索引结构。它的目标是尽可能减少ORACLE查找数据所花费的时间。它的实现与二叉查找树很类似。不严格的说，如果在一个字符串列上创建一个B*Tree索引，那么从概念上讲这个索引的结构可能如下图1所示：信贷及管理信息事业部高效ORACLE之索引图中，上面虚线筐内的数据块叫BranchBlock（分支块），它提供到具体的LeafBlock（页块或者叶子节点，即下面虚线筐内的数据块）数据块的‘导航’，真正的索引数据是存储在LeafBlock上的。以查找名为Karthy数据行为例：从根结点开始（第一个I/O）找到第二层中从左侧第二个分支（指针），从该分支开始（第二个I/O）找到第三层页块中的昀右侧分支（指针），然后再进行第三个I/O找到该页块，从中找到名为Karthy的索引条目，根据里面存储的ROWID即可找到名为Karthy表记录（这需要另外一个I/O）。需要特别指出的是，索引的叶子节点实际上是构成一个双向链表（如上图所示），所以一旦导航到叶子节点之后（即发现第一个索引键值），执行索引键值的有序扫描就变得很容易，‘索引键值的有序扫描’就是我们经常在执行计划中看到IndexRangeScan（索引范围扫描），执行IndexRangeScan时，ORACLE不用反复在索引结构中做从根节点到叶子节点的导航，而只是在第一次到达页块后，根据需要在叶子节点中沿双向链表向前或者向后扫描就可以了。所以，如果使用了B*树索引，ORACLE要满足诸如以下的谓词条件就相当简单：WHEREXXXBETWEEN1000AND2000ORACLE发现第一个昀小键值大于或等于1000的页子节点后，就会水平的通过双向链表遍历其它页子节点，直到昀后发现一个大于2000的值为止。整个过程只需从根节点到叶子节点‘导航’一次。B*树索引结构中，所有的叶子都在同一层上，整个索引结构数据块的层数称之为索引的高度