Ora Ora Oracle电子杂志：关于索引的检验 之 1－4

“有了索引，检索就会变快了。”这种想法当然不算错，因为索引本来就是为了让数据查询加快的最常用的方法。可是，各位读者可曾思考过：索引在任何情况下都起作用吗？[@more@]

“有了索引，检索就会变快了。”这种想法当然不算错，因为索引本来就是为了让数据查询加快的最常用的方法。可是，各位读者可曾思考过：索引在任何情况下都起作用吗？这次我们要让索引构造恶化，从各种角度分析这个问题。 【实验的目的与环境】 目的：确认执行什么样的处理会导致索引变慢，并以实验检验变慢的原因。 假设：业务操作以Insert为主，而且业务中会对大量旧数据执行删除操作 实验的前提： ◎在采样各个测试结果之前，重新启动Oracle，清空数据库Cache ◎包含索引在内的所有数据都放在一个extent(initial extent)，放在同一个scheme下 ◎表数据和索引分别放在各自的表空间 ◎以独特方法让测量时间只包含物理读取时间，而不包含将结果输出的时间（比如输出到控制台的I/O时间） ◎初始设定参数db_file_multiblock_read_count设为预设值8 ◎实验结果中的 PHYSICAL READ（DATA）表示每次访问db_file_multiblock_read_count（该值当前被设定为8）个block时的I/O次数，把这个I/O次数乘以8就是实际上访问的数据块的数目(但是索引检索的时候是以1block为单位，这时候的PH YSICAL READ（DATA）值和实际上访问的Block数目是相同的) 【数据的数量】 1. 1万条记录 2. 100万条记录（从第1万条记录开始反覆执行插入操作，直到达到100万条记录） 3. 1万条（从100万条记录中删掉99万条记录，剩余1万条记录） 在以上3种情形下，我们进行各种检验。实验目的不是为了比较各种性能，而是为了找出索引构造恶化的原因。为了尽量让大家能亲自体验“索引检索突然变慢了”的奇怪现象，我们选择使用NT环境。 【索引不会理会已被删除的数据】 进行这个实验的原因是因为一次TREEDUMP的经历（以后会详细说明）。明明已经删除表中的数据了，但索引却一直维持0。“这个0是不是代表存在完全没有ROWID（后面将解释）的数据块（空数据块）呢？”为了解答这个问题，我们建立了一个有100万笔数据的表，接着将其中99万笔删除，然后用条件表达式执行使用了索引的SELECT语句。 【刚开始的预想是正确的】 和我们实验之前想的结果一样，即使删除了的表中的数据，数据块已经是空的了，Oracle仍然无法通过索引判断那个数据块是不是空的，导致连没有意义的数据块都被访问到了。以具体的数值表示就是，没有索引的检索比有索引的检索慢了3倍以上。对于都使用索引的检索，相较于没有执行删除，且原本就是1万笔数据的表（情形1），删除后才变成1万笔数据的表（情形2）的I/O次数多了300倍。下面就是检验结论： 1.删除后已经空掉的数据块，Oracle还是会继续保存。 2.因为索引缺乏判断数据块里有没有数据的要素，所以进行如下（＜＝）的范围检索时，Oracle还是不知道那些是空的数据块，而是持续往下读取，结果如下 这是索引构造的问题：即使表中数据已经被刪除，也不代表索引中对应的数据块也被刪掉，Oracle会将已经为空的数据块预备给将来数据的插入，即作为Place Holder。实验结果证明，把删掉的数值重新输入一次，只要索引构造没有太大的改变，各条记录就一定会对号入座。 由此可知，索引对单一检索有用，但是对范围检索未必有用，至少对于经常进行大量删除的表进行大范围检索的时候，索引几乎完全无用。不过如果定期进行数据库维护（执行REBUILD等等），情况就未必如此。只是在什么时候、哪个数据块的数据密度变低、从哪里到哪里变成空的，这些几乎不可能预测。由于这个原因频繁执行维护是没有太大实际意义的。所以DBA除了掌握各个表的特性（比如某个表经常有大量删除，而不是只有由小到大的插入）之外，还得从数据库维护和SQL结构（例如根据情况使用索引）两方面思考如何处理问题。 有了这些基础知识，下次我们开始介绍实际的检验结果，希望能帮助大家判断何时进行维护。

　

