PostgreSQL B-Tree 索引深度解析：从原理到 pageinspect 实操
从 B-Tree 数据结构讲起，用 pageinspect 插件直接查看 PostgreSQL 索引的内部结构，理解索引如何工作、如何调优。适合想真正搞懂索引原理的开发者。
原文来源：Medium — Understanding B-Tree Indexes in PostgreSQL: A Comprehensive Guide – Part 1 — 深入解析 PostgreSQL B-Tree 索引的工作原理和内部实现。
大部分开发者都知道「加索引能加速查询」，但索引到底是怎么工作的？为什么 B-Tree 索引能加速几乎所有类型的查询？为什么有时候加了索引反而更慢？
这篇文章不讲空话，直接打开 PostgreSQL 索引的「黑箱」，用 pageinspect 插件一层层看进去。
PostgreSQL 有哪几种索引？
先来个全景。PostgreSQL 提供了 6 种索引算法，各有适用场景：
| 索引类型 | 适用场景 | 特点 |
|---|---|---|
| B-tree | 通用场景：等值查询、范围查询、排序 | 默认索引类型，支持 > < = BETWEEN IN LIKE（非前缀） |
| Hash | 等值查询（=） | 无法排序或范围查询 |
| GiST | 全文搜索、几何数据、模糊搜索 | 可扩展，支持自定义数据类型 |
| SP-GiST | 分区搜索（网络地址、四叉树） | 内存效率高于 GiST |
| GIN | 倒排索引（数组、JSONB、全文检索） | 适合组合值类型的搜索 |
| BRIN | 超大表、天然有序数据（时间戳等） | 极省空间，但需要数据物理上接近有序 |
如果不指定类型，PostgreSQL 默认创建的就是 B-tree。它是最通用的选择，没有之一。
—— 广告 ——
B-Tree 的逻辑结构
B-Tree 的全称是 Balanced Tree（平衡树）。它的核心设计是：
从根节点到任意叶子节点，路径长度完全相同。
听起来简单，但这意味着无论你要找的数据在树的哪个分支，查找时间都是稳定的 O(log N)。对于数据库这个「高频读写」场景来说，这一点极其重要。
B-Tree 的关键性质
- 每个节点可以有多个子节点（不像二叉树只能有两个），子节点数量范围是 2 到 M（M 为树的阶）
- 节点内按排序顺序组织：小值在左、大值在右
- 所有叶子节点在同一层，没有空子树
- 插入/删除后自动平衡，无需手动维护
这意味着即使表里有 1 亿行数据，B-Tree 的高度通常也只有 3-4 层。每次查找只需要 3-4 次节点访问，相比全表扫描的 1 亿次，差距是数量级的。
与全表扫描的复杂度对比
| 操作 | 全表扫描 | B-Tree 索引 |
|---|---|---|
| 查询（最坏情况） | O(N) | O(log N) |
| 范围查询 | O(N) | O(log N + K) |
| 排序 | O(N log N) | O(N)（索引已有序） |
PostgreSQL 的 B-Tree 实现基于 Lehman & Yao 算法（1981），这个算法被证明在并发环境下也能保持一致性。
PostgreSQL 的数据存储方式
在深入索引之前，得先搞清楚 PostgreSQL 是怎么存数据的。
表与文件
每个 PostgreSQL 表对应一个物理文件，文件名就是表的 OID（对象标识符）。对于小于 1GB 的表，只有一个文件；更大的表会切割成 1GB 的段。
用这个查询可以找到表对应的物理文件：
SELECT pg_relation_filepath('你的表名');
-- 输出类似: base/16384/16389页面结构
PostgreSQL 把每个文件分成固定大小的页面（默认 8KB）。每个页面从两头向中间增长：
- 从上往下：页面头 + Item 指针数组（指向每个行的偏移量）
- 从下往上：实际的行数据（Tuple）
- 特殊区域：页面最底部，索引结构专用（普通表不用的）
行的物理位置由 ctid 标识，格式是 (block_number, offset)。比如 (0, 1) 表示第 0 个块的第 1 个行。
这个 ctid 是 B-Tree 索引和表数据之间的桥梁：B-Tree 的叶子节点不存实际数据，只存键值和对应的 ctid。找到 ctid 后，PostgreSQL 直接跳到对应页面取数据。
B-Tree 索引的内部结构
创建一个 B-Tree 索引非常简单：
CREATE INDEX idx_client_id ON public.client (id);PostgreSQL 会在后台创建一个独立的索引文件，包含一棵完整的 B-Tree。
搜索过程演示
假设表中有 id 从 1 到 100 的数据，索引搜索 id=37 的过程：
- 根节点：存储了各个分支的边界值，比如 13 和 41。37 > 13，进入右分支
- 中间节点：存储了 31 和 41 的边界信息。37 在 31 和 41 之间，进入对应的子节点
- 叶子节点：找到 id=37 及其对应的
ctid（比如(3, 5)——第 3 页的第 5 行） - 取数据：直接跳到第 3 页偏移 5，取出完整行
如果没索引，PostgreSQL 需要从头到尾遍历所有页面，逐一检查每一行。
用 pageinspect 实操查看索引内部
光说不练假把式。PostgreSQL 提供了 pageinspect 插件，可以直接查看索引页面的内部数据。
准备环境
-- 创建测试表
CREATE TABLE IF NOT EXISTS public.client (
id int,
name varchar
);
-- 插入一条数据
INSERT INTO public.client VALUES (1, 'Row #1');
-- 创建索引
CREATE INDEX idx_client ON public.client (id);
-- 启用 pageinspect
CREATE EXTENSION IF NOT EXISTS pageinspect;查看索引元信息
SELECT * FROM bt_metap('idx_client');输出显示根页面是 1，且根页面同时也是叶子节点（只有一个数据时，根 = 叶）。
查看页面统计
SELECT * FROM bt_page_stats('idx_client', 1);关键字段：
type=L（Leaf，叶子节点）live_items= 1（当前 1 条存活数据）dead_items= 0（没有过期数据）
查看页面内的具体条目
SELECT * FROM bt_page_items('idx_client', 1);输出会显示每个索引条目指向的 ctid，以及键值内容。对于我们的 client 表，可以看到 id=1 的条目指向了具体的数据页面位置。
插入更多数据观察树的变化
INSERT INTO public.client VALUES
(2, 'Row #2'),
(3, 'Row #3'),
(5, 'Row #5'),
(7, 'Row #7');重新查看 bt_metap，当数据量增长到跨页面时，PostgreSQL 会自动分裂页面、提升树的高度。你会看到 fastroot 指向一个新的根页面，树从单层变成多层。
索引维护的最佳实践
清理膨胀的索引
索引在频繁的更新和删除操作后会产生碎片。Periodically 重建索引可以回收空间：
REINDEX INDEX idx_client;
-- 或者重建表的所有索引
REINDEX TABLE public.client;对于 PostgreSQL 12+，推荐用 REINDEX CONCURRENTLY（不会锁表）：
REINDEX INDEX CONCURRENTLY idx_client;监控索引使用情况
SELECT
schemaname,
tablename,
indexname,
idx_scan, -- 索引被扫描的次数
idx_tup_read, -- 通过索引读取的行数
idx_tup_fetch -- 实际取回的行数
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;如果某个索引的 idx_scan 接近 0，说明它几乎没被使用，可以考虑删掉。
复合索引的列顺序
复合索引 (a, b) 和 (b, a) 不是一回事。规则是：等值条件放前面，范围条件放后面。
-- 适用于 WHERE a = 1 AND b > 10
CREATE INDEX idx_a_b ON t (a, b);
-- 适用于 WHERE b = 10 AND a > 1
CREATE INDEX idx_b_a ON t (b, a);部分索引
如果你只关心某个条件下的查询，可以用部分索引节省大量空间：
-- 只索引活跃用户，不索引已删除的
CREATE INDEX idx_active_users ON users (email) WHERE status = 'active';这个索引只有 status = 'active' 的数据，体积小、维护成本低。
常见的索引误区
误区 1：索引越多越好
错。每个索引在 INSERT、UPDATE、DELETE 时都要同步维护。写密集的表上过多的索引会导致写入大幅降低。一个表 5 个索引通常就够了，超过 10 个要慎重。
误区 2：加索引一定能加速查询
如果查询要返回表中大部分数据（比如超过 5-10%），PostgreSQL 优化器会直接选择全表扫描，跳过索引。因为顺序读比随机读快得多。用小表、或者选择性差的列，索引反而拖后腿。
误区 3：LIKE '%xxx' 可以用 B-Tree 索引
B-Tree 只能加速 LIKE 'xxx%'（左前缀匹配）。LIKE '%xxx' 和 LIKE '%xxx%' 无法利用 B-Tree 索引。这种情况需要 pg_trgm 扩展或者换搜索引擎。
误区 4：索引能自动维护
索引不会自动回收空间。频繁更新的表，索引会越来越膨胀。建议设置定期维护窗口（比如每周末低峰期）执行 REINDEX。
总结
B-Tree 索引的原理可以用一句话概括：用有序的树结构，把全表扫描的 O(N) 降低到 O(log N)。看似简单，但这个平衡树的设计经过了 40 年的工程验证，至今仍是数据库索引的主心骨。
理解索引的原理不是为了应付面试，而是为了在生产中做出更好的决策：
- 加索引前先确认选择性够不够高
- 复合索引的列顺序要对
- 定期监控和重建索引
- 不要为了「万全」堆满索引
下一篇可以聊聊 GIN 和 GiST——当你需要全文搜索或者 JSONB 查询时，为什么 B-Tree 就不够用了。
© 2026 四月
原文链接：https://www.aprilzz.com/tutorials/postgres-btree-index