基于执行计划分析的查询优化：EXPLAIN ANALYZE

真相时刻来了：SQL 查询可不只是几行代码，而是你和数据库的对话。如果你温柔地对它说 "SELECT *"，数据库大概率会乖乖照做。但如果你扔给它一篇结构混乱的 SQL 小说，数据库可能会思考人生……然后开始卡顿。

查询优化就是学会用数据库能听懂的、简洁的语言和它交流。写得清楚高效的查询，执行起来又快又省资源，也不会拖慢别的进程。反过来，写得烂的查询能让整个系统变慢：数据库 CPU、内存吃爆，磁盘疯狂读写，连用数据库的应用都跟着卡。

EXPLAIN ANALYZE 就是帮你找出这些卡点，看看到底是哪一步让查询“累了”。这就像做体检——没体检，性能问题根本无从下手。

查询里的常见问题以及怎么发现它们

现在，是时候认识下那些让性能变差的“嫌疑人”了。我们要用的武器就是 EXPLAIN ANALYZE。

问题 1：顺序扫描（Seq Scan）

Seq Scan（顺序扫描）就是 PostgreSQL 逐行查找表里的数据。表小还行，表一大，这种方式简直折磨人。

怎么知道用了 Seq Scan？ 直接用 EXPLAIN ANALYZE 分析下。比如：

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

结果可能长这样（注意 Seq Scan）：

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

怎么解决？

如果还没有索引，就给 student_id 建个索引：

CREATE INDEX idx_student_id ON students(student_id);

然后再跑一次 EXPLAIN ANALYZE，你应该能看到 Index Scan 替代了 Seq Scan。

问题 2：条件选择性太低

选择性就是要查到目标，需要处理多少行。如果你的过滤条件几乎把整张表都扫一遍，索引也救不了你。

低选择性查询示例：

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

如果表里 90% 的学生都是 Computer Science，即使 program 有索引，查询也可能用 Seq Scan。

怎么优化？

1. 重新考虑下查询逻辑：也许你需要加点别的过滤条件。
2. 确保表统计信息是最新的（这样 PostgreSQL 才能正确估算选择性）：

ANALYZE students;

3. 如果查询本来该用索引却用了顺序扫描，可以强制 PostgreSQL 用索引：

SET enable_seqscan = OFF;

问题 3：多余的排序操作

排序（Sort）很费资源，尤其是数据放不进内存的时候。最典型的就是 ORDER BY。

问题示例：

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

你可能会看到类似这样：

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

怎么让排序更快？ 如果你经常按某个字段排序，可以建个索引：

CREATE INDEX idx_last_name ON students(last_name);

这样 PostgreSQL 就能直接用索引里的顺序拿数据，省掉额外的排序操作。

问题 4：没有限制（LIMIT）

如果你用 SELECT 查数据时没限制返回行数，查询可能会把整张表都处理一遍，哪怕你只想要第一行。

比如这样：

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

如果表有一百万行，gpa > 3.5 又能查出 80% 的数据，你可能要等到天荒地老。

如果你只想要前 10 个最牛的学生，用 LIMIT：

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

另外，配合 LIMIT 用 OFFSET，还能做分页。

执行参数管理：SET

PostgreSQL 里的 SET 命令用来修改会话或查询的参数。这就像临时调个设置，只影响当前连接。

简单说，SET 就是随时随地调 PostgreSQL “心情”，不用改全局配置。

常见用法：

• 跑报表前，改下语言或日期格式。
• 给某个大查询临时加点内存。
• 批量导入时关掉日志。
• 临时改下 schema 搜索路径（search_path）。
• 安全相关（比如临时降下用户权限）。

通用语法

SET 参数 = 值;

想看当前参数值，可以用：

SHOW 参数;

恢复默认值：

RESET 参数;

综合优化案例

假设我们有个任务：找出 Computer Science 专业 GPA 最高的最后 10 个学生。原始查询如下：

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. 查询分析： 先跑下 EXPLAIN ANALYZE：

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   如果你看到顺序扫描和排序，那就该优化了。

2. 按过滤和排序建组合索引：

   建个组合索引，把两个字段都包含进去：

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. 检查优化效果：

   再跑一次 EXPLAIN ANALYZE。现在查询应该会用上刚建的索引，省掉排序和顺序扫描。

查询优化方法论

1. 先分析当前执行计划。 用 EXPLAIN ANALYZE 找出慢的操作。

2. 定位瓶颈。 找出最耗时或最吃资源的节点。

3. 建索引。 看看哪些字段参与过滤和排序，建好对应索引。

4. 减少数据量。 用 LIMIT、OFFSET，还有精准的过滤条件。

5. 更新统计信息。 跑下 ANALYZE，让 PostgreSQL 拥有最新的数据分布信息。

6. 测试优化效果。 优化后再用 EXPLAIN ANALYZE 看看性能是不是提升了。

接下来干啥？

你刚刚刷完了查询优化速成课，恭喜！多玩玩 EXPLAIN ANALYZE，你会越来越懂 PostgreSQL 的底层逻辑。记住：再神的索引也救不了结构混乱或模糊的查询。SQL 跟别的语言一样，最爱清晰明了。