# 索引类型大全 · 六种索引怎么选

> PostgreSQL 内置 6 种索引——B-tree、Hash、GIN、GiST、SP-GiST、BRIN，各擅长不同的访问模式。再加上部分索引、表达式索引、覆盖索引这些「修饰」。选对索引类型，是让大表查得快的第一步；BRIN 在时序大表上能小到只有 B-tree 的几分之一。

- 作者：David（道雾轩）
- 专栏：PostgreSQL 特性详解（https://daiw.org/manual/postgresql.md）
- 最后更新：2026-10-01
- 原文：https://daiw.org/manual/postgresql/indexes
- 转载与引用：请注明出处并附原文链接（https://daiw.org/about/copyright）

# 索引类型大全 · 六种索引怎么选

大多数数据库，「索引」基本等于「B-tree」。PostgreSQL 不一样：它内置 **6 种**索引，每一种针对不同的数据形状与访问模式。选对类型，同样的查询可能快几十倍、索引还小几倍。这一章把六种索引 + 三种「索引修饰」讲清楚，并给出选择的判断标准。

## 六种索引，各管一摊

| 索引 | 擅长 | 一句话原理 |
| --- | --- | --- |
| **B-tree** | `=` `<` `>` `BETWEEN` `ORDER BY` | 默认；有序平衡树，范围与排序天然友好 |
| **Hash** | 只做 `=` 等值 | 哈希桶；PG 10 起写 WAL 后才生产可用 |
| **GIN** | 数组 / JSONB / 全文检索等**多值** | 倒排索引：值 → 含它的行 |
| **GiST** | 几何 / 范围 / 最近邻 / 自定义类型 | 可扩展的平衡树，存的是「谓词」 |
| **SP-GiST** | 非均匀分布（前缀树 / 四叉树） | 空间分区、非平衡结构 |
| **BRIN** | 物理顺序≈值顺序的**超大表** | 每个块区间只记 min/max，索引极小 |

`B-tree` 是默认，90% 的场景够用。真正拉开差距的，是知道**另外五种什么时候该上**。

## B-tree：默认，但别小看

有序、平衡、支持范围与排序——`=` `<` `>` `BETWEEN` `ORDER BY` `IS NULL` 全走它。它是 Lehman & Yao 那套高并发 B-tree 的工程实现，几乎所有主键、外键、常规查询列都靠它。你不指定索引类型时，建的就是 B-tree。

## GIN：多值字段的倒排索引

前面数组、JSONB、全文检索三章的加速，靠的都是 **GIN**。它是**倒排索引**：不为「整行」建一条目，而是为字段里的**每个元素 / 键 / 词元**建「元素 → 行列表」的映射。所以 `tags @> ARRAY['eng']`、`doc @> '...'`、`tsv @@ '...'` 这类「含某值」的查询，直接查元素对应的行列表，不必全表扫。**凡是一列里装了多个可检索单元（数组、JSONB、tsvector），就是 GIN 的地盘。**

## GiST / SP-GiST：空间、范围、最近邻

