PostgreSQL 索引 - 光之星

PostgreSQL 的索引本质上是与数据表（Heap）并行的独立物理结构。它存储了字段值以及指向该值在表中物理位置的指针（即我们之前讨论过的 ctid）。

在 2026 年的数据库开发中，理解索引不只是为了性能，更是为了在 RAG 等 AI 场景中优化海量向量数据的检索速度。

1. 核心工作流程

当你执行 SELECT * FROM table WHERE id = 100 时：

查找索引：数据库先在索引树中搜索 100。
获取 ctid：索引返回该值对应的物理坐标，例如 (10, 5)。
回表（Heap Access）：根据 ctid 直接跳到磁盘第 10 页的第 5 行读取完整数据。
可见性检查：结合 MVCC 检查该 ctid 对应的版本对当前事务是否可见。

2. 常见的索引类型及其原理

A. B-Tree 索引（默认且最通用）

原理：基于平衡多叉树结构。数据有序存储，叶子节点保存 ctid。
特点：支持范围查询（>、<）、排序和等值查询。
适用场景：主键、日期、数值范围。

B. Hash 索引

原理：通过 Hash 函数将键映射到对应的桶中。
特点：仅支持等值查询（=），不支持范围查询。
2026 现状：随着磁盘 I/O 优化，B-Tree 已经很快，Hash 索引的使用频率在降低，但在某些超大并发等值场景下依然有效。

C. GIN (Generalized Inverted Index) 索引

原理：倒排索引。将复合项（如数组、JSONB、全文搜索的词）拆解，记录每个子项出现在哪些行。
适用场景：全文检索、JSONB 数据、数组。

D. GiST (Generalized Search Tree) 索引

原理：高度通用的平衡树结构，允许自定义如何平衡。
适用场景：地理空间数据（PostGIS）、范围类型、相似度搜索。

E. IVFFlat / HNSW (向量索引)

这是 RAG 系统 的核心。在安装了 pgvector 扩展后使用。

原理：
- IVFFlat：将向量聚类成桶，查询时只查最近的几个桶。
- HNSW：构建多层图结构，查询速度极快，是目前 2026 年处理向量检索的主流方案。

3. 索引的“代价”与 MVCC 的冲突

这是 PostgreSQL 中最值得注意的底层设计：

索引不带 MVCC 信息：索引里没有 xmin 和 xmax。
索引放大效应：由于 MVCC 的非原地更新，每次 UPDATE 产生新 ctid 时，所有索引都必须更新，指向新的 ctid。
解决手段：HOT (Heap Only Tuple)
如果更新后的数据能放在原数据页内，且索引列未改变，PostgreSQL 会通过旧行的隐藏指针指向新行，索引不需要更新，从而极大提升写入性能。

4. 索引优化实战建议

覆盖索引 (Index Only Scan)

通过将查询的列包含在索引中，让数据库不再“回表”查询。

CREATE INDEX idx_user_email ON users(email) INCLUDE (name);
-- 查询 name 和 email 时，直接在索引里拿数据，不看磁盘表文件

部分索引 (Partial Index)

只对经常查询的子集建立索引，减小索引体积并加速写入。

CREATE INDEX idx_active_orders ON orders(id) WHERE status = 'shipped';

联合索引的最左匹配原则

如果你建立了 INDEX(a, b, c)，查询 a 或 a, b 会走索引，但只查询 b, c 通常不会走索引（或者效率极低）。

如何判断索引是否生效？

永远使用 EXPLAIN ANALYZE：

EXPLAIN ANALYZE SELECT * FROM your_table WHERE your_column = 'value';

Index Scan：好，走了索引并回表。
Index Only Scan：极好，没回表。
Bitmap Index Scan：较好，将多个索引结果合并。
Seq Scan：全表扫描（注意，数据量极小时 Seq Scan 反而比索引快，优化器会自动选择）。

为何使用B-Tree

在数据库领域，B-Tree（及其变种 B+Tree）之所以能打败众多的数据结构（如二叉树、红黑树、Hash表），成为 PostgreSQL、MySQL 等主流数据库的默认索引结构，主要基于以下四个底层逻辑：

1. 降低磁盘 I/O：高度极低的“扁平化”设计

数据库的数据是存储在磁盘上的。磁盘 I/O 是数据库性能的最大瓶颈。

二叉树的问题：如果有 100 万行数据，二叉树的高度大约是 20 层（\(2^{20} \approx 10^6\)）。这意味着查找一行数据可能需要进行 20 次随机磁盘读取。
B-Tree 的优势：B-Tree 是一个“矮胖”的多叉树。在 PostgreSQL 中，一个索引页（通常 8KB）可以存放几百个键值。
- 即使有千万级甚至亿级数据，B-Tree 的高度通常也只有 3 到 4 层。
- 这意味着定位任何一行数据，只需要 3 到 4 次磁盘访问。

