索引为什么能加速查询：从联合索引到 LIMIT 提前停止

上一篇文章从连接、解析、优化、执行和存储层串起了一条 SQL 的完整执行过程。其中有一个看似简单、实际上决定了大量查询性能的问题：为什么同一条 SQL，有时需要扫描和排序大量记录，有时却能沿着索引读取几条记录后立即返回？

答案不能只概括成“B-Tree 查找是 O(log N)”。一次索引扫描还可能访问许多叶子项和数据页，检查多版本并发控制（MVCC）下的可见性，回到表中取得完整记录，甚至因为返回范围过大而比顺序扫描更昂贵。索引真正改变的，是数据库取得结果时需要完成的工作：从哪里开始读、需要读多大范围、数据是否已经符合目标顺序，以及何时可以停止。

本文继续沿用订单查询作为唯一主线：

SELECT
  id,
  status,
  created_at
FROM orders
WHERE user_id = :user_id
ORDER BY created_at DESC
LIMIT 10;

假设 orders 有 100 万行，目标用户有 1000 张订单，而查询只需要最新 10 张。我们将依次观察四条访问路径：没有相关索引、只有 user_id 单列索引、能够提供顺序的联合索引，以及覆盖查询所需列的索引。

在本文使用的合成数据集中，created_at 没有重复值，因此四条路径会得到同样的 10 行；一般情况下，它们都满足同一查询语义，但如果排序键存在并列，具体返回的行可能不同。无论结果是否恰好相同，“返回 10 行”都不代表这些路径检查、排序和访问的数据量相同。理解这种差异，才是理解索引的起点。

1. LIMIT 10 为什么仍可能做大量工作

在没有相关索引时，PostgreSQL 可能选择下面的计划：

Limit
└── Sort(created_at DESC)
    └── Seq Scan on orders
        Filter: user_id = :user_id

计划需要从下向上理解。Seq Scan 按数据页扫描 orders，逐条判断 user_id 是否匹配。假设目标用户只有 1000 张订单，扫描节点仍可能检查表中的 100 万行，再把 1000 条匹配记录交给 Sort。

本文穿插引用同一组本地对照实验：PostgreSQL 18、100 万张订单、1000 位均匀分布的用户，每位用户约有 1000 张订单，并在当前会话关闭并行计划。下面的精确数字只是这组数据的一次观察结果，不是机制必然值；完整造数与复现步骤放在第 5 章。

这次实验得到的第一组关键输出如下：

Limit (actual rows=10 loops=1)
  -> Sort (actual rows=10 loops=1)
       Sort Key: created_at DESC
       Sort Method: top-N heapsort  Memory: 26kB
       -> Seq Scan on orders_demo (actual rows=1000 loops=1)
            Filter: (user_id = 42)
            Rows Removed by Filter: 999000
            Buffers: shared hit=7353

Buffers 统计的是块访问次数，不是去重后的页面数。这里的 shared hit 表示访问所需块时，它已经位于 PostgreSQL shared buffers 中；同一块被多次访问会重复计数。

这里最容易误读的是 Sort actual rows=10。它表示 Sort 最终只向上输出了 10 行，并不表示它只读取了 10 行。它的子节点输出了 1000 条候选记录，还丢弃了 999000 条不匹配记录。

1.1 Top-N Sort 减少保存量，不减少候选扫描

如果完整排序 1000 条候选记录，典型比较工作可以近似理解为 O(M log M)，其中 M 是候选数。由于这里只需要前 10 条，数据库可以使用 Top-N 算法，只维护当前时间最新的 10 条，把排序工作近似降低到 O(M log 10)，并显著减少排序内存。

但在检查最后一条候选之前，数据库无法确定它是否会进入最新 10 条。因此 Top-N Sort 仍要消费下层提供的全部候选：

Top-N Sort 减少需要保留的记录
        ≠
Top-N Sort 只需要读取 N 条记录

1.2 LIMIT 限制输出，不自动限制下层输入

Limit 只有在下层能够逐步产生正确顺序的结果时，才能在取得 10 行后停止。如果下层是 Sort，第一次请求结果时，Sort 往往已经需要消费大量甚至全部候选，才能确定哪一行应该排在第一位。

所以，没有合适访问路径时：

LIMIT 10
    ↓
只返回 10 行
    ↓
底层仍可能扫描整表并处理全部候选

