PostgreSQL / 系统课时

B-tree 与复合索引

B-tree 与复合索引

这节课解决一个常见问题:同样是 B-tree 索引,为什么有的查询能快速定位,有的却只能扫描大量索引项甚至放弃索引。你将用订单表观察范围扫描、排序利用、复合索引列顺序,以及“最左匹配”在 PostgreSQL 中真正意味着什么。

学习目标

  • 理解 B-tree 如何把等值、范围和排序条件转换为连续索引扫描
  • 能根据高频查询设计复合索引的列顺序
  • 能用 EXPLAIN 判断索引是否缩小扫描范围或省掉排序

B-tree 为什么适合范围与排序

PostgreSQL 的 B-tree 按索引键保持有序,因此既适合 =,也适合 <、<=、>、>=、BETWEEN 这类范围条件。扫描器可以先定位起点,再沿叶子项顺序前进,而不是逐行检查整张表。

复合索引按第一列、第二列、第三列依次进行字典序排列。例如索引 (customer_id, created_at) 会先把相同 customer_id 的记录排列在一起,再在每个客户分组内部按 created_at 排序。

复合 B-tree 的列顺序与扫描范围

用执行计划验证复合索引

下面创建可重复实验数据,并建立 (customer_id, created_at) 复合索引。ANALYZE 用于让优化器获得数据分布统计。

sql
DROP TABLE IF EXISTS demo_orders;

CREATE TABLE demo_orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL,
    created_at timestamp NOT NULL,
    amount numeric(10,2) NOT NULL
);

INSERT INTO demo_orders (customer_id, created_at, amount)
SELECT
    (g % 200) + 1,
    timestamp '2026-01-01 00:00:00' + g * interval '1 minute',
    ((g % 5000) + 100)::numeric / 100
FROM generate_series(1, 20000) AS g;

CREATE INDEX idx_demo_orders_customer_created
ON demo_orders (customer_id, created_at);

ANALYZE demo_orders;

EXPLAIN (COSTS OFF)
SELECT id, created_at, amount
FROM demo_orders
WHERE customer_id = 42
  AND created_at >= timestamp '2026-01-05 00:00:00'
  AND created_at < timestamp '2026-01-10 00:00:00'
ORDER BY created_at;

预期结果

text
Index Scan using idx_demo_orders_customer_created on demo_orders
  Index Cond: ((customer_id = 42) AND (created_at >= '2026-01-05 00:00:00'::timestamp without time zone) AND (created_at < '2026-01-10 00:00:00'::timestamp without time zone))

具体成本和条件显示顺序可能随 PostgreSQL 版本、统计信息而变化。关键是能看到复合索引承担过滤和有序扫描,并且通常不再需要单独的 Sort 节点。

列顺序决定扫描区间

对多列 B-tree,最容易缩小扫描范围的模式通常是:前导列先使用等值条件,随后第一列非等值条件形成范围边界。以 (customer_id, created_at) 为例,customer_id = 42 先把搜索限制到一个客户分组,时间条件再把该分组缩成连续区间。

如果查询只有 created_at 条件,就无法把整个索引简单缩成一个连续时间区间,因为不同客户各自拥有一段时间序列。PostgreSQL 在适合的数据分布和版本中还可能采用 skip scan 等优化,因此“缺少最左列就绝对不能使用索引”并不准确;是否有效仍应以执行计划和实际读取量判断。

排序方向与 LIMIT

B-tree 可以正向或反向扫描,因此普通升序索引也能服务反向读取。对于复合索引,还要考虑各列的排序组合;如果业务经常查询“某客户最近 20 条订单”,把客户作为前导等值列,再按时间排列,就能在定位客户后立即从最新记录开始读取。

sql
CREATE INDEX idx_demo_orders_customer_created_desc
ON demo_orders (customer_id, created_at DESC);

EXPLAIN (COSTS OFF)
SELECT id, created_at
FROM demo_orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

预期结果

text
Limit
  ->  Index Scan using idx_demo_orders_customer_created_desc on demo_orders
        Index Cond: (customer_id = 42)

优化器也可能选择前一个升序索引并执行反向扫描,这同样是合理计划。检查重点不是索引名称必须固定,而是查询能否避免全表扫描和大范围额外排序,并在取得 20 行后尽早停止。

什么时候需要重新设计索引

当查询长期以 created_at 为唯一过滤条件时,应考虑独立的时间索引,而不是期待 (customer_id, created_at) 覆盖所有访问模式。反过来,如果业务总是先按客户定位,再按时间过滤或排序,这个复合索引通常比两个互不相关的单列索引更直接。

复合索引也不是把常用列全部塞进去。增加列会提高索引体积、写入维护成本和缓存压力。最终应使用真实查询执行 EXPLAIN (ANALYZE, BUFFERS),比较扫描行数、读取数据块和排序节点,再决定哪些复合索引值得长期保留。

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

目录与阅读设置

阅读设置

17

记录一个想法

B-tree 与复合索引

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

登录哪都通