インデックス（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。如果我们指定它，索引将只允许向表中插入唯一值。实际上，这是实现唯一键的基础。唯一键在数据库中广泛使用。也就是说，主键是唯一键索引，但并非每个唯一键都是主键。我们上面只讨论了主键。主键是最常用的唯一键类型。为表创建主键时，会自动创建一个唯一索引。它被赋予一个由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.

Третий способ перестройки индекса основан на свойстве резервных копий баз данных InterBase, создаваемых утилитой gbak. Дело в том, что при резервном копировании данные, включенные в индекс, не сохраняются в резервной копии, сохраняется только определение индекса. При восстановлении из резервной копии индекс пересоздается. Если вы хотите узнать о резервном копировании более подробно, см. главу «Резервное копирование и восстановление из резервной копии» (часть 4).

Четвертый способ повышения производительности индексов - сбор статистики по индексам с помощью команды SET STATISTICS. Статистика таблицы - это значение в диапазоне от 0 до 1, которое зависит от количества различных записей в таблице. Оптимизатор InterBase использует статистику для определения эффективности применения того или иного индекса в запросе. Когда количество записей в таблице может существенно измениться (например, из-за большого количества вставок или удалений), пересчет статистики может значительно повысить производительность. Команда пересчета статистики выглядит следующим образом:

SET STATISTICS INDEX name;

Здесь name - это имя индекса, для которого пересчитывается статистика. Пересчет статистики не перестраивает индекс, и поэтому он свободен от большинства ограничений, установленных для описанных выше способов повышения производительности, за исключением того, что пересчитывать статистику может только создатель индекса или системный администратор (пользователь с именем SYSDBA). Правильная статистика позволяет оптимизатору принять верное решение об использовании того или иного индекса.

Мы рассмотрели несколько способов повышения производительности индексов. Используя команды ALTER INDEX и DROP/CREATE INDEX, мы можем перестроить любые индексы, кроме системных индексов, созданных автоматически и предназначенных для обеспечения ссылочной целостности. Если вы хотите перестроить эти индексы, вам следует использовать команды изменения и создания таблиц - ALTER TABLE и CREATE TABLE, поскольку эти индексы являются неотъемлемой частью табличных ключей.