要让 LIMIT 真正减少读取，数据库首先需要一条能够直接产生目标顺序的路径。

2. 联合索引如何形成有序访问路径

可以把普通索引项抽象为：

索引键
  +
定位实际记录所需的信息
  +
可选的附加数据

索引不仅建立“键值到记录位置”的映射，还按照索引类型定义的规则组织键值。对于常见 B-Tree 索引，这种顺序使数据库能够执行等值查找、范围扫描，并在顺序匹配时直接满足 ORDER BY。

数据库以页为单位组织表和索引。B-Tree 可以用下面的多层页结构理解：

根页
  ↓ 根据分隔键选择子页
中间页
  ↓ 继续缩小范围
叶子页
  ↓ 找到索引项或范围起点
相邻叶子项
  ↓ 沿键值顺序继续扫描

内部页能够保存许多分隔键和子页指针，因此分支因子远大于二叉树。即使索引包含大量记录，树通常也不会很深。不过，树高不能直接等同于磁盘 I/O 次数：根页和内部页可能已经缓存在内存，范围扫描可能跨越多个叶子页，取得完整记录还可能访问其他数据页。

因此，一次范围索引扫描的工作更接近：

定位范围起点
  +
扫描需要的叶子索引项
  +
取得实际记录
  +
检查可见性与剩余条件

“树查找是对数复杂度”只描述了第一部分。索引是否真正减少总工作量，还取决于后面的范围有多大、行怎样存储以及查询还需要做什么。

2.1 只有 user_id 索引：解决定位，没有解决排序

先建立一个单列索引：

CREATE INDEX idx_orders_user
ON orders(user_id);

它可以帮助数据库回答“哪些订单属于这个用户”，把候选范围从整张表缩小到目标用户的 1000 条订单。PostgreSQL 可能采用普通 Index Scan，也可能先收集行位置，再通过 Bitmap Heap Scan 按 Heap 页读取数据。

这里先说明即将出现的几个 PostgreSQL 术语。Heap 是保存表行版本的主体存储，普通索引使用 TID 定位其中的候选行版本；Bitmap Index Scan 先从索引收集候选 TID，Bitmap Heap Scan 再按照 Heap 页访问这些候选。它们是同一条 Bitmap 路径的两个步骤，不是两棵独立索引。

本地实验中，优化器选择了 Bitmap Scan：

Limit (actual rows=10 loops=1)
  -> Sort (actual rows=10 loops=1)
       Sort Key: created_at DESC
       Sort Method: top-N heapsort  Memory: 26kB
       -> Bitmap Heap Scan on orders_demo (actual rows=1000 loops=1)
            Recheck Cond: (user_id = 42)
            Heap Blocks: exact=1000
            -> Bitmap Index Scan on idx_orders_demo_user
                 Index Cond: (user_id = 42)
                 actual rows=1000 loops=1

这里先区分两个容易误读的字段：Heap Blocks: exact=1000 表示本次访问了 1000 个仍保留精确候选位置的 Heap 块，不是“返回了 1000 行”；Recheck Cond 标出 Bitmap Heap Scan 对应的索引条件，不能只凭它判断本次是否逐行重新检查了该条件。第 5 章会继续说明位图出现有损页项（lossy pages）时的情况。

这条路径不再检查其他用户的全部记录，却仍然需要读取目标用户的 1000 条候选并排序。原因是 (user_id) 索引没有声明同一个 user_id 下的记录应该按 created_at 排列。

Bitmap Scan 还会把索引得到的行位置按 Heap 页面组织，以减少分散访问。这样有利于批量读取数据页，却不会保留 B-Tree 原本可能提供的键值顺序。因此，两个单列索引即使可以通过 Bitmap 组合找出候选集合，也通常不能替代一条直接提供复合顺序的联合索引。

单列索引只解决了第一个问题：

哪些订单属于目标用户？       已解决
这些订单是否已经按时间排列？ 未解决
拿到 10 条后能否停止？       未解决

2.2 联合索引的关键是复合键顺序

现在建立联合索引：

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

联合索引不是两棵独立的单列索引。它为复合键建立一套整体顺序，可以近似理解为字典序比较：

先比较 user_id
  ↓
user_id 相同时，再比较 created_at
  ↓
created_at 使用 DESC 方向

索引项会形成类似下面的顺序：

(7,  2026-08-25 15:00)
(7,  2026-08-24 09:00)

