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 排序。

用执行计划验证复合索引
下面创建可重复实验数据,并建立 (customer_id, created_at) 复合索引。ANALYZE 用于让优化器获得数据分布统计。
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;预期结果
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 条订单”,把客户作为前导等值列,再按时间排列,就能在定位客户后立即从最新记录开始读取。
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;预期结果
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),比较扫描行数、读取数据块和排序节点,再决定哪些复合索引值得长期保留。