索引与性能优化

这篇讲 PostgreSQL 的索引类型、如何用 EXPLAIN ANALYZE 分析慢查询，以及常见的性能优化策略。

PG 的索引类型

PG 的索引类型比 MySQL 丰富得多：

索引类型	说明	适用场景
B-tree	默认索引，平衡树	`=`、`>`、`<`、`BETWEEN`、`ORDER BY`
Hash	仅等值查询	`=` 查询（不常用，B-tree 已覆盖）
GIN	倒排索引	全文搜索、JSONB、数组包含
GiST	通用搜索树	地理空间（PostGIS）、全文搜索
BRIN	块范围索引	超大表、自然排序的数据（时序）

B-tree 索引

-- 单列索引
CREATE INDEX idx_users_email ON users(email);

-- 联合索引（注意列的顺序！）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 部分索引（只索引活跃用户）
CREATE INDEX idx_active_users ON users(email)
    WHERE active = true;

联合索引的列顺序至关重要。(a, b) 的索引可以覆盖 WHERE a = ? 和 WHERE a = ? AND b = ?，但覆盖不了 WHERE b = ?。把区分度最高的列放前面。

GIN 索引——JSONB 全文搜索

-- 为 JSONB 列创建 GIN 索引
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- 全文搜索专用索引
CREATE INDEX idx_articles_search ON articles
    USING GIN (to_tsvector('simple', title || ' ' || content));

-- 查询时使用
SELECT title, ts_rank(
    to_tsvector('simple', title || ' ' || content),
    to_tsquery('simple', 'PostgreSQL & 性能')
) AS rank
FROM articles
WHERE to_tsvector('simple', title || ' ' || content)
      @@ to_tsquery('simple', 'PostgreSQL & 性能')
ORDER BY rank DESC;

BRIN 索引——大表的时间序列

-- 对按时间顺序插入的大表最适用
CREATE INDEX idx_events_created ON events
    USING BRIN (created_at)
    WITH (pages_per_range = 32);

-- BRIN 索引比 B-tree 小 100-1000 倍，适合数十亿行的表

EXPLAIN ANALYZE——分析查询性能

-- 查看执行计划（不实际执行）
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- 实际执行并显示耗时
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- 更详细的信息
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM users WHERE email = '[email protected]';

读懂 EXPLAIN 输出：

Index Scan using idx_users_email on users  (cost=0.29..8.31 rows=1 width=68)
                                             (actual time=0.021..0.022 rows=1 loops=1)
  Index Cond: (email = '[email protected]'::text)

关键指标：

Seq Scan：全表扫描——数据量大时是性能杀手
Index Scan：索引扫描——好的
cost：起始成本..总成本（相对值）
actual time：启动时间..总时间（毫秒）
rows：预估行数 vs 实际行数——差距大说明统计信息过期

常见优化策略

1. 更新统计信息

ANALYZE users;   -- 更新单表统计
ANALYZE;         -- 更新全库统计

2. 避免 N+1 查询

-- ❌ N+1 问题
SELECT * FROM users;
-- 然后对每个 user：SELECT * FROM orders WHERE user_id = ?;

-- ✅ JOIN 一次性查完
SELECT u.*, o.order_date, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

3. 合理使用连接池

# PG 每个连接消耗 ~10MB 内存，高并发必须用连接池
# 推荐：PgBouncer（轻量）或 Pgpool-II（功能全）

4. 慢查询日志配置

# postgresql.conf
log_min_duration_statement = 1000   # 记录超过 1 秒的查询
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
```sql

### 5. 索引使用查询

```sql
-- 查看未使用的索引（候选删除）
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 查看被多次扫描的表的索引缺失
SELECT schemaname, tablename, seq_scan, seq_tup_read,
       idx_scan, seq_tup_read / seq_scan AS avg_row_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;

PG 的内置统计视图非常强大（pg_stat_*、pg_statio_*）。养成定期查看的习惯——它们能告诉你索引用了没、慢在哪里、缓存命中率如何。

一句话小结

B-tree 适合大多数场景，JSONB 用 GIN，超大时间序列表用 BRIN。EXPLAIN ANALYZE 是性能调试的起点——看到 Seq Scan 就检查是否需要索引。PG 的统计视图是你最忠实的 DBA 助手。下一篇讲备份与权限。

最后更新于 2026-06-16