第11章 索引（postgresql v17）

V17
Chapter 11. Indexes

索引是增强数据库性能的常用方法。索引允许数据库服务器查找和检索特定的行，比没有索引要快得多。但是索引也会给整个数据库系统增加开销，因此应该合理使用索引。

1、介绍

假设我们有一个类似这样的表:

CREATE TABLE test1 (
    id integer,
    content varchar
);

应用程序发出许多这种格式的查询:

SELECT content FROM test1 WHERE id = constant;

如果没有事先准备，系统将不得不逐行扫描整个test1表，以找到所有匹配的条目。如果test1中有很多行，而这样的查询只会返回几行(可能是0行或1行)，那么这显然是一个效率低下的方法。但是，如果指示系统在id列上维护索引，则可以使用更有效的方法来定位匹配行的位置。例如，它可能只需要在搜索树中走几个层次。

在大多数非小说类书籍中也采用了类似的方法:读者经常查找的术语和概念在书的末尾按字母顺序收集起来。感兴趣的读者可以相对快速地浏览索引并翻到适当的页面，而不是必须阅读整本书才能找到感兴趣的材料。正如作者的任务是预测读者可能查找的条目一样，数据库程序员的任务是预测哪些索引将是有用的。

下面的命令可以在id列上创建索引，如下所示:

CREATE INDEX test1_id_index ON test1 (id);

名称test1_id_index可以自由选择，但是您应该选择能够让您稍后记住索引的用途的名称。

需要删除索引，使用DROP INDEX命令。可以随时向表中添加和删除索引。

一旦创建了索引，就不需要进一步的干预:当表被修改时，系统将更新索引，并且当它认为这样做比顺序表扫描更有效时，它将在查询中使用索引。但是，您可能必须定期运行ANALYZE命令来更新统计信息，以便查询规划器能够做出明智的决策。请参阅第14章，了解如何确定是否使用了索引，以及何时以及为什么计划器可能选择不使用索引。

索引还可以使带有搜索条件的UPDATE和DELETE命令受益。索引还可以用于连接搜索。因此，在作为连接条件一部分的列上定义的索引也可以显著加快使用连接的查询速度。

一般来说，PostgreSQL索引可以用来优化包含一个或多个WHERE或JOIN子句的查询：

indexed-column indexable-operator comparison-value

在这里，索引列（indexed-column）是定义了索引的列或表达式。可索引操作符（indexable-operator）是一个操作符，它是索引列的索引操作符类的成员。(详情见下文。)比较值（comparison-value ）可以是任何非易变且不引用索引表的表达式。

在某些情况下，查询规划器可以从另一个SQL构造中提取这种形式的可索引子句。一个简单的例子是，如果原条款是

comparison-value operator indexed-column

然后，如果原始操作符(operator)有一个交换子操作符，并且该交换子操作符是索引操作符类的成员，则可以将其翻转为可索引的形式。

在大型表上创建索引可能需要很长时间。默认情况下，PostgreSQL允许在创建索引的同时对表进行读操作(SELECT语句)，但是写操作(INSERT, UPDATE, DELETE)会被阻塞，直到索引创建完成。在生产环境中，这通常是不可接受的。允许写操作与索引创建并行进行是可能的，但是需要注意几个注意事项——有关更多信息，请参阅并发构建索引。

创建索引后，系统必须使其与表保持同步。这增加了数据操作的开销。索引还可以防止创建仅限堆的元组。因此，应该删除查询中很少使用或从不使用的索引。

2、Index Types

PostgreSQL提供了几种索引类型:B-tree、Hash、GiST、SP-GiST、GIN、BRIN和扩展bloom。每种索引类型使用最适合不同类型的可索引子句的不同算法。默认情况下，CREATE INDEX命令创建B-tree索引，这适合最常见的情况。其他索引类型是通过写入关键字USING和索引类型名称来选择的。例如，要创建一个哈希索引:

CREATE INDEX name ON table USING HASH (column);

2.1. B-Tree

b树可以处理按某种顺序排序的数据的相等性和范围查询。特别地，PostgreSQL查询规划器将考虑使用B-tree索引，当索引列涉及到使用以下操作符之一进行比较时:

