PostgreSQL / 系统课时

选择 B-tree 索引

选择 B-tree 索引

本课解决的核心问题是:面对等值、范围、排序以及多列过滤时,什么时候应该优先使用 PostgreSQL 的 B-tree。学完后,你可以从查询条件与排序方式判断默认索引是否真的适用。

学习目标

  • 判断 B-tree 对等值、范围和排序操作的适用边界
  • 理解多列 B-tree 的列顺序与查询条件之间的关系
  • 能用 EXPLAIN 验证索引是否被优化器采用

B-tree 为什么适合作为默认索引

B-tree 将索引键按有序结构组织,因此既能定位某个具体值,也能快速找到一个连续范围。常见的 =, <, <=, >, >= 以及 BETWEEN 查询,都属于它擅长的访问方式。

排序也是 B-tree 的重要能力。由于索引本身有序,查询在满足条件时可以直接按索引顺序输出,减少额外的排序工作。对于经常同时过滤和排序的查询,这一点尤其重要。

需要注意的是,B-tree 并不意味着“只要建了索引,所有查询都会变快”。优化器会比较索引扫描、顺序扫描等成本;当返回行数很多,顺序扫描反而可能更便宜。

从单列查询判断是否适用

先用一个最小实验验证等值和范围条件。下面的表同时准备离散值和时间范围,索引只建立在查询真正使用的列上。

sql
CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    created_at timestamptz NOT NULL,
    amount numeric(12,2) NOT NULL
);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

EXPLAIN (COSTS OFF)
SELECT id, amount
FROM orders
WHERE customer_id = 10001;

EXPLAIN (COSTS OFF)
SELECT id, amount
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-02-01 00:00:00+00';

预期结果

text
QUERY PLAN
Index Scan using idx_orders_customer_id on orders
  Index Cond: (customer_id = 10001)

QUERY PLAN
Index Scan using idx_orders_created_at on orders
  Index Cond: ((created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-02-01 00:00:00+00'::timestamp with time zone))

实际计划会受数据量、统计信息和成本参数影响,因此未必总是显示 Index Scan。这里真正需要验证的是:查询条件是否能形成有效的索引条件,以及索引扫描的成本是否低于顺序扫描。

多列 B-tree 先看列顺序

多列索引的关键不是“列越多越好”,而是列顺序是否符合主要查询模式。例如:

sql
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);

EXPLAIN (COSTS OFF)
SELECT id, amount
FROM orders
WHERE customer_id = 10001
  AND created_at >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-02-01 00:00:00+00'
ORDER BY created_at;

预期结果

text
QUERY PLAN
Index Scan using idx_orders_customer_created on orders
  Index Cond: ((customer_id = 10001) AND (created_at >= '2026-01-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-02-01 00:00:00+00'::timestamp with time zone))

对于 (customer_id, created_at),把 customer_id 放在前面意味着先缩小到某个客户,再在该范围内按 created_at 定位和读取。这样的组合尤其适合“等值过滤 + 范围过滤/排序”的查询。

如果查询只过滤第二列,例如只按 created_at 查询,不能简单假定这个复合索引等价于单独的 created_at 索引。现代 PostgreSQL 在部分场景下可能采用跳跃扫描等策略,但是否发生取决于版本、数据分布和优化器估算,因此设计索引时仍应以实际查询模式和 EXPLAIN 为准。

什么时候不该优先选择 B-tree

B-tree 的默认地位来自适用范围广,而不是任何场景都最优。遇到只需要哈希等值匹配的场景,可以比较 Hash;需要全文搜索、几何对象或其他特殊操作符时,则应根据操作符类和访问方式选择其他索引类型。

还要警惕低选择性条件。例如状态列只有两三个值时,即使存在 B-tree,也可能因为单次查询返回大量行而不值得走索引。索引设计最终应回到三个问题:查询条件是什么、希望利用什么顺序、优化器实际选择了什么计划。

使用 B-tree 时,最稳妥的判断方法不是背“默认索引万能”,而是先根据操作符与列顺序设计候选索引,再用 EXPLAIN 验证它是否真正改善了目标查询。

按自己的节奏,确认真正理解。

目录与阅读设置

记录一个想法

选择 B-tree 索引

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通