简介

慢查询是后端系统在负载下最明显的症状。在 PostgreSQL 中,扫描数百万行的查询与精确取出十条记录的查询之间的差异,通常归结于一件事:合适的索引。本课将带你了解后端工作中最常用的三种索引类型——B-tree、组合索引和部分索引——并展示如何阅读 EXPLAIN ANALYZE 计划,从而判断你需要哪一种索引。

B-tree 索引:默认的主力

当你直接写 CREATE INDEX 时,PostgreSQL 创建的就是 B-tree 索引。它会保持列值有序,因此等值查找（=）和范围查找（>,<, BETWEEN）都非常快速。当某列经常出现在 WHERE 子句或 JOIN 条件中时,就应当使用它。

CREATE INDEX idx_users_email ON users (email);

SELECT * FROM users WHERE email = 'alice@example.com';

如果没有索引,PostgreSQL 会执行顺序扫描——逐行读取。有了索引,它就能直接跳转到匹配的记录。在一张拥有千万行的表上,这两者的差距大约是 200ms 与 2ms 之间的区别。

组合索引:列顺序至关重要

当查询按多个列进行过滤或排序时,组合索引可以同时满足两种需求。其中关键的细节是列的顺序:只有当索引的前导列被约束时,索引才能被高效使用。

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

此处 (user_id, created_at) 上的索引让 PostgreSQL 先按 user_id 过滤,且此时行已经按 created_at 排好序——无需额外的排序步骤。一个常见的错误是把列的顺序弄反。(created_at, user_id) 上的索引无法帮助此查询,因为 user_id 不是前导列。

部分索引：只索引真正重要的内容

部分索引在索引本身中添加一个 WHERE 子句，因此它只存储匹配特定条件的行。当你的大多数查询只关心表的某个子集时，这样可以保持索引小巧且快速。

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

如果 99% 的订单已经完成，只有 1% 是待处理状态，那么对 status 建立完整索引就是浪费。部分索引的体积可能小 1000 倍，但对于真正重要的查询来说同样有效。

阅读 EXPLAIN ANALYZE

EXPLAIN ANALYZE 会执行查询并报告 PostgreSQL 实际选择的执行计划，包含真实的耗时和行数。需要关注的几个最重要信号：

- 大表上出现 Sequential Scan → 你很可能需要建立索引。- 被 filter 过滤掉的行数远高于规划器估算的行数 → 统计信息已过时；请运行 ANALYZE table_name。- 大结果集上方出现 Sort 节点 → 考虑建立一个匹配 ORDER BY 的索引，或者更早地加上 LIMIT。

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

在输出中查找 Index Scan using idx_orders_user_date。如果看到的是 Seq Scan，说明该索引没有被使用——通常是因为表太小，规划器认为不值得使用索引，或者你的统计信息已过时。

常见陷阱

为每一列都建立索引比不建索引更糟糕：每个索引都会拖慢写入操作（INSERT、UPDATE、DELETE），因为 PostgreSQL 还必须同步更新索引。一个好的经验法则是：为那些经常出现在查询的 WHERE、ORDER BY 或 JOIN 中、且涉及大量行的列建立索引。

另一个常见错误是在 WHERE 子句中对索引列使用函数，这会导致索引无法被使用：

-- This WILL use an index on users.email
SELECT * FROM users WHERE email = 'alice@example.com';

-- This WILL NOT use that index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

如果需要不区分大小写的查询，可以存储一个规范化（normalized）的列，或者创建一个函数索引：CREATE INDEX ON users (lower(email))。

摘要

PostgreSQL 中的高效索引，关键在于将索引类型和列顺序与实际查询模式相匹配。B-tree 索引可处理大多数等值查询和范围查询。复合索引在过滤和排序场景中表现出色，前提是前导列已被条件约束。部分索引则可将索引范围缩小到仅包含真正需要的行。EXPLAIN ANALYZE 是你的反馈循环——在添加索引前后务必运行它，以确认执行计划确实发生了变化。