DevOps知识中心
DevOps知识中心

精通 EXPLAIN ANALYZE：PostgreSQL 查询计划优化指南

通过我们关于 EXPLAIN ANALYZE 的全面指南，解锁 PostgreSQL 的性能。学习解释查询执行计划、识别瓶颈并优化您的 SQL 查询。本指南涵盖了基本概念、节点类型、输出解释以及带有可操作示例的实际优化策略。通过了解 PostgreSQL 如何运行您的查询来掌握数据库性能。

35 浏览量

精通 EXPLAIN ANALYZE：PostgreSQL 查询计划优化指南

在使用 PostgreSQL 时，理解数据库如何执行 SQL 查询对于实现最佳性能至关重要。即使是最精心设计的数据库模式，如果底层执行计划效率低下，也会导致查询速度缓慢。PostgreSQL 提供了强大的工具来检查这些计划，其中 EXPLAIN 和 EXPLAIN ANALYZE 是查询优化的基石。本指南将带您深入了解使用 EXPLAIN ANALYZE 来解读查询执行计划、识别性能瓶颈，并最终优化您的 SQL 查询以获得显著的速度提升。

有效利用 EXPLAIN ANALYZE 可以让开发人员和数据库管理员深入了解查询执行过程。通过理解每个步骤的成本估算、实际执行时间以及处理的行数，您可以精确地找出查询花费大部分时间的地方。这些知识使您能够就索引、查询重构和数据库配置做出明智的决策，从而构建一个响应更快、效率更高的 PostgreSQL 环境。

理解 EXPLAIN 与 EXPLAIN ANALYZE 的区别

在深入研究 EXPLAIN ANALYZE 之前，区分它与更简单的 EXPLAIN 至关重要。

EXPLAIN

当您运行一个以 EXPLAIN 开头的查询时，PostgreSQL 会生成预期的执行计划，而不会实际执行查询。这对于以下情况很有用：

  • 预览计划： 您可以看到 PostgreSQL 认为执行查询的最佳方式。
  • 估算成本： 它为计划中的每个节点提供成本估算，让您对资源使用有一个相对的概念。

示例：

EXPLAIN SELECT * FROM users WHERE registration_date > '2023-01-01';

EXPLAIN ANALYZE

EXPLAIN ANALYZE 更进一步。它不仅显示计划的执行，还会实际执行查询，然后报告实际执行的统计信息。这意味着您可以获得：

  • 实际执行时间： 每个步骤实际花费的时间。
  • 实际行数： 每个节点实际处理的行数。
  • 估算验证： 您可以将估算的行数与实际行数进行比较，以查看 PostgreSQL 的优化器是否做出了准确的预测。

这使得 EXPLAIN ANALYZE 成为实际性能调优不可或缺的工具，因为它揭示了查询在您的特定数据和系统上的真实行为。请注意，EXPLAIN ANALYZE 会执行查询，因此在生产环境中使用 UPDATE、DELETE 或 INSERT 语句时要谨慎，除非您已做好数据修改的充分准备。

示例：

EXPLAIN ANALYZE SELECT * FROM users WHERE registration_date > '2023-01-01';

解读 EXPLAIN ANALYZE 的输出

EXPLAIN ANALYZE 的输出乍一看可能显得密集，但理解其关键组成部分是根本。

核心组成部分：

  • 节点类型（Node Type）： 标识正在执行的操作（例如，Seq Scan、Index Scan、Hash Join、Nested Loop、Sort、Aggregate）。
  • 成本（Cost）： 显示为 (startup_cost .. total_cost)。
    • startup_cost：检索第一行的成本。
    • total_cost：检索所有行的成本。
    • 注意：成本是用于比较的任意单位，并非直接的时间或内存单位。
  • 行数（Rows）： 优化器预计此节点返回的行数。
  • 宽度（Width）： 此节点返回的行的估算平均宽度（以字节为单位）。
  • 实际时间（Actual Time）： 显示为 (startup_time .. total_time)。这是执行此节点的实际时间（以毫秒为单位）。
    • startup_time：返回第一行的实际时间。
    • total_time：返回所有行的实际时间。
  • 实际行数（Actual Rows）： 此节点实际返回的行数。
  • 循环次数（Loops）： 执行此节点的次数。对于顶层节点，通常为 1。对于嵌套操作，可能更高。