(42, 2026-08-25 16:30)
(42, 2026-08-25 12:10)
(42, 2026-08-23 08:20)

(88, 2026-08-25 17:00)

同一用户的索引项首先形成连续范围，这个范围内部再按照创建时间从新到旧排列。因此对于 user_id = 42，数据库可以定位该范围的起点；随后首先遇到的是 created_at 最大的候选项。候选项通过可见性与剩余条件检查后，才会成为查询返回的最新订单。

相同两列，顺序相反并不等价。

如果索引改为：

CREATE INDEX idx_orders_created_user
ON orders(created_at DESC, user_id);

索引首先按照全站订单时间排列。目标用户的订单会散落在其他用户之间：

(17:00, user 88)
(16:30, user 42)
(16:29, user 7)
(16:28, user 88)
(12:10, user 42)

数据库可以从最新订单开始扫描并过滤 user_id = 42，但无法通过一次普通范围定位直接得到“用户 42 的连续时间区间”。为了凑齐 10 条结果，它可能跨过许多其他用户的索引项。

联合索引列顺序不是“把常用列都放进去”，而是在设计数据库如何划分搜索空间。

2.3 B-Tree 如何定位用户范围并沿叶子扫描

联合索引的树下降只负责找到范围起点。对于 user_id = 42，过程可以概括为：

根页：42 应该进入哪个大区间？
  ↓
中间页：目标范围位于哪个叶子页？
  ↓
叶子页：找到 user_id = 42 的第一条索引项
  ↓
沿相邻叶子项按 created_at DESC 继续扫描

这里真正带来收益的不是单独一次树下降，而是三件事同时发生：

1. 其他用户的索引范围被跳过，不再成为候选。
2. 目标范围本身已经符合查询要求的时间顺序。
3. 上层只需要 10 条；扫描在取得 10 条满足全部条件且对当前事务可见的记录后即可停止，因此通常不必读完整个用户范围。如果可见结果不足，或者大量候选被过滤，仍可能扫描完整范围。

可以把三条路径的主要工作粗略对比为：

无相关索引：扫描整表 T + 处理用户候选 M
单列索引：  树定位 + 扫描用户候选 M + 排序 M
联合索引：  树定位 + 扫描得到 K 条合格结果所需的索引项

其中 T 是表总行数，M 是目标用户候选数，K 是最终需要的 10 条。真实成本还包括页面访问、可见性检查和返回列读取，不能只用这一行公式代替执行计划。

2.4 索引匹配的工程边界

“最左前缀”更适合作为效率模型，而不是绝对禁令。对于多列 B-Tree，前导列上的等值条件，以及其后的第一个范围条件，通常决定能够缩小的连续扫描区间；更右侧的条件仍可能在索引内判断或帮助覆盖查询。

排序匹配也不要求覆盖完整索引键序。ORDER BY 可以匹配索引键序的一个前导前缀；如果更前面的键已经被等值条件固定，则可以从后续键开始匹配。匹配时还要考虑扫描方向，以及可空列的 NULLS FIRST、NULLS LAST 顺序。

PostgreSQL 18 新增了 B-Tree Skip Scan：当前导列缺少等值约束、其不同值较少且后续列约束足够有效时，优化器可能通过多次树搜索跳过无关范围。PostgreSQL 17 及更早版本不能假定具备这项能力。

因此，不应把规则简化成“缺少第一列条件，索引绝对不可使用”。更准确的理解是：

缺少对前导列的有效约束时，数据库通常不能通过一次普通树定位得到一个很小的连续范围，索引收益会依赖其他优化和实际数据分布。

PostgreSQL B-Tree 还可以反向扫描。当前查询已经通过等值条件固定 user_id，所以普通 (user_id, created_at) 通常也可以通过反向扫描满足 created_at DESC。显式写出 DESC 能更直接地表达目标顺序，但不能推导出“所有倒序查询都必须建立 DESC 索引”。

当多列需要混合方向时，索引方向才更加关键。例如 (x, y) 可以正向产生 ORDER BY x, y，反向产生 ORDER BY x DESC, y DESC，却不能直接产生 ORDER BY x ASC, y DESC。

3. Sort 消失后，LIMIT 如何提前停止

联合索引能够按目标顺序返回记录时，计划可以变为：

Limit
└── Index Scan using idx_orders_user_created
    Index Cond: user_id = :user_id