2. 局部性原理与预读（Page-Friendly）

数据库是以 Page（页） 为单位读写磁盘的。

B-Tree 的每个节点的大小通常被设计为与操作系统的文件页（或数据库页）大小一致。
当数据库读取一个 B-Tree 节点时，一次 I/O 就能获取大量的索引键值。这种设计极大提高了缓存命中率，利用了计算机系统的空间局部性。

3. 全能的查询支持（范围查询）

虽然 Hash 表在“等值查询”（WHERE id = 1）时速度是 \(O(1)\)，但它无法处理范围查询。

Hash 表：数据经过哈希计算后是乱序的。如果你查询 WHERE age > 18，Hash 表必须全表扫描。
B-Tree：数据在索引中是严格按顺序存储的。
- 范围查询：定位到左边界后，直接在叶子节点顺序遍历即可。
- 排序（ORDER BY）：索引本身就是有序的，可以直接利用索引消除内存排序操作。
- 模糊查询：支持前缀匹配，如 WHERE name LIKE 'Zhang%'。

4. 性能的稳定性（Worst-case Guarantee）

自平衡能力：B-Tree 在插入或删除数据时，会通过分裂（Split）或合并（Merge）来保持树的平衡。
可预测性：无论你查询的是表中第一行还是最后一行，所花费的 I/O 次数几乎是固定的。这种稳定性对于企业级应用的 SLA（服务等级协议）至关重要。

5. PostgreSQL 中的 B-Tree 进化

在 PostgreSQL 中，B-Tree 还引入了以下高级特性：

重复值优化：在 12 版本之后，B-Tree 会对重复的键值进行压缩存储，显著减小索引体积。
并发控制：PostgreSQL 使用 L&W 锁（Lightweight Locks） 和 B-Tree 页面分裂算法，保证了在多事务并发写入时，索引不会成为全局锁瓶颈。

总结：为何不选其他结构？

数据结构	缺点	为什么不选它做默认索引？
二叉搜索树	树太深	磁盘 I/O 次数太多。
红黑树/AVL树	内存结构	不适合大规模磁盘数据存储。
Hash 表	无序	无法进行范围查询、排序、前缀匹配。
LSM Tree	读放大	写性能极强但查询开销不稳定（常用于 NoSQL 如 Cassandra）。

一句话总结： B-Tree 是在 磁盘访问开销、内存利用率 以及 SQL 查询灵活性 之间取得的最完美平衡。

事实上，PostgreSQL 的 B-Tree 索引本质上就是 B+Tree 的一种变体。

在数据库理论中，原生的 B-Tree 在内部节点（非叶子节点）也存储数据；而 B+Tree 将所有数据都放在叶子节点，内部节点只存储索引键值。

PostgreSQL 官方文档虽然称其为“B-Tree”，但在实现上它采用了 Lehman & Yao 的 B-Tree 算法，这在结构特征上更符合我们通常所说的 B+Tree。

PostgreSQL 为什么选择这种“类 B+Tree”结构，而不是原生 B-Tree 的深层原因：

1. 内部节点更“纯粹”，搜索效率更高

在 B+Tree 结构中，内部节点不存储指向 Heap（表文件）的指针，只存储键值。

高扇出（Fan-out）：因为不存数据，一个索引页（8KB）可以容纳更多的键值。
更矮的树：由于每一层能指向的分支更多，树的高度被压得更低，从而减少了磁盘 I/O。

2. 叶子节点双向链表：范围扫描的“神技”

这是 B+Tree 相比原生 B-Tree 的核心优势。

B+Tree 的设计：所有的叶子节点通过指针连接在一起。当你查询 WHERE age BETWEEN 18 AND 30 时，数据库只需找到 18 所在的叶子节点，然后顺着链表水平向右读，直到遇到 30。
原生 B-Tree 的困境：数据分散在各个层级。范围查询需要频繁地在中序遍历中“上下爬升”，这会产生大量不必要的随机 I/O。

3. PostgreSQL 版本的特殊增强（nbtree）

虽然它具备 B+Tree 的外壳，但 Postgres 的 nbtree 实现有几个独门绝技：

A. 唯一性与版本处理

由于 MVCC 的存在，索引中可能存在多个指向不同版本（但键值相同）的记录。PostgreSQL 的 B-Tree 能够高效处理这些重复键值，并在索引层面进行一定的可见性预判。

B. 高并发下的“非阻塞”分裂（Lehman & Yao 算法）