输出示例解释：

让我们看一个对大表进行Seq Scan（顺序扫描）的简化示例：

Seq Scan on users  (cost=0.00..15000.00 rows=1000000 width=100) (actual time=0.020..150.500 rows=950000 loops=1)
  Filter: (registration_date > '2023-01-01')
  Rows Removed by Filter: 50000

解释：

  • Seq Scan on users：数据库正在读取 users 表中的每一行。
  • cost=0.00..15000.00：优化器估计总成本约为 15000 个单位。
  • rows=1000000：优化器估计表中有一百万行。
  • actual time=0.020..150.500：完成扫描和过滤实际花费了 150.5 毫秒。
  • rows=950000：实际返回了 950,000 行（过滤后）。
  • loops=1：此扫描执行了一次。
  • Filter: (registration_date > '2023-01-01')：这是应用于过滤行的条件。
  • Rows Removed by Filter: 50000：过滤器丢弃了 50,000 行。

瓶颈识别： 如果一个节点的 actual time 明显高于其他节点，特别是如果 total_cost 也很高，那么这个节点就是优化的首要候选对象。

常见的查询计划节点和优化策略

理解不同类型的节点以及如何优化它们是掌握查询性能的关键。

1. 顺序扫描（Seq Scan）

  • 是什么： 读取表中的每一行。这对于大表通常效率低下，尤其是在对特定条件进行过滤时。
  • 何时可以接受： 对于小表，或者当您需要检索表行的大部分时。
  • 优化： 在 WHERE 子句中使用的列上创建索引。这允许 PostgreSQL 使用 Index Scan 或 Index Only Scan，对于选择性查询来说要快得多。

2. 索引扫描（Index Scan）

  • 是什么： 使用索引查找匹配 WHERE 子句的行。PostgreSQL 遍历索引，然后从表中检索相应的行。
  • 优化： 确保在正确的列上定义了索引，并且查询的编写方式能够利用该索引。如果查询还需要索引中不存在的列，则需要访问表堆，有时可以通过覆盖索引进一步优化。

3. 仅索引扫描（Index Only Scan）

  • 是什么： 一种优化的 Index Scan，其中查询所需的所有数据都直接包含在索引中。PostgreSQL 不需要访问表堆。
  • 何时有效： 当所有选定的列都是索引的一部分，并且查询不需要索引中不存在的列时。
  • 优化： 如果优化器没有自动选择 Index Only Scan 并且数据主要通过索引检索，可以考虑创建覆盖索引（例如，在 PostgreSQL 11+ 中使用 INCLUDE，或在旧版本中将所有必需的列包含在索引定义中）。

4. 连接操作（Nested Loop、Hash Join、Merge Join）

  • Nested Loop： 对于外部关系的每一行，PostgreSQL 都会扫描内部关系。对于小型的外部关系或内部关系可以通过索引快速访问时，它效率很高。
  • Hash Join： 使用一个关系（构建端）构建哈希表，并用另一个关系（探测端）的行进行探测。对于大型表且索引对连接条件无益时，它效率很高。
  • Merge Join： 要求两个关系在连接键上都已排序。合并已排序的列表。对于大型、已排序的输入，它效率很高。
  • 优化：
    • 确保连接列上存在索引。
    • 审查连接顺序。PostgreSQL 通常会选择一个好的顺序，但有时可能需要手动干预或提示（尽管 PostgreSQL 不像其他一些数据库那样支持提示）。
    • 检查 EXPLAIN ANALYZE 中连接节点的 loops 计数是否过大或 actual time 是否过高。

5. 排序（Sort）

  • 是什么： 对行进行排序。尤其是在大型数据集上，这可能计算成本很高。
  • 优化：
    • 在索引定义中添加 ORDER BY 子句。
    • 通过添加更严格的 WHERE 子句来减少要排序的行数。
    • 确保配置了足够的 work_mem，以允许排序在内存中而不是在磁盘上进行。