<   <=   =   >=   >

等价于这些操作符的组合的构造，例如BETWEEN和IN，也可以通过b树索引搜索来实现。此外，索引列上的IS NULL或IS NOT NULL条件也可以与b树索引一起使用。

优化器还可以对涉及模式匹配操作符LIKE和~的查询使用b树索引，如果模式是常量并且锚定在字符串的开头—例如，col LIKE 'foo%'或col ~ '^foo'，但不能使用col LIKE '%bar'。但是，如果您的数据库不使用C语言环境，则需要使用特殊的操作符类创建索引，以支持模式匹配查询的索引;参见下面第11.10节。也可以为ILIKE和~*使用b -树索引，但前提是模式以非字母字符开始，即不受大小写转换影响的字符。

b树索引还可以用于按排序顺序检索数据。这并不总是比简单的扫描和排序更快，但它通常是有用的。

2.2. Hash

哈希索引存储从索引列的值派生的32位哈希码。因此，这样的索引只能处理简单的相等比较。查询规划器将考虑在使用相等操作符进行比较时使用散列索引:

=

2.3. GiST

GiST索引不是一种索引，而是一种基础设施，可以在其中实现许多不同的索引策略。因此，可以使用GiST索引的特定操作符取决于索引策略(操作符类)。作为一个例子，PostgreSQL的标准发行版包含了一些二维几何数据类型的GiST操作符类，它们支持使用这些操作符进行索引查询:

<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   &&

(这些操作符的含义参见9.11节。)标准发行版中包含的GiST操作符类见表64.1。许多其他GiST操作符类可以在贡献contrib 中或作为单独的项目使用。有关更多信息，请参见第64.2节。

GiST索引还能够优化“最近邻”搜索，例如

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

它会找出离给定目标点最近的10个位置。这样做的能力同样依赖于所使用的特定操作符类。在表64.1中，可以以这种方式使用的操作符列在“排序操作符”中。

2.4. SP-GiST

与GiST索引一样，SP-GiST索引提供了支持各种搜索的基础设施。SP-GiST允许实现各种不同的基于磁盘的非平衡数据结构，例如四叉树、k-d树和基数树(尝试)。举个例子，PostgreSQL的标准发行版包含了用于二维点的SP-GiST操作符类，它支持使用以下操作符进行索引查询:

<<   >>   ~=   <@   <<|   |>>

(这些操作符的含义参见9.11节。)标准发行版中包含的SP-GiST操作符类见表64.2。有关更多信息，请参见第64.3节。

和GiST一样，SP-GiST支持“最近邻”搜索。对于支持距离排序的SP-GiST操作符类，相应的操作符列在表64.2的“排序操作符”列中。

2.5. GIN

GIN索引是“倒排索引(inverted indexes)”，适用于包含多个组件值的数据值，比如数组。倒排索引包含每个组件值的单独条目，并且可以有效地处理测试特定组件值是否存在的查询。

与GiST和SP-GiST一样，GIN可以支持许多不同的用户定义索引策略，并且可以使用GIN索引的特定操作符因索引策略而异。例如，PostgreSQL的标准发行版包含了一个用于数组的GIN操作符类，它支持使用以下操作符进行索引查询:

<@   @>   =   &&

(这些操作符的含义见第9.19节。)表64.3记录了标准分布中包含的GIN操作符类。许多其他GIN操作符类可以在贡献（contrib ）集合中或作为单独的项目使用。有关更多信息，请参见第64.4节。

2.6. BRIN

BRIN索引(块范围索引的简写)存储存储在表中的连续物理块范围中的值的摘要。因此，对于那些值与表行物理顺序密切相关的列，它们是最有效的。像GiST, SP-GiST和GIN一样，BRIN可以支持许多不同的索引策略，并且可以使用BRIN索引的特定操作符根据索引策略而变化。对于具有线性排序顺序的数据类型，索引的数据对应于每个块范围的列中值的最小值和最大值。它支持使用以下操作符的索引查询:

<   <=   =   >=   >

标准分布中包含的BRIN操作符类见