在传统的 B+Tree 中，页面分裂（Split）可能需要复杂的锁定机制。PostgreSQL 使用了 High Key 和 Link Pointer：

当一个进程在查找时，如果页面刚好在分裂，它可以根据 High Key 发现自己要找的数据可能已经移到了右邻居页面，从而直接横向跳转，而不需要重新从根节点搜索。这极大地提高了并发写入性能。

C. 索引元组压缩 (Index Deduplication)

从 PostgreSQL 13 开始，如果索引中有大量重复值（例如省份、性别），B-Tree 会在内部将这些重复值合并，只存一个键值和一组 ctid 列表。这显著缩小了索引体积，提高了缓存命中率。

4. 总结：名谓之争

原生 B-Tree：适合在内存中查找单个元素。
B+Tree：专为磁盘设计，通过将数据下沉到叶子节点，最大化了 I/O 效率和范围扫描能力。

结论：PostgreSQL 没用“原生 B-Tree”，它用的是工业级增强版的 B+Tree，只是在命名上遵循了学术界早期的习惯，统一简称为 B-Tree。

在对比 MySQL（明确标注为 B+Tree）和 PostgreSQL 的差异时感到困惑，其实它们在索引底层逻辑上是非常接近的。

GIN (Generalized Inverted Index)，即通用倒排索引。如果说 B-Tree 是为“单一值”设计的（如数字、字符串），那么 GIN 就是为“多值容器”设计的（如数组、JSONB、全文检索）。

它的核心逻辑是：不在乎一行数据是什么，而在乎这行数据里包含了哪些“元素”。

1. GIN 的底层原理：倒排索引

传统的索引是“行 \(\rightarrow\) 属性”，而 GIN 是“属性值 \(\rightarrow\) 行 ID 列表”。

索引项 (Entry)：GIN 会将复合数据拆解。比如一个数组 {apple, orange, banana}，GIN 会生成三个索引项：apple、orange 和 banana。
发布列表 (Posting List)：每个索引项后面跟着一个 ctid 列表，记录了包含该元素的所有物理行。
B-Tree 结构：这些拆解后的索引项（Entry）本身是存储在一棵 B-Tree 里的，以便快速定位某个特定的键。

2. GIN 的内部设计

A. 压缩存储 (Posting Tree)

如果某个元素（比如“北京”这个词）在几百万行中都出现了，那么它的 ctid 列表会非常大。为了优化空间，GIN 会将这个庞大的列表转化成一棵独立的 Posting Tree（也是一种 B-Tree 结构），存储在索引页中。

B. 待处理列表 (Pending List)

由于 GIN 索引在插入时需要拆解数据并更新多个索引项，直接写入会非常慢。

机制：新数据先写入一个 Pending List（临时顺序存储）。
生效：当 Pending List 满了或者触发了 VACUUM 时，再批量合并到主索引中。
代价：查询时需要扫描主索引 + Pending List，如果 Pending List 过大，查询性能会下降。

3. 典型应用场景

① JSONB 数据检索

这是 GIN 最常用的场景。它可以让你高效查询 JSON 内部的某个键值对。

-- 创建 GIN 索引
CREATE INDEX idx_user_data ON users USING GIN (data);

-- 高效查询包含特定 key-value 的行
SELECT * FROM users WHERE data @> '{"city": "Shanghai"}';

② 全文检索 (Full Text Search)

将长文本拆分为分词（Tokens），搜索关键词时秒级返回。

CREATE INDEX idx_content_search ON articles USING GIN (to_tsvector('english', content));

③ 数组类型 (Arrays)

查询数组中是否包含某个元素。

SELECT * FROM products WHERE tags @> ARRAY['electronics'::text];

4. GIN vs. GiST：该选哪一个？

特性	GIN	GiST
查询速度	更快（直接定位 Posting List）。	较慢。
更新速度	极慢（需要维护多个 Entry）。	较快。
构建时间	较长。	较短。
适用场景	静态数据、读多写少、JSONB。	动态更新频繁、地理位置数据。

5. 性能调优建议

控制 Pending List：
可以通过参数 gin_pending_list_limit 调整缓存大小。对于写频繁的表，适当调大可以减少频繁的合并操作。
禁用 Fast Update：
如果你需要数据插入后立即获得最快的查询速度，可以关闭 fastupdate 模式，但这会显著增加 INSERT 的耗时。
使用 jsonb_path_ops：
在为 JSONB 创建索引时，如果你只使用 @> 操作符，使用 jsonb_path_ops 存储参数会比默认的 jsonb_ops 更小、更快。