`GiST` 是一棵**可扩展的平衡树**，节点里存的是「谓词」（比如「这棵子树的所有几何图形都落在这个框里」）。它是 PostGIS 地理索引、范围类型 EXCLUDE 约束（见 [范围与生成列](https://daiw.org/manual/postgresql/range-generated)）、以及「最近邻」查询（`ORDER BY point <-> target LIMIT 5`，找最近的 5 个）的底座。

`SP-GiST`(Space-Partitioned GiST)是它的非平衡版，适合**天然分区、分布不均**的数据：前缀树（文本 / IP）、四叉树（点）、k-d 树。数据分布越不均匀，它相对 GiST 越有优势。

## BRIN：时序大表的「省钱利器」

`BRIN`(Block Range Index)是最该认识的一个「非主流」索引。它不为每一行建条目，而是把表按物理块分成一段段，**每段只记这段里的 min/max**。查 `WHERE ts BETWEEN ...` 时，它快速跳过「min/max 完全不沾边」的块区间。

它的杀手锏是**小**。看实测里同一张日志表上各索引的物理大小：

```text
600 kB  logs_payload_gin           (JSONB GIN)
216 kB  logs_level_time_covering   (覆盖索引)
128 kB  logs_occurred_btree        (时间列 B-tree)
 40 kB  logs_errors_only           (部分索引)
 24 kB  logs_occurred_brin         (时间列 BRIN)   ← 同一列,只有 B-tree 的 1/5
616 kB  logs                       (表本体)
```

同样是给 `occurred_at` 列建索引，**BRIN 只有 B-tree 的约 1/5**，数据量越大差距越夸张（几十 GB 的表可能是几百倍）。

<Callout type="info">
  BRIN 生效有个**前提**：物理存储顺序要和列值顺序**大致相关**。时间序列天然满足——日志按时间追加，磁盘上早的在前、晚的在后。这时 BRIN 用极小的体积换来「跳过大片无关块」的能力。但如果数据是乱序插入的（比如按随机 UUID），BRIN 就几乎没用——每个块的 min/max 都横跨全域，跳不掉任何块。**时序、单调追加的大表用 BRIN；乱序数据别用。**
</Callout>

## 三种「索引修饰」：同样的类型，更省更快

索引类型之外，还有几种正交的「修饰」，常常比换类型更立竿见影：

**① 部分索引(Partial)** —— 只索引满足条件的行：

```sql
-- 只给 error 日志建索引:99% 的 info 日志根本不进索引
CREATE INDEX logs_errors_only ON logs (occurred_at) WHERE level = 'error';
```

实测里它只有 **40 kB**（全量 B-tree 是 128 kB）。当你**总是只查一小部分行**（未处理的任务、错误日志、未删除的记录），部分索引又小又快，是性价比极高的一招。

**② 表达式索引(Expression)** —— 给「函数的结果」建索引：

```sql
CREATE INDEX logs_msg_lower ON logs (lower(message));
-- 于是 WHERE lower(message) = '...' 能走索引(大小写不敏感查询)
```

**③ 覆盖索引(Covering，`INCLUDE`)** —— 把查询要读的列一起塞进索引，实现**索引-only 扫描**（不用再回表）：

```sql
CREATE INDEX logs_covering ON logs (level, occurred_at) INCLUDE (user_id);
-- 查 level+时间排序、只要 user_id 时,全程读索引,不碰表
```

<Callout type="info">
  「回表」是性能的隐形杀手：普通索引扫描找到匹配行的位置后，还要去**堆表(heap)** 把整行捞出来读需要的列。覆盖索引把那几列直接放进索引页，于是「索引-only 扫描」全程不碰堆表，I/O 少一大截。代价是索引更大、写入更慢——用在读多、列少的热点查询上最划算。
</Callout>

实测最后验证了部分索引真的被选中：查「error 日志按时间倒序取 10 条」，planner 走的正是那个 40 kB 的 `logs_errors_only`——`Index Scan Backward using logs_errors_only`。

## 横向对比

| 能力 | PostgreSQL | MySQL(InnoDB) | Oracle | SQL Server |
| --- | --- | --- | --- | --- |
| B-tree | 有 | 有（主力） | 有 | 有 |
| 倒排（多值） | **GIN** | 有限（多值索引 8.0+） | 域索引 | 全文专用 |
| 空间 | GiST / SP-GiST | R-tree（空间） | R-tree | 空间索引 |
| 块范围 | **BRIN** | ❌ 无 | 有（存储索引，商业版特性） | Columnstore（不同思路） |
| 部分索引 | **有** | ❌ 无 | 有（基于函数变通） | **有**（筛选索引） |
| 表达式索引 | 有 | 8.0.13+ | 有 | 计算列 + 索引 |
| 覆盖索引 `INCLUDE` | 有 | 靠联合索引 | 有 | 有 |

PostgreSQL 的索引「工具箱」是这几家里最全的：**BRIN、部分索引、GIN 的多值倒排**是 MySQL 明显缺的三块，分别对应时序大表、稀疏查询、数组/JSON/全文——都是实打实能省一个量级资源的场景。

## 应用场景

- **时序 / 日志大表**：主查询列用 BRIN（省几十倍空间），高频精确过滤列再叠一个部分索引。
- **状态机 / 任务队列**：`WHERE status='pending'` 的部分索引，只索引待处理的少数行。
- **大小写 / 格式不敏感查询**：`lower(email)`、`(data->>'city')` 用表达式索引。
- **热点只读查询**：高频的 `SELECT a,b WHERE c=?` 用覆盖索引做索引-only 扫描。
- **数组 / JSONB / 全文**：一律 GIN。

## 小结与下一章

「加索引」不该只是无脑 `CREATE INDEX`。选对**类型**(B-tree / GIN / BRIN / GiST)匹配数据形状，再叠上**修饰**（部分 / 表达式 / 覆盖）贴合查询模式，才是让大表查得快又不浪费空间的正解。PostgreSQL 给的这套工具箱，比大多数数据库都全。

索引让「查得快」，但当单表大到几亿行，光靠索引也扛不住——这时要把大表**物理拆小**。下一章看声明式分区。👉 [表分区：把一张大表拆成很多小表](https://daiw.org/manual/postgresql/partitioning)。

## 参考文献

- [1] PostgreSQL 官方文档.「Chapter 11. Indexes.」https://www.postgresql.org/docs/current/indexes.html —— 索引类型、部分 / 表达式 / 覆盖索引、索引-only 扫描。
- [2] PostgreSQL 官方文档.「11.2. Index Types.」https://www.postgresql.org/docs/current/indexes-types.html —— 六种索引类型对照。
- [3] PostgreSQL 官方文档.「64.5. BRIN Indexes.」https://www.postgresql.org/docs/current/brin.html —— BRIN 的块范围原理与适用前提。