PostgreSQL 慢查询优化与 pg_stat_statements 使用

在 PostgreSQL 中，优化慢查询的过程通常包括启用慢查询日志、收集慢查询信息、分析查询执行计划并进行查询优化。以下是如何有效地识别和优化 PostgreSQL 中的慢查询的步骤：

---

1. 启用慢查询日志

首先，确认慢查询日志是否已启用。可以通过查询 pg_settings 视图来检查 log_min_duration_statement 的值。如果该值为 -1，表示慢查询日志未开启。你可以将其设置为一个正数值（以毫秒为单位），例如5000毫秒，以记录执行时间超过5秒的查询。

-- 查看当前的设置
SELECT name, setting FROM pg_settings WHERE name = 'log_min_duration_statement';

-- 启用慢查询日志，设置为 5000 毫秒（即超过 5 秒的查询将被记录）
SET log_min_duration_statement = 5000;

通过开启慢查询日志，你可以捕获到所有执行时间超过 5 秒的查询。

---

2. 使用 pg_stat_statements 扩展收集查询统计信息

pg_stat_statements 是 PostgreSQL 的一个扩展，用于收集所有 SQL 查询的执行统计信息。确保该扩展已经启用：

-- 启用扩展
CREATE EXTENSION pg_stat_statements;

启用后，可以使用以下查询来查看所有查询的统计信息，包括查询文本、执行时间、执行次数等：

SELECT * FROM pg_stat_statements;

---

3. 查找执行时间较长的查询

使用 pg_stat_statements 查看执行时间超过 5 秒（5000 毫秒）的查询，帮助识别慢查询：

SELECT query, 
       total_exec_time, 
       calls, 
       mean_exec_time
FROM pg_stat_statements
WHERE total_exec_time > 5000;

解释：

• total_exec_time：查询的总执行时间（以毫秒为单位）。
• calls：该查询的执行次数。
• mean_exec_time：每次执行的平均时间。

这样可以快速找出耗时较长的查询，便于进一步优化。

---

4. 分析慢查询的执行计划

对于识别出的慢查询，使用 EXPLAIN ANALYZE 命令来分析查询的执行计划，帮助找出性能瓶颈：

EXPLAIN ANALYZE SELECT * FROM your_table WHERE some_column = 'value';

该命令会显示查询的执行计划，包括查询操作类型、扫描方式、使用的索引等信息，并提供执行的详细时间数据。通过分析执行计划，可以判断查询是否存在不必要的全表扫描、是否缺少索引等问题。

---

5. 优化查询

根据 EXPLAIN ANALYZE 的分析结果，考虑以下优化策略：

• 添加索引：为查询中的筛选条件添加索引，减少全表扫描。
• 重写查询：优化查询结构，避免使用复杂的子查询或者非必要的联接。
• 数据分区：对于非常大的表，可以考虑使用表分区来提高查询性能。
• 优化配置：调整 PostgreSQL 的配置参数，如 work_mem、shared_buffers 等，来提高查询性能。

---

6. 检查并发连接和系统资源

有时慢查询的原因不仅仅在于查询本身，还可能是由于系统资源的瓶颈或过多的并发连接。通过以下查询，可以监控当前的并发连接和数据库活动：

-- 查看当前正在运行的查询
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- 查看锁的信息，检查是否存在锁等待
SELECT * FROM pg_locks WHERE granted = 'f';

这些查询可以帮助你了解是否有大量的并发连接或者锁竞争导致查询变慢。

---

7. 进一步优化

• 定期清理表碎片：对于经常更新的表，可以定期使用 VACUUM 和 ANALYZE 来清理死锁和统计信息，保持查询性能。

  -- 清理表碎片
  VACUUM ANALYZE your_table;
  

• 调整数据库配置：根据查询分析的结果，调整 PostgreSQL 的配置文件（如 postgresql.conf）中的参数，以优化数据库性能。例如，增加 shared_buffers 和 work_mem 以提高内存使用。

---

