选择正确的索引：PostgreSQL 索引类型指南

错误的 PostgreSQL 索引会浪费磁盘空间、拖慢写入速度，并且仍然会让你的查询扫描数百万行。正确的索引取决于 WHERE 子句中的运算符、列类型以及数据的形状。

从 B-tree 开始，用于普通的等值和范围查找。当你的查询模式需要特定的运算符支持时，再考虑使用 GIN、GiST、BRIN 或 SP-GiST。

索引在 PostgreSQL 中的重要性

在 PostgreSQL 中，索引的核心作用是减少查询时需要检查的数据量。没有索引，PostgreSQL 对许多查询将不得不执行全表扫描，这对于大表来说可能非常缓慢。索引创建了一种数据结构，使数据库能够快速定位相关行。索引的有效性在很大程度上取决于：

• 使用的索引类型： 不同的索引类型适用于不同的数据结构和查询操作。
• 数据分布： 倾斜的数据会影响索引性能。
• 查询模式： 你如何查询数据是一个重要因素。

以下是您最常需要在其中进行选择的索引类型。

PostgreSQL 索引类型详解

PostgreSQL 提供了几种索引类型。日常性能优化中最有用的是 B-tree、GIN、GiST、BRIN 和 SP-GiST。

1. B-Tree 索引

B-tree 是 PostgreSQL 的默认且最通用的索引类型。它适用于常见的比较运算符，包括 =、<、>、<= 和 >=。B-tree 索引非常适合等值检查、范围扫描、排序、唯一约束和主键。

工作原理： B-Tree 索引将数据存储在排序的树结构中。树中的每个节点都包含键和指向子节点的指针。这种结构确保了搜索、插入和删除数据的高效性，通常具有对数时间复杂度。

使用场景：

• 等值搜索（WHERE column = value）
• 范围查询（WHERE column BETWEEN value1 AND value2 或 WHERE column > value）
• 排序（ORDER BY column）
• 查找最小值或最大值（ORDER BY column LIMIT 1）
• 唯一约束和主键（隐式使用 B-Tree）

示例：

考虑一个包含数百万条记录的 users 表。使用 B-Tree 索引 email 列将显著加快通过电子邮件地址查找特定用户的速度。

CREATE INDEX idx_users_email ON users (email);
-- 现在，像这样的查询会快得多：
SELECT * FROM users WHERE email = '[email protected]';

提示： B-Tree 索引通常是一个很好的起点，并且对于许多常见的数据库操作来说通常已经足够。但是，对于全文搜索或地理空间数据等特定用例，其他索引类型可能性能更佳。

2. GIN（广义倒排索引）索引

GIN 索引专为索引复合值或包含多个项的值而设计，例如数组、JSON 文档或全文搜索文档（tsvector）。它们对于搜索这些复合值中是否存在特定元素的查询特别有效。

工作原理： GIN 索引将复合值中的每个元素映射到包含该元素的行列表。它是一个倒排索引，意味着它索引的是值本身，而不是直接索引行。这使得它能够高效地检查某个特定项是否存在于更大的结构中。

使用场景：

• 全文搜索（tsvector vs. tsquery）
• 索引数组（ANY、@> 运算符）
• 索引 JSONB 数据（?、?|、?&、@>、<@ 运算符）

示例：

假设你有一个 documents 表，其中包含一个字符串数组类型的 tags 列。你想找到所有标记为 'database' 的文档。

CREATE INDEX idx_documents_tags ON documents USING GIN (tags);
-- 查询带有 'database' 标签的文档：
SELECT * FROM documents WHERE tags @> ARRAY['database'];
-- 或者用于 JSONB：
CREATE TABLE products (id SERIAL PRIMARY KEY, details JSONB);
CREATE INDEX idx_products_details ON products USING GIN (details);
SELECT * FROM products WHERE details ? 'manufacturer';

注意： GIN 索引的更新速度可能比 B-Tree 索引慢，因为它们需要重新索引每个元素。但是，对于涉及复合类型内元素的搜索，它们提供了卓越的查询性能。

3. GiST（广义搜索树）索引

GiST 索引是一个框架，允许创建自定义索引类型。它们通常用于索引几何数据类型和全文搜索。当数据复杂且不适合 B-Tree 结构时，GiST 索引特别有用。

工作原理： GiST 是一种高度灵活的索引方法。它通过递归划分数据空间来工作。虽然内部结构可能因使用的特定运算符类而异，但它通常以树状结构组织数据。

使用场景：

• 几何数据类型（点、线、多边形）用于空间查询（&&、@>）。
• 范围索引。
• 使用 GiST 运算符类进行全文搜索。

示例：

对于空间索引，假设有一个兴趣点（POI）表，你想找到某个地理区域内的所有 POI。

CREATE TABLE pois (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOMETRY(Point, 4326) -- 使用 PostGIS 扩展
);

-- 在 location 列上创建 GiST 索引
CREATE INDEX idx_pois_location ON pois USING GIST (location);

-- 查找边界框内的 POI（使用 PostGIS 函数的示例）
SELECT * FROM pois WHERE ST_Intersects(location, ST_MakeEnvelope(lon1, lat1, lon2, lat2, 4326));

提示： GiST 索引对于复杂数据类型和空间查询非常强大。它们也可以用于部分索引，仅索引基于条件的行子集，这可以进一步优化性能。

4. BRIN（块范围索引）索引

BRIN 索引专为非常大的表设计，这些表的数据与其在磁盘上的物理存储位置具有自然的相关性。它们通过索引物理块地址的范围而不是单个行的值来工作。这使得它们非常小且创建速度快，但仅当索引列的值与其物理顺序相关时才有效。

