管理索引表：深入研究B树索引--B树索引的相关概念

索引概述
　　索引与表一样，也属于段（segment）的一种。里面存放了用户的数据，跟表一样需要占用磁盘空间。只不过，在索引里的数据存放形式与表里的数据存放形式非常的不一样。在理解索引时，可以想象一本书，其中书的内容就相当于表里的数据，而书前面的目录就相当于该表的索引。同时，通常情况下，索引所占用的磁盘空间要比表要小的多，其主要作用是为了加快对数据的搜索速度，也可以用来保证数据的唯一性。但是，索引作为一种可选的数据结构，你可以选择为某个表里的创建索引，也可以不创建。这是因为一旦创建了索引，就意味着oracle对表进行DML（包括INSERT、UPDATE、DELETE）时，必须处理额外的工作量（也就是对索引结构的维护）以及存储方面的开销。所以创建索引时，需要考虑创建索引所带来的查询性能方面的提高，与引起的额外的开销相比，是否值得。

　　从物理上说，索引通常可以分为：分区和非分区索引、常规B树索引、位图（bitmap）索引、翻转（reverse）索引等。其中，B树索引属于最常见的索引，由于我们的这篇文章主要就是对B树索引所做的探讨，因此下面只要说到索引，都是指B树索引。

B树索引内部结构:
1) 叶子节点（Leaf node）：数据行的键值（key value）、键值对应数据行的 ROWID。
2) 分支节点（Branch node）：最小的键值前缀（minimum key prefix），用于在（本块的）两个键值之间做出分支选择，指向包含所查找键值的子块（child block）的指针（）所有的 键值-ROWID 对（key and ROWID pair）都与其左右的兄弟节点（sibling）向链接（link），并按照（key，ROWID）的顺序排序
3) 根节点（Root node）：一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。 可以用下图一来描述B树索引的结构。其中，B表示分支节点，而L表示叶子节点。

　　分支节点块（包括根节点块）:其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。索引条目（也可以叫做每条记录,例如分支节点块中的一个条目（0 L1））包含有两个字段：第一个字段 表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0 B1）、（500 B2）、（1000 B3），它们指向三个分支节点块。其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。而B1、B2和B3则表示所指向的三个分支节点块的地址。

　　叶子节点块：其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）索引条目（也可以叫做每条记录）包含有两个字段：第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。

　　知道这些信息以后，我们可以举个例子来说明如何估算每个索引能够包含多少条目，以及对于表来说，所产生的索引大约多大。对于每个索引块来说，缺省的PCTFREE为10％，也就是说最多只能使用其中的90％。同时9i以后，这90％中也不可能用尽，只能使用其中的87％左右。也就是说，8KB的数据块中能够实际用来存放索引数据的空间大约为6488（8192×90％×88％）个字节。

　　假设我们有一个非分区表，表名为warecountd，其数据行数为130万行。该表中有一个列，列名为goodid，其类型为char（8），那么也就是说该goodid的长度为固定值：8。同时在该列上创建了一个B树索引。

　　在叶子节点中，每个索引条目都会在数据块中占一行空间。每一行用2到3个字节作为行头，行头用来存放标记以及锁定类型等信息。同时，在第一个表示索引的键值的字段中，每一个索引列都有1个字节表示数据长度，后面则是该列具体的值。那么对于本例来说，在叶子节点中的一行所包含的数据大致如下图二所示：

　　从上图可以看到，在本例的叶子节点中，一个索引条目占18个字节。同时我们知道8KB的数据块中真正可以用来存放索引条目的空间为6488字节，那么在本例中，一个数据块中大约可以放360（6488/18）个索引条目。而对于我们表中的130万条记录来说，则需要大约3611（1300000/360）个叶子节点块。

　　对于分支节点里的一个条目（一行）来说，由于它只需保存所链接的其他索引块的地址即可，而不需要保存具体的数据行在哪里，因此它所占用的空间要比叶子节点要少。分支节点的一行中所存放的所链接的最小键值所需空间与上面所描述的叶子节点相同；而存放的索引块的地址只需要4个字节，比叶子节点中所存放的ROWID少了2个字节，少的这2个字节也就是ROWID中用来描述在数据块中的行号所需的空间。因此，本例中在分支节点中的一行所包含的数据大致如下图三所示：

