PostgreSQL索引种类这么多，如何选择最适合的来提升查询性能？

在PostgreSQL数据库中，索引的使用对于提升查询性能至关重要。索引不仅能大幅度减少查询时间，还能优化数据存储和访问。然而，PostgreSQL 提供了多种索引类型，如何选择适合的索引类型，以及如何在具体场景中最大化它们的性能是每位数据库管理员和开发者必须面对的关键问题。本文将全面介绍PostgreSQL中的多种索引类型及其应用场景，并提供关于如何高效使用这些索引的最佳实践，以帮助您优化数据库的查询性能。

文章导航

一、PostgreSQL的多种索引类型及其最佳应用场景

PostgreSQL提供了多种索引类型： B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。

每种索引类型使用不同的算法，适合不同类型的的查询，可以根据不同的需求选择适合的索引类型以提高查询性能。

默认情况下，CREATE INDEX命令创建B-tree索引，适用于大多数常见情况。其他索引类型可以通过写入关键字USING，后跟索引类型名称来选择。例如，要创建一个Hash索引：

CREATE INDEX name ON table USING HASH (column);

1. B 树索引 (B-tree Index)

B 树索引是最常见的索引类型，适用于范围查询、等值查询和排序。

查询规划器将在涉及到使用以下运算符之一的比较时，会考虑使用B-tree索引：

< <= = >= >
BETWEEN、IN
IS NULL、IS NOT NULL
匹配操作符LIKE和~ 的查询，仅限开头不是通配符，例如：col LIKE 'foo%'或者col ~ '^foo'

2. 哈希索引 (Hash Index)

Hash索引，存储从索引列的值派生的32位哈希码。因此，这样的索引只能处理简单的等式比较。相比于 B 树索引，哈希索引在等值查询时可以提供更快的查找速度，但是在范围查询和排序等方面性能较差。

hash索引特别适用于字段VALUE非常长的场景

查询规划器将在涉及使用等式运算符进行比较时，考虑使用哈希索引：

3. GiST 索引 (Generalized Search Tree)

GiST索引不是一种单一类型的索引，而是一种通用的索引结构，可以用于实现很多不同索引策略的基础设施。可以使用的特定运算符取决于索引策略（操作符类）。

PostgreSQL的标准捐献包中包括了用于多种二维几何数据类型的GiST操作符类，它用来支持使用下列操作符的索引化查询：

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

4. SP-GiST 索引 (Space-Partitioned Generalized Search Tree)

与GiST索引类似，SP-GiST索引也提供了支持各种搜索的基础设施。SP-GiST允许实现各种不平衡的基于磁盘的数据结构，例如四叉树、k-d树和基数树（tries）。例如，PostgreSQL的标准分发包含了用于二维点的SP-GiST操作符类，支持使用以下运算符进行索引查询：

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

5. GIN 索引 (Generalized Inverted Index)

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

与GiST和SP-GiST类似，GIN可以支持许多不同的用户定义的索引策略，而GIN索引可以使用的特定运算符取决于索引策略。例如，PostgreSQL的标准分发包含了用于数组的GIN操作符类，支持使用以下运算符进行索引查询：

<@ @> = &&

6. BRIN 索引 (Block Range Index)

BRIN 索引（块范围索引的缩写）存储有关表的连续物理块范围中存储的值的摘要。因此，它们对于其值与表行的物理顺序密切相关的列最为有效。与GiST、SP-GiST和GIN类似，BRIN可以支持许多不同的索引策略，而BRIN索引可以使用的特定运算符取决于索引策略。对于具有线性排序顺序的数据类型，索引化的数据对应于每个块范围中列值的最小值和最大值。这支持使用以下运算符进行索引查询：

< <= = >= >

二、如何有效利用多列索引进行查询优化

基于多个字段的索引，也就是多列索引（复合索引）。目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列。

对于多列索引，应该将最常作为查询条件使用的字段放在左边，较少使用的字段放在右边。

例如，在(company_id, type_id, creator_id)上有一个索引并且给定一个查询条件

WHERE company_id = 5 AND type_id >= 42 AND creator_id < 77，对索引的扫描将从第一个具有company_id = 5和type_id = 42的项开始向上进行，直到最后一个具有company_id = 5的项。在扫描过程中，具有creator_id = 77的索引项将被跳过，但是它们还是会被扫描到。

基于(company_id, type_id, creator_id)创建的索引可以优化以下查询：

WHERE company_id = 3 and type_id = 5 and creator_id = 7;
WHERE company_id = 3 and type_id = 5;
WHERE company_id = 3;

但是以下查询无法使用该索引：

WHERE type_id = 5;
WHERE creator_id = 7;
WHERE type_id = 5 and creator_id = 7;

