基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

如何在 PostgreSQL 中排查慢查询和识别缺失的索引？

知识点图片

在 PostgreSQL 中排查慢查询并识别缺失的索引是数据库性能优化的核心工作。这个过程通常分为三个阶段：发现问题（监控）、分析问题（执行计划） 和 解决问题（创建索引或优化查询）。

以下是系统性的排查与优化指南：

第一步：发现慢查询

要想解决慢查询，首先得找到它们。PostgreSQL 提供了几种强大的工具：

1. 开启慢查询日志

最简单直接的方法是通过修改 postgresql.conf 文件把慢查询记录到日志中：

plaintext 
# 将执行时间超过 1000 毫秒 (1秒) 的 SQL 记录到日志中
log_min_duration_statement = 1000

修改后需要重新加载配置：SELECT pg_reload_conf();

2. 使用 pg_stat_statements 插件（强烈推荐）

这是 PostgreSQL 最强大的性能分析工具，它可以统计所有执行过的 SQL 的耗时、调用次数等。

配置与启用：

  1. 在 postgresql.conf 中添加：shared_preload_libraries = 'pg_stat_statements'（需重启数据库）。
  2. 在数据库中启用插件：CREATE EXTENSION pg_stat_statements;

查找最耗时的 TOP 5 查询：

sql 
SELECT 
    query, 
    calls, 
    total_exec_time / 1000.0 AS total_sec, 
    mean_exec_time AS avg_ms, 
    rows 
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 5;

3. 实时查看正在运行的慢查询

如果你发现数据库当前 CPU 或 IO 飙升，可以实时查看运行超过一定时间的查询：

sql 
SELECT 
    pid, 
    now() - query_start AS duration,
    query, 
    state
FROM pg_stat_activity 
WHERE state = 'active' 
  AND now() - query_start > interval '2 seconds'
ORDER BY duration DESC;

第二步：分析慢查询 (使用 EXPLAIN)

找到慢查询后，使用 EXPLAIN ANALYZE 来查看数据库是如何执行这条 SQL 的。

sql 
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM users WHERE status = 'active' AND created_at > '2023-01-01';

注意：ANALYZE 会真实执行该 SQL，如果是 UPDATE/DELETE 请放在事务中并 ROLLBACK。

在执行计划中寻找以下“坏味道”：

  1. Seq Scan (全表扫描)： 如果表很大，且返回的结果集只占全表的一小部分，但出现了全表扫描，通常意味着缺失索引。
  2. Filter 过滤了大量数据： 如果执行计划中显示 Rows Removed by Filter: 1000000，说明数据库读取了 100 万行但最终丢弃了，这是极好的建索引机会。
  3. External Merge Disk (磁盘排序)： 说明 work_mem 不够，或者缺少用于排序的索引。
  4. 估算行数与实际行数差距巨大： (cost=... rows=1000 actual time=... rows=1)，这说明统计信息过期了，需要执行 ANALYZE table_name;。

第三步：识别与定位缺失的索引

除了通过 EXPLAIN 逐条分析外，你还可以通过系统视图从宏观上找出哪些表最缺索引。

1. 查找全表扫描严重的大表

对比表的顺序扫描（Seq Scan）和索引扫描（Index Scan）次数。如果一个大表顺序扫描次数远超索引扫描，通常需要加索引。

sql 
SELECT 
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0 
  AND seq_tup_read > 100000 -- 过滤掉小表
ORDER BY seq_tup_read DESC;

解读：如果 seq_tup_read (全表扫描读取的行数) 非常大，说明该表经常被全表扫描。

2. 结合业务逻辑判断缺失的字段

当你定位到全表扫描的表后，回看慢查询日志或 pg_stat_statements 中的 SQL：

  • 出现在 WHERE 子句中的字段。
  • 出现在 JOIN ... ON 条件中的字段。
  • 出现在 ORDER BY 或 GROUP BY 中的字段。

第四步：创建索引的最佳实践

确认缺失索引后，不要盲目加 B-Tree 索引，请考虑以下高级技巧：

1. 复合索引 (Multi-column Index)

如果查询条件经常是 WHERE a = 1 AND b = 2，应创建复合索引。
原则： 将等值查询（=）的列放在前面，范围查询（>, <）的列放在后面。

sql 
CREATE INDEX idx_users_status_created ON users (status, created_at);

2. 部分索引 (Partial Index)

如果表中只有 1% 的用户是 status = 'pending'，且你经常查询这部分用户：

sql 
-- 仅对 pending 状态的数据建索引，大大减小索引体积
CREATE INDEX idx_users_pending ON users (created_at) WHERE status = 'pending';

3. 覆盖索引 (Covering Index / INCLUDE)

如果查询是 SELECT name FROM users WHERE email = 'a@b.com'，可以使用 INCLUDE 避免回表：

sql 
CREATE INDEX idx_users_email ON users (email) INCLUDE (name);

4. 生产环境安全建索引 (并发建索引)

在千万级以上大表创建索引会锁表，阻塞写操作。必须使用 CONCURRENTLY 关键字：

sql 
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);

补充：排查其他导致慢查询的“元凶”

有时候慢查询并不是因为缺索引：

  1. 统计信息过期： 导致规划器选择了错误的执行路径（比如该用索引却用了全表扫描）。
    • 解决： 运行 ANALYZE table_name;，并检查 Auto-vacuum 是否正常工作。
  2. 表膨胀 (Table Bloat) 和索引膨胀： 大量的 UPDATE/DELETE 产生死元组。
    • 解决： 运行 VACUUM ANALYZE table_name;，严重时可能需要 VACUUM FULL（注意会锁表）或使用 pg_repack。
  3. 无效/冗余索引拖慢写入： 索引不是越多越好，过多索引会严重拖慢 INSERT/UPDATE。
    • 排查从未使用的索引：
      sql 
      SELECT relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0 AND idx_scan IS NOT NULL;
      (确认确实没用后，可以将其删除)
00:00
00:00