第 6 章索引体系：从 B-Tree 到 BRIN 的全方位选型指南

目标读者：知道「索引能加速查询」，但搞不清 GIN 和 GiST 区别、不会看 EXPLAIN、踩过「索引明明建了却不走」的坑的同学。
学完你会：根据数据特征和查询模式主动选择合适的索引，会读 EXPLAIN (ANALYZE, BUFFERS) 的每一个字段，能诊断常见的「索引失效」。

0. 导读：索引不是万能的

很多同学对索引的认知停留在：「慢了就加个索引」。但真相是：

索引会让写入变慢（插入 / 更新 / 删除都要顺带维护索引）
索引占空间（B-Tree 常是表 20%-40%，GIN 可能比表还大）
错误的索引比没有索引更坏（误导优化器、浪费缓存）

PostgreSQL 相比 MySQL 最大的索引优势是索引种类多：MySQL InnoDB 就 B+Tree / Hash（只在内存）/ Fulltext 这么几种。而 PG 有六大正规军 + 一堆扩展（pg_trgm、rum、bloom 等），对 JSONB、数组、地理、全文、时序数据都有专用索引。

学会为不同数据挑对索引，是 PG 开发者的核心分水岭。

1. PG 六大索引方法全景图

索引方法 (Access Method) = 索引用什么数据结构 + 支持什么运算符
┌─────────────────────────────────────────────────────────────┐
│ B-Tree       排序平衡树   =,<,<=,>,>=,BETWEEN,IN,LIKE 'a%' │  ← 默认，80% 场景
│ Hash         哈希表       =                                 │  ← 仅等值，很少用
│ GIN          倒排索引     @>,?,?&,?|,@@                     │  ← JSONB/数组/全文
│ GiST         通用搜索树   <->,&&,@@,~=                      │  ← 几何/范围/相似度
│ SP-GiST      空间分区树   同 GiST 的一部分                  │  ← 非均匀分布
│ BRIN         块范围摘要   =,<,<=,>,>=,BETWEEN               │  ← 超大表+物理有序
└─────────────────────────────────────────────────────────────┘

1.1 B-Tree（默认，最常用）

数据结构：多路平衡查找树（B+Tree 的变种），叶节点按 key 排序并用双向链表连接。

擅长：

精确等值、范围、排序、LIKE 'abc%'（前缀模糊）、IS NULL、ORDER BY、GROUP BY
复合索引按「最左前缀」原则匹配（(a,b,c) 能用 WHERE a=... AND b>=...，不能用 WHERE b=...）

不擅长：

LIKE '%abc'（后模糊 / 中间模糊）——要用 pg_trgm + GiST
JSONB 内部字段——要用 GIN
数组包含——要用 GIN

ASCII 图示（4 阶 B+Tree 查找 id = 42）：

             ┌──────────[30|60]──────────┐
             │          │                │
       [10|20|25]  [35|42|50]     [70|80|90]
                      │               
                     42 ✓  命中叶节点，拿到 ctid

1.2 Hash

结构：哈希表。只支持 = 运算符。

历史包袱：PG 10 之前 Hash 索引不写 WAL，崩溃后会坏。PG 10+ 才安全。

现实选择：几乎不用。因为 B-Tree 在等值场景下也非常快，而且还能支持范围和排序。只有一个特例值得考虑：key 非常长（几 KB 的字符串）时 Hash 索引比 B-Tree 小。

1.3 GIN（Generalized Inverted Index）

结构：倒排索引（Inverted Index）。想象你是图书馆管理员：

正排：「第 1 本书 → 苹果、香蕉、西瓜」
倒排：「苹果 → 第 1,3,5 本；香蕉 → 第 1,7 本」

检索「有苹果的书」只要看「苹果」这一行，立刻拿到书号列表。

适合数据：

一列里有多个值：tags TEXT[]、JSONB、tsvector（全文检索）
查询是 @>（包含）、?（key 存在）、@@（文本匹配）

写入慢，查询快：每插入一行都要拆成 N 个 token 更新倒排。PG 有 fastupdate 缓冲机制缓解。

示例：

sql

-- 为 JSONB 字段建 GIN
CREATE INDEX idx_orders_tags ON orders USING GIN (tags jsonb_path_ops);

-- 下面这种查询就能走 GIN
SELECT * FROM orders WHERE tags @> '{"vip": true}';

jsonb_path_ops vs 默认 jsonb_ops：前者索引更小、查询更快，但只支持 @>；后者支持更多运算符但更大。

1.4 GiST（Generalized Search Tree）

结构：可插拔「扩展点」的搜索树。说白了，GiST 是一个「做树的框架」，开发者根据数据类型提供比较函数，就能建「自定义搜索树」。

典型场景：

几何 / 地理（PostGIS 底层就是 GiST）
范围类型（int4range、tsrange）的「重叠查询」&&
模糊匹配：pg_trgm 扩展提供 % 相似度运算符，GiST + pg_trgm 能加速 LIKE '%abc%'
最近邻查询（KNN）：ORDER BY col <-> 'point(1,2)' 走索引扫描

sql

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
-- 加速模糊搜索
SELECT * FROM users WHERE name LIKE '%alice%';

pg_trgm 同时支持 GIN 和 GiST，一般 GIN 查询更快，GiST 更新更快。

1.5 SP-GiST（Space-Partitioned GiST）

结构：空间分区树（类似 quad tree、k-d tree、radix trie）。

擅长：分布不均匀、层次/前缀特征明显的数据：

电话号码（按前缀分区）
IP 地址（inet 类型）
地理点数据在不均匀分布时
字符串前缀搜索（比 B-Tree 更省空间）

一般业务里用得少，但电信、网络设备类场景很合适。

1.6 BRIN（Block Range INdex）

结构：按块范围做摘要。每 128 个数据页（可配置）存一个 min/max 摘要。

突破点：索引体积极小（可能只有表的 0.01%）。

前提：表的物理顺序和逻辑顺序强相关。典型：时序表按 created_at 追加写入。

查询逻辑：WHERE created_at BETWEEN ... AND ... → BRIN 看哪些块的 min/max 和条件相交 → 直接扫这些块。查准度取决于数据有序性。

sql

CREATE INDEX idx_log_time_brin ON logs USING BRIN (created_at);
-- 一亿行的表，BRIN 可能只有几 MB，B-Tree 会有几 GB

失效条件：如果数据乱序（比如频繁 UPDATE 导致 HOT 不成立、或应用随机 INSERT），BRIN 索引会「几乎所有块都命中」，等同于全表扫描。

2. 索引选型决策树

数据是什么形状？
├─ 普通标量列（INT/TEXT/DATE）+ 等值/范围/排序 → B-Tree
├─ 仅极短等值、key 非常长                       → Hash（慎用）
├─ JSONB / 数组 / tsvector / 多值列              → GIN
├─ 几何 / 范围 / 相似度 / KNN 最近邻             → GiST
├─ IP / 电话前缀 / 字典树场景                    → SP-GiST
├─ 超大表 + 物理顺序=逻辑顺序（时序）            → BRIN
└─ 模糊匹配 LIKE '%abc%'                         → pg_trgm + GIN 或 GiST

3. 特殊索引特性

3.1 部分索引（Partial Index）

只为符合条件的行建索引。减少索引体积 + 提高针对性。

sql

-- 软删除表：只为未删除的行建索引
CREATE INDEX idx_users_active_email ON ch6_users (email)
WHERE deleted_at IS NULL;

-- 订单表：只为未完成订单建索引（已完成的占 95% 不需要频繁查）
CREATE INDEX idx_orders_open ON ch6_orders (user_id, created_at)
WHERE status IN ('pending', 'paid');

使用条件：查询语句里的 WHERE 必须能被优化器证明蕴含部分索引的条件，否则走不上。

3.2 表达式索引（Functional / Expression Index）

为列的某个函数结果建索引。典型用来绕过「函数包裹列导致索引失效」：

sql

-- 大小写不敏感登录
CREATE INDEX idx_users_email_lower ON ch6_users (LOWER(email));
-- 现在下面这句能走索引
SELECT * FROM ch6_users WHERE LOWER(email) = 'a@x.com';

-- JSONB 字段里的某个 key 作为索引键（如 ch6_docs.tags）
CREATE INDEX idx_docs_vip ON ch6_docs ((tags->>'vip'));

注意：表达式必须不可变（IMMUTABLE）。NOW()、RANDOM() 不能索引。

3.3 覆盖索引 `INCLUDE`（PG 11+）

