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' 也无法从字符串开头确定搜索范围。

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

目录与阅读设置

阅读设置

17

记录一个想法

理解 B-tree 索引

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

登录哪都通