要使索引起作用，查询条件中的列必须要使用适合于索引类型的操作符，使用其他操作符的子句将不会被考虑使用索引。多列索引应该较少地使用。在绝大多数情况下，单列索引就足够了且能节约时间和空间。具有超过三个列的索引不太有用，除非该表的使用是极端程式化的。

三、PostgreSQL中索引与ORDER BY的使用指南

除了找到查询返回的行之外，索引还可以按特定的排序顺序返回。可以在不需要额外排序步骤的情况下，按照查询中指定的顺序返回结果。在PostgreSQL当前支持的索引类型中，只有B-tree可以产生排序后的输出，其他索引类型会把行以一种没有指定的且与实现相关的顺序返回。

规划器会考虑以两种方式来满足ORDER BY：

使用可用的索引来扫描符合排序要求的数据，并按照指定的顺序返回结果。
先以物理顺序扫描表然后再显式排序。

2种方式对比：

显式排序可能更快：对于大部分需要扫描表的查询，显式排序往往比使用索引更快。这是因为显式排序通常可以利用顺序访问模式，从而减少磁盘I/O的次数。相比之下，使用索引可能需要更多的随机访问，导致更多的磁盘I/O。
索引在少数行时更有用：当只有少数行需要被检索时，索引通常更有用。因为索引可以直接定位到符合条件的行，而不需要扫描整个表。
当ORDER BY与LIMIT n一起使用时，索引可以更加有效。因为有一个符合ORDER BY的索引，数据库可以直接获取前n行，而无需扫描整个数据集。

B-tree索引默认将其项以升序方式存储，并将空值放在最后。索引的一次前向扫描将产生满足ORDER BY x（ORDER BY x ASC NULLS LAST）的结果；被后向扫描，产生满足ORDER BY x DESC（ORDER BY x DESC NULLS FIRST）

在创建B-tree索引时，可以使用ASC、DESC、NULLS FIRST和NULLS LAST选项来指定索引的排序方式和对NULL值的处理方式。例如，使用NULLS FIRST选项可以将空值放在最前面。

四、如何在PostgreSQL中高效组合多个索引来优化查询

只有查询子句中在索引列上使用了索引操作符类中的操作符并且通过AND连接时才能使用单一索引。例如，给定一个(a, b) 上的索引，查询条件WHERE a = 5 AND b = 6可以使用该索引，而查询WHERE a = 5 OR b = 6不能直接使用该索引。

PostgreSQL具有组合多个索引（包括多次使用同一个索引）的能力来处理那些不能用单个索引扫描实现的情况。系统能在多个索引扫描之间安排AND和OR条件。例如， WHERE x = 42 OR x = 47 OR x = 53 OR x = 99这样一个查询可以被分解成为四个独立的在x上索引扫描，每一个扫描使用其中一个条件。这些查询的结果将被“或”起来形成最后的结果。

另一个例子是如果我们在x和y上都有独立的索引，WHERE x = 5 AND y = 6这样的查询的一种可能的实现方式就是分别使用两个索引配合相应的条件，然后将结果“与”起来得到最后的结果行。

五、如何用唯一索引确保数据唯一性

索引也可以被用来强制列值的唯一性，或者是多个列组合值的唯一性。

CREATE UNIQUE INDEX name ON table (column [, ...]);

当前，只有B-tree能够被声明为唯一。

当一个索引被声明为唯一时，索引中不允许多个表行具有相同的索引值。NULL值被视为不相同。

六、如何在PostgreSQL中使用表达式索引优化查询性能

一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。

例如，一种进行大小写不敏感比较的常用方法是使用lower函数：

SELECT * FROM entries WHERE lower(buyer_nick) = 'value';

这种查询可以利用一个建立在lower(buyer_nick)函数结果之上的索引：

CREATE INDEX idx_lower_buyer_nick ON entries (lower(buyer_nick));

另外一个例子，如果我们经常进行如下的查询：

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

那么值得创建一个这样的索引：

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不需要重新计算，因为它们的结果已经被存储在索引中了。

七、如何在PostgreSQL中使用部分索引提升查询效率

部分索引（partial index）是只针对表中部分数据行创建的索引，通过一个WHERE子句指定需要索引的行。

orders包含已完结和未完结的订单，其中未完结的订单在整个表中占据一小部分且它们是最经常被访问的行。可以通过只在未完结的行上创建一个索引来提高性能。创建索引的命令如下：

CREATE INDEX orders_not_end_index ON orders (plat_order_id)
    WHERE origin_status not in ('TRADE_CLOSED');

使用该索引的一个可能查询是：

SELECT * FROM orders WHERE origin_status not in ('TRADE_CLOSED') AND plat_order_id = '123';

然而，索引也可以用于完全不涉及plat_order_id的查询，例如：

SELECT * FROM orders WHERE origin_status not in ('TRADE_CLOSED') AND created_at > 1653816694;