把额外列塞进索引，让查询直接走 Index Only Scan（不回表）：

sql

CREATE INDEX idx_orders_cover
ON ch6_orders (user_id)
INCLUDE (amount, status);

-- 查询只需要 user_id, amount, status，完全不回表
SELECT amount, status FROM ch6_orders WHERE user_id = 42;

关键点：INCLUDE 的列不参与排序和比较，只是「顺手带上」，区别于把列塞进 key 位置。

Index Only Scan 的前提：

查询列必须全部在索引中（key 或 INCLUDE）
可见性映射 VM 里该页「所有行都对所有事务可见」—— 所以新数据块要等 VACUUM 后才能走 IOS

3.4 唯一索引 vs 约束

sql

-- 两种写法，底层都一样
CREATE UNIQUE INDEX idx_u ON t (col);
ALTER TABLE t ADD CONSTRAINT u UNIQUE (col);

区别：

UNIQUE INDEX 更灵活（可以是部分或 表达式 唯一）
CONSTRAINT 在元数据里有记录，\d t 时会显示「unique constraint」标签

3.5 多列索引 & 最左前缀

索引 (a, b, c) 能用于：

WHERE a=?
WHERE a=? AND b=?
WHERE a=? AND b=? AND c=?
WHERE a=? AND b>? ORDER BY b, c

不能用（或用得差）：

WHERE b=?（越过 a）
WHERE a>? AND b=?（a 是范围，b 就不能精确用索引了，只能过滤）

列顺序选择原则：

选择度（distinct 值多）的列放前面
经常出现在 WHERE = ? 的列放前面
排序用的列放后面（可合并 ORDER BY）

3.6 在线建索引 `CONCURRENTLY`

sql

-- 普通 CREATE INDEX 会锁表（阻塞所有写）
CREATE INDEX idx_big ON big_table (col);

-- CONCURRENTLY：不锁表，但执行时间更长、失败后可能留下无效索引
CREATE INDEX CONCURRENTLY idx_big ON big_table (col);

工作原理：扫表 2 次 + 等所有旧事务结束。不能在事务块内执行。

失败善后：如果失败，\d 会显示 INVALID，需要手动 DROP INDEX 后重建（或 REINDEX CONCURRENTLY）。

📌 与 MySQL 对比：MySQL 5.6+ 的 Online DDL 也能在线建索引，但 PG 的 CONCURRENTLY 在锁粒度上更友好（只要 SHARE UPDATE EXCLUSIVE）。

4. `EXPLAIN` 深度解读（全章最重要）

4.1 三种 EXPLAIN

sql

-- 1) 纯估算，不真跑
EXPLAIN SELECT ...;

-- 2) 真跑一次，带 actual time
EXPLAIN ANALYZE SELECT ...;

-- 3) 最详细：带缓存命中、内存、JSON 输出等
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) SELECT ...;

注意：EXPLAIN ANALYZE 会真正执行 SQL，包括 INSERT/UPDATE/DELETE！想看 DML 执行计划又不想改数据，要包事务：

sql

BEGIN;
EXPLAIN ANALYZE DELETE FROM t WHERE ...;
ROLLBACK;

4.2 扫描节点

节点	含义	触发条件
Seq Scan	全表扫	无可用索引 / 优化器估计回表代价过高
Index Scan	走索引找 ctid 再回表取行	走得上索引，且需要非索引列
Index Only Scan	只读索引不回表	查询列全在索引里 + VM 允许
Bitmap Index Scan	先拿一堆 ctid 放位图	匹配行较多，多索引合并
Bitmap Heap Scan	根据位图去回表	总是和 Bitmap Index Scan 配对
Tid Scan	直接按 ctid 定位	`WHERE ctid = ...`

4.3 JOIN 节点

节点	适用	复杂度
Nested Loop	小表 × 大表（有索引）	O(N·logM)
Hash Join	无排序 + 有内存建哈希表	O(N+M)
Merge Join	两侧已有序	O(N+M)，需预排序

4.4 字段解读

Seq Scan on ch6_orders  (cost=0.00..1860.00 rows=100000 width=24)
                    (actual time=0.012..34.567 rows=100000 loops=1)
  Filter: (amount > 500)
  Rows Removed by Filter: 400000
  Buffers: shared hit=100 read=900 dirtied=0 written=0

字段	含义
`cost=START..TOTAL`	估算「启动代价..总代价」（单位≈读 1 顺序页）
`rows=N`	估算返回行数
`width=B`	估算每行字节
`actual time=START..TOTAL`	实测耗时（ms）
`loops=K`	本节点被循环执行的次数（Nested Loop 内侧会 > 1）
`Buffers: shared hit/read`	缓冲命中 / 磁盘读取（hit 远多 read 才健康）
`Rows Removed by Filter`	被 WHERE 刷掉的行数

排错 4 步法：

看 rows 估算准不准：估算 rows=100，实际 rows=10000 → 统计信息过期，跑 ANALYZE。
看有没有大表 Seq Scan：大表全扫 + 过滤只留下小部分 → 应该加索引或部分索引。
看 Buffers read 占比：read 占比高 → 缓存装不下，考虑加 shared_buffers 或优化查询减少数据量。
看 Sort / Hash 的内存：有 external merge Disk: 15000kB → work_mem 不够，或排序数据量过大。

4.5 完整示例

sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, SUM(amount)
FROM ch6_orders
WHERE created_at >= '2025-01-01'
GROUP BY user_id
HAVING SUM(amount) > 10000
ORDER BY SUM(amount) DESC
LIMIT 10;

输出（示例）：