【索引的构造】 解释实验结果之前，先简单说明B-Tree的构造。（即使很多读者已经都知道了） 索引就像 所示的结构一样，这种构造称为平衡树(Balance Tree)。根节点(Root node)和枝节点(Branch node)里有包含叶子区索引键集合中最前面的那个索引键和叶子区的数据块地址（Data Block Address，DBA）,而叶子区包含了实际的索引键。换句话说，叶子区(leaf block)包含索引键和真实数据的ROWID，ROWID代表数据在数据库中偏移。 在本例的图示中，枝的首部没有键，只记录数据块地址。Oracle不保留枝下面的最前面的（即最靠左的）叶子节点最前面的叶键（leaf block key）。这意味着，比枝节点的第2个DBA的键更小的值，会放在最前(左)面的DBA里面。图示中可以看到，最左边的DBA中，所记录的叶键（10～50）都是小于其所在枝节点记录的最小的键值（60）。 【附注】ROWID = 真实数据存放的物理位置 【发生叶分割的时间点】 看 可以了解到，叶子块（Leaf block）中的键(Leaf key)会从小到大排列。现在假想每个叶子都已经满了，“新增插入记录35会放到哪里？” 想把新记录35插入第1个叶数据块的30下面，第一个数据块里没有空隙，这时候就会发生叶分割。如果不考虑性能，简单的把从第一个叶数据(leaf data)开始到最后一个叶数据往后挪动，把第一个叶数据的空间空出来，然后把数据35插入第一个叶数据块。这样做的操作代价非常大，Oracle当然不会这么做。图示说明了需要进行叶分割，记录35会与紧接其后的40等若干叶数据结合为一个新的叶数据块。这样一来，新的叶和被分割的叶可能会出现许多空闲空间。至于空出来多少，受插入数据的顺序影响。 如果数据是由小到大顺序插入，那么就不会出现空闲空间，但是如果插入的时候由大到小或者毫无次序的插入，叶分割就会频繁发生，并产生大量空闲区。实际的系统并不会考虑插入数据的顺序，因此无法预测叶分割的情况。 【索引不停止？】 索引会经过以上所说的三个阶段（根、枝、叶），然后抵达目标记录，但只有在执行唯一检索(unique search)的时候才会如此。如果是范围检索，抵达记录的过程是相同的，但是，如果检索中利用了索引由小到大存放的特性寻址符合检索条件的目标记录，那就会在读取到第一个符合条件判断的那个数据块后，持续向前（或者向后）读取，直到检索出所有目标数据块。 这次的实验重点在于了解索引构造的特性，以范围检索为主要示例，实际就是让叶数据块产生偏差(Skew)，以验证数据库会“数据已被删除，但其索引键仍然被读取”的奇妙现象。

　

【索引的构造】 表TEST01里有EMPNO01和EMPNO02字段，包含相同的数据值。在EMPNO01字段上创建索引。让存放在EMPNO01和EMPNO02字段中的数据从10000010开始以10为单位增加，直到10100000为止，共计10000条记录。 表TEST01如下面链接所示： 分别在EMPNO01字段和EMPNO02字段附加查询条件对该表进行如下查询： 1. 查询第5000条数据 2. 查询前5000条数据 EMPNO01字段（有索引）和EMPNO02字段（没有索引），执行后的结果下面网页所示： 上图的(※2)、(※3) 、(※4)是利用统计I/O次数的SQL语句，求出执行以上查询前后的I/O统计结果。 【统计I/O次数的SQL语句】 ************************************************************* SELECT SUBSTR(A.NAME, INSTRB(A.NAME, '../', -1) + 1, 20) FILE_NAME ,A.STATUS ,TO_CHAR(B.PHYRDS, '99999999999990') PHYRDS FROM V$DATAFILE A, V$FILESTAT B WHERE A.FILE# = B.FILE# ; ************************************************************* 请大家注意检索结果中【检索1】的查询条件是以索引（EMPNO01）进行唯一查询。前面已经介绍过“索引的构造”知识，索引是经过根、枝、叶三阶段抵达目的记录，这种查询对于索引的数据块只会发生2次I/O，表TEST01数据只有1万条记录，所以只用到第二次I/O。我们可以用下面的ANALYZE命令确认这件事情。 ************************************************************* SQL> ANALYZE INDEX TEST01 VALIDATE STRUCTURE ; └→ 不是表名称而是索引名称 ************************************************************* 分析结果如下： ************************************************************* SQL> SELECT HEIGHT, -- 枝节点的阶层有多高 2 BLOCKS, -- 全数据块数 3 LF_ROWS, -- 叶行数 4 LF_BLKS, -- 叶数据块数 5 BR_ROWS, -- 枝行数 6 BR_BLKS -- 枝数据块数 7 FROM INDEX_STATS ; ************************************************************* ************************************************************* HEIGHT BLOCKS LF_ROWS LF_BLKS BR_ROWS BR_BLKS --------- --------- --------- --------- --------- --------- 2 51200 10000 87 86 1 ************************************************************* 请大家注意上面的数据块数总数（BLOCKS）”结果是51200，叶数据块与枝数据块总计是88个。这是因为以CREATE INDEX创建索引的时候，为了不让extent存在未使用数据块以及因为索引的“空间管理机制”、“性能优先”所衍生的未使用数据块，而让索引的初始化空间为100 M所造成的。 接下来请大家注意【检索3】的查询条件使用索引（EMPNO01）进行的范围检索。这里对于索引进行了45次I/O，从第1条的值10000010到第5000条的值10050000存放在总共44个叶数据块，加上1个枝数据块的值。下次介绍的TREEDUMP功能就可以确认这个部分。

　