总结： GIN 是 PostgreSQL 处理非结构化数据（JSONB、全文搜索）的“核武器”。它通过牺牲写入性能，换取了在海量复杂数据中极其强大的多维检索能力。

为了展示 GIN 索引的强大之处，我们以一个 “AI 知识库系统” 为例。在这个场景中，我们需要处理 JSONB 格式的复杂元数据和文本搜索。

1. 环境准备

创建一个包含 JSONB 元数据和标签数组的表。

CREATE TABLE ai_documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[],             -- 标签数组
    metadata JSONB,          -- 元数据（包含作者、版本、类别等）
    content_vector tsvector  -- 全文检索分词向量
);

2. 创建不同类型的 GIN 索引

GIN 索引不是万能的，针对不同的数据类型，我们需要指定不同的“操作符类（Operator Class）”。

A. 数组 GIN 索引

用于快速查找包含特定标签的文档。

CREATE INDEX idx_gin_tags ON ai_documents USING GIN (tags);

B. JSONB 默认 GIN 索引 (jsonb_ops)

支持对 JSON 内部任意键值对、嵌套路径的检索。

CREATE INDEX idx_gin_metadata ON ai_documents USING GIN (metadata);

C. JSONB 路径优化索引 (jsonb_path_ops)

如果你只使用 @>（包含）操作符，这个索引比上面的更小、性能更好。

CREATE INDEX idx_gin_metadata_path ON ai_documents USING GIN (metadata jsonb_path_ops);

D. 全文检索 GIN 索引

CREATE INDEX idx_gin_content ON ai_documents USING GIN (content_vector);

3. 数据插入

INSERT INTO ai_documents (title, tags, metadata, content_vector) VALUES
(
    'PostgreSQL MVCC Deep Dive', 
    ARRAY['database', 'postgresql', 'advanced'], 
    '{"author": "Gemini", "category": "Technical", "version": 1.5}',
    to_tsvector('english', 'PostgreSQL uses MVCC to provide high concurrency and performance.')
),
(
    'Generative AI Trends 2026', 
    ARRAY['ai', 'future', 'trends'], 
    '{"author": "Oracle", "category": "News", "hot": true}',
    to_tsvector('english', 'Generative AI is evolving rapidly in 2026 with new models.')
);

4. 完整查询示例及原理解析

场景一：查询包含特定标签的文档

-- 查询包含 'postgresql' 标签的所有行
SELECT title FROM ai_documents 
WHERE tags @> ARRAY['postgresql'::text];

原理：GIN 会在内部 B-Tree 中定位到 postgresql 这个条目，直接取出对应的 ctid 列表。

场景二：深层嵌套的 JSON 查询

-- 查询作者是 'Gemini' 且类别是 'Technical' 的文档
SELECT title FROM ai_documents 
WHERE metadata @> '{"author": "Gemini", "category": "Technical"}';

原理：GIN 将 JSON 拆解为多个键值对（Entry），查询时进行位图合并（Bitmap And）。

场景三：全文关键词搜索

-- 搜索包含 'concurrency' 单词的文档
SELECT title FROM ai_documents 
WHERE content_vector @@ to_tsquery('english', 'concurrency');

5. 性能验证与监控

使用 EXPLAIN ANALYZE 确认是否走了 Bitmap Index Scan。GIN 索引通常不会触发普通的 Index Scan，而是先生成位图再回表。

EXPLAIN ANALYZE 
SELECT * FROM ai_documents WHERE tags @> ARRAY['ai'::text];

预期输出关键行：

Bitmap Index Scan on idx_gin_tags
Index Cond: (tags @> '{ai}'::text[])

6. GIN 索引的维护要点

Pending List 调优：
GIN 写入很慢。PostgreSQL 先把新数据放进“缓冲区”（Pending List）。
```
-- 查看索引状态（需要安装 pgstattuple 扩展）
SELECT * FROM pgstatginindex('idx_gin_tags');
```
如果 pending_pages 太多，查询会变慢。可以手动触发清理：
```
VACUUM ai_documents; -- 这会强制合并 Pending List 到主索引
```
存储空间管理：
GIN 索引可能比数据本身还大。对于 JSONB，尽量只对经常查询的路径建立 表达式索引：
```
-- 只对作者字段建索引，不索引整个 JSON
CREATE INDEX idx_author ON ai_documents USING GIN ((metadata->'author'));
```

总结

GIN 是处理多对多关系（一个词对应多个文档，一个 JSON 对应多个键值对）的终极方案。

注意点：

优点：查询极快，支持复杂类型。
缺点：插入/更新极慢（由于要拆分多个 Entry），不适合频繁更新的字段。

发表于 2026-04-24 15:03 光之星阅读(51) 评论(0) 收藏举报