Postgres 中数据库索引简介

A shelf with books perfectly lined up. Book indexes work like database indexes.

作者：Umair Shahid

什么是索引？

从根本上说，数据库索引是一种经过战略性设计的的数据结构，可以提高数据库表中数据检索活动的速度。您可以将其比作书籍中的目录，它可以让您快速找到特定主题，而无需翻阅整本书。

同样，PostgreSQL 中的索引就像数据库引擎的导航系统。它们有助于快速有效地定位和提取特定行，无需进行全表扫描。简单地说，索引就像表中数据的路标。 

了解从 Postgres 表创建、查看、列出和删除索引的基本命令。

PostgreSQL 中索引的优势

在 PostgreSQL 中利用索引具有几个关键优势

  1. 快速数据访问：索引有助于大幅缩短检索数据所需的时间，尤其是从大型表中检索数据。如果没有索引，则需要进行完整的表扫描，这可能会非常耗时。

  2. 提高查询效率：在 WHERE 子句中包含条件或需要表连接的查询，在使用索引时，其性能会得到显著提高。此类查询利用索引快速查找到满足设定条件的行。

  3. 减少磁盘 I/O：由于索引只保存表数据的一个子集，因此可以显著减少磁盘 I/O 操作。这不仅可以加快查询执行速度，还可以减轻存储系统的负载。

  4. 维护数据完整性：唯一索引可以防止特定列中出现重复值，从而通过确保没有两行在指定列中具有相同的值来维护数据完整性。

PostgreSQL 中索引的劣势

尽管有这些优势，但在 PostgreSQL 中使用索引也有一些潜在的缺陷

  1. 增加存储需求：使用索引最明显的缺点是它们需要额外的存储空间。确切的数量取决于表的大小和索引列的数量。通常情况下，这只是表总大小的一小部分。但是，对于大型数据集，添加多个索引可能会导致存储使用量显著增加。

  2. 写入操作变慢：每次插入、更新或删除行时，都必须更新索引。因此，写入操作可能会变慢。在考虑使用索引时，平衡读取和写入操作至关重要。如果您的应用程序严重依赖写入操作，则应仔细权衡更快读取的优势与写入速度较慢的成本。

  3. HOT 更新：PostgreSQL 采用一种称为多版本并发控制 (MVCC) 的机制进行更新。但是，索引会导致“HOT”（仅堆元组）更新。每个更新操作实际上都会导致生成一个新的行版本，并在每个关联的索引中生成一个新的条目，而不是允许直接的就地更新。这会导致 I/O 活动增加，并在数据库中添加无效行。

尽管 PostgreSQL 索引在查询性能方面具有显著优势，但在存储效率和写入性能至关重要的场景下，尤其需要权衡这些优势与潜在的劣势。

接下来，让我们探讨 PostgreSQL 提供的各种类型的索引。

常见的 PostgreSQL 索引类型（示例）

1. B 树索引

B 树（平衡树）索引是 PostgreSQL 中默认和最广泛使用的索引类型。 当索引列参与使用以下任何运算符的比较时：<、<=、=、>=、>，查询规划器将考虑使用 B 树索引。

B 树索引还可以有效地与 BETWEEN 和 IN 等运算符一起使用。 此外，索引列上的 IS NULL 或 IS NOT NULL 条件可以与 B 树索引组合使用。

默认情况下，B 树索引按升序排列其条目，空值最后。 如果您希望更深入地了解索引排序及其在特定场景中的潜在优势，请参阅此页面。

示例

CREATE INDEX index_product_id ON products (product_id);

在此示例中，在 products 表的 product_id 列上创建了一个 B 树索引。 该索引提高了查询特定 product_id 值或 product_id 范围的速度。

2. 哈希索引

哈希索引非常适合基于相等的查找，但不支持范围查询或排序。 它们适用于整数之类的数据类型，并且对于相等性检查通常比 B 树索引更快。

示例：

CREATE INDEX index_product_id ON products USING HASH (product_id);

3. 复合索引

多列索引是在表的多个列上定义的。

示例

CREATE INDEX index_product_id_name ON products (product_id, product_name);
假设在从 products 表中检索数据时，product_id 列被大量使用。 因此，我们优先考虑 product_id 列而不是 product_name 列。

在决定创建单列索引还是多列索引时，需要考虑查询的 WHERE 子句中经常用作过滤条件的列。

将使用上述索引 (index_product_id_name) 的查询如下

  1. Select * from products where product_id = 1 and product_name = 'a'; (index_product_id_name is used)


  2. Select * from products where product_id = 1; 
(index_product_id_name is used)


  3. Select * from products where product_name = 'a';
 (index_product_id_name is not used)

4. 部分索引

部分索引是建立在表子集上的索引； 子集由条件表达式（称为部分索引的谓词）定义。 索引仅包含满足谓词的表行的条目。

示例

CREATE INDEX index_product_id ON products(product_id) where product_available = ‘true’;

部分索引的一种常见用例是过滤掉与大多数查询无关的行。 例如，假设您有一个产品表，其中包含一个名为 product_available 的列，该列可以是“true”或“false”。 大多数查询只需要访问可用的产品，因此您可以在可用性列上创建一个部分索引，其中 available = 'true'。

5. 覆盖索引

如果查询中的选择列表与索引中包含的列匹配，则覆盖索引允许用户执行仅索引扫描。可以使用 INCLUDE 关键字指定其他列。

示例

CREATE INDEX index_product_id_name_status ON products (product_id, product_name) include (status);

当用户运行以下查询时

EXPLAIN ANALYZE SELECT product_id,product_name,status from products
WHERE
    product_id < 10;

SELECT 子句中指定的所有列都将直接从索引页中检索。 从理论上讲，这可以显着减少查询访问信息所需的 I/O（输入/输出）量。

传统上，I/O 操作是数据库系统中的一个重要瓶颈，因此通过避免访问堆（表数据）和最大限度地减少对多个 I/O 操作的需求，PostgreSQL 可以提高查询性能。

但是，在实现覆盖索引时需要注意。添加到索引中的每一列仍然占用磁盘空间，并且维护索引会产生相关的成本，尤其是在行更新方面。

6. 块范围索引 (BRIN)

BRIN 索引专为具有排序数据的大型表而设计，例如时间序列数据。 它们将表划分为逻辑块，其中每个块包含一定范围的值。 索引不是为每一行存储单独的索引条目，而是存储每个块内的最小值和最大值，从而使其尺寸比其他索引类型更小。

示例：

CREATE INDEX btree_example_index ON logs(log_date);
索引大小：541 MB


CREATE INDEX brin_example_index ON logs USING BRIN(log_date);
索引大小：544 kB

我们在 log_date 列上创建了 B-TREE 和 BRIN 索引。 这表明 BRIN 索引确实具有很高的空间效率，并且在处理以顺序工作负载为特征的大型表时可以提供显着优势。

7. 其他索引

对于数组或几何形状等复杂数据类型，请使用 GiST、GIN 或 SP-GIST 索引。

继续阅读

要详细了解影响索引使用的关键服务器参数，请阅读这篇关于优化数据库索引以微调 PostgreSQL 性能的文章。 对于 Timescale 中的索引还有疑问？我们在这篇博文中回答了这些问题。