数据库性能管理：B*树索引优化

2023-11-01 理论教育版权反馈

【摘要】：B*树状索引具有树形结构。图3-1B*树原理下面介绍如何使用B*树索引搜索数据。B*树的特征如下：最常用作 Oracle的默认索引。为更好地调优，应充分了解B*树索引的缺点。以下为B*树索引的缺点：在B*树索引中，实际列值也应保存在索引中，因此管理大容量数据时可能会成为负担。 B*树索引的列值选择度良好才能保证性能良好。在B*树Index中，访问相同的表时将并行使用两个以上的索引，因此有许多限制。

在关系型数据库中最常使用的索引就是B*树状索引（balance*tree index）。 B*树状索引具有树形结构。该结构先将指定值与列表中间点的值进行比较，如果该值更大将舍弃列表值小的一半；如果该值更小则舍弃列表值大的一半，直到发现一个值或列表结束前将重复进行此操作进行搜索。在图3-1中root列表的中间值 F保存到 root-Level的数据块中。然后以中间值为标准，将较小值和较大值的信息分别保存到 branch level的左右数据块中。当然，branch level数据块的信息也会一同保存至 root level数据块。最后 leaflevel数据块中将同时保存生成表索引的列信息和ROWID信息，完成B*树。

pagenumber_ebook=54,pagenumber_book=43

图3-1　B*树原理

下面介绍如何使用B*树索引搜索数据。首先假设用户搜索FIFA资料信息。先以索引方式生成包含FIFA的列。然后对WHERE子句创建并运行SQL语句，以便查找FIFA值。然后服务器在分析该SQL语句时，条件子句的FIFA值列便会知道已生成索引，先从索引的 root-level进行搜索。FIFA比root-level的值大，因此会移动到右侧 branch，然后向位于最左侧的branch移动，最后移动到leaf-level便能找到所需资料。由于应用二分法扫描，资料搜索量减少50%，可以得到良好的性能。

B*树的特征如下：

（1）最常用作 Oracle的默认索引。

（2）用于选择度良好的列时可保证良好的性能。

（3）列中的NULL值较多时，若生成索引可有效搜索数据。

（4）连接时如果用于经常同时使用的列，则能保证良好的性能。

（5）列中不使用函数或运算符而且经常用于WHERE子句时可以使用。不适合随函数或运算符变化的列。

（6）在等值查询、范围查询、少量查询中可保证良好的性能。

为更好地调优，应充分了解B*树索引的缺点。以下为B*树索引的缺点：

（1）在B*树索引中，实际列值也应保存在索引中，因此管理大容量数据时可能会成为负担。

（2） B*树索引的列值选择度良好才能保证性能良好。

（3）在组合索引中，不使用条件的列或非同等条件的列位于组合索引中时会降低访问效率。(www.chuimin.cn)

（4）为兼容多种访问模式（access pattern），需要使用大量的索引。

（5）使用NOT或NULL，或者使用复杂的OR条件时无法保证索引的性能。

（6）存储空间浪费。在表列值和集内，B*树索引作为以排序形式保存相应数据ROWID的结构，相同值的物理地址不同时会重复保存相同的值，会造成存储空间浪费。此时，表列值的长度较大时将保留index的原始值，使Index的大小增大。

（7）灵活性（flexibility）缺乏。在B*树Index中，访问相同的表时将并行使用两个以上的索引，因此有许多限制。但在实际业务环境中用户的要求多种多样。为满足这些要求，要创建表中所有列组合数的B*树index数，此时可能会出现index的大小反而比表更大的奇怪现象。此外，管理各个索引的成本可能会超出管理表本身的成本。因此，可以说是无法应用到实际业务中的情况。

（8）如果有两个选择度良好的B*树索引，要同时使用两个以上的索引时会有某些限制条件。所以B*树索引可使用多种选项：

① UNIQUE INDEX: UNIQUE INDEX的优点是使用时可以不包含使用索引的列的重复值。在primary key和unique限制条件下生成的索引为unique索引。生成Script :

CREATE UNIQUE INDEX ［index name］ON table（column）；

② NON-UNIQUE INDEX: NON-UNIQUE INDEX可包含使用索引的列的重复数据值。生成Script :

CREATE INDEX ［index_name］ON table（column）；

③ CONCATENATED INDEX：组合索引通常用于比较搜索两个列时。如果生成的组合索引为index ON table（A， B， C），则从前面开始按顺序进行排序。即对A列进行排序后再对B列进行排序，对B列进行排序后再对C列进行排序。且生成索引时，应将条件搜索时重要的条件（列）放在最前面。

A.SELECT * FROM table WHERE A=20 AND B=30 AND C=10；

B.SELECT * FROM table WHERE A=20时较快。

C.SELECT * FROM table WHERE C=20 AND B=30 AND A=10；时 INDEX不起作用。

采用A和B时，生成组合索引时表现出良好的性能。但用C时，组合索引不起作用。在以下章节中将进一步说明组合索引使用的相关内容。