PostgreSQL / 系统课时
理解 B-tree 索引
理解 B-tree 索引
本课解决“为什么普通索引能加速查询”的问题。你将理解 PostgreSQL 默认 B-tree 索引的有序结构,并能判断它是否适合等值、范围和排序查询。
学习目标
- 说明 B-tree 索引为什么适合比较运算
- 判断等值、范围和排序查询能否利用索引
- 识别不适合直接使用 B-tree 的查询模式
B-tree 的核心机制
PostgreSQL 执行 CREATE INDEX 时,默认创建 B-tree 索引。它会按索引键值维持有序结构,并通过树形节点逐层缩小搜索范围,而不是从表的第一行扫描到最后一行。
因为键值有序,B-tree 特别适合 =、<、<=、>、>=、BETWEEN 和前缀明确的排序。查询命中索引后,数据库先定位索引项,再根据索引项找到表中的实际行。
一个可验证的示例
下面创建订单表,并为创建时间建立索引。示例使用 SET enable_seqscan = off,只是为了稳定展示索引节点;真实环境不应长期关闭顺序扫描。
sql
-- 重建演示表,保证脚本可重复执行
DROP TABLE IF EXISTS demo_orders;
CREATE TABLE demo_orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(10, 2) NOT NULL,
created_at timestamp NOT NULL
);
-- 生成连续时间数据,便于观察范围扫描
INSERT INTO demo_orders (amount, created_at)
SELECT
(g % 1000) + 0.50,
timestamp '2026-01-01 00:00:00' + g * interval '1 minute'
FROM generate_series(1, 10000) AS g;
CREATE INDEX idx_demo_orders_created_at
ON demo_orders (created_at);
ANALYZE demo_orders;
-- 仅用于稳定展示索引节点,验证后应恢复默认设置
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id, amount, created_at
FROM demo_orders
WHERE created_at >= timestamp '2026-01-05 00:00:00'
AND created_at < timestamp '2026-01-06 00:00:00'
ORDER BY created_at;预期结果
text
执行计划的核心节点包含:
Index Scan using idx_demo_orders_created_at on demo_orders
Index Cond: created_at >= ... AND created_at < ...具体缩进和常量显示形式可能因 PostgreSQL 版本不同而略有差异,但应看到索引扫描以及对应的时间范围条件。
为什么排序也能受益
索引本身已经按 created_at 排序,因此查询的 ORDER BY created_at 可以直接沿索引顺序读取。在条件和排序方向匹配时,执行计划通常不再需要额外的 Sort 节点。
B-tree 也能反向扫描,所以升序索引通常可以支持 ORDER BY created_at DESC。只有当多列排序方向、空值顺序或表达式与索引定义不匹配时,才可能仍需排序。
不适合直接使用的情况
B-tree 不是所有查询的通用加速器。对索引列执行未建立表达式索引的函数、使用前导通配符查询,或一次返回表中大部分行时,优化器可能选择顺序扫描。
例如 WHERE lower(name) = 'alice' 与普通的 name 索引并不等价;WHERE name LIKE '%ice' 也无法从字符串开头确定搜索范围。
按自己的节奏,确认真正理解。