SQL优化实战：用EXPLAIN ANALYZE诊断PostgreSQL查询性能

write_bot

2026-2-15

环境准备

本教程基于 PostgreSQL 15 进行演示，但相关方法同样适用于其他主流版本。你需要一个可运行的 PostgreSQL 实例，并拥有一个数据库权限。我们将创建一个名为 `performance_demo` 的数据库，并在其中创建一张示例表 `orders`。请确保你已安装 PostgreSQL 客户端工具 `psql`，并能通过命令行连接到服务器。如果你使用的是 Docker，可以快速启动一个实例：`docker run --name pg15 -e POSTGRES_PASSWORD=yourpassword -p 5432:5432 -d postgres:15`。连接字符串为 `psql -h localhost -U postgres`。

# 1. 创建数据库（如果尚未创建）
createdb -h localhost -U postgres performance_demo

# 2. 连接到数据库
psql -h localhost -U postgres -d performance_demo

# 3. 在 psql 中创建示例表并插入数据
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

-- 插入约 100 万条测试数据，模拟真实场景
INSERT INTO orders (customer_id, order_date, amount, status)
SELECT 
    (random() * 10000)::integer,
    '2023-01-01'::date + (random() * 365)::integer,
    (random() * 1000)::numeric(10,2),
    CASE WHEN random() < 0.8 THEN 'completed' ELSE 'pending' END
FROM generate_series(1, 1000000);

-- 查看数据量，确认插入成功
SELECT COUNT(*) FROM orders;

执行上述命令后，你应该在 `psql` 中看到输出 `1000000`，确认 `orders` 表中插入了 1,000,000 行数据。这是后续性能分析的基础。插入过程可能需要几分钟，请耐心等待。

步骤拆解：使用 EXPLAIN ANALYZE 分析查询

我们将分析一个典型的业务查询：查找特定客户在特定日期范围内的订单。首先，我们执行一个没有索引的查询，观察其执行计划。在 `psql` 中运行以下命令。

-- 这是一个没有索引的查询
EXPLAIN ANALYZE
SELECT order_id, customer_id, amount, status
FROM orders
WHERE customer_id = 1234
  AND order_date BETWEEN '2023-06-01' AND '2023-06-30';

预期输出会显示一个 `Seq Scan`（顺序扫描），并带有较高的执行时间和成本估算。例如，你可能会看到 `Planning Time: 0.123 ms` 和 `Execution Time: 150.456 ms`，并且扫描的行数接近 1,000,000。这表明数据库正在扫描整个表来查找符合条件的行，这是性能瓶颈的典型特征 [来源#1]。

解读执行计划：关注 `Seq Scan`、`Index Scan`、`Hash Join` 等节点，以及实际执行时间（`Actual Time`）和行数估算（`Rows`）。
识别瓶颈：如果看到 `Seq Scan` 且扫描行数（`Rows Removed by Filter`）很高，说明查询效率低下。
制定策略：根据查询条件，考虑在 `customer_id` 和 `order_date` 上创建复合索引。

结果验证：创建索引并重新分析

根据上一步的分析，我们创建一个复合索引。索引的顺序很重要，应遵循查询条件的顺序，通常将等值条件列放在前面，范围条件列放在后面 [来源#2]。

-- 创建复合索引
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

-- 重新运行 EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT order_id, customer_id, amount, status
FROM orders
WHERE customer_id = 1234
  AND order_date BETWEEN '2023-06-01' AND '2023-06-30';

预期输出现在应该显示 `Index Scan` 使用了我们创建的索引 `idx_orders_customer_date`。执行时间（`Execution Time`）会显著降低，可能从 150ms 降至 5ms 以下，扫描的行数（`Rows Removed by Filter`）也会大幅减少。这验证了索引优化的有效性。

对比优化前后：记录优化前后的执行时间、扫描行数等关键指标，例如将 `Execution Time` 从 150ms 降至 5ms。
使用 `EXPLAIN (ANALYZE, BUFFERS)`：获取更详细的 I/O 信息，如共享块命中率，命令为 `EXPLAIN (ANALYZE, BUFFERS) SELECT ...`。
验证索引使用：确认执行计划中出现了 `Index Scan` 或 `Index Only Scan`，并检查索引名称是否正确。

常见错误与排查

在优化过程中，可能会遇到一些常见问题。以下是三个典型错误及其排查方法，每个方法都包含可执行的命令和预期结果。

错误1：索引未被使用
即使创建了索引，查询计划仍显示 `Seq Scan`。

排查方法：检查 WHERE 子句是否对索引列进行了操作，例如 `WHERE lower(customer_name) = 'abc'`。如果使用了函数，考虑创建函数索引。同时，可以运行 `ANALYZE orders;` 更新表的统计信息 [来源#1]。验证命令：运行 `EXPLAIN ANALYZE` 查看计划是否变为 `Index Scan`。如果仍无效，检查 `customer_id` 的数据分布，使用 `SELECT COUNT(DISTINCT customer_id) FROM orders;` 确认基数足够高。

错误2：索引选择不当
查询使用了索引，但性能提升不明显。

排查方法：使用 `EXPLAIN (ANALYZE, VERBOSE)` 查看详细的执行计划，确认索引是否被正确使用。对于多列查询，确保索引列的顺序与查询条件的顺序匹配 [来源#2]。例如，如果查询是 `WHERE order_date = '2023-06-01' AND customer_id = 1234`，而索引是 `(customer_id, order_date)`，则可能无法高效使用。验证命令：运行 `EXPLAIN (ANALYZE, VERBOSE) SELECT ...`，并检查 `Index Cond` 是否覆盖所有条件。

错误3：过度索引
创建了过多索引，导致写入性能下降。

排查方法：定期审查索引使用情况，可以使用 `pg_stat_user_indexes` 视图查看索引的扫描次数。对于很少使用的索引，考虑删除。平衡读写性能是关键。验证命令：运行 `SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE tablename = 'orders';`，如果 `idx_scan` 为 0 或很低，则该索引可能无用。

通过本教程，你已经掌握了使用 EXPLAIN ANALYZE 诊断 PostgreSQL 查询性能的完整流程。从创建测试环境、分析执行计划、创建索引到验证结果，每一步都提供了可执行的命令和预期输出。记住，性能优化是一个持续的过程，需要结合业务场景和实际负载进行调整。

参考链接

阅读剩余

在浏览本站资源前，请先认真阅读免责声明。
站长邮箱：wnluo@wnluo.com

本站提供的一切软件、教程和内容信息仅限用于学习和研究目的；不得将上述内容用于商业或者非法用途，否则，一切后果请用户自负。本站信息来自网络收集整理，版权争议与本站无关。您必须在下载后的24个小时之内，从您的电脑或手机中彻底删除上述内容。如果您喜欢该程序和内容，请支持正版，购买注册，得到更好的正版服务。我们非常重视版权问题，如有侵权请邮件与我们联系处理。敬请谅解！

THE END

Docker部署实战：Python应用镜像体积从数百MB优化至数十MB

<<上一篇