管理索引表：深入研究B树索引--DML对B树索引的影响(理论篇2)

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则可能会导致完全索引扫描。