　　从上图可以看到，在本例的分支节点中，一个索引条目占16个字节。根据上面叶子节点相同的方式，我们可以知道一个分支索引块可以存放大约405（6488/16）个索引条目。而对于我们所需要的3611个叶子节点来说，则总共需要大约9个分支索引块。

　　这样，我们就知道了我们的这个索引有2层，第一层为1个根节点，第二层为9个分支节点，而叶子节点数为3611个，所指向的表的行数为1300000行。但是要注意，在oracle的索引中，层级号是倒过来的，也就是说假设某个索引有N层，则根节点的层级号为N，而根节点下一层的分支节点的层级号为N-1，依此类推。对本例来说，9个分支节点所在的层级号为1，而根节点所在的层级号为2。

　　注意：在Oracle中null被定义为无限大，且null不等于null，故在索引不会存有与null值对应的条目。如果不加其他限制条件的对表进行is null扫描，将会是全表扫描，如果是is not null扫描将会是全索引扫描

B树索引的访问

当oracle进程需要访问数据文件里的数据块时，oracle会有两种类型的I/O操作方式：

1） 随机访问，每次读取一个数据块（通过等待事件“db file sequential read”体现出来）。

2） 顺序访问，每次读取多个数据块（通过等待事件“db file scattered read”体现出来）。

　　第一种方式则是访问索引里的数据块，而第二种方式的I/O操作属于全表扫描。这里顺带有一个问题，为何随机访问会对应到db file sequential read等待事件，而顺序访问则会对应到db file scattered read等待事件呢？这似乎反过来了，随机访问才应该是分散（scattered）的，而顺序访问才应该是顺序（sequential）的。其实，等待事件主要根据实际获取物理I/O块的方式来命名的，而不是根据其在I/O子系统的逻辑方式来命名的。下面对于如何获取索引数据块的方式中会对此进行说明。事实上在B树索引虽然为一个树状的立体结构，但其对应到数据文件里的排列当然还是一个平面的形式，也就是像下面这样。

/根/分支/分支/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/.....

　　因此，当oracle需要访问某个索引块的时候，势必会在这个结构上跳跃的移动。

　　当oracle需要获得一个索引块时，首先从根节点开始，根据所要查找的键值，从而知道其所在的下一层的分支节点，然后访问下一层的分支节点，再次同样根据键值访问再下一层的分支节点，如此这般，最终访问到最底层的叶子节点。可以看出，其获得物理I/O块时，是一个接着一个，按照顺序，串行进行的。在获得最终物理块的过程中，我们不能同时读取多个块，因为我们在没有获得当前块的时候是不知道接下来应该访问哪个块的。因此，在索引上访问数据块时，会对应到db file sequential read等待事件，其根源在于我们是按照顺序从一个索引块跳到另一个索引块，从而找到最终的索引块的。
　　对于全表扫描来说，则不存在访问下一个块之前需要先访问上一个块的情况。全表扫描时，oracle知道要访问所有的数据块，因此唯一的问题就是尽可能高效的访问这些数据块。因此，这时oracle可以采用同步的方式，分几批，同时获取多个数据块。这几批的数据块在物理上可能是分散在表里的，因此其对应到db file scattered read等待事件

插入（INSERT）对的B树索引影响
　　对于B树索引的插入情况的描述，可以分为两种情况：一种是在一个已经充满了数据的表上创建索引时，索引是怎么管理的；另一种则是当一行接着一行向表里插入或更新或删除数据时，索引是怎么管理的。

　　第一种情况比较简单，当在一个充满了数据的表上创建索引（create index命令）时，oracle会先扫描表里的数据并对其进行排序，然后生成叶子节点。生成所有的叶子节点以后，根据叶子节点的数量生成若干层级的分支节点，最后生成根节点。这个过程是很清晰的。