工作原理： BRIN 索引存储表块范围的最小值和最大值。查询时，PostgreSQL 检查块范围的最小/最大值。如果查询条件落在此范围之外，则跳过整个块范围，从而避免全表扫描。这对于自然排序的数据（如时间戳或序列 ID）最有效。

使用场景：

• 非常大的表。
• 与其物理存储顺序有很强自然相关性的列（例如，created_at 时间戳、自增 ID）。
• 当块中的值范围明显小于该块中的行数时。

示例：

考虑一个包含数十亿条条目的日志表，按 timestamp 排序。

CREATE TABLE logs (
    id BIGSERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 在 created_at 上创建 BRIN 索引
CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at);

-- 查询特定日期的日志：
SELECT * FROM logs WHERE created_at >= '2023-10-26 00:00:00' AND created_at < '2023-10-27 00:00:00';

警告： BRIN 索引仅在数据物理排序时才有效。如果数据以随机顺序插入，或者列值与其物理位置不相关，BRIN 索引将不会提供显著的性能提升，甚至可能降低性能。可以调整 pages_per_range 参数以优化 BRIN 索引效率。

5. SP-GiST（空间分区广义搜索树）索引

SP-GiST 是另一种广义搜索树，类似于 GiST，但针对以不平衡方式划分空间的算法进行了优化。它特别适用于索引非均匀数据分布和复杂的空间数据结构，如四叉树或 k-d 树。

工作原理： SP-GiST 使用多种分区策略，使其能够适应不同的数据类型和查询模式。对于某些类型的数据，尤其是在处理高度聚集或稀疏分布的数据集时，它可能比 GiST 更高效。

使用场景：

• 使用 k-d 树或四叉树的点数据。
• 网络数据。
• 地理空间数据。
• 文本搜索。

示例：

虽然通常用于复杂的几何结构，但一个常见的用例涉及索引大量的点。

-- 假设有一个包含点坐标的表
CREATE TABLE points (id SERIAL PRIMARY KEY, coord POINT);

-- 创建 SP-GiST 索引
CREATE INDEX idx_points_coord ON points USING SPGIST (coord);

-- 查询某个区域内的点
SELECT * FROM points WHERE coord <@ box '((x1,y1),(x2,y2))';

考虑因素： SP-GiST 索引可以为特定的数据结构和查询模式提供性能优势，而传统的 B-Tree 甚至 GiST 可能难以胜任。然而，它们的复杂性意味着它们通常不是首选，除非特定的基准测试表明有好处。

其他索引类型（简要介绍）

• 哈希索引： 仅支持等值比较（=）。在现代 PostgreSQL 版本中，它们会被记录到 WAL 日志中，但 B-tree 索引仍然是通常的首选，因为它们支持更多运算符和排序。
• 部分索引： 这些索引仅索引满足 WHERE 子句的表行子集。如果查询经常针对特定的数据子集，它们可以节省空间并提高性能。
• 表达式索引： 您可以基于一个或多个列的表达式或函数创建索引。这对于在 WHERE 子句中频繁使用这些表达式的查询非常有用，例如 lower(email)。

何时使用哪种索引类型？

选择正确的索引是 PostgreSQL 性能调优的关键部分。以下是一个快速指南，可帮助您做出决定：

索引类型	最适合用于	支持的运算符	注意事项
B-Tree	通用目的、等值、范围、排序	=, <, >, <=, >=	默认、通用、良好的全能型。
GIN	全文搜索、数组、JSONB、复合类型	@@, @>, <@, ?, `?	, ?&`
GiST	空间数据、几何类型、全文搜索	&&, @>, <@, @@（以及通过运算符类的其他运算符）	灵活，适用于复杂数据结构，可能比 B-Tree 慢。
BRIN	具有物理相关数据的非常大的表	<, >, <=, >=, =	体积小，创建快，仅对有序数据相关有效。
SP-GiST	非均匀数据、复杂空间结构	因运算符类而异（例如，空间、网络）	对于某些分区策略高效，调整起来可能更复杂。

需要考虑的因素：

1. 查询模式： 你最常运行哪种类型的查询？是等值检查、范围扫描、全文搜索还是空间查询？
2. 数据类型： 被索引的数据类型（例如，字符串、数字、数组、JSON、几何点）在很大程度上影响最佳索引选择。
3. 数据分布： 你的数据是自然排序的（如时间戳）还是随机分布的？
4. 更新频率： 索引列中的数据多久更新一次？GIN 和 GiST 索引的更新速度可能比 B-Tree 慢。
5. 表大小： 对于非常大的表，如果存在数据相关性，BRIN 索引可能具有优势。
6. 索引大小和维护： 考虑索引所需的磁盘空间以及维护它的开销。

创建和管理索引

PostgreSQL 提供了用于管理索引的简单 SQL 命令：

• 创建索引：

  CREATE INDEX index_name ON table_name USING index_type (column_name [ASC|DESC] [NULLS FIRST|LAST], ...);
  

• 删除索引：

  DROP INDEX index_name;
  

• 查看现有索引：

  \d+ table_name;
  

最佳实践： 在将更改应用到生产环境之前，始终在暂存环境中测试创建或更改索引的性能影响。使用 EXPLAIN ANALYZE 来了解你的查询如何使用索引。

要点

选择与你的运算符和数据形状匹配的索引，然后使用 EXPLAIN ANALYZE 进行验证。索引也属于写入路径的一部分，因此保留那些服务于实际查询的索引，并移除那些只会增加维护成本的索引。