这并不如在plat_order_id列上部分索引有效，因为系统必须扫描整个索引。然而，如果有相对较少的未上账订单，使用这个部分索引来查找未上账订单将会更好。

注意这个查询将不会使用该索引：

SELECT * FROM orders WHERE plat_order_id = '123';

订单可能在已完结或未完结中。

八、只用索引的扫描和覆盖索引

只用索引的扫描

PostgreSQL中的所有索引是二级索引，每个索引都是与表的主数据区（在PostgreSQL术语称为表的堆中）分开存储。这意味着在普通索引扫描中，每行检索都需要从索引和堆中取数据。此外，虽然匹配给定的可索引WHERE条件的索引条目通常在一起靠近存储，但它们引用的表行可能在堆中的任何地方。因此索引扫描的堆访问部分涉及到对堆的大量随机访问，这可能很慢，特别是在传统旋转媒介上。位图扫描尝试通过按排序的顺序进行堆访问来减少成本，但这远远不够）。

为了解决这种性能问题，PostgreSQL支持只用索引的扫描，这类扫描可以仅用一个索引来回答查询而不产生任何堆访问。其基本思想是直接从每一个索引项中直接返回值，而不是去参考相关的堆项。在使用这种方法时有两个根本的限制：

索引类型必须支持只用索引的扫描。B-树索引总是支持只用索引的扫描。GiST 和 SP-GiST 索引只对某些操作符类支持只用索引的扫描。其他索引类型不支持这种扫描。底层的要求是索引必须在物理上存储或者可以重构出每一个索引项对应的原始数据值。GIN 索引是一个不支持只用索引的扫描的反例，因为它的每一个索引项通常只包含原始数据值的一部分。
查询必须只引用存储在该索引中的列。例如，给定的索引建立在表的列x和y上，而该表还有一个列z，这些查询可以使用只用索引的扫描：

SELECT x, y FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND y < 42;

但是这些查询不能使用只用索引的查询：

SELECT x, z FROM tab WHERE x = 'key';
SELECT x FROM tab WHERE x = 'key' AND z < 42;

覆盖索引

为了有效利用仅索引扫描功能，可以创建一个覆盖索引，包含经常运行的特殊类型查询所需要的列。由于查询通常需要检索的列不仅仅是他们搜索的列，这可以通过添加INCLUDE来完成子句来列出了额外的列。

例如，如果通常可以运行这样的查询：

SELECT y FROM tab WHERE x = 'key';

加快此类查询的传统方法是仅在x上的索引。但是，一个索引定义为

CREATE INDEX tab_x_y ON tab(x) INCLUDE (y);

可以将这些查询作为仅索引扫描处理，因为y可以从索引中获取而不需要访问堆。

因为列y不是搜索键的一部分，它不必是索引可以处理的数据类型；它只存储在索引中，不由索引机解释。另外，如果索引是唯一的索引，则

CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);

唯一性条件仅适用于x列，而不是x和y的组合。（如果使用和在索引中设置的类似语法，一个INCLUDE子句可以写在UNIQUE和PRIMARY KEY约束中。）

原则上，只用索引的扫描可以被用于表达式索引。例如，给定一个f(x)上的索引（x是一个表列），可以把

SELECT f(x) FROM tab WHERE f(x) < 1;

作为只用索引的扫描执行

九、如何利用只用索引的扫描和覆盖索引提升查询性能

一个索引在每一个索引列上只能支持一种排序规则。如果需要多种排序规则，可能需要多个索引。

考虑这些语句：

CREATE TABLE test1c (
    id integer,
    content varchar COLLATE "x"
);

CREATE INDEX test1c_content_index ON test1c (content);

该索引自动使用下层列的排序规则。因此一个如下形式的查询：

SELECT * FROM test1c WHERE content > constant;

可以使用该索引，因为比较会默认使用列的排序规则。但是，这个索引无法加速涉及到某些其他排序规则的查询。因此对于下面形式的查询：

SELECT * FROM test1c WHERE content > constant COLLATE "y";

可以创建一个额外的支持"y"排序规则的索引，例如：

CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");

十、检查索引使用

检查真实的查询负载实际使用了哪些索引非常重要，检查一个独立查询的索引使用情况可以使用EXPLAIN命令。

总结

PostgreSQL为开发者和数据库管理员提供了多种索引类型和优化手段，从多列索引到部分索引，从只用索引扫描到覆盖索引，每一种索引都有其独特的应用场景。通过灵活运用这些索引，您不仅可以显著提升查询效率，还能更好地管理数据存储和访问。记得定期使用EXPLAIN命令检查索引的使用情况，以确保数据库持续高效运行。

延展阅读：

人工智能客服机器人为什么答非所问？怎么解决？

多模态AI技术解析：如何结合文本和图像提升AI应答智能度

淘宝机器人客服：如何运用回复技巧语提升用户体验？