Limit  (cost=1580.20..1580.23 rows=10 width=20)
       (actual time=42.113..42.115 rows=10 loops=1)
  Buffers: shared hit=120 read=380
  ->  Sort  (cost=1580.20..1582.70 rows=1000 width=20)
            (actual time=42.112..42.113 rows=10 loops=1)
        Sort Key: (sum(amount)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  HashAggregate  (cost=1550.00..1560.00 rows=1000 width=20)
                           (actual time=42.000..42.050 rows=500)
              Group Key: user_id
              Filter: (sum(amount) > 10000)
              Rows Removed by Filter: 500
              ->  Index Scan using idx_ch6_orders_created_at on ch6_orders
                    Index Cond: (created_at >= '2025-01-01')
                    Buffers: shared hit=120 read=380
Planning Time: 0.612 ms
Execution Time: 42.180 ms

逐行解读：

最底层：用 idx_ch6_orders_created_at 做范围扫描，精确命中条件。Buffers hit=120 read=380 说明首次跑，缓存还没热。
HashAggregate：用哈希表做 GROUP BY user_id，再应用 HAVING 过滤，500 行被干掉。
Sort + Limit：用 top-N heapsort（只保留 top 10），内存只用 26kB，非常省。
总时间 42ms，规划时间 0.6ms。

5. 索引失效场景 + 破解方法

5.1 函数 / 表达式包裹列

sql

-- ❌ 索引失效
WHERE LOWER(email) = 'a@x.com'
WHERE DATE(created_at) = '2025-01-01'
WHERE amount + 10 > 100

原因：B-Tree 索引按原始值排序。函数结果未知时，优化器不敢用索引。

破解：

建表达式索引：CREATE INDEX ... ON t (LOWER(email))
改写 SQL 把函数挪到右边：WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02'

5.2 隐式类型转换

sql

-- id 是 BIGINT，这里传了 TEXT
WHERE id = '12345'   -- PG 会转换 id，相当于 CAST(id AS TEXT)，失效

-- 应该：
WHERE id = 12345

类型不匹配 → 可能被包装成 CAST，索引失效。写代码时参数类型要对齐。

5.3 `LIKE '%abc'`（前模糊 / 中间模糊）

sql

-- ❌ B-Tree 不能用
WHERE name LIKE '%alice%'

-- ✅ 用 pg_trgm
CREATE EXTENSION pg_trgm;
CREATE INDEX idx ON t USING GIN (name gin_trgm_ops);

5.4 `OR` 条件

sql

-- ❌ 如果 a 和 b 分别有索引，OR 会导致优化器不得不全扫
WHERE a = 1 OR b = 2

破解：PG 通常用 BitmapOr 合并两个索引扫描结果：

Bitmap Heap Scan
  ->  BitmapOr
        ->  Bitmap Index Scan on idx_a (a=1)
        ->  Bitmap Index Scan on idx_b (b=2)

但只有当每个条件都高度选择时才划算。如果 a=1 占 40% 数据，优化器还是走 Seq Scan。

治本：改成 UNION ALL：

sql

SELECT ... WHERE a = 1
UNION ALL
SELECT ... WHERE b = 2 AND a <> 1;

5.5 数据倾斜 / 选择度低

sql

-- 1000 万行里，status='paid' 占 900 万
-- 优化器会直接 Seq Scan，因为回表比扫表还贵
WHERE status = 'paid'

破解：

改用部分索引：CREATE INDEX ... WHERE status <> 'paid' 聚焦少数派
复合索引：(status, created_at) 让索引能定位范围

5.6 `IS NULL` / `IS NOT NULL`

B-Tree 索引支持 NULL（PG 把 NULL 放在索引最前或最后）。但 WHERE col IS NOT NULL 在「大部分行不为 NULL」时，仍可能走全扫。

破解：部分索引 WHERE col IS NOT NULL。

6. 统计信息：优化器的眼睛

6.1 `pg_stats` 视图

sql

SELECT attname, n_distinct, most_common_vals, most_common_freqs, histogram_bounds
FROM pg_stats
WHERE tablename = 'ch6_orders';

每列的关键字段：

n_distinct：估计 distinct 值个数（正数：绝对值；负数：相对比例，-0.5 = 50% 是唯一的）
most_common_vals：MCV 列表（最常见值）
histogram_bounds：非 MCV 值的直方图分桶

6.2 `ANALYZE`

sql

-- 手动更新单表统计信息
ANALYZE ch6_orders;

-- 更新所有表
ANALYZE;

-- 更新全库 + 清理死元组（下一章讲）
VACUUM ANALYZE;

autovacuum 守护进程会自动跑，但大批量变更（如 COPY 导入、DELETE 大量数据）后手动 ANALYZE 能立即让优化器看见。

6.3 `default_statistics_target`

默认值 100（每列 100 个桶 + 100 个 MCV）。

sql

-- 对高基数 / 复杂分布的列提高精度
ALTER TABLE ch6_orders ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE ch6_orders;

太高会拖慢 ANALYZE；太低会让分布估算失真。重要列才加。

6.4 扩展统计信息（PG 10+）

问题：默认统计是按列独立的。但列间有相关性（例如 城市='北京' 和 省='北京' 强相关），独立假设会导致严重低估：

sql

-- 假设分别选中 1%，独立假设 → 0.01% 行，实际 1%
WHERE city = 'Beijing' AND province = 'Beijing'

破解：创建扩展统计对象：

sql

CREATE STATISTICS city_prov_stat (dependencies, ndistinct, mcv)
  ON city, province FROM addresses;
ANALYZE addresses;

dependencies：函数依赖（一列决定另一列）
ndistinct：多列 distinct 估计
mcv：多列 MCV

7. 与 MySQL 对比

维度	PostgreSQL	MySQL InnoDB
表组织	堆表 + 独立索引	聚簇索引（主键就是数据）
主键查询	1 次索引 + 1 次回表	1 次索引即得行
非主键查询	1 次索引 + 1 次回表	2 次索引（二级索引→主键→数据）
索引方法数	6 种 + 扩展	B+Tree / Hash（内存表） / Fulltext
JSON 索引	✅ GIN 天然支持	虚拟列 + B+Tree
空间索引	✅ PostGIS 业界第一	简单 SPATIAL INDEX
覆盖索引	`INCLUDE (col)`	直接把列放进联合索引
部分索引	✅ `WHERE` 子句	❌
表达式索引	✅	8.0+ 有「函数索引」
索引下推 ICP	逻辑上由 Index Only Scan 达成	显式的 ICP（Index Condition Pushdown）
在线建索引	`CONCURRENTLY`	8.0 Online DDL

一个重要不同：MySQL 的聚簇索引让二级索引查询必须经过主键跳转（「两次 B+Tree」）。PG 的堆表 + 索引分离则是「索引→ctid→堆表」的一次跳转。

HOT（Heap-Only Tuple）更新：PG 在更新时，若新版本放在同一个页且没有索引列变化，可以不更新索引，极大减轻更新放大。MySQL InnoDB 没有直接对应概念，但主键索引树自动更新。

8. 底层原理：一次 Index Scan 发生了什么？

SQL: SELECT * FROM orders WHERE id = 42;

1. 解析器生成语法树
2. 查询重写（规则系统）
3. 优化器估算：B-Tree idx(id) 比 Seq Scan 便宜 → 选 Index Scan
4. 执行器：
   4.1 从根节点下沉到叶节点（3~4 次 I/O，可能命中 shared_buffers）
   4.2 叶节点拿到 (id=42) 对应的 ctid=(page=15, offset=3)
   4.3 去堆表 page 15 第 3 个 tuple 读原始行
   4.4 MVCC 可见性判断：xmin/xmax 是否对当前事务可见
        (下一章 MVCC 专讲)
   4.5 可见 → 返回给客户端；不可见 → 找下一个版本
5. 返回结果集

EXPLAIN 把 3+4 步的估算/实测给你展开。

9. 小结

索引体系
├─ 索引方法 (Access Method)
│  ├─ B-Tree  默认，最常用
│  ├─ Hash    几乎不用
│  ├─ GIN     JSONB / 数组 / 全文
│  ├─ GiST    几何 / 范围 / 相似度
│  ├─ SP-GiST IP / 前缀 / 非均匀
│  └─ BRIN    时序 / 超大表
├─ 索引特性
│  ├─ 部分索引 WHERE ...
│  ├─ 表达式索引 ON (func(col))
│  ├─ 覆盖索引 INCLUDE (...)
│  ├─ 唯一索引 UNIQUE
│  ├─ 多列索引 + 最左前缀
│  └─ 在线建索引 CONCURRENTLY
├─ EXPLAIN 解读
│  ├─ Seq / Index / Bitmap / Index Only Scan
│  ├─ Nested / Hash / Merge Join
│  └─ cost / rows / actual time / Buffers
├─ 失效排雷
│  ├─ 函数包列 → 表达式索引
│  ├─ 隐式转换 → 类型对齐
│  ├─ 前模糊 → pg_trgm
│  ├─ OR → Bitmap Or 或 UNION
│  └─ 低选择度 → 部分索引
└─ 统计信息
   ├─ pg_stats 视图
   ├─ ANALYZE 手动更新
   ├─ default_statistics_target
   └─ CREATE STATISTICS 扩展统计

10. 面试高频题（7 题）

Q1：PG 的索引方法有哪些？分别适用什么场景？

考察点：PG 特色、索引选型能力。

答案：

B-Tree：默认，支持 =, <, <=, >, >=, BETWEEN, IN, LIKE 'a%'、ORDER BY。80% 场景直接上。
Hash：只支持 =。PG 10 之前不写 WAL，很危险；10+ 才安全。但 B-Tree 在等值上也极快，几乎不用。
GIN：倒排索引。一列多值、JSONB、数组、全文检索、多值查询 @> ? @@。写入慢、查询快，有 fastupdate 缓冲。
GiST：通用搜索树框架。几何（PostGIS）、范围类型、相似度、KNN 最近邻查询。
SP-GiST：空间分区树。适合分布不均匀、有层次 / 前缀特征（电话号段、IP 范围、字典树）。
BRIN：块范围摘要。极度省空间（可能只占表的 0.01%），但前提是物理顺序和查询列逻辑顺序强相关，典型是时序数据。

加分项：提到 pg_trgm 扩展让 GIN/GiST 能加速 LIKE '%abc%' 模糊匹配；btree_gin / btree_gist 扩展让多列混合类型索引成为可能（例如 JSONB + INT 联合）。

Q2：什么是「索引失效」？列举 5 种常见场景和解决方法。

考察点：调优实战经验。

答案：

函数 / 表达式包裹列：如 LOWER(email) = 'a@x.com'，B-Tree 按原始值排序，函数结果无法用。解决：建表达式索引 CREATE INDEX ... ON t (LOWER(email))，或改写 SQL 把函数挪到等号右边。
隐式类型转换：WHERE bigint_col = '42'，PG 可能把 bigint_col 转成 text 再比较，索引失效。解决：参数类型与列类型严格对齐。
前 / 中模糊匹配：LIKE '%abc%' B-Tree 无法。解决：安装 pg_trgm 扩展，建 GIN 或 GiST 索引。
低选择度 + 大数据：WHERE status = 'paid' 如果 paid 占 90%，回表比全扫还贵，优化器选 Seq Scan。解决：部分索引只针对小众状态，或把高频值放复合索引靠后的位置。
OR 条件：PG 可以用 BitmapOr 合并，但只有各条件都很选择才会走；否则退化全扫。解决：改写成 UNION ALL，或合并成一个复合索引。
NOT IN + NULL：虽不是索引失效，但会把整个查询结果变空集。解决：用 NOT EXISTS。

加分项：提到可以用 EXPLAIN ANALYZE 对比前后执行计划，并通过 pg_stat_statements 定位高频慢 SQL。

Q3：什么是 Index Only Scan？它何时能走到？

考察点：覆盖索引、可见性映射 VM。

答案：

定义：查询所需的所有列都能从索引拿到，不用回表。
触发条件：
- 查询字段全在索引的 key 或 INCLUDE 里。
- PG 还要去**可见性映射（VM）**检查这块堆页是否「全可见」（all-visible 标志）。若是，直接用索引的值；若不是，仍要回表做 MVCC 可见性判断。
为什么要 VM：索引里只有 ctid 和索引键，没有 xmin/xmax。新插入或更新的行对其他事务可能不可见，必须回表判断。VM 位图由 VACUUM 维护，只有 VACUUM 过的页才会被标记 all-visible。
优化实践：
- 用 INCLUDE (col1, col2) 把 SELECT 中的非条件列塞进索引。
- 重要大表上定期 VACUUM（或保证 autovacuum 能跟上），让 VM 及时更新。
- 用 EXPLAIN 查看 Heap Fetches 行：数值大说明即使走了 Index Only Scan，仍频繁回表。
与 MySQL 对比：MySQL 的「覆盖索引（Covering Index）」不需要像 PG 这样查 VM，因为 InnoDB 是聚簇索引，二级索引叶节点就包含主键 → 若索引已含所有列，直接返回。

Q4：写一下 `EXPLAIN (ANALYZE, BUFFERS)` 输出里各个字段的含义，并说如何定位慢查询。

考察点：执行计划解读。

答案：

核心字段：
- cost=START..TOTAL：优化器估算启动/总代价（单位约等于读 1 顺序页）。
- rows=N：优化器估算返回行数。
- width=B：估算每行字节数。
- actual time=START..TOTAL：实测启动/总耗时（ms）。
- loops=K：此节点被执行 K 次（Nested Loop 内侧会 > 1）。
- Buffers: shared hit / read / dirtied / written：共享缓冲命中 / 磁盘读 / 修改 / 落盘。
- Rows Removed by Filter：被 WHERE 过滤掉的行数，反映过滤效率。
常用节点类型：
- 扫描：Seq Scan / Index Scan / Index Only Scan / Bitmap Index Scan + Bitmap Heap Scan。
- 连接：Nested Loop / Hash Join / Merge Join。
- 聚合：HashAggregate / GroupAggregate。
- 排序：Sort（内存 / external merge Disk）。
定位慢查询 4 步：
- 估算 vs 实际差得多 → 跑 ANALYZE；非常大的差距（10 倍以上）→ 考虑扩展统计。
- 大表出现 Seq Scan + Rows Removed by Filter 很高 → 缺索引或索引失效。
- Buffers: read 远多于 hit → 缓冲池小，shared_buffers 需调优，或数据冷热不均。
- 看到 external merge Disk: ...kB → work_mem 小了。
实战补充：带上 VERBOSE 看到具体列名，FORMAT JSON 可程序化分析（配合在线工具如 pev2 可视化）。

Q5：PostgreSQL 的堆表 + 索引分离 vs MySQL InnoDB 聚簇索引，有什么优劣？

考察点：存储模型对比、HOT 更新。

答案：

结构差异：
- MySQL InnoDB：主键就是数据（聚簇），二级索引的叶节点存主键 → 查询走二级索引需要二次跳转（先找主键，再按主键找数据）。
- PostgreSQL：堆表是无序的数据堆，所有索引（包括主键）都存 ctid=(page,offset) 指向堆行。任何索引走一次跳转即得数据。
优势对比：
- PG 的优势：所有索引对等，不存在「主键索引更优」的偏见；更新非主键字段时，若不改索引列，可走 HOT 更新（Heap-Only Tuple），新版本放同页不动索引，极大减轻写放大。
- InnoDB 的优势：按主键顺序读取（范围扫描）非常快；主键字段自然聚集，少一次索引跳。
坑点：
- PG：更新频繁 + 索引多时，不是 HOT 的更新会同时更新所有索引，写放大明显；需要 fillfactor 预留空间。
- InnoDB：二级索引大量查询必须回主键树，主键如果是 UUID / 乱序值，B+Tree 分裂严重；所以业界推荐用自增主键。
对应到索引选型：
- PG 常为大表加 INCLUDE 实现「真正的覆盖索引」。
- InnoDB 为避免回表，把常用查询列都塞进联合索引，利用「二级索引叶节点带主键」的特性。
Index Only Scan vs ICP：PG 的 Index Only Scan 是「完全不回表」，依赖 VM；MySQL 的 ICP 是「在索引层先过滤，再回表」，思路不同但都在减少回表代价。

Q6：什么是「扩展统计信息」？什么场景下必须手动建？

考察点：多列相关性、高级调优。

答案：

背景：默认统计是按单列独立的，优化器假设列之间独立。当列有相关性时，独立假设会导致估算严重失真。
典型场景：
- 表里 province 和 city 强相关（北京市只在北京省），WHERE province='Beijing' AND city='Beijing' 优化器估算为 0.01%，实际是 1%，差 100 倍。
- 低估会选 Nested Loop + 大表扫描；高估会选 Hash Join + 占用大量内存。

语法：

sql

CREATE STATISTICS stat_name (dependencies, ndistinct, mcv)
ON col1, col2, ... FROM table_name;
ANALYZE table_name;

三种统计类型：
- dependencies：函数依赖（α → β），估算条件 AND 的选择度。
- ndistinct：多列 distinct 值估计，改善 GROUP BY a, b 的 HashAgg 桶数估计。
- mcv：多列最常见值对（PG 12+），直接给组合值列表。
何时用：
- 发现某 SQL 的 rows 估算和 actual 差 10 倍以上，且 ANALYZE + 提高 statistics_target 没解决。
- 多列业务相关性明显（地理维度、组合分类码）。
代价：ANALYZE 时间变长、pg_statistic_ext_data 占空间。所以只为「重要且痛」的列组建。
加分项：PG 14+ 支持表达式扩展统计：CREATE STATISTICS s ON (col1 * col2) FROM t 让复杂表达式也能有准确估算。

Q7：`CREATE INDEX` 和 `CREATE INDEX CONCURRENTLY` 有什么区别？生产上哪个该用？

考察点：锁机制、DDL 最佳实践。

答案：

锁强度差异：
- CREATE INDEX：申请 SHARE 锁，阻塞所有写入（INSERT/UPDATE/DELETE）直到建完。读不受影响。
- CREATE INDEX CONCURRENTLY：申请更弱的 SHARE UPDATE EXCLUSIVE 锁，读写都不阻塞。
执行过程：
- 普通：一次扫表建好。
- CONCURRENTLY：扫表两次 + 在两次之间等所有活跃事务结束（防止漏掉当时写入的行）。时间是普通版本的 2-3 倍。
限制：
- CONCURRENTLY 不能在事务块内执行（因为要等别的事务，自己不能在一个事务里）。
- 失败时索引会留在 INVALID 状态，需要 DROP INDEX 后重试。
- PG 12+ 支持 REINDEX CONCURRENTLY，以前只能手动「建新 + 改名」。
生产选择：
- 小表（MB 级）/ 维护窗口：用普通 CREATE INDEX，快。
- 大表（GB ~ TB）/ 在线服务：必用 CONCURRENTLY，否则业务直接卡死。
- 确保磁盘空间足：建索引过程中旧索引和新索引共存。
配合建议：
- 观察 pg_stat_progress_create_index 视图看进度。
- 建完后跑 ANALYZE table，让优化器立即感知新索引的选择度。
- 用 \d table_name 查看索引是否 VALID。
与 MySQL 对比：MySQL 5.6+ 的 Online DDL 能做到不阻塞 DML，但某些索引类型（如全文）仍要 COPY 整表。PG 的 CONCURRENTLY 更统一。

本章配套 init.sql（生成百万行订单表）、demo.html（6 种索引选择助手）、5 个 Python 脚本都在同名目录 06_index/ 下。

🎮 配套演示

可视化页面：06_index/demo.html —— 「6 种索引选择助手」，输入数据形态 / 查询模式即可推荐 B-Tree / GIN / BRIN 等索引方法。
Python 实战脚本（06_index/code/）：
- 01_btree_vs_seqscan.py —— B-Tree 索引建立前后 EXPLAIN 的对比。
- 02_gin_jsonb.py —— JSONB / 数组 GIN 索引，演示 @> 包含查询。
- 03_partial_expression_index.py —— 部分索引 + 表达式索引的典型用法。
- 04_explain_analyze.py —— 解读 EXPLAIN (ANALYZE, BUFFERS) 的关键字段。
- 05_brin_timeseries.py —— BRIN 在亿级时序数据上的体积优势。
数据准备：先执行 06_index/init.sql 生成 ch6_orders（约百万行）、ch6_users、ch6_logs、ch6_docs，再按需运行脚本。

🔗 延伸阅读

第 5 章高级查询 —— 复杂 SQL 都依赖索引才能跑得快，先把 EXPLAIN 学透。
第 7 章事务与 MVCC —— Index Only Scan 离不开 VM，VM 又由 VACUUM 维护。
第 9 章性能调优 —— 把本章的 EXPLAIN 解读和统计信息串成完整的调优套路。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

python

"""公共工具：psycopg v3 连接 + 计时 + 美化 EXPLAIN。

依赖：pip install "psycopg[binary]"
"""
from __future__ import annotations

import json
import os
import time
from contextlib import contextmanager
from typing import Iterable, Sequence

import psycopg


def conninfo() -> str:
    host = os.getenv("PGHOST", "127.0.0.1")
    port = os.getenv("PGPORT", "5432")
    db   = os.getenv("PGDATABASE", "learn_pg")
    user = os.getenv("PGUSER", "postgres")
    pwd  = os.getenv("PGPASSWORD", "")
    parts = [f"host={host}", f"port={port}", f"dbname={db}", f"user={user}"]
    if pwd:
        parts.append(f"password={pwd}")
    return " ".join(parts)


@contextmanager
def connect():
    with psycopg.connect(conninfo(), autocommit=False) as conn:
        yield conn


def section(title: str) -> None:
    bar = "=" * 76
    print("\n" + bar)
    print(f"  {title}")
    print(bar)


def print_table(rows: Sequence[Sequence], headers: Iterable[str]) -> None:
    headers = list(headers)
    str_rows = [[("" if v is None else str(v)) for v in r] for r in rows]
    widths = [len(h) for h in headers]
    for r in str_rows:
        for i, v in enumerate(r):
            if i < len(widths):
                widths[i] = max(widths[i], len(v))
    line = "+" + "+".join("-" * (w + 2) for w in widths) + "+"
    print(line)
    print("| " + " | ".join(h.ljust(widths[i]) for i, h in enumerate(headers)) + " |")
    print(line)
    for r in str_rows:
        print("| " + " | ".join(r[i].ljust(widths[i]) for i in range(len(widths))) + " |")
    print(line)


def time_query(cur, sql: str, params: tuple | None = None, runs: int = 3) -> float:
    """取 runs 次的最小耗时（毫秒），消除抖动。"""
    best = float("inf")
    for _ in range(runs):
        t0 = time.perf_counter()
        cur.execute(sql, params or ())
        cur.fetchall()
        dt = (time.perf_counter() - t0) * 1000
        best = min(best, dt)
    return best


def explain_analyze(cur, sql: str, params: tuple | None = None) -> dict:
    """执行 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 返回 plan 字典。"""
    cur.execute("EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql, params or ())
    return cur.fetchone()[0][0]


def summarize_plan(plan_root: dict) -> dict:
    """从 EXPLAIN JSON 提炼关键指标。"""
    top = plan_root["Plan"]
    return {
        "node": top["Node Type"],
        "total_cost": round(top["Total Cost"], 2),
        "actual_ms": round(top["Actual Total Time"], 3),
        "rows": top["Actual Rows"],
        "shared_hit": top.get("Shared Hit Blocks", 0),
        "shared_read": top.get("Shared Read Blocks", 0),
        "planning_ms": round(plan_root.get("Planning Time", 0), 3),
        "exec_ms": round(plan_root.get("Execution Time", 0), 3),
    }

python

"""01_btree_vs_seqscan.py —— B-Tree 索引 vs 全表扫描

基于 init.sql 生成的 ch6_orders（100 万行）：
  1) 无索引查单用户订单（Seq Scan）
  2) 加索引后同一查询（Index Scan）
  3) 范围查询，对比 `(user_id)` 与 `(user_id, created_at)` 复合索引
"""
from __future__ import annotations

from _common import connect, explain_analyze, section, summarize_plan, time_query


def run(cur) -> None:
    # 清理可能存在的索引，保证重复运行
    cur.execute("DROP INDEX IF EXISTS idx_ch6_orders_user")
    cur.execute("DROP INDEX IF EXISTS idx_ch6_orders_user_time")

    USER_ID = 42
    SQL_POINT = "SELECT COUNT(*) FROM ch6_orders WHERE user_id = %s"
    SQL_RANGE = """
        SELECT id, amount, created_at FROM ch6_orders
        WHERE user_id = %s AND created_at >= NOW() - INTERVAL '90 days'
        ORDER BY created_at DESC LIMIT 20
    """

    section("1. 无索引 —— 百万行 Seq Scan")
    t0 = time_query(cur, SQL_POINT, (USER_ID,))
    p = summarize_plan(explain_analyze(cur, SQL_POINT, (USER_ID,)))
    print(f"  单点等值 COUNT  : best={t0:.2f}ms  node={p['node']}  "
          f"cost={p['total_cost']}  actual={p['actual_ms']}ms  "
          f"buffers hit={p['shared_hit']} read={p['shared_read']}")

    t1 = time_query(cur, SQL_RANGE, (USER_ID,))
    p = summarize_plan(explain_analyze(cur, SQL_RANGE, (USER_ID,)))
    print(f"  范围+排序  : best={t1:.2f}ms  node={p['node']}  "
          f"cost={p['total_cost']}  actual={p['actual_ms']}ms")

    section("2. 加 B-Tree 单列索引 (user_id)")
    cur.execute("CREATE INDEX idx_ch6_orders_user ON ch6_orders (user_id)")
    cur.execute("ANALYZE ch6_orders")

    t2 = time_query(cur, SQL_POINT, (USER_ID,))
    p = summarize_plan(explain_analyze(cur, SQL_POINT, (USER_ID,)))
    print(f"  单点等值 COUNT  : best={t2:.2f}ms  node={p['node']}  "
          f"cost={p['total_cost']}  actual={p['actual_ms']}ms  "
          f"buffers hit={p['shared_hit']} read={p['shared_read']}")
    print(f"  >>> 加速比: {(t0/t2):.1f}x")

    section("3. 加复合索引 (user_id, created_at DESC) —— 匹配范围+排序")
    cur.execute("DROP INDEX idx_ch6_orders_user")
    cur.execute("CREATE INDEX idx_ch6_orders_user_time "
                "ON ch6_orders (user_id, created_at DESC)")
    cur.execute("ANALYZE ch6_orders")

    t3 = time_query(cur, SQL_RANGE, (USER_ID,))
    p = summarize_plan(explain_analyze(cur, SQL_RANGE, (USER_ID,)))
    print(f"  范围+排序  : best={t3:.2f}ms  node={p['node']}  "
          f"cost={p['total_cost']}  actual={p['actual_ms']}ms")
    print(f"  >>> 对比无索引加速比: {(t1/t3):.1f}x")

    section("4. INCLUDE 实现 Index Only Scan")
    cur.execute("DROP INDEX idx_ch6_orders_user_time")
    cur.execute(
        "CREATE INDEX idx_ch6_orders_cover ON ch6_orders (user_id) "
        "INCLUDE (amount, status)"
    )
    cur.execute("ANALYZE ch6_orders")
    cur.execute("VACUUM ch6_orders")   # 让 VM 标记 all-visible，才能走 IOS

    SQL_COVER = (
        "SELECT amount, status FROM ch6_orders WHERE user_id = %s LIMIT 100"
    )
    p = summarize_plan(explain_analyze(cur, SQL_COVER, (USER_ID,)))
    print(f"  覆盖查询节点: {p['node']}  actual={p['actual_ms']}ms")
    print("  期望看到 'Index Only Scan'；若看到 'Index Scan'，说明 VM 尚未标记。")

    cur.execute("DROP INDEX idx_ch6_orders_cover")


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            run(cur)
            conn.commit()


if __name__ == "__main__":
    main()

python

"""02_gin_jsonb.py —— GIN 索引在 JSONB 字段上的威力

基于 init.sql 的 ch6_docs（10 万行，每行是一个 JSONB）：
  1) 无索引查「有 vip=true 标签的文档」
  2) 加 GIN（jsonb_path_ops）索引后再查
  3) 对比 jsonb_ops vs jsonb_path_ops 的索引体积
  4) 演示 @> / ? / ?& 运算符的执行计划
"""
from __future__ import annotations

from _common import connect, explain_analyze, section, summarize_plan, time_query


QUERIES = {
    "vip=true 筛选":        ("SELECT COUNT(*) FROM ch6_docs WHERE tags @> %s",
                             ('{"vip": true}',)),
    "country=JP 筛选":      ("SELECT COUNT(*) FROM ch6_docs WHERE tags @> %s",
                             ('{"country": "JP"}',)),
    "level=3 筛选":         ("SELECT COUNT(*) FROM ch6_docs WHERE tags @> %s",
                             ('{"level": 3}',)),
}


def run_batch(cur, title: str) -> None:
    section(title)
    for name, (sql, params) in QUERIES.items():
        t = time_query(cur, sql, params, runs=3)
        plan = summarize_plan(explain_analyze(cur, sql, params))
        print(f"  {name:22s}  node={plan['node']:20s}  "
              f"actual={plan['actual_ms']:.3f}ms  rows={plan['rows']}  "
              f"best={t:.2f}ms")


def index_size(cur, idx_name: str) -> str:
    cur.execute("SELECT pg_size_pretty(pg_relation_size(%s))", (idx_name,))
    (sz,) = cur.fetchone()
    return sz


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            cur.execute("DROP INDEX IF EXISTS idx_ch6_docs_gin_ops")
            cur.execute("DROP INDEX IF EXISTS idx_ch6_docs_gin_path")

            run_batch(cur, "1. 无索引 —— 全表 Seq Scan")

            section("2. 建默认 GIN (jsonb_ops)")
            cur.execute("CREATE INDEX idx_ch6_docs_gin_ops ON ch6_docs USING GIN (tags)")
            cur.execute("ANALYZE ch6_docs")
            print(f"  索引体积: {index_size(cur, 'idx_ch6_docs_gin_ops')}")
            run_batch(cur, "   查询表现")

            cur.execute("DROP INDEX idx_ch6_docs_gin_ops")

            section("3. 建 GIN (jsonb_path_ops) —— 更小更快但只支持 @>")
            cur.execute(
                "CREATE INDEX idx_ch6_docs_gin_path ON ch6_docs "
                "USING GIN (tags jsonb_path_ops)"
            )
            cur.execute("ANALYZE ch6_docs")
            print(f"  索引体积: {index_size(cur, 'idx_ch6_docs_gin_path')}")
            run_batch(cur, "   查询表现")

            section("4. 顶层 key 存在查询 `tags ? 'vip'`（jsonb_path_ops 走不了）")
            sql = "SELECT COUNT(*) FROM ch6_docs WHERE tags ? %s"
            plan = summarize_plan(explain_analyze(cur, sql, ('vip',)))
            print(f"  节点: {plan['node']}  actual={plan['actual_ms']}ms")
            print("  说明：jsonb_path_ops 不支持 `?` 运算符，此时只能 Seq Scan。")
            print("  若业务需要 `?`，换回默认 jsonb_ops。")

            cur.execute("DROP INDEX idx_ch6_docs_gin_path")
            conn.commit()


if __name__ == "__main__":
    main()

python

"""03_partial_expression_index.py —— 部分索引 + 表达式索引

场景：
  1) 软删除表 ch6_users：只为未删除的行建部分索引
  2) 大小写不敏感登录：LOWER(email) 表达式索引
  3) 未完成订单：部分索引 WHERE status IN (...)
"""
from __future__ import annotations

from _common import connect, explain_analyze, section, summarize_plan


def demo_partial_on_users(cur) -> None:
    section("1. 部分索引：WHERE deleted_at IS NULL")
    cur.execute("DROP INDEX IF EXISTS idx_ch6_users_active_email")

    # 先看无索引时的执行计划
    p = summarize_plan(explain_analyze(
        cur,
        "SELECT * FROM ch6_users WHERE email = %s AND deleted_at IS NULL",
        ("user1234@example.com",),
    ))
    print(f"  无索引:      node={p['node']}  actual={p['actual_ms']}ms")

    cur.execute(
        "CREATE INDEX idx_ch6_users_active_email ON ch6_users(email) "
        "WHERE deleted_at IS NULL"
    )
    cur.execute("ANALYZE ch6_users")

    # 查询条件必须「蕴含」部分索引的条件，才能走上
    p = summarize_plan(explain_analyze(
        cur,
        "SELECT * FROM ch6_users WHERE email = %s AND deleted_at IS NULL",
        ("user1234@example.com",),
    ))
    print(f"  命中部分索引: node={p['node']}  actual={p['actual_ms']}ms")

    # 没带 deleted_at 条件时是否能命中？—— 不能，因为无法证明满足部分索引
    p = summarize_plan(explain_analyze(
        cur,
        "SELECT * FROM ch6_users WHERE email = %s",
        ("user1234@example.com",),
    ))
    print(f"  没带 deleted_at 条件: node={p['node']}  actual={p['actual_ms']}ms")

    # 查索引大小，应该比全量索引小 20 倍
    cur.execute("SELECT pg_size_pretty(pg_relation_size('idx_ch6_users_active_email'))")
    (sz,) = cur.fetchone()
    print(f"  部分索引体积: {sz}  (只索引 95% 未删除行)")


def demo_expression_on_users(cur) -> None:
    section("2. 表达式索引：LOWER(email)")
    cur.execute("DROP INDEX IF EXISTS idx_ch6_users_email_lower")

    SQL = "SELECT * FROM ch6_users WHERE LOWER(email) = %s"
    p = summarize_plan(explain_analyze(cur, SQL, ("user9999@example.com",)))
    print(f"  无索引:       node={p['node']}  actual={p['actual_ms']}ms")

    cur.execute("CREATE INDEX idx_ch6_users_email_lower ON ch6_users (LOWER(email))")
    cur.execute("ANALYZE ch6_users")

    p = summarize_plan(explain_analyze(cur, SQL, ("user9999@example.com",)))
    print(f"  表达式索引:   node={p['node']}  actual={p['actual_ms']}ms")

    # 反面教材：用 B-Tree(email) 时，LOWER() 包裹后无法使用
    cur.execute("DROP INDEX idx_ch6_users_email_lower")
    cur.execute("CREATE INDEX idx_ch6_users_email ON ch6_users (email)")
    cur.execute("ANALYZE ch6_users")

    p = summarize_plan(explain_analyze(cur, SQL, ("user9999@example.com",)))
    print(f"  有(email)索引但被 LOWER() 破坏: node={p['node']}  actual={p['actual_ms']}ms")
    cur.execute("DROP INDEX idx_ch6_users_email")


def demo_partial_on_orders(cur) -> None:
    section("3. 部分索引：未完成订单（status IN (pending,paid)）")
    cur.execute("DROP INDEX IF EXISTS idx_ch6_orders_open")
    cur.execute(
        "CREATE INDEX idx_ch6_orders_open ON ch6_orders (user_id, created_at) "
        "WHERE status IN ('pending', 'paid')"
    )
    cur.execute("ANALYZE ch6_orders")

    p = summarize_plan(explain_analyze(cur,
        "SELECT id FROM ch6_orders WHERE status IN ('pending','paid') "
        "AND user_id = %s ORDER BY created_at DESC LIMIT 10",
        (42,),
    ))
    print(f"  命中部分索引: node={p['node']}  actual={p['actual_ms']}ms")

    cur.execute("SELECT pg_size_pretty(pg_relation_size('idx_ch6_orders_open'))")
    (sz,) = cur.fetchone()
    print(f"  索引体积: {sz}  (只索引约 40% 未完成订单)")

    cur.execute("DROP INDEX idx_ch6_orders_open")


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            demo_partial_on_users(cur)
            demo_expression_on_users(cur)
            demo_partial_on_orders(cur)
            conn.commit()


if __name__ == "__main__":
    main()

python

"""04_explain_analyze.py —— 用 psycopg 抓 EXPLAIN JSON 并美化输出

功能：
  1) 递归遍历 EXPLAIN JSON 树
  2) 用 ANSI 颜色标注昂贵节点、估算偏差大的节点
  3) 高亮 Buffers 命中率低的节点
  4) 最终给出 3 条调优建议
"""
from __future__ import annotations

import json
import sys

from _common import connect, section


def color(s: str, c: str) -> str:
    codes = {
        "red": "31", "yellow": "33", "green": "32", "cyan": "36",
        "gray": "90", "bold": "1",
    }
    return f"\x1b[{codes[c]}m{s}\x1b[0m"


def render(node: dict, depth: int = 0, suggestions: list | None = None) -> None:
    """递归打印单个 Plan 节点。"""
    if suggestions is None:
        suggestions = []
    prefix = "  " * depth + ("└─ " if depth else "")

    nt = node["Node Type"]
    cost = node.get("Total Cost", 0)
    rows_est = node.get("Plan Rows", 0)
    rows_act = node.get("Actual Rows", 0)
    loops = node.get("Actual Loops", 1)
    ms = node.get("Actual Total Time", 0)
    hit = node.get("Shared Hit Blocks", 0)
    read = node.get("Shared Read Blocks", 0)

    # 估算偏差比（避免除 0）
    bias = None
    if rows_est > 0 and rows_act > 0:
        bias = rows_act / rows_est if rows_act >= rows_est else rows_est / rows_act

    head = f"{prefix}{color(nt, 'bold')}  cost={cost:.1f}"
    head += f"  est_rows={rows_est}  actual_rows={rows_act}"
    if loops > 1:
        head += f"  loops={loops}"
    head += f"  actual_ms={ms:.3f}"
    if hit or read:
        head += f"  buffers(hit={hit}, read={read})"
    print(head)

    # 附加信息（Filter、Index Cond、Sort Key 等）
    for key in ("Index Cond", "Recheck Cond", "Filter", "Hash Cond",
                "Sort Key", "Group Key", "Join Filter", "Merge Cond"):
        if key in node:
            print("  " * (depth + 1) + color(f"{key}: ", "gray") + str(node[key]))

    # 规则告警
    if nt == "Seq Scan" and rows_act > 10000:
        msg = f"大表 Seq Scan 返回 {rows_act} 行 → 考虑加索引"
        print("  " * (depth + 1) + color(f"⚠ {msg}", "red"))
        suggestions.append(msg)
    if bias and bias > 10:
        msg = (f"{nt} 估算 {rows_est} vs 实际 {rows_act}，"
               f"偏差 {bias:.1f}× → 跑 ANALYZE 或扩展统计")
        print("  " * (depth + 1) + color(f"⚠ {msg}", "yellow"))
        suggestions.append(msg)
    if read > 0 and hit + read > 100 and read > hit:
        msg = f"{nt} 缓冲读磁盘 {read} > hit {hit} → 缓存未命中，考虑 warm cache"
        print("  " * (depth + 1) + color(f"⚠ {msg}", "yellow"))
        suggestions.append(msg)

    for child in node.get("Plans", []) or []:
        render(child, depth + 1, suggestions)


def analyze(cur, sql: str, params: tuple | None = None) -> None:
    section(f"SQL: {sql.strip()[:80]}")
    cur.execute("EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) " + sql, params or ())
    plan = cur.fetchone()[0][0]

    suggestions: list[str] = []
    render(plan["Plan"], 0, suggestions)

    print()
    print(f"Planning Time:  {plan.get('Planning Time', 0):.3f} ms")
    print(f"Execution Time: {plan.get('Execution Time', 0):.3f} ms")

    if suggestions:
        print("\n" + color("📌 调优建议", "cyan"))
        for i, s in enumerate(set(suggestions), 1):
            print(f"  {i}. {s}")
    else:
        print("\n" + color("✓ 未发现显著性能问题", "green"))


def main() -> None:
    demos = [
        # 1) 故意没建索引 → Seq Scan
        ("大表 Seq Scan 慢查询",
         "SELECT COUNT(*) FROM ch6_orders WHERE amount > 4800", None),

        # 2) 配合索引 → Index Scan
        ("复合条件：期望走 Index Scan",
         "SELECT id FROM ch6_orders WHERE user_id = %s AND created_at >= %s "
         "ORDER BY created_at DESC LIMIT 20",
         (42, "2024-01-01")),

        # 3) JOIN 聚合
        ("JOIN + GROUP BY 复杂计划",
         """
         SELECT u.city, COUNT(*) AS cnt
         FROM ch6_users u JOIN ch6_orders o ON o.user_id = u.id
         WHERE o.created_at >= NOW() - INTERVAL '30 days'
         GROUP BY u.city ORDER BY cnt DESC
         """, None),
    ]

    with connect() as conn:
        with conn.cursor() as cur:
            for title, sql, params in demos:
                try:
                    print("\n" + color(">>> " + title, "cyan"))
                    analyze(cur, sql, params)
                except Exception as e:
                    print(color(f"跳过（{type(e).__name__}: {e}）", "gray"))


if __name__ == "__main__":
    main()

python

"""05_brin_timeseries.py —— BRIN 索引在时序数据上的体积优势

基于 init.sql 的 ch6_logs（50 万行，按 created_at 顺序追加）：
  1) 对比 B-Tree 与 BRIN 索引的体积
  2) 同一范围查询下的执行计划和耗时
  3) 调 BRIN 的 pages_per_range 看对体积/速度影响
"""
from __future__ import annotations

from _common import connect, explain_analyze, section, summarize_plan, time_query


def sizes(cur) -> None:
    cur.execute(
        """
        SELECT indexname,
               pg_size_pretty(pg_relation_size(format('%I', indexname)::regclass)) AS size
        FROM pg_indexes
        WHERE tablename = 'ch6_logs'
        ORDER BY indexname
        """
    )
    rows = cur.fetchall()
    print("  当前 ch6_logs 索引：")
    for idx, sz in rows:
        print(f"    {idx:40s}  {sz}")


def bench(cur, label: str) -> None:
    SQL = """
        SELECT COUNT(*) FROM ch6_logs
        WHERE created_at >= %s AND created_at < %s
    """
    params = ("2024-03-01", "2024-03-07")
    t = time_query(cur, SQL, params, runs=3)
    p = summarize_plan(explain_analyze(cur, SQL, params))
    print(f"  [{label}]  node={p['node']:20s}  actual={p['actual_ms']:.3f}ms  "
          f"best={t:.2f}ms  buffers(hit={p['shared_hit']}, read={p['shared_read']})")


def main() -> None:
    with connect() as conn:
        with conn.cursor() as cur:
            cur.execute("DROP INDEX IF EXISTS idx_ch6_logs_time_btree")
            cur.execute("DROP INDEX IF EXISTS idx_ch6_logs_time_brin")
            cur.execute("DROP INDEX IF EXISTS idx_ch6_logs_time_brin_dense")

            section("1. 基准：无索引")
            bench(cur, "无索引(Seq Scan)")

            section("2. B-Tree 索引")
            cur.execute("CREATE INDEX idx_ch6_logs_time_btree ON ch6_logs (created_at)")
            cur.execute("ANALYZE ch6_logs")
            sizes(cur)
            bench(cur, "B-Tree")
            cur.execute("DROP INDEX idx_ch6_logs_time_btree")

            section("3. BRIN 索引（默认 pages_per_range=128）")
            cur.execute("CREATE INDEX idx_ch6_logs_time_brin ON ch6_logs USING BRIN (created_at)")
            cur.execute("ANALYZE ch6_logs")
            sizes(cur)
            bench(cur, "BRIN 默认")

            section("4. BRIN 调密集（pages_per_range=8）—— 更准但更大")
            cur.execute("DROP INDEX idx_ch6_logs_time_brin")
            cur.execute(
                "CREATE INDEX idx_ch6_logs_time_brin_dense ON ch6_logs "
                "USING BRIN (created_at) WITH (pages_per_range = 8)"
            )
            cur.execute("ANALYZE ch6_logs")
            sizes(cur)
            bench(cur, "BRIN pages=8")

            print("\n观察要点：")
            print("  * BRIN 索引可能只有 B-Tree 的 1/1000 大小。")
            print("  * 数据按 created_at 物理有序时，BRIN 查询速度接近 B-Tree。")
            print("  * 数据物理乱序时（比如频繁乱序 INSERT），BRIN 会退化成 Seq Scan。")

            cur.execute("DROP INDEX idx_ch6_logs_time_brin_dense")
            conn.commit()


if __name__ == "__main__":
    main()

markdown

# 第 6 章 · 代码示例：索引体系实战

本目录的脚本配合本章 `init.sql` 演示 PostgreSQL 六大索引方法 + 高级特性，
所有用到的表都加了 **`ch6_` 前缀**（`ch6_orders` / `ch6_users` / `ch6_logs` / `ch6_docs`），
索引则统一以 **`idx_ch6_...`** 命名，避免和其他章节冲突。

## 准备工作

1. 一个本地或测试用的 PostgreSQL 实例（建议 14+，部分 `INCLUDE` / 扩展统计需要新版本）。
2. 安装 `psycopg`（v3）：
   ```bash
   pip install "psycopg[binary]>=3.1"

设置连接信息（与上一章相同）：

bash

export PGHOST=127.0.0.1 PGPORT=5432 PGUSER=postgres PGDATABASE=learn

先初始化数据（百万行级，耗时几十秒）：
bash
```
psql -f ../init.sql
```
1

脚本一览

脚本	关键 PG 特性
`01_btree_vs_seqscan.py`	B-Tree 单列 / 复合索引、`INCLUDE` 覆盖索引、`EXPLAIN (ANALYZE, BUFFERS)` 对比
`02_gin_jsonb.py`	JSONB GIN 索引（`jsonb_ops` vs `jsonb_path_ops`），`@>` 包含查询
`03_partial_expression_index.py`	部分索引（`WHERE deleted_at IS NULL`）、表达式索引（`LOWER(email)`）
`04_explain_analyze.py`	`EXPLAIN` 三种形态、读 cost / actual / Buffers / Rows Removed by Filter
`05_brin_timeseries.py`	BRIN 在时序大表上的体积优势、`pages_per_range` 调优

_common.py 提供统一的连接函数 connect()，所有脚本共享。

预期输出

以 01_btree_vs_seqscan.py 为例（百万行 ch6_orders）：

== 无索引：Seq Scan ==
Seq Scan on ch6_orders  (cost=0.00..18334.00 rows=200 width=24)
                        (actual time=0.012..89.45 rows=187 loops=1)
  Filter: (user_id = 12345)

== 建 idx_ch6_orders_user 后：Index Scan ==
Index Scan using idx_ch6_orders_user on ch6_orders
  (cost=0.42..12.30 rows=200) (actual time=0.018..0.21 rows=187)

05_brin_timeseries.py 会展示 BRIN 体积只有 B-Tree 的 1/100 量级； 02_gin_jsonb.py 会显示 jsonb_path_ops 比默认 jsonb_ops 更小、查询更快。

常见报错

relation "ch6_xxx" does not exist：先跑 psql -f ../init.sql 初始化。
EXPLAIN ANALYZE 看到的 Buffers: read 远多于 hit：缓存还是冷的，多跑两次再观察。
CREATE INDEX CONCURRENTLY 报错 cannot run inside a transaction block：改用 psycopg 的 autocommit=True 模式，脚本里已经处理。

_common.py ↗ · 01_btree_vs_seqscan.py ↗ · 02_gin_jsonb.py ↗ · 03_partial_expression_index.py ↗ · 04_explain_analyze.py ↗ · 05_brin_timeseries.py ↗ · README.md ↗

第 6 章 索引体系：从 B-Tree 到 BRIN 的全方位选型指南 ​

0. 导读：索引不是万能的 ​

1. PG 六大索引方法全景图 ​

1.1 B-Tree（默认，最常用） ​

1.2 Hash ​

1.3 GIN（Generalized Inverted Index） ​

1.4 GiST（Generalized Search Tree） ​

1.5 SP-GiST（Space-Partitioned GiST） ​

1.6 BRIN（Block Range INdex） ​

2. 索引选型决策树 ​

3. 特殊索引特性 ​

3.1 部分索引（Partial Index） ​

3.2 表达式索引（Functional / Expression Index） ​

3.3 覆盖索引 INCLUDE（PG 11+） ​

3.4 唯一索引 vs 约束 ​

3.5 多列索引 & 最左前缀 ​

3.6 在线建索引 CONCURRENTLY ​

4. EXPLAIN 深度解读（全章最重要） ​

4.1 三种 EXPLAIN ​

4.2 扫描节点 ​

4.3 JOIN 节点 ​

4.4 字段解读 ​

4.5 完整示例 ​

5. 索引失效场景 + 破解方法 ​

5.1 函数 / 表达式包裹列 ​

5.2 隐式类型转换 ​

5.3 LIKE '%abc'（前模糊 / 中间模糊） ​

5.4 OR 条件 ​

5.5 数据倾斜 / 选择度低 ​

5.6 IS NULL / IS NOT NULL ​

6. 统计信息：优化器的眼睛 ​

6.1 pg_stats 视图 ​

6.2 ANALYZE ​

6.3 default_statistics_target ​

6.4 扩展统计信息（PG 10+） ​

7. 与 MySQL 对比 ​

8. 底层原理：一次 Index Scan 发生了什么？ ​

9. 小结 ​

10. 面试高频题（7 题） ​

Q1：PG 的索引方法有哪些？分别适用什么场景？ ​

Q2：什么是「索引失效」？列举 5 种常见场景和解决方法。 ​

Q3：什么是 Index Only Scan？它何时能走到？ ​

Q4：写一下 EXPLAIN (ANALYZE, BUFFERS) 输出里各个字段的含义，并说如何定位慢查询。 ​

Q5：PostgreSQL 的堆表 + 索引分离 vs MySQL InnoDB 聚簇索引，有什么优劣？ ​

Q6：什么是「扩展统计信息」？什么场景下必须手动建？ ​

Q7：CREATE INDEX 和 CREATE INDEX CONCURRENTLY 有什么区别？生产上哪个该用？ ​

🎮 配套演示 ​

🔗 延伸阅读 ​

🎬 可视化演示 ​

💻 示例代码 ​

脚本一览 ​

预期输出 ​

常见报错 ​

第 6 章索引体系：从 B-Tree 到 BRIN 的全方位选型指南

0. 导读：索引不是万能的

1. PG 六大索引方法全景图

1.1 B-Tree（默认，最常用）

1.2 Hash

1.3 GIN（Generalized Inverted Index）

1.4 GiST（Generalized Search Tree）

1.5 SP-GiST（Space-Partitioned GiST）

1.6 BRIN（Block Range INdex）

2. 索引选型决策树

3. 特殊索引特性

3.1 部分索引（Partial Index）

3.2 表达式索引（Functional / Expression Index）

3.3 覆盖索引 `INCLUDE`（PG 11+）

3.4 唯一索引 vs 约束

3.5 多列索引 & 最左前缀

3.6 在线建索引 `CONCURRENTLY`

4. `EXPLAIN` 深度解读（全章最重要）

4.1 三种 EXPLAIN

4.2 扫描节点

4.3 JOIN 节点

4.4 字段解读

4.5 完整示例

5. 索引失效场景 + 破解方法

5.1 函数 / 表达式包裹列

5.2 隐式类型转换

5.3 `LIKE '%abc'`（前模糊 / 中间模糊）

5.4 `OR` 条件

5.5 数据倾斜 / 选择度低

5.6 `IS NULL` / `IS NOT NULL`

6. 统计信息：优化器的眼睛

6.1 `pg_stats` 视图

6.2 `ANALYZE`

6.3 `default_statistics_target`

6.4 扩展统计信息（PG 10+）

7. 与 MySQL 对比

8. 底层原理：一次 Index Scan 发生了什么？

9. 小结

10. 面试高频题（7 题）

Q1：PG 的索引方法有哪些？分别适用什么场景？

Q2：什么是「索引失效」？列举 5 种常见场景和解决方法。

Q3：什么是 Index Only Scan？它何时能走到？

Q4：写一下 `EXPLAIN (ANALYZE, BUFFERS)` 输出里各个字段的含义，并说如何定位慢查询。

Q5：PostgreSQL 的堆表 + 索引分离 vs MySQL InnoDB 聚簇索引，有什么优劣？

Q6：什么是「扩展统计信息」？什么场景下必须手动建？

Q7：`CREATE INDEX` 和 `CREATE INDEX CONCURRENTLY` 有什么区别？生产上哪个该用？

🎮 配套演示

🔗 延伸阅读

🎬 可视化演示

💻 示例代码

脚本一览

预期输出

常见报错