　　第二种情况会复杂很多，叶子节点将被拆分。这个过程需要分成两种，一种是插入的键值不是最大值；另一种是插入的键值是最大值。当一个非最大键值(第一种状况)要进入索引，但是发现所应进入的索引块不足以容纳当前键值时：

　　1.从索引可用列表上获得一个新的索引数据块

　　2.将当前充满了的索引中的索引条目分成两部分，一部分是具有较小键值的，另一部分是具有较大键值的。Oracle会将具有较大键值的部分移入新的索引数据块，而较小键值的部分保持不动。

　　3.将当前键值插入合适的索引块中，可能是原来空间不足的索引块，也可能是新的索引块。

　　4.更新原来空间不足的索引块的kdxlenxt信息，使其指向新的索引块。

　　5.更新位于原来空间不足的索引块右边的索引块里的kdxleprv，使其指向新的索引块。

　　6.向原来空间不足的索引块的上一级的分支索引块中添加一个索引条目，该索引条目中保存新的索引块里的最小键值，以及新的索引块的地址。

　　从上面有关叶子节点分裂的过程可以看出，其过程是非常复杂的。因此如果发生的是插入的键值是最大值时(第二种状况)，则为了简化该分裂过程，oracle省略了上面的第2步，而是直接进入第3步，将新的键值插入新的索引块中。

　　当叶子节点越来越多，导致原来的根节点不足以存放新的索引条目（这些索引条目指向叶子节点）时，则该根节点必须进行分裂。当根节点进行分裂时：

　　1.从索引可用列表上获得两个新的索引数据块。

　　2.将根节点中的索引条目分成两部分，这两部分分别放入两个新的索引块，从而形成两个新的分支节点。

　　3.更新原来的根节点的索引条目，使其分别指向这两个新的索引块。

　　因此，这时的索引层次就变成了2层。同时可以看出，根节点索引块在物理上始终都是同一个索引块。而随着数据量的不断增加，导致分支节点又要进行分裂。分支节点的分裂过程与根节点类似（实际上根节点分裂其实是分支节点分裂的一个特例而已）：

　　1.从索引可用列表上获得一个新的索引数据块。

　　2.将当前满了的分支节点里的索引条目分成两部分，较小键值的部分不动，而较大键值的部分移入新的索引块。

　　3.将新的索引条目插入合适的分支索引块。

　　4.在上层分支索引块中添加一个新的索引条目，使其指向新加的分支索引块。

　　当数据量再次不断增加，导致原来的根节点不足以存放新的索引条目（这些索引条目指向分支节点）时，再次引起根节点的分裂，其分裂过程与前面所说的由于叶子节点的增加而导致的根节点分裂的过程是一样的。每个拆分过程都是要花费一定的开销的，特别是要进行物理硬盘I/O动作。

删除（DELETE）对B树索引的影响

　　在介绍删除索引键值的机制之前，先介绍与索引相关的一个比较重要的视图：index_stats。该视图显示了大量索引内部的信息，该视图正常情况下没有数据，只有在运行了下面的命令以后才会被填充数据，而且该视图中只能存放一条与分析过的索引相关的记录，不会有第二条记录。同时，也只有运行了该命令的session才能够看到该视图里的数据，其他session不能看到其中的数据。

　　analyze index INDEX_NAME validate structure;

　　要注意一点，就是该命令有一个坏处，就是在运行过程中，会锁定整个表，从而阻塞其他session对表进行插入、更新和删除等操作。这是因为该命令的主要目的并不是用来填充index_stats视图的，其主要作用在于校验索引中的每个有效的索引条目都对应到表里的一行，同时表里的每一行数据在索引中都存在一个对应的索引条目。为了完成该目的，所以在运行过程中要锁定整个表，同时对于很大的表来说，运行该命令需要耗费非常多的时间。