上次对1万笔数据的表进行了1～5000笔（一半）的范围检索，结果对索引发生了45次I/O。这次我们要以TREEDUMP功能确认结果。 要取得TREEDUMP，首先必须知道索引的OBJECT_ID才行。要知道OBJECT_ID，请用下面的SELECT语句。 ************************************************************* SELECT OBJECT_NAME, OBJECT_ID >FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX' ; OBJECT_NAME OBJECT_ID ----------- --------- ID_EMP 3461 PK_DEPT 1924 PK_EMP 1926 TEST01 3539 ************************************************************* 接下来，指定OBJECT_ID取得TREEDUMP。 ************************************************************* ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 3539' ; └→分配给TEST01的OBJECT_ID ************************************************************* 此外，TREEDUMP的输出目的地是数据库初始化参数user_dump_dest指定的目录位置，文件名为ora_xxxx.trc，其中的最新版本即是当前运行命令后生成的。 ************************************************************* 【TREEDUMP】 ----- begin tree dump branch: 0x5800003 92274691 (0: nrow: 87, level: 1) (1) leaf: 0x2000004 33554436 (-1: nrow: 116) ↑ leaf: 0x2000005 33554437 (0: nrow: 116) ｜ leaf: 0x2000006 33554438 (1: nrow: 116) ｜ leaf: 0x2000007 33554439 (2: nrow: 116) ｜ leaf: 0x2000008 33554440 (3: nrow: 116) ｜ : ｜ : ｜ leaf: 0x2000018 33554456 (19: nrow: 116) ｜ leaf: 0x2000019 33554457 (20: nrow: 116) ｜ leaf: 0x200001a 33554458 (21: nrow: 116) ｜ leaf: 0x200001b 33554459 (22: nrow: 116) ｜ leaf: 0x200001c 33554460 (23: nrow: 116) ｜ leaf: 0x200001d 33554461 (24: nrow: 116) ｜(3) leaf: 0x200001e 33554462 (25: nrow: 116) ｜ leaf: 0x200001f 33554463 (26: nrow: 116) ｜ leaf: 0x2000020 33554464 (27: nrow: 116) ｜ leaf: 0x2000021 33554465 (28: nrow: 116) ｜ leaf: 0x2000022 33554466 (29: nrow: 116) ｜ leaf: 0x2000023 33554467 (30: nrow: 116) ｜ leaf: 0x2000024 33554468 (31: nrow: 116) ｜ leaf: 0x2000025 33554469 (32: nrow: 116) ｜ leaf: 0x2000026 33554470 (33: nrow: 116) ｜ : ｜ : ｜ leaf: 0x200002d 33554477 (40: nrow: 116) ｜ leaf: 0x200002e 33554478 (41: nrow: 116) ｜ leaf: 0x200002f 33554479 (42: nrow: 116) (2) leaf: 0x2000030 33554480 (43: nrow: 116) leaf: 0x2000031 33554481 (44: nrow: 116) leaf: 0x2000032 33554482 (45: nrow: 116) leaf: 0x2000033 33554483 (46: nrow: 116) leaf: 0x2000034 33554484 (47: nrow: 116) : : leaf: 0x2000058 33554520 (83: nrow: 116) leaf: 0x2000059 33554521 (84: nrow: 116) leaf: 0x200005a 33554522 (85: nrow: 24) ↑ ↑ Leaf No. 键的数量 ----- end tree dump ************************************************************* (1)读取枝数据块，求出存放了第5000笔的值10050000的DBA。 (2)读取存放了第5000笔的值10050000的叶数据块的号码42。 (3)其后的数据块都符合条件式（EMPNO01 <= 10050000），所以会读取叶资料块号码42 ～ －1的数据块。 ◎检索的过程如图 下次我们会让表的数据增加到100万件，看看出现多次叶分割的情况。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3898/viewspace-803891/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3898/viewspace-803891/