把本地实验中与早停直接相关的字段统一标注后，可以写成下面这样。estimated rows 是节点完整执行时的估算输出，并非 PostgreSQL 原始文本中的字段名称；统计信息来自抽样，因此这里保留约数：

Limit
  estimated rows: 10
  actual rows: 10 (loops=1)
  -> Index Scan using idx_orders_demo_user_created
       estimated rows: ≈1000
       actual rows: 10 (loops=1)
       Index Cond: (user_id = 42)
       Buffers: shared hit=10 read=3

这里的 shared read 表示块从数据文件读入 PostgreSQL shared buffers，但底层读取仍可能由操作系统页面缓存满足，不能直接等同于物理磁盘读取。

计划中不再有 Sort。联合索引的 Index Scan 在计划阶段估算完整执行会输出约 1000 行，本次却只向上输出了 10 行。结合上一篇介绍的需求拉取模型可以理解这一差距：索引扫描持续提供顺序正确的候选记录，Limit 取得第 10 条可见且符合条件的记录后不再请求下一行，扫描也就无须继续输出剩余用户范围。

与之相对，如果 Limit 的子节点是 Sort，Sort 通常必须先消费全部候选，才能返回第一条结果。真正决定能否早停的不是 SQL 中是否写了 LIMIT，而是下层路径能否按目标顺序持续产生合格记录。

3.1 返回 10 行，不保证只检查 10 个索引项

早停的准确表述应该是：

当下层能够按目标顺序逐步返回记录时，执行器可以在取得 10 条符合全部条件且对当前事务可见的记录后停止继续扫描。

下面几种情况都可能让数据库检查超过 10 个索引项。

额外条件只作为 Filter。

查询增加状态条件：

SELECT id, status, created_at
FROM orders
WHERE user_id = :user_id
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

如果索引仍是 (user_id, created_at DESC)，它能够定位用户范围并提供时间顺序，却不能通过键值范围只读取 paid。最近的候选可能依次是 cancelled、paid、shipped，数据库要继续扫描，直到找到第 10 条 paid 订单。

执行计划中可能出现：

Index Cond: user_id = :user_id
Filter: status = 'paid'
Rows Removed by Filter: ...

这里仍然可能早停，只是停止位置取决于取得 10 条合格结果需要跨过多少候选项。

MVCC 可见性与旧行版本。

PostgreSQL 索引找到的是候选行版本。普通 Index Scan 还要访问 Heap，根据当前事务快照判断该版本是否可见。不可见、已被替代或等待清理的候选项可能被跳过，扫描需要继续向后寻找下一条可见记录。

因此，Index Scan actual rows=10 表示节点最终向上输出了 10 行，不是底层只检查了 10 个物理对象。

前两种情况说明的是：为了产出 10 条合格结果，索引扫描本身可能需要检查更多候选项。深分页还会带来另一类边界：停止位置会随着偏移量不断后移。

3.2 OFFSET 仍会推迟停止位置

前面的四阶段主线查询没有 OFFSET。本节只是说明分页场景中的生产边界，不改变本文实验所验证的原查询。

ORDER BY created_at DESC
OFFSET 100000
LIMIT 10;

即使索引顺序完全匹配，执行器通常仍要取得并跳过前 100000 个结果。

分页边界：排序语义与索引顺序不是一回事。

深分页通常可以改用键集分页（Keyset Pagination），把“跳过多少行”转换成“从哪个复合键之后继续”：

SELECT id, status, created_at
FROM orders
WHERE user_id = :user_id
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 10;

由于 id 是主键，ORDER BY created_at DESC, id DESC 本身已经为并列时间规定了确定顺序；即使没有完全匹配的索引，数据库也必须通过额外排序等方式遵守这个语义。若希望 B-Tree 直接提供完整顺序，并让复合游标条件形成紧凑范围，可以使用 (user_id, created_at DESC, id DESC)。ORDER BY 中的 id 保证结果语义稳定，索引键中的 id 则让访问路径直接提供该顺序。

4. 索引路径的取行、覆盖与成本边界

索引叶子最终保存什么、怎样取得完整记录，取决于数据库的存储组织。“回表”是方便的统称，却不是所有产品都使用同一条路径。

4.1 PostgreSQL：索引通过 TID 指向 Heap 行版本

PostgreSQL 的表数据保存在 Heap 中，普通索引与 Heap 分开存储。B-Tree 叶子项包含索引键和指向 Heap 行版本的 TID，可以把普通 Index Scan 概括为：

