索引（InterBase 和 Firebird）

Alexey Kovyazin，最后更新于2005年9月7日

作为索引基础的概念简单直观，是数据库设计最重要的基础之一。许多数据库基本对象都建立在索引之上，而且正确使用索引是提高数据库应用程序生产力的关键。然而，什么是索引？索引是表中记录的有序指针。指针意味着索引包含表中一个或多个字段的值以及这些值所在数据页的地址（有关数据页的详细信息，请参阅“InterBase数据库结构”一章）（第4部分）。换句话说，索引由“字段值”-“该字段的物理位置”的值对组成。

因此，通过索引中包含的字段（或字段组）的值，使用索引我们可以快速找到表中包含该值的记录所在的位置。有序意味着存储在索引中的字段值是有序排列的。索引经常被比作图书馆目录，其中所有书籍都记录在卡片上，并按某种方式排序：按字母顺序或主题，每张卡片都包含关于某本书在存储中的确切位置的信息。

为什么需要索引？

索引唯一促进的是通过其索引字段（索引字段–指包含在索引中的字段）加速记录检索。索引的主要功能是提供表中记录的快速检索。任何索引的使用都归结于此。

这个检索功能是如何实现的？在这个函数的输入端，我们有索引字段（或几个字段）的值。作为检索的结果，我们应该收到完整的记录，其中索引字段具有预设值。首先在索引中（更准确地说，在索引字段值的有序数组中）搜索所需的值，然后获取所需记录所在数据页的地址，服务器转到该页并读取找到的记录。这看起来相当不方便，然而，使用索引的搜索比从表中顺序枚举所有值要快得多。

如果我们继续将索引与图书馆目录进行类比，我们会发现使用索引的记录检索与使用卡片搜索书籍非常相似。当我们在一个相对较小的目录（与整个图书馆存储相比）中找到一本书时，我们立即获得关于该书确切存储位置的信息，并且可以直接前往那里。不使用索引的搜索可以比作对图书馆中所有书籍的顺序枚举！

对表中所有记录的枚举称为直接或自然枚举。我们应该说，尽管现代计算机功能强大，但如果表中包含大量记录，自然枚举可能会非常耗时。

它们是如何组织的？

索引不是表的一部分，它是与表和其他数据库对象相连的独立对象。这是DBMS实现中非常重要的一点，允许将信息存储与其表示分离。

InterBase与任何其他关系数据库一样，以无序方式在表中存储记录，即完全不关心记录在表中如何物理分配。无序存储意味着依次添加到表中的两条记录可能不彼此相邻。此外，从表中提取的数据也没有顺序，除非用户在执行检索查询时明确指定。

然而，我们不能没有对存储数据进行排序：应用程序的最终用户希望以定义的顺序查看数据–例如，按字母顺序排列的姓氏。索引解决了以有序方式表示数据的问题。包含在索引中的字段值被排序，并以针对搜索所需值优化的特殊视图表示（即，这对于创建有序序列至关重要）。

将数据存储与其表示分离，与直接排序相比带来了额外的好处–也许您需要以不同方式对原始表进行排序。那么索引将帮助您–每个表最多可以有64个索引！

如果我们从物理层面讨论索引的实现，它们表示一个二叉树，其节点表示“索引中的字段值”-“表中的数据分配”对。在索引中检索所需记录是使用哈希搜索机制执行的–这是最快的搜索算法之一。

索引的应用

现在，当我们清楚可以从索引中要求什么时，是时候了解它们在数据库中的功能了。索引在三种主要情况下使用：

加速查询执行。为用于SQL查询搜索条件的字段创建索引。
支持字段值的唯一性；主键约束（在“表。主键”一章中已讲述）要求表中不能有两个相同的包含在主键中的字段值。为了满足此条件，在插入新记录时，应搜索将要插入的相同值。为了记录检索，使用索引的特殊变体–唯一索引（见下文）。
支持引用完整性。外键约束（在“数据库约束”一章中讨论）用于检查插入表中的值必须存在于其他表中。创建外键时自动创建索引。此索引用于加速使用表连接的查询，以及检查外键的条件。我们已经简要介绍了所有可能的索引应用。现在我们将更详细地考虑每种情况的特殊性，并回答有关索引应用的最常见问题。

使用索引加速查询执行

上面已经描述，索引的应用可以大大加速查询的执行。在大多数情况下确实如此，但有一些限制条件。首先，我们将回答那些刚熟悉索引的人中经常出现的问题。如果索引加速了数据库的检索，为什么不对表中的所有字段建立索引呢？有两个因素阻碍了全面索引–磁盘空间和修改表中数据时的成本。每个创建的索引的大小等于索引字段中的数据大小，加上记录分配的数据大小。如果我们为表中的每个字段创建索引，它们的总大小将超过表中的数据大小！因此，创建大量索引会导致磁盘空间的大量消耗。