　　在视图index_stats中，height表示B树索引的高度；blocks表示分配了的索引块数，包括还没有被使用的；pct_used表示当前索引中被使用了的空间的百分比。其值是通过该视图中的(used_space/btree_space)*100计算而来。used_space表示已经使用的空间，而btree_space表示索引所占的总空间；del_lf_rows表示被删除的记录行数（表里的数据被删除并不会立即将其对应于索引里的索引条目清除出索引块，我们后面会说到）；del_lf_rows_len表示被删除的记录所占的总空间；lf_rows表示索引中包含的总记录行数，包括已经被删除的记录行数。这样的话，索引中未被删除的记录行数就是lf_rows-del_lf_rows。同时我们可以计算未被删除的记录所对应的索引条目（也就是有效索引条目）所占用的空间为((used_space – del_lf_rows_len) / btree_space) * 100。

对索引的删除情况总结如下：

　　1.删除表里的一条记录时，其对应于索引里的索引条目并不会被物理的删除，只是做了一个删除标记。

　　2.当一个新的索引条目进入一个索引叶子节点的时候，oracle会检查该叶子节点里是否存在被标记为删除的索引条目，如果存在，则会将所有具有删除标记的索引条目从该叶子节点里物理的删除。

　　3.当一个新的索引条目进入索引时，oracle会将当前所有被清空的叶子节点（该叶子节点中所有的索引条目都被设置为删除标记）收回，从而再次成为可用索引块。

　　尽管被删除的索引条目所占用的空间大部分情况下都能够被重用，但仍然存在一些情况可能导致索引空间被浪费，并造成索引数据块很多但是索引条目很少的后果，这时该索引可以认为出现碎片。而导致索引出现碎片的情况主要包括：

　　1.不合理的、较高的PCTFREE。很明显，这将导致索引块的可用空间减少。

　　2.索引键值持续增加（比如采用sequence生成序列号的键值），同时对索引键值按照顺序连续删除，这时可能导致索引碎片的发生。因为前面我们知道，某个索引块中删除了部分的索引条目，只有当有键值进入该索引块时才能将空间收回。而持续增加的索引键值永远只会向插入排在前面的索引块中，因此这种索引里的空间几乎不能收回，而只有其所含的索引条目全部删除时，该索引块才能被重新利用。
　　3.经常被删除或更新的键值，以后几乎不再会被插入时，这种情况与上面的情况类似。

　　对于如何判断索引是否出现碎片，方法非常简单：直接运行ANALYZE INDEX … VALIDATE STRUCTURE命令，然后检查index_stats视图的pct_used字段，如果该字段过低（低于50％），则说明存在碎片

更新（UPDATE）对B树索引的影响
　　而对于值被更新对于索引条目的影响，则可以认为是删除和插入的组合。也就是将被更新的旧值对应的索引条目设置为D（删除）标记，同时将更新后的值按照顺序插入合适的索引块中。这里就不重复讨论了

 

总结 ：

　　1、避免对那些可能会产生很高的更新动作的列进行索引。

　　2、避免对那些经常会被删除的表中的多个列进行索引。若有可能，只对那些在这样的表上会进行删除的主关键字与/或列进行索引。如果对多个列进行索引是不可避免的，那么就应该考虑根据这些列对表进行划分，然后在每个这样的划分上执行TRUNCATE动作（而不是DELETE动作）。TRUNCATE在与DROP STORAGE短语一同使用时，通过重新设置高水位标来模拟删除表与索引以及重新创建表与索引的过程。

　　3、避免为那些唯一度不高的列创建B*树索引。这样的低选择性将会导致树节点块的稠密性，从而导致由于索引“平铺( flat)”而出现的大规模索引扫描。唯一性的程度越高，性能就越好，因为这样能够减少范围扫描，甚至可能用唯一扫描来取代范围扫描。

　　4.空值不存储在单列索引中。对于复合索引的方式，只有当某个列不空时，才需要进行值的存储。在为DML语句创建IS NULL或IS NOT NULL短语时，应该切记这个问题。

　　5.IS NULL不会导致索引扫描，而一个没有带任何限制的IS NOT NULL则可能会导致完全索引扫描。

参考至：http://btxigua.itpub.net/post/34419/406433

             http://space.itpub.net/?uid-9842-action-viewspace-itemid-324139

             http://space.itpub.net/?uid-9842-action-viewspace-itemid-312607
             http://space.itpub.net/?uid-9842-action-viewspace-itemid-324586