PostgreSQL - 索引

PostgreSQL - 索引

索引是至关重要的数据库对象，可以显著加快数据检索速度。索引的作用类似于书本的目录：数据库可以利用索引快速找到相关数据的确切位置，而不是扫描整本书（表）来查找某个主题。

尽管索引可以显著加速带有 WHERE 子句和 JOIN 操作的 SELECT 查询，但它们也有成本。对索引列的每一次 INSERT、UPDATE 或 DELETE 操作都需要对索引进行相应的更新，这会增加少量开销。关键在于策略性地创建索引。

创建索引：CREATE INDEX 命令

CREATE INDEX 语句用于创建新索引。您必须为索引提供一个名称，并指定它所应用于的表和列。

CREATE INDEX index_name ON table_name (column_name, ...);

索引类型

PostgreSQL 提供了多种索引类型，每种类型都使用不同的算法，适用于特定的查询模式。默认且最常见的类型是 B-Tree。

• B-Tree：默认类型。非常适用于可排序数据上的相等查询（=）和范围查询（<、>、BETWEEN）。它是通用的主力索引。
• Hash：仅适用于简单的相等比较（=）。它们不记录 WAL（预写日志），并且在崩溃后可能需要手动重建。通常，B-Tree 更受青睐。
• GiST (Generalized Search Tree)：用于索引复杂数据类型，例如几何数据（查找重叠形状）和全文搜索。
• SP-GiST (Space-Partitioned GiST)：GiST 的增强版，用于特定的非平衡数据结构，如四叉树和 k-d 树。
• GIN (Generalized Inverted Index)：非常适合索引复合值，其中元素可以多次出现，例如用于全文搜索的 tsvector 中的单词或 JSONB 数组中的元素。
• BRIN (Block Range Index)：最适用于数据值与物理位置具有自然关联的超大型表（例如，日志表中的时间戳列）。它们非常小巧且高效。

常见索引策略

单列索引和多列（复合）索引

对 WHERE 子句中频繁使用的列创建索引。如果您经常按单个列进行筛选，则单列索引就足够了。如果您同时按两个或更多列进行筛选，则复合索引会更有效得多。

-- 单列索引
CREATE INDEX idx_products_price ON products (price);

-- 用于按类别和状态同时筛选的复合索引
CREATE INDEX idx_products_category_status ON products (category_id, status);

唯一索引

唯一索引通过确保在索引列中没有两行具有相同的值来强制执行数据完整性。PRIMARY KEY 主键约束会自动创建一个唯一索引。

-- 确保每个用户都有唯一的电子邮件地址
CREATE UNIQUE INDEX idx_users_email_unique ON users (LOWER(email));

部分索引

部分索引仅覆盖表中由 WHERE 子句定义的一小部分行。这对于在频繁查询的数据子集上创建更小的索引非常高效。

-- 仅为支付处理系统中的活跃、未付款订单创建索引
CREATE INDEX idx_orders_pending_payment ON orders (customer_id)
WHERE status = 'pending' AND is_paid = false;

表达式索引

您可以对函数或表达式的结果进行索引。这对于在 WHERE 子句中使用函数的查询（例如不区分大小写的搜索）非常强大。

-- 加速对用户名进行不区分大小写的搜索
-- 如果没有此索引，`WHERE LOWER(username) = 'admin'` 会很慢。
CREATE INDEX idx_users_username_lower ON users (LOWER(username));

覆盖索引 (INCLUDE 子句)

覆盖索引包含额外的非键列。这允许“仅索引扫描”，即 PostgreSQL 可以仅使用索引来回答查询，而无需访问表本身，从而显著提高性能。

-- 此查询仅使用索引即可回答
-- SELECT email, last_login FROM users WHERE user_id = 123;
CREATE INDEX idx_users_user_id_cover_email ON users (user_id) INCLUDE (email, last_login);

使用 EXPLAIN 验证索引使用情况

如何知道您的索引是否正在被使用？EXPLAIN 命令显示查询执行计划。EXPLAIN ANALYZE 执行查询并显示计划以及实际时间。

-- 在创建索引之前，您可能会看到 'Seq Scan'（顺序扫描）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 500000;

-- >> QUERY PLAN: Seq Scan on users (cost=0.00..18334.00 rows=1 width=80) (actual time=25.4..52.1 ms)

-- 创建索引
CREATE INDEX idx_users_id ON users (id);

-- 创建索引后，计划应显示 'Index Scan'（索引扫描）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 500000;

-- >> QUERY PLAN: Index Scan using idx_users_id on users (cost=0.43..8.45 rows=1 width=80) (actual time=0.04..0.05 ms)

管理索引

删除索引很简单，但应谨慎操作，因为它会严重降低查询性能。

DROP INDEX IF EXISTS idx_products_price;

您可以使用 psql 命令 \di 列出当前数据库中的所有索引。

何时避免或重新考虑索引

• 在小表上：索引扫描的开销可能大于简单的顺序扫描。
• 在写入操作繁重的表上：如果一个表有非常频繁的 INSERT/UPDATE 操作但读取不频繁，维护索引的成本可能大于其带来的好处。
• 在基数低的列上：对布尔类型或性别列进行索引通常无效，因为查询优化器无论如何都可能选择表扫描。
• 冗余索引：避免创建已被其他现有索引覆盖的索引。例如，如果您已经有一个针对 (a, b) 的索引，则不需要再为 (a) 创建单独的索引。