第二个因素更重要。这是修改表中数据时的开销。在关系型DBMS中，如您所知，表中的记录是无序的，因此添加/删除记录不会产生显著的服务器资源开销。即使从数据库中间删除一条记录，也不需要移动数据来填补这个空白–这是不必要的：服务器只需标记空位置，并在需要时在那里写入内容。至于添加，在大多数情况下是在表的末尾执行的。然而，尽管服务器在修改时不会移动表中的主要数据，但存储在索引中的数据在每次添加/删除记录时都会重新排序！换句话说，当向表中间添加记录时，服务器必须重建索引。当然，索引实现某种程度上是为了频繁重组而设计的，但这些操作仍然需要时间和处理器资源，当表中有大量索引时，其中的数据修改可能比没有索引的同一表慢得多！

这是阻碍全面索引的两个主要原因。除此之外，还有一些限制索引应用的注意事项。第一个是20%规则。它说如果检索查询返回表中超过20%的记录，使用索引可能会减慢数据检索！当然，情况取决于具体的查询和检索条件，但我们应该记住，20%的记录是索引使用效率变得可疑的阈值。第二个注意事项没有如此明确地表述。它与InterBase优化器的工作有关。

优化器是一组机制的集合，它负责制定执行查询的计划。当用户向InterBase提交任何SQL查询时，他指定了服务器在执行查询后应返回什么，但没有定义服务器应如何执行该查询。优化器基于给定的查询创建其执行计划，即从何处以及以何种顺序获取执行查询所需的数据，以及在此过程中将使用哪些索引。当服务器分析检索条件（这些主要是WHERE、ORDER BY等表达式的部分）时，对于条件中包含的每个字段，服务器都会尝试使用索引。不幸的是，创建计划的算法并不完善，优化器经常使用对于具体查询来说不够有效的索引，这可能导致执行时间显著变慢。因此，创建不必要的索引可能导致生成非最优的计划。

需要指出的是，在Yaffil克隆中，这个问题通过使用现代的计划生成算法得到了解决。第三种不需要索引的情况是值集有限的字段–例如，存储人员性别信息且仅包含两个可能值（“F”和“M”）的字段；对此字段建立索引没有意义。至此，我们已经考虑了创建索引的主要限制。现在我们应该讨论何时需要使用索引以提高生产力的问题。有三种主要情况需要对字段建立索引：

当该字段在查询的检索条件下使用时
当表连接使用该字段时
当该字段在ORDER BY排序语句中使用时

如果字段以上述方式被使用，为其创建索引可以提高查询生产力。

让我们考虑创建索引的语法。以下是允许创建索引的DDL命令的完整格式：

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX index ON table (col [, col …]);

创建索引的最小表达式如下：

CREATE INDEX my_index ON Table_example(ID)

在此示例中，为表Table_example创建了名为my_index的索引，ID字段是被索引的字段。该索引是升序的，即其中的值按升序排列，同时也是非唯一的，这意味着ID字段可以有多个相同的值。这当然是最简单的索引示例–也是最常见的。从语法描述中可以看出，索引可以包含不止一个字段，而是多个字段。当查询频繁执行且包含索引字段组合的搜索或排序条件时，会使用此类索引。例如，如果我们有一个包含姓氏、名字和父名字段的表，当执行使用按姓氏、名字和父名排序的查询时，将应用此类索引。一般来说，不必为索引中应用的所有3个字段指定条件才能利用其优势。如果我们想对查询结果进行排序，只要排序条件中的第一个字段与索引中的第一个字段一致，就会使用该索引。例如，在按姓氏和名字排序的情况下，我们的索引将被应用。

根据文档，为了优化包含WHERE语句中带有OR条件的字段连接的查询执行，我们不应使用聚合索引，而应为OR条件中包含的所有字段使用多个单一索引。

至于索引的排序顺序问题，它可以是升序或降序。为什么我们需要不同的排序顺序？显然，是为了不同的排序需求！如果我们希望按姓氏升序排列人员，我们创建升序索引（ASC）；如果按降序（从Z到A）排列–则创建降序索引！如果我们两者都需要，就必须创建两个索引。

使用索引支持引用完整性

索引定义中还有一个选项–UNIQUE。如果我们指定它，索引将只允许向表中插入唯一值。实际上，这是实现唯一键的基础。唯一键在数据库中被广泛使用。也就是说，主键（РК）是一种唯一键索引，但并非每个唯一键（UK）都是主键。我们上面只讨论了主键。主键是最常用的唯一键类型。为表创建主键时，会自动创建一个唯一索引。该索引的名称由RDB$PRIMARYNNN组成，其中NNN是数据库内的顺序唯一编号。因此，引用完整性的两个主要约束–唯一键和主键–是通过使用唯一索引来实现的。显然，唯一性的概念与未定义值的概念是不兼容的。换句话说，包含在唯一索引中的字段不应有任何NULL类型的值。在为字段创建唯一索引之前，有必要设置NOT NULL约束。如果索引是为已存在的数据创建的，那么在创建时，将检查被索引字段是否包含任何重复值。如果包含，将禁止创建索引。