8. 查看某条SQL

select query_id,query from pg_stat_statements where query like 'xxxx%';

select min_exec_time,max_exec_time,query from pg_stat_statements where query_id=xxxxx;

在 PostgreSQL 中，query_id 是由 pg_stat_statements 视图中的一个字段，主要用于唯一标识查询的内容。query_id 是由 PostgreSQL 自动生成的，它是基于查询文本的哈希值，因此它有以下特点：

1. 如何生成 query_id

query_id 是通过对查询文本进行哈希运算生成的。具体来说，PostgreSQL 使用一种加密哈希算法（如 pg_catalog.pg_stat_statements_hash）来计算查询文本的哈希值。这个哈希值用于标识相同的查询，即使查询的执行计划或执行环境不同，只要查询文本相同，query_id 也是相同的。

2. query_id 是否会变化？

query_id 的变化主要取决于以下几个因素：

• 查询文本变化：如果查询文本发生变化，例如查询的字段、表名、条件、排序方式等发生了变化，生成的哈希值就会变化，从而导致 query_id 发生变化。

  例如，下面两个查询的 query_id 会不同：

  SELECT * FROM users WHERE name = 'Alice';
  SELECT * FROM users WHERE name = 'Bob';
  

  这两个查询虽然可能有相同的执行计划，但是由于查询条件不同，它们的 query_id 是不同的。

• SQL 中的常量或其他变动：即使查询本身的结构相同，但如果查询中使用了常量值（例如 SELECT * FROM users WHERE id = 10 和 SELECT * FROM users WHERE id = 20），query_id 也会不同。

3. 是否会变动？

• 相同查询，query_id 不变：如果查询的文本完全相同（例如查询条件、字段、表等相同），则 query_id 是一致的。

• 查询文本变化，query_id 变动：如果查询的文本有所变化，query_id 也会发生变化。

• 执行环境或计划不影响 query_id：虽然查询的执行计划、执行时间等可能会因数据库的状态或其他因素有所不同，但这些因素并不影响 query_id 的生成。query_id 只依赖于查询文本。

4. 为什么使用 query_id？

query_id 主要用于统计和优化目的。在 PostgreSQL 中，pg_stat_statements 会记录查询的执行统计信息，包括执行次数、总执行时间等。query_id 作为唯一标识符，帮助 PostgreSQL 将具有相同查询文本的查询合并为一个统计条目，以便更高效地跟踪和分析查询性能。

5. query_id 与执行计划的关系

query_id 只与查询的 文本 相关，而与查询的 执行计划 或 数据库状态 无关。即使查询的执行计划或数据库状态发生变化，只要查询文本不变，query_id 也不会变化。这意味着，即使查询的性能发生了变化，query_id 仍然保持不变。

结论

query_id 是基于查询文本的哈希值生成的，并且是唯一且不变的，只要查询文本相同，query_id 就不会变化。因此，query_id 可以帮助 PostgreSQL 将相同查询的执行统计信息聚合到一起，从而更有效地进行性能分析。如果查询文本发生变化（如修改了查询条件或字段），query_id 会发生变化。

---

9.全表更新

在 PostgreSQL 中，执行全表更新（UPDATE）时，通常会对被修改的每一行加上行级锁，以防止其他事务同时修改相同的行。
这意味着，虽然 UPDATE 操作本身是行级锁，但如果没有适当的索引，查询可能会导致全表扫描，从而影响性能。
此外，频繁的全表更新可能导致大量的死锁，影响数据库的并发性能。
因此，建议在 UPDATE 操作中使用适当的索引，以提高查询效率，并避免频繁的全表更新，以减少死锁的风险。

总结

通过启用慢查询日志、使用 pg_stat_statements 收集查询统计、分析执行计划、优化查询和调整数据库配置，可以有效地识别和优化 PostgreSQL 中的慢查询，提高数据库的整体性能。

希望这个结构清晰、详细的笔记能帮助你更好地理解和解决 PostgreSQL 中的慢查询问题。