第二章：索引工程——让查询快 100 倍

数据库慢，90% 的时候是索引的问题。但大多数开发者对索引的理解停留在"加一个 CREATE INDEX"，然后发现没有变快，甚至变慢了。

---

一、索引的本质

索引是一个独立的数据结构，用来加速查询。代价是：

• 占用额外存储空间
• 每次 INSERT / UPDATE / DELETE 都要维护索引（写操作变慢）

所以索引不是越多越好——正确的索引是那些能让最慢的查询变快，同时写入开销可以接受的索引。

---

二、PostgreSQL 索引类型详解

2.1 B-Tree——默认索引，适用 80% 场景

B-Tree（平衡树）是 PostgreSQL 的默认索引类型。

-- 这两个语句等价
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_email ON users USING BTREE(email);

适用操作：=, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE 'xxx%'（前缀匹配）

不适用：LIKE '%xxx'（后缀匹配）、全文搜索、数组包含、JSONB 查询

-- 有效利用 B-Tree 索引的查询
SELECT * FROM orders WHERE user_id = 123;           -- = 精确匹配 ✅
SELECT * FROM orders WHERE created_at > '2024-01-01'; -- 范围查询 ✅
SELECT * FROM users WHERE email LIKE 'john%';       -- 前缀模糊 ✅

-- 无法使用 B-Tree 索引的查询
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- 后缀模糊 ❌
SELECT * FROM users WHERE LOWER(email) = 'john@example.com'; -- 函数变换 ❌

2.2 GIN——用于多值类型（数组、JSONB、全文搜索）

GIN（Generalized Inverted Index，广义倒排索引）。把每个元素映射到包含它的行，类似搜索引擎的倒排索引。

-- JSONB 字段的 GIN 索引
CREATE INDEX idx_products_specs ON products USING GIN(specs);

-- 支持的查询操作
SELECT * FROM products WHERE specs @> '{"category": "electronics"}'; -- 包含 ✅
SELECT * FROM products WHERE specs ? 'discount';                       -- 键存在 ✅

-- 数组字段的 GIN 索引
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];  -- 数组包含 ✅
SELECT * FROM posts WHERE tags && ARRAY['sql', 'database']; -- 数组交集 ✅

-- 全文搜索的 GIN 索引
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('english', content));

SELECT * FROM articles
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'postgresql');

GIN vs B-Tree：

• GIN 索引构建慢，占用空间大
• GIN 查询速度快（对多值查询）
• 写入时维护成本高

优化技巧：频繁写入的表上的 GIN 索引，使用 gin_pending_list_limit 参数缓冲写入：

-- 设置 GIN Pending List 大小（默认 4MB）
SET gin_pending_list_limit = 64MB;

2.3 GiST——用于范围和几何类型

GiST（Generalized Search Tree）适合需要"重叠"或"距离"语义的查询。

-- 范围类型索引
CREATE INDEX idx_reservations_during ON reservations USING GIST(during);

-- 查询重叠的时间段
SELECT * FROM reservations
WHERE during && '[2024-01-15, 2024-01-20)'::tstzrange; -- 使用 GiST ✅

-- 地理信息索引（PostGIS）
CREATE INDEX idx_stores_location ON stores USING GIST(location);

-- 距离查询
SELECT name
FROM stores
ORDER BY location <-> ST_MakePoint(-122.4, 37.78)  -- <-> 是 KNN 距离算子
LIMIT 5;

GiST vs GIN：

• GiST 支持更多算子（包括最近邻 KNN 搜索）
• GIN 查询一般比 GiST 快（精确匹配场景）
• GiST 更新比 GIN 快

2.4 BRIN——超大表的轻量索引

BRIN（Block Range Index）只记录每个数据块范围内的最小值和最大值，不记录具体位置。

-- 适合：数据有物理排序特征的大表（如按时间插入的日志表）
CREATE INDEX idx_logs_created_at ON logs USING BRIN(created_at)
    WITH (pages_per_range = 128);

-- 索引大小对比（1亿行的 created_at 列）
-- B-Tree: ~2.1 GB
-- BRIN:   ~48 KB（!!!)

适用场景：

• 数据按时间顺序插入的表（日志、事件、订单）
• 表非常大（> 1 亿行）
• 主要做时间范围扫描（不是精确匹配）

不适用场景：

• 数据插入顺序和查询字段无关
• 需要精确查找单行

---

三、覆盖索引——Index Only Scan 的威力

普通索引包含键值 + 行指针（CTID），查到行指针后还需要回表读取完整数据。

覆盖索引把查询需要的所有列都包含在索引里，完全不需要回表。

-- 普通索引：需要回表
CREATE INDEX idx_orders_user ON orders(user_id);

-- 查询：需要先用索引找到行，再回表读取 amount
SELECT user_id, amount FROM orders WHERE user_id = 123;
-- 执行计划：Index Scan（需要回表）

-- 覆盖索引：包含查询所需的所有列
CREATE INDEX idx_orders_user_cover ON orders(user_id) INCLUDE (amount, created_at);

-- 执行计划：Index Only Scan（不需要回表，速度提升 3-10 倍）
EXPLAIN SELECT user_id, amount, created_at FROM orders WHERE user_id = 123;

查看是否使用了 Index Only Scan

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, amount FROM orders WHERE user_id = 123;

-- 输出示例（好的情况）
-- Index Only Scan using idx_orders_user_cover on orders
--   Index Cond: (user_id = 123)
--   Heap Fetches: 0  ← 0 表示完全没有回表

---

四、部分索引——只索引你需要的行

