PostgreSQL慢查询日志里explain analyze和explain的区别

PostgreSQL慢查询日志里EXPLAIN和EXPLAIN ANALYZE的区别

当你的PostgreSQL查询变慢时，EXPLAIN和EXPLAIN ANALYZE是两个核心的诊断工具。它们在慢查询日志中出现，但作用和输出信息有本质区别。理解差异是优化查询性能的第一步。

阶段一：认识EXPLAIN——预测蓝图

EXPLAIN 命令用于显示一个查询的“执行计划”。执行计划就是数据库打算怎么一步步查数据的蓝图。它基于数据库的统计信息（比如表里有多少行、数据的分布情况）进行估算，并不会真正运行查询。

执行以下命令查看执行计划：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

其输出会显示一系列操作节点，例如：

• Seq Scan：全表扫描（从头到尾读一遍表）。
• Index Scan：使用索引扫描。
• Nested Loop：嵌套循环连接。
• Sort：排序操作。

关键点：

• 不执行查询：它只展示计划，不返回数据，也不占用实际执行的时间或资源。
• 基于估算：行数（rows）、成本（cost）等都是根据统计信息估算出来的，可能与实际有偏差。
• 无副作用：安全，可以随意用于生产环境查询。

阶段二：认识EXPLAIN ANALYZE——实战报告

EXPLAIN ANALYZE 是 EXPLAIN 的增强版。它在显示执行计划的同时，真正地运行该查询，并测量每个步骤的实际耗时和行数。

执行以下命令获取实战报告：

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

其输出在原有计划的基础上，增加了关键信息：

• 实际行数：(actual rows=...)
• 实际循环次数：(loops=...)
• 实际执行时间：(actual time=... ...)
• 实际总时间：Planning Time 和 Execution Time

关键点：

• 真实执行查询：它会触发查询的完整执行，如果是一个更新或删除操作（如 UPDATE, DELETE），数据会被真实修改。
• 基于实测：提供了真实的运行时统计数据，是评估查询性能的黄金标准。
• 有副作用：可能产生I/O开销、修改数据或获取锁，务必谨慎在生产环境对写操作使用。

阶段三：核心区别对比

阶段四：如何正确使用它们

1. 优化查询的常规流程

1. 发现慢查询：从日志或监控中定位到一条耗时长的SQL。
2. 安全分析：首先使用 EXPLAIN 查看其执行计划。观察是否存在明显的性能问题点，如全表扫描（Seq Scan）、昂贵的连接（Hash Join）或排序（Sort）。
3. 构建假设：根据计划，提出优化假设，例如：“添加索引应该能让这里的Seq Scan变成Index Scan”。
4. 实施与验证：
   • 在开发或测试环境，执行优化操作（如 CREATE INDEX）。
   • 使用 EXPLAIN ANALYZE 再次运行查询，比较优化前后的实际执行时间（Execution Time）和行数变化，验证优化是否生效。
5. 谨慎应用生产：确认优化有效且无副作用后，在生产环境实施。

2. 分析EXPLAIN ANALYZE输出的要点

当你拿到一份EXPLAIN ANALYZE的输出时，重点关注：

• 耗时最长的节点：找到 actual time 中第二个数字（总耗时）最大的节点，这通常是性能瓶颈。
• 行数的巨大差异：比较估算行数（rows）和实际行数（actual rows）。如果差异巨大（例如估算10行，实际10万行），说明统计信息过时，可能导致数据库选择了糟糕的计划。执行 ANALYZE table_name; 更新统计信息。
• 循环次数与行数的关系：在嵌套循环（Nested Loop）中，如果内层表被扫描了远超预期的行数，可能意味着连接条件或索引有问题。

3. 在慢查询日志中识别它们

在PostgreSQL的慢查询日志中，它们通常以如下形式出现：

• 可能直接记录了执行计划字符串。
• 或者记录了类似 duration: 1234.567 ms statement: EXPLAIN ANALYZE SELECT ... 的条目，这表明有用户或应用直接执行了分析命令。

重要提示：如果在日志中发现 EXPLAIN ANALYZE 后面跟随的是 INSERT, UPDATE, DELETE 或数据定义语言（DDL）语句，必须高度警惕。这很可能是一个误操作，会导致数据被意外修改或表结构变更。立即与相关开发或运维人员确认。

阶段五：最佳实践与注意事项

1. 先用EXPLAIN：在不确定查询影响时，永远先使用 EXPLAIN。
2. 分析写语句用EXPLAIN：对于 UPDATE, DELETE, INSERT ... SELECT 等可能修改数据的查询，仅使用 EXPLAIN 来分析计划。除非你完全确定并准备好接受其后果，且在一个可回滚的测试事务中。

   BEGIN;
   EXPLAIN ANALYZE DELETE FROM ... WHERE ...;
   -- 检查输出和影响
   ROLLBACK; -- 不提交更改

3. 使用EXPLAIN (ANALYZE, BUFFERS)：要获取更详细的缓存命中信息，加入 BUFFERS 选项。

   EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

   输出中会增加 shared hit/read 等信息，告诉你多少数据块是从内存缓存读的（快），多少是从磁盘读的（慢）。

4. 理解EXPLAIN的选项：EXPLAIN 支持多种格式和选项，如 JSON, YAML, TEXT 格式，以及 COSTS, TIMING, VERBOSE 等开关。ANALYZE 本质上是 EXPLAIN 的一个选项。使用 EXPLAIN (FORMAT JSON, ANALYZE) ... 可以获取更结构化、便于程序解析的输出。

执行 EXPLAIN 是安全侦察，执行 EXPLAIN ANALYZE 是实弹演习。在日常优化中，组合使用它们，先用 EXPLAIN 制定策略，再用 EXPLAIN ANALYZE 在安全环境验证战果，这是处理慢查询最可靠的路径。