6. 聚合（Aggregate）

  • 是什么： 执行 COUNT()、SUM()、AVG()、GROUP BY 等操作。
  • 优化：
    • 确保 WHERE 子句效率高，在聚合之前减少行数。
    • 如果聚合是频繁且缓慢的操作，请考虑使用物化视图来存储预聚合数据。
    • 为 GROUP BY 子句中使用的列建立索引。

使用带选项的 EXPLAIN ANALYZE

EXPLAIN ANALYZE 具有几个有用的选项，可以提供更详细的信息。

VERBOSE

  • 功能： 显示查询计划的额外信息，例如带模式限定的表名和输出列名。
EXPLAIN (ANALYZE, VERBOSE) SELECT u.name FROM users u WHERE u.id = 1;

COSTS

  • 功能： 在输出中包含估算成本。这是默认行为，但您可以显式关闭它。
EXPLAIN (ANALYZE, COSTS FALSE) SELECT COUNT(*) FROM orders;

BUFFERS

  • 功能： 报告有关缓冲区使用情况（共享、临时和本地）的信息。这有助于识别 I/O 瓶颈。
    • shared hit：在 PostgreSQL 的共享缓冲区缓存中找到的块。
    • shared read：从磁盘读取到共享缓冲区的块。
    • temp read/written：读/写到临时文件（通常用于超出 work_mem 的排序或哈希）的块。
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE category = 'Electronics';

TIMING

  • 功能： 包括每个节点的实际启动时间和总时间。这是 ANALYZE 的默认行为。
EXPLAIN (ANALYZE, TIMING FALSE) SELECT * FROM logs LIMIT 10;

组合选项

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.order_date, COUNT(oi.product_id)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2023-01-01'
GROUP BY o.order_date;

实用技巧和最佳实践

  • 从 EXPLAIN ANALYZE 开始： 始终使用 EXPLAIN ANALYZE 进行实际性能分析。仅使用 EXPLAIN 是不够的。
  • 关注 actual time： 优先优化 actual time 最高的节点。
  • 比较 rows（估算 vs 实际）： 差异很大表明 PostgreSQL 的查询优化器可能做出了不准确的假设。这通常可以通过使用 ANALYZE <table_name>; 更新表统计信息或创建适当的索引来修复。
  • 使用 BUFFERS： 分析缓冲区使用情况以了解查询是否受 I/O 限制。
  • 使用真实数据进行测试： 在具有代表性数据量和与生产环境相似数据分布的数据库上运行 EXPLAIN ANALYZE。
  • 分阶段优化： 不要试图一次性优化所有内容。首先解决最大的瓶颈。
  • 考虑 work_mem： 如果您看到排序或哈希过程中有大量磁盘读取（BUFFERS 中的 temp read/written），增加 work_mem（按会话或全局）可能会有帮助，但要注意内存使用情况。
  • 明智地创建索引： 只创建实际使用且有益的索引。过多的索引会减慢写入速度并占用磁盘空间。
  • 检查 PostgreSQL 版本： 新版本通常具有改进的查询优化器和影响性能的新功能。

结论

EXPLAIN ANALYZE 是 PostgreSQL 性能调优工具箱中不可或缺的工具。通过仔细解析输出，您可以摆脱猜测，实施有针对性的优化。理解节点类型、成本估算、实际执行时间和缓冲区使用情况，可以帮助您识别瓶颈、优化索引策略并改进 SQL 查询。持续应用这些技术将大大提高 PostgreSQL 数据库的效率和响应能力。

下一步：

  1. 识别应用程序中的慢查询。
  2. 在该查询上运行 EXPLAIN (ANALYZE, BUFFERS)。
  3. 分析输出，重点关注 actual time 最高的节点。
  4. 推测潜在的优化（例如，添加索引、重写查询）。
  5. 实现优化并重新运行 EXPLAIN ANALYZE 来衡量改进情况。
  6. 重复此过程，直到达到满意的性能为止。