大多数查询只关心表的一个子集，但普通索引包含所有行。部分索引（Partial Index）只索引满足条件的行。

-- 订单表：90% 是已完成订单，但查询几乎都是"待处理"订单
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE status = 'pending';

-- 等价查询：比全表索引快（索引更小，查询命中率更高）
SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01';

-- 用户表：软删除，大量已删除用户，但查询通常只查活跃用户
CREATE UNIQUE INDEX idx_users_email_active ON users(email)
    WHERE deleted_at IS NULL;
-- 同时还保证了活跃用户 email 唯一（已删除用户可以复用 email）

---

五、函数索引——索引计算后的值

当查询对列使用了函数转换，普通索引无效，需要函数索引：

-- 问题：这个查询无法使用普通索引
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- 解决：创建函数索引
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 现在这个查询可以使用索引
SELECT * FROM users WHERE LOWER(email) = 'john@example.com'; -- ✅

-- 时间函数索引
CREATE INDEX idx_orders_date ON orders(DATE(created_at));

-- 查询某天的所有订单（可以使用索引）
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15'; -- ✅

---

六、EXPLAIN ANALYZE——读懂执行计划

EXPLAIN ANALYZE 是优化查询的最重要工具：

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;

读懂输出

Sort  (cost=1234.56..1234.61 rows=10 width=40) (actual time=45.123..45.125 rows=10 loops=1)
  Sort Key: (count(o.id)) DESC
  Sort Method: top-N heapsort  Memory: 25kB
  ->  HashAggregate  (cost=1200.00..1220.00 rows=2000 width=40) (actual time=44.800..44.950 rows=2000 loops=1)
        Group Key: u.id, u.name
        ->  Hash Join  (cost=500.00..1100.00 rows=20000 width=16) (actual time=12.500..38.000 rows=18500 loops=1)
              Hash Cond: (o.user_id = u.id)
              ->  Seq Scan on orders o  (cost=0.00..400.00 rows=20000 width=8) (actual time=0.100..8.000 rows=20000 loops=1)
              ->  Hash  (cost=450.00..450.00 rows=4000 width=20) (actual time=11.000..11.000 rows=3800 loops=1)
                    Buckets: 4096  Batches: 1  Memory Usage: 240kB
                    ->  Index Scan using idx_users_created on users u  (cost=0.43..450.00 rows=4000 width=20) (actual time=0.050..9.500 rows=3800 loops=1)
                          Index Cond: (created_at > '2024-01-01')

关键指标：

• cost=X..Y：估算成本（X=启动成本，Y=总成本）
• actual time=X..Y：实际执行时间（毫秒）
• rows=N：实际行数
• loops=N：执行次数（嵌套循环时 > 1）

危险信号：

-- 1. 估算行数与实际行数差距大（统计信息过期）
(cost=... rows=100 ...) (actual time=... rows=50000 ...)
-- 解决：ANALYZE table_name

-- 2. Seq Scan on 大表（全表扫描）
Seq Scan on orders (cost=0.00..45000.00 rows=1000000 ...)
-- 需要加索引

-- 3. Hash Batches > 1（work_mem 不足，溢出到磁盘）
Batches: 8  Memory Usage: 128MB
-- 解决：增大 work_mem

可视化工具

-- 输出 JSON 格式，粘贴到 explain.dalibo.com 或 pgMustard.com 可视化
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT ...;

---

七、索引膨胀与维护

PostgreSQL 的索引因为 MVCC 会产生膨胀：删除或更新的行在索引中留下"死项"（dead tuple），需要 VACUUM 清理。

-- 查看索引大小和膨胀估算
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查找从未被使用的索引（可以考虑删除）
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pk_%'  -- 排除主键
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重建膨胀的索引（不锁表）
REINDEX INDEX CONCURRENTLY idx_orders_user;
-- 注意：CONCURRENTLY 需要 PostgreSQL 12+

---

八、实战：如何给慢查询加索引

步骤一：找到最慢的查询

-- 启用 pg_stat_statements 扩展
CREATE EXTENSION pg_stat_statements;

-- 查找最耗时的查询
SELECT
    query,
    calls,
    round(total_exec_time / calls) AS avg_ms,
    round(total_exec_time) AS total_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

步骤二：EXPLAIN ANALYZE 分析

EXPLAIN (ANALYZE, BUFFERS)
-- 把最慢的查询粘贴到这里

步骤三：识别问题模式

执行计划关键词	含义	解决方案
Seq Scan on large table	全表扫描	加 B-Tree 索引
Seq Scan 但 rows 很少	统计信息不准	ANALYZE 表
Nested Loop 大行数	嵌套循环性能差	增加 work_mem，或加连接字段索引
Hash Batches > 1	work_mem 不足溢出磁盘	增加 work_mem
Index Scan + Heap Fetches 多	需要覆盖索引	加 INCLUDE 列

步骤四：验证改进

-- 对比加索引前后
-- 注意：需要在真实数据量上测试，测试数据可能失效（PG 可能选择 Seq Scan）
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

---

关键认知

索引不是越多越好：每个索引都会减慢写入速度。一个有 15 个索引的表，INSERT 操作需要更新 15 个独立数据结构。

合适的索引策略：

1. 主键和外键：必须索引
2. 高基数的过滤列（用户 ID、邮箱）：B-Tree
3. JSONB 和数组字段：GIN
4. 大表时间列（日志）：BRIN
5. 90% 场景下的部分数据：部分索引
6. 查询只需少量列：覆盖索引

“数据库优化不是玄学，是科学。EXPLAIN ANALYZE 给你所有需要的信息，你只需要学会读懂它。”