除了唯一键和主键约束之外，索引机制还支撑着另一个引用完整性约束的实现–外键。外键约束设置在任何表的一个或多个字段上，防止向这些字段插入不包含在另一个父表主键中的值。为了实现外键，即执行父表中是否存在值的检查，会自动创建一个特殊索引。其名称为RDB$FOREIGNNN，其中NNN是数据库内的顺序唯一编号。

为什么使用索引机制来实现引用完整性约束？关键在于，InterBase中的索引处于特殊的优先位置–据说它们在事务上下文之外执行。这是一个非常重要的属性。我们将在专门讨论事务的章节中稍后详细说明。现在我们只提一下，当索引处于事务之外时，意味着所有同时使用同一表中数据的用户都必须遵守引用完整性约束。

索引生产力的优化

在本部分的标题中，我们可以发现一些悖论–索引，如上所述，是为了加速查询执行，而结果却发现它们也需要被优化！但有什么办法呢（生活就是这样）–总得有人来照顾索引。索引发生了什么？为什么它们会“走形”？我们不得不再次说明，索引是以二叉树的形式实现的。当向表中添加新记录（更新、删除–随你便）时，树中会添加一个新分支。这些分支不是添加到树的中间，而是添加到其他分支的顶端。逐渐地，树变得越来越枝繁叶茂（或不平衡），搜索效率也越来越低。重建树或（在某些情况下）重新计算统计信息可以改善这种情况。

定期需要重新创建索引以恢复其生产力。索引重建发生在以下情况：

使用ALTER INDEX命令重建索引时。
使用DROP INDEX和CREATE INDEX命令删除并重新创建索引时。
使用gbak工具进行备份并从备份副本恢复时。

您还可以使用统计信息重新计算。但必须理解，此操作不会改变索引状态，它只是向优化器提供关于其状态的精确信息，使其能够正确使用该索引。换句话说，统计信息重新计算不是索引的“治疗”，而只是对其状态的准确诊断。让我们更详细地考虑所有这些索引优化方法。使用ALTER INDEX命令的格式如下：

ALTER INDEX name {ACTIVE | INACTIVE};

这里name是索引的名称，ACTIVE和INACTIVE是使用ALTER INDEX命令可以将索引转换到的两种状态。参数ACTIVE表示索引处于活动状态，可以在所有查询和过程中应用。如果将索引设置为INACTIVE，将导致其使用被断开。为了重新排列树，需要依次执行两个命令：

ALTER INDEX name INACTIVE; ALTER INDEX name ACTIVE;

因此，索引将被重建。使用 ALTER INDEX 存在一些限制：不能重建用于主键、唯一键和外键的索引；如果索引当前正被任何查询使用，则不能重建；此外，修改索引需要具有管理员（SYSDBA）权限，或者是该索引的创建者。

使用 DROP INDEX 和 CREATE INDEX 命令重建索引会导致索引从数据库中完全删除，然后从空白状态重新创建。DROP INDEX 命令的语法很直观：

DROP INDEX 索引名;

删除后，需要使用 CREATE INDEX 命令以相同的名称和参数重新创建索引，该命令的语法我们之前已经讨论过。通过完全重建索引的方式与使用 ALTER INDEX 具有类似的限制。

第三种重建索引的方法基于 gbak 工具创建的 InterBase 数据库备份副本的特性。关键在于，备份时索引中包含的数据不会保存到备份副本中，只存储索引定义。从备份副本恢复时，索引会被重新创建。如果您想更详细地了解备份，请参阅“备份和从备份副本恢复”一章（第 4 部分）。

第四种提高索引性能的方法是使用 SET STATISTICS 命令收集索引统计信息。表统计信息是 0 到 1 之间的一个值，其值取决于表中不同记录的数量。InterBase 优化器使用统计信息来确定查询中某个索引的应用效率。当表中的记录数量可能发生显著变化时（例如，由于大量插入或删除），重新计算统计信息可以显著提高性能。重新计算统计信息的命令如下：

SET STATISTICS INDEX name;

这里 name 是要重新计算统计信息的索引名称。重新计算统计信息不会重建索引，因此它不受上述提高性能方法的大多数限制，但只有索引的创建者或系统管理员（用户名为 SYSDBA 的用户）才能重新计算统计信息。正确的统计信息使优化器能够做出关于是否使用某个索引的正确决策。

我们已经讨论了几种提高索引性能的方法。使用 ALTER INDEX 和 DROP/CREATE INDEX 命令，我们可以重建除自动创建、用于提供引用完整性的系统索引之外的任何索引。如果要重建这些索引，应使用 ALTER TABLE 和 CREATE TABLE 命令来修改和创建表，因为这些索引是表键的组成部分。