B-Tree 索引项
    ↓ TID：数据块号 + ItemId 编号
Heap 数据页中的 tuple version
    ↓
MVCC 可见性检查与剩余过滤
    ↓
取得 id、status 等完整列

TID 由数据块号和页内 ItemId（line pointer）编号组成，不是 tuple 在页内的字节偏移，也不是稳定的业务行 ID。一次更新可能产生新的 tuple version，不同事务快照可能需要看到不同版本。

4.2 InnoDB：二级索引通过聚簇键访问聚簇索引

上一篇已经对比过 PostgreSQL Heap 与 InnoDB 聚簇索引，这里只保留当前查询需要的实现边界：本文示例显式以 id 为主键，因此 id 也是聚簇键，聚簇索引叶子保存完整行；二级索引 (user_id, created_at) 的记录则携带二级键与聚簇键 id。

二级索引 (user_id, created_at)
    ↓ 得到聚簇键 id
聚簇索引 (PRIMARY KEY id)
    ↓
包含 status 等列的完整记录

由于二级索引已经携带 id，当前查询缺少的是 status；二级索引未覆盖它时，InnoDB 通常仍要根据 id 访问聚簇索引。这条“二级键 → 聚簇键 → 完整行”路径，与 PostgreSQL 的“TID → Heap 行版本”不能混写成一种通用实现。

4.3 覆盖索引减少取行，但不保证零 Heap Fetch

为了让 PostgreSQL 索引包含当前查询所需的全部列，可以把 id 和 status 通过 INCLUDE (id, status) 保存为非键 payload；第 5 章的实验会给出完整建索引语句。

其中：

• user_id 用于定位目标范围；
• created_at DESC 提供原查询需要的时间顺序；
• id 和 status 是 payload，不参与树导航或键值排序；对于原查询，它们让返回值可以直接从索引取得。

从列可得性来看，这个索引也包含第 3.2 节中键集分页查询所需的全部列，因此分页查询同样具备使用 Index Only Scan 的物理条件。但 id 只是 INCLUDE payload，不能参与树导航、形成复合游标的紧凑扫描范围，也不能提供 ORDER BY created_at DESC, id DESC 所需的完整键序。如果业务希望索引直接满足这条分页访问路径，id DESC 应该成为第三个索引键；这是访问路径优化，不是保证 SQL 排序语义正确的前提。

当查询需要的值都能从索引得到时，Index Only Scan 在物理上成为可能。本地实验在创建覆盖索引并执行 VACUUM (ANALYZE) 后得到：

Limit (actual rows=10 loops=1)
  Buffers: shared hit=1 read=3
  -> Index Only Scan using idx_orders_demo_user_created_cover
       Index Cond: (user_id = 42)
       actual rows=10 loops=1
       Heap Fetches: 0
       Buffers: shared hit=1 read=3

但下面三个概念必须分开：

1. 覆盖查询：查询需要的值都在索引中。
2. Index Only Scan：优化器选择的计划节点。
3. Heap Fetches: 0：本次运行确实没有为可见性访问 Heap。

PostgreSQL 索引项本身没有完整的 MVCC 可见性信息。Index Only Scan 会检查对应 Heap 页在 Visibility Map 中是否标记为 all-visible：标记存在时可以跳过 Heap；标记不存在时仍要访问 Heap 判断可见性。因此，计划名叫 Index Only Scan，不代表 Heap Fetches 必然为零。

覆盖索引也不是免费的。更宽的索引会占用更多存储和缓存空间，每个页面能够容纳的索引项减少；订单状态经常从 pending 更新为 paid、shipped，把 status 放入索引还意味着每次状态变化都要维护这份索引数据。是否值得覆盖，要结合读频率、更新频率和实际 Heap Fetches 判断。

4.4 为什么存在索引，优化器仍可能选择顺序扫描

索引提供候选访问路径，不是强制执行指令。下面几种场景中，顺序扫描可能更便宜：

• 表很小，直接读取少量数据页比树导航更简单；
• 条件会返回表中很大比例的记录；
• 查询需要大量不在索引中的列，索引路径会访问许多分散的 Heap 页；
• 索引列顺序无法形成紧凑范围或满足目标排序；
• 统计信息陈旧，优化器错误估算了目标用户的订单数；
• 参数分布严重倾斜，普通用户和超级大客户适合不同计划；
• 额外 Filter 会丢弃大量索引候选项；
• effective_cache_size 等缓存命中假设、页面相关性、并行能力和成本参数改变了候选路径的相对代价。

