xingliuhuapgsql-24 索引原理与类型
1. 概述
索引是数据库性能优化的核心。PostgreSQL 相比 MySQL 最大的特色之一，就是拥有极其丰富的索引类型家族——除了通用的 B-tree，还有专为全文、JSON、数组、地理、大范围数据设计的专用索引。理解"什么场景用什么索引"是 PG 面试和调优的必备功底。
与 MySQL 的根本差异：MySQL(InnoDB) 的主键索引是聚簇索引（数据即索引，表数据按主键物理排序），二级索引叶子存主键值需"回表"；PostgreSQL 所有索引都是二级索引（非聚簇），索引项通过
ctid（物理位置）指向堆表中的行，不存在 InnoDB 那种聚簇结构。这是两者索引模型最本质的区别，后面详述。
2. PostgreSQL 六大索引类型总览
| 索引类型 | 底层结构 | 擅长的查询 | 典型场景 |
|---|---|---|---|
| B-tree | 平衡树 | = < > BETWEEN 排序 |
默认，绝大多数场景 |
| Hash | 哈希表 | 仅 = 等值 |
等值查询（较少用） |
| GIN | 倒排索引 | 包含/多值 @> ? |
JSONB、数组、全文搜索 |
| GiST | 通用平衡树 | 范围重叠、最近邻 | 几何/空间、范围类型、KNN |
| SP-GiST | 空间分区树 | 非平衡结构 | 四叉树、IP、电话号前缀 |
| BRIN | 块范围摘要 | 有序大表范围 | 时序/日志超大表 |
3. B-tree 索引（默认、最常用）
基于平衡多路搜索树，支持等值、范围、排序、LIKE 'prefix%'（前缀匹配）。CREATE INDEX 默认就是 B-tree。
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at);
-- 复合索引：遵循"最左前缀"原则
CREATE INDEX idx_orders_uc ON orders (user_id, created_at);
-- 能用：WHERE user_id=1 / WHERE user_id=1 AND created_at>...
-- 不能用（跳过最左列）：WHERE created_at>...
-- 支持指定排序方向和 NULL 位置（利于 ORDER BY 消除排序）
CREATE INDEX idx_orders_desc ON orders (created_at DESC NULLS LAST);
面试点：最左前缀原则与 MySQL 完全一致——复合索引 (a,b,c) 只能被"从最左列开始连续"的条件利用。
4. Hash 索引
只支持 = 等值查询，不支持范围和排序。PG 10 后 Hash 索引才支持 WAL（崩溃安全），但实践中很少用：B-tree 对等值查询已经足够快，还兼顾范围和排序，Hash 几乎没有优势场景。
CREATE INDEX idx_sessions_token ON sessions USING HASH (token);
5. GIN 索引（多值/包含查询之王）
GIN（Generalized Inverted Index，通用倒排索引）：为"一个字段包含多个值"的场景设计。它把每个元素建成倒排项，指向包含它的行。
核心场景：
-- 1) JSONB 查询（见 JSON 篇）
CREATE INDEX idx_prod_attr ON products USING GIN (attributes);
SELECT * FROM products WHERE attributes @> '{"brand":"Dell"}';
-- 2) 数组包含查询
CREATE INDEX idx_post_tags ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];
-- 3) 全文搜索（见全文搜索篇）
CREATE INDEX idx_doc_fts ON docs USING GIN (search_vector);
-- 4) 配合 pg_trgm 让 LIKE '%x%' 走索引
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
特点：查询快、体积大、更新较慢。写多的场景可开 fastupdate 缓冲更新。
6. GiST 索引（范围/空间/最近邻）
**GiST（Generalized Search Tree）**是可扩展的平衡树框架，擅长"重叠、包含、距离"这类查询。
-- 1) 几何/空间数据（PostGIS 用它）
CREATE INDEX idx_places_geom ON places USING GiST (geom);
-- 2) 范围类型的重叠查询
CREATE INDEX idx_reservation ON reservations USING GiST (during);
SELECT * FROM reservations WHERE during && tsrange('2026-01-01', '2026-01-05');
-- 3) 排他约束（防止时间段重叠预订）
ALTER TABLE reservations ADD CONSTRAINT no_overlap
EXCLUDE USING GiST (room_id WITH =, during WITH &&);
-- 4) KNN 最近邻检索（<-> 距离运算符）
SELECT * FROM places ORDER BY geom <-> ST_Point(116,39) LIMIT 5;
**排他约束（EXCLUDE）**是 PG 独有的强大特性，MySQL 无法用约束直接实现"防止时间段重叠"，这是 GiST 的杀手锏。
7. SP-GiST 索引
空间分区 GiST，适合非平衡的数据结构（四叉树、k-d 树、基数树/radix）。典型：IP 地址范围、电话号码前缀、点数据。
CREATE INDEX idx_ip ON logs USING SPGIST (client_ip inet_ops);
8. BRIN 索引（超大表利器）
BRIN（Block Range Index，块范围索引）：不为每一行建索引，而是记录每个数据块范围的最小/最大值摘要。索引体积极小（可能只有 B-tree 的千分之一）。
前提：数据在物理上要与索引列有序相关（如自增时间戳的日志表，新数据总在表尾）。
-- 亿级时序表，按时间范围查询
CREATE INDEX idx_metrics_time ON metrics USING BRIN (created_at);
SELECT * FROM metrics WHERE created_at BETWEEN '2026-07-01' AND '2026-07-02';
| 对比 | B-tree | BRIN |
|---|---|---|
| 索引体积 | 大 | 极小 |
| 精确度 | 精确定位行 | 定位到块范围，需再扫描 |
| 适用 | 通用 | 物理有序的超大表（时序/日志） |
9. 高级索引技巧
9.1 部分索引（Partial Index）
只对满足条件的行建索引，大幅减小索引体积。PG 特色，MySQL 不支持（MySQL 需借助其他手段）。
-- 只给"未删除"的行建索引（软删除场景，绝大多数查询只查未删除）
CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;
-- 只给待处理订单建索引
CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending';
9.2 表达式索引（Expression Index）
对函数/表达式结果建索引，让"列上带函数"的查询也能走索引。
-- 大小写不敏感查询
CREATE INDEX idx_lower_email ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com'; -- 命中
-- 对 JSONB 字段某个 key 建索引
CREATE INDEX idx_brand ON products ((attributes->>'brand'));
9.3 覆盖索引（INCLUDE，PG 11+）
把额外列"带"进索引但不参与排序，实现 Index Only Scan（只查索引不回表）。
CREATE INDEX idx_cover ON orders (user_id) INCLUDE (amount, status);
-- SELECT amount, status FROM orders WHERE user_id=1 → 可只扫索引
9.4 并发建索引（避免锁表）
-- 普通建索引会锁表（阻塞写），生产大表要用 CONCURRENTLY
CREATE INDEX CONCURRENTLY idx_big ON big_table (col);
-- 代价：更慢、需扫两遍表、失败会留下无效索引需手动清理
10. Index Only Scan 与可见性
即使走了覆盖索引，PG 仍可能需要回堆表检查元组可见性（MVCC 死元组问题）。为此每个表有个 Visibility Map 标记"整页都可见"的页。VACUUM 会更新这个图，使 Index Only Scan 真正避免回表——这又一次说明 VACUUM与表膨胀 对性能的重要性。
11. PostgreSQL vs MySQL 索引对比（面试核心）
| 维度 | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| 主键索引 | 非聚簇，索引指向 ctid | 聚簇索引，数据按主键物理排序 |
| 二级索引回表 | 索引→ctid→堆表 | 二级索引→主键→聚簇索引（回表两次结构） |
| 索引类型 | B-tree/Hash/GIN/GiST/SP-GiST/BRIN 六种 | 主要 B+tree，另有 FULLTEXT、SPATIAL、Hash(Memory) |
| 部分索引 | ✅ 支持 | ❌ 不支持 |
| 表达式索引 | ✅ 支持 | ✅ 8.0.13+ 函数索引 |
| 覆盖索引 | INCLUDE 显式 | 二级索引天然含主键，选合适列即可覆盖 |
| 排他约束 | ✅ GiST EXCLUDE | ❌ |
面试金句：
MySQL 主键是聚簇索引，表数据物理上按主键排列，二级索引存的是主键值、查询需"回表"到聚簇索引；PostgreSQL 没有聚簇索引概念，所有索引都指向堆表的物理位置 ctid。因此 MySQL 选主键要慎重（影响物理存储和二级索引大小），PG 则更灵活；而 PG 用丰富的专用索引（GIN/GiST/BRIN）和部分索引、排他约束在复杂场景更强。
注：PG 有
CLUSTER命令可按某索引物理重排表，但那是一次性操作，之后新数据不会自动保持有序，与 MySQL 的自动聚簇完全不同。
12. 索引使用建议
- 默认用 B-tree；有特殊数据类型（JSONB/数组/全文/空间）才考虑 GIN/GiST。
- 复合索引注意最左前缀，把选择性高、常用于等值的列放左边。
- 软删除、状态过滤场景优先用部分索引省空间。
- 超大有序表（时序/日志）用 BRIN。
- 生产大表建索引一律
CONCURRENTLY。 - 索引不是越多越好——每个索引都拖慢写入并占空间，用
pg_stat_user_indexes找出从不使用的索引删掉。
-- 找出从未被使用的索引
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0;
13. 面试高频问答
Q1：PostgreSQL 有哪些索引类型？分别适合什么？ B-tree（默认，等值/范围/排序）、Hash（等值）、GIN（JSONB/数组/全文等多值包含）、GiST（空间/范围重叠/最近邻）、SP-GiST（非平衡结构如 IP/前缀）、BRIN（物理有序的超大表）。
Q2：PostgreSQL 和 MySQL 的索引最大区别？（高频） MySQL 主键是聚簇索引、数据按主键物理排序、二级索引需回表；PG 无聚簇索引，所有索引指向 ctid。PG 还独有部分索引、排他约束和更丰富的专用索引类型。
Q3：什么是部分索引？有什么好处？
只对满足 WHERE 条件的行建索引（如 WHERE deleted_at IS NULL），索引更小、更新更快，适合软删除、状态过滤等只查子集的场景。MySQL 不支持。
Q4：GIN 和 GiST 的区别？ GIN 是倒排索引，适合"字段含多个值"的包含查询（JSONB、数组、全文），查询快体积大更新慢；GiST 是通用平衡树，适合空间、范围重叠、最近邻查询，更新快。
Q5：什么是覆盖索引 / Index Only Scan？PG 里有什么注意点？ 查询所需列都在索引里，可只扫索引不回表。PG 中还需 Visibility Map 标记页全可见才能真正避免回表，因此依赖 VACUUM 保持可见性图最新。
Q6：BRIN 索引什么时候用？ 数据物理上与索引列有序相关的超大表（如自增时间的日志/时序表），BRIN 只存块范围摘要、体积极小，范围查询高效。
14. 下一步
学习 索引优化策略，把索引知识用于实战调优。