这些统计信息、相关性、缓存命中假设、并行能力与成本参数会影响当前 SELECT 的成本估算；规划器并不感知此刻 shared buffers 或操作系统缓存中具体有哪些页面。它也不会因为某棵索引将来的写入和维护代价更高，就在当前这次 SELECT 的计划成本中给它额外惩罚。

4.5 索引的系统性代价属于设计决策

在执行计划之外，索引的读收益还要与整个生命周期的维护成本一起考虑：

• INSERT 需要写入每一棵相关索引；
• 修改索引键或 payload 需要维护索引项；
• 页面空间不足时可能分裂；
• 更多索引会增加 WAL、物理复制流量、物理备份体积和清理工作；
• 宽索引会挤占原本可以缓存表页或其他索引的内存。

因此，索引设计不是“这个字段是否常被查询”，而是：

这个高频查询是否值得拥有一条专门的有序访问路径，它节省的读取、过滤和排序工作，能否覆盖长期空间与写入成本？

5. 用 EXPLAIN 验证、反推并回顾

本文的四阶段实验使用 PostgreSQL 18、100 万条本地生成订单和 1000 位均匀分布的用户。为了让四条计划更便于横向比较，实验关闭了当前会话的并行计划。这项设置只用于减少并行执行对计划展示的干扰，不是生产调优建议。

下面的 SQL 会创建并写入一张 100 万行的普通表，只应在可以随时销毁的一次性本地测试库中执行，不能直接复制到生产数据库。这里不用临时表，是为了让示例中的 Buffers: shared ... 与本文实测口径一致；PostgreSQL 临时表对应的是 local 缓冲区统计。

SET max_parallel_workers_per_gather = 0;

DROP TABLE IF EXISTS orders_demo;

CREATE TABLE orders_demo (
  id bigint PRIMARY KEY,
  user_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO orders_demo (id, user_id, status, created_at)
SELECT
  value,
  (value % 1000) + 1,
  CASE value % 3
    WHEN 0 THEN 'paid'
    WHEN 1 THEN 'shipped'
    ELSE 'cancelled'
  END,
  timestamptz '2026-01-01 00:00:00+00'
    + value * interval '1 second'
FROM generate_series(1, 1000000) AS series(value);

ANALYZE orders_demo;

然后在四种索引状态下运行同一条查询：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders_demo
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

阶段 1 不创建额外索引。后续阶段按顺序一次只改变一种索引状态，使计划差异可以归因于当前索引；每个代码块都包含该阶段的索引变更和同一条 EXPLAIN。

-- 阶段 2：只有 user_id 单列索引
CREATE INDEX idx_orders_demo_user
ON orders_demo(user_id);
ANALYZE orders_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders_demo
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

阶段 2 记录完成后，移除单列索引并进入阶段 3：

DROP INDEX idx_orders_demo_user;
CREATE INDEX idx_orders_demo_user_created
ON orders_demo(user_id, created_at DESC);
ANALYZE orders_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders_demo
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

阶段 3 记录完成后，移除非覆盖联合索引并进入阶段 4：

DROP INDEX idx_orders_demo_user_created;
CREATE INDEX idx_orders_demo_user_created_cover
ON orders_demo(user_id, created_at DESC)
INCLUDE (id, status);
VACUUM (ANALYZE) orders_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, created_at
FROM orders_demo
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

四个阶段完成后，清理测试表并恢复会话设置：

DROP TABLE orders_demo;
RESET max_parallel_workers_per_gather;

本次运行得到的关键证据如下：

第 2.1 节已经区分了 Recheck Cond 与 Heap Blocks: exact。这里再补充一个运行时边界：如果位图因内存压力将部分精确 TID 项压缩为只记录页号的有损页项（lossy pages），数据库还必须重新检查这些页内的记录是否满足条件。

这些数字来自上述可复现的本地对照实验，不是生产基准。创建索引和重复查询会改变缓存状态，真实负载还受硬件、并发、数据倾斜、表膨胀与配置影响，因此不应只比较四次执行时间。

上一篇已经介绍过执行计划的通用阅读方法。对照这四组证据时，先用 Index Cond 确认哪些谓词参与索引定位，再把 estimated rows 当作规划器对节点完整执行时输出行数的估算，而不是逻辑候选范围的实测值。随后结合扫描节点的实际输出、Rows Removed by Filter、Heap Blocks 与 Buffers 判断实际工作量。Sort 是否消失，则反映索引是否直接提供目标顺序。

早停证据不是 estimated rows 与 actual rows 的差异本身。实验数据构造已经确定 user_id = 42 有 1000 条候选，第 3 阶段的计划又呈现无 Sort 的 Limit → Index Scan，而扫描节点最终只向上输出 10 行；三者结合，才说明上层 Limit 在取得结果后停止继续拉取。若缺少已知候选规模，estimated rows 与 actual rows 的差异也可能来自基数估算误差，不能单独证明早停。

取行成本需要继续区分普通 Index Scan、Index Only Scan 与运行时的 Heap Fetches，不能只凭节点名称下结论。

cost 是优化器比较候选方案的内部单位，不是毫秒。actual rows 是节点每次执行平均向上输出的行数；当 loops > 1 时，需要结合 actual rows × loops 估算总输出，本文四组实验均为 loops=1。它仍不等于节点检查过的所有底层对象。

前文已经区分了 shared hit 与 shared read。这里还需注意，两者统计的都是块访问次数，不是去重后的页面数；父节点的 Buffers 包含该节点自身及全部子节点的贡献，不能沿计划树逐层相加。比较访问工作时，应选择同一层级或完整扫描子树，并结合节点、行数与 Heap Blocks 判断工作范围。

5.1 从查询形状反推索引，而不是套用口诀

先回到全文始终验证的原查询：它按 user_id 定位、按 created_at DESC 排序，并返回 id、status 与 created_at。按职责推导出的 PostgreSQL 候选索引，就是实验阶段 4 已经建立的 (user_id, created_at DESC) INCLUDE (id, status)。

这只是针对当前高频查询的候选设计，不是所有订单查询的通用答案。正式采用前仍要验证：

• 实际查询是否总是按用户过滤；
• status 更新频率是否会放大索引维护；
• 目标用户数据是否严重倾斜；
• 计划是否自然选择该索引；
• Sort、扫描节点输出、页面访问证据和 Heap Fetches 是否按预期下降；
• 是否已有前缀重复或作用重叠的索引。

真正可复用的不是“等值列在前、排序列在后”这一句口诀，而是一套推导过程：先写清楚查询需要定位哪个范围，再写清楚范围内部需要什么顺序，最后判断返回列是否值得为覆盖查询付出额外成本。

5.2 常见误解与完整回顾

现在可以把整条因果链重新串起来：

查询条件匹配联合索引前导键
    ↓
目标记录形成较小的连续范围
    ↓
B-Tree 快速定位范围起点
    ↓
后续键顺序直接满足 ORDER BY
    ↓
Sort 消失
    ↓
Limit 得到足够的可见、合格记录后停止
    ↓
如果返回列被覆盖且可见性允许
还可以减少 Heap 或聚簇索引访问

索引加速查询，不只是因为它“查找更快”，而是因为它把查询需要的数据预先组织成了一条可定位、可按目标顺序读取、可提前停止的访问路径。路径与查询形状越匹配，数据库需要完成的无效扫描、过滤、排序和取行工作就越少；一旦范围、顺序或返回成本不再匹配，索引的优势也会减弱。

本文已经说明哪些因素会让索引路径失去优势；下一篇将继续解释优化器如何利用统计信息估算基数，并把行数、页面 I/O 与 CPU 转换成候选计划成本。

参考资料

• PostgreSQL 18 Documentation: Introduction to Indexes
• PostgreSQL 18 Documentation: B-Tree Indexes
• PostgreSQL 18 Documentation: Multicolumn Indexes
• PostgreSQL 18 Release Notes: B-Tree Skip Scan
• PostgreSQL 18 Documentation: Indexes and ORDER BY
• PostgreSQL 18 Documentation: Index-Only Scans and Covering Indexes
• PostgreSQL 18 Documentation: Combining Multiple Indexes
• PostgreSQL 18 Documentation: Using EXPLAIN
• PostgreSQL 18 Documentation: Database Page Layout
• PostgreSQL 18 Documentation: Visibility Map
• MySQL 8.4 Reference Manual: Clustered and Secondary Indexes
• MySQL 8.4 Reference Manual: How MySQL Uses Indexes
• MySQL 8.4 Reference Manual: LIMIT Query Optimization