2. 复杂查询的识别与分类

在 PostgreSQL 性能治理中，第一步不是急着加索引或改参数，而是先判断“这条 SQL 到底复杂在哪里”。很多慢查询表面上是执行时间长，根因却完全不同：有的卡在多表连接的连接顺序上，有的卡在子查询被反复执行，有的卡在 CTE 物化后无法下推谓词，还有的卡在窗口函数排序或聚合分组的内存与临时文件上。识别与分类的价值，就是让后续优化动作有明确靶点，而不是盲目试错。

一、从执行结构识别复杂查询形态

PostgreSQL 的复杂查询通常由若干“结构单元”组合而成。识别时建议先看 SQL 文本结构，再结合 EXPLAIN (ANALYZE, BUFFERS) 观察实际执行节点。常见形态包括：

  • 多表连接：出现两个以上表，且连接条件、过滤条件、连接顺序会显著影响中间结果集大小。典型节点为 Nested Loop、Hash Join、Merge Join。
  • 子查询：分为标量子查询、IN/EXISTS 子查询、派生表（FROM 中的子查询）。相关子查询可能被逐行执行，是慢查询高发区。
  • CTE：WITH 定义的公共表达式。PostgreSQL 12 之后默认可能内联，但含 MATERIALIZED 或多次引用时会被物化，形成优化屏障。
  • 窗口函数：OVER (PARTITION BY ... ORDER BY ...)，通常伴随排序、分区和帧计算，节点表现为 WindowAgg。
  • 聚合：GROUP BY、HAVING、DISTINCT，可能触发 HashAggregate 或 GroupAggregate，并消耗内存或落盘。
  • 集合操作：UNION、UNION ALL、INTERSECT、EXCEPT，其中 UNION 隐含去重排序，代价通常高于 UNION ALL。

这些形态往往叠加出现。例如“先按用户分组聚合，再用窗口函数排名，最后与维表连接”的报表 SQL，就同时包含聚合、窗口和连接三类结构。分类时应记录主形态与次形态，避免只贴一个标签。

二、按业务场景归类，明确优化优先级

同一结构在不同业务场景下的容忍度不同。可按以下维度归类：

业务场景典型查询特征主要代价来源优化关注点
在线点查/详情页主键或唯一索引 + 少量连接随机 I/O、连接次数响应时间、索引命中
列表分页/筛选多条件过滤 + 排序 + LIMIT排序、回表、扫描行数复合索引、排序消除
报表统计大范围聚合 + 多表连接扫描量、哈希/排序落盘预聚合、分区裁剪
实时看板窗口函数 + 时间范围过滤排序、窗口帧计算时间分区、增量计算
数据导出/批处理CTE + 集合操作 + 全表扫描临时文件、并行度并行查询、物化策略

归类后，优化优先级自然浮现：在线场景优先保证低延迟，报表场景优先降低总扫描与落盘，批处理场景可接受更长耗时但要求资源可控。

三、按执行代价归类，定位瓶颈节点

执行代价不能只看总耗时，要拆到节点。建议按以下顺序阅读 EXPLAIN (ANALYZE, BUFFERS)：

  1. 找 actual time 最大的节点，而不是 cost 最大的节点。
  2. 看 rows 估算值与实际值偏差，偏差大说明统计信息或选择率估算有问题。
  3. 看 Buffers 中 shared read 与 temp read/write，判断是缓存不足还是排序/哈希落盘。
  4. 看 loops，若某节点 loops 很高且单次耗时不小，通常是相关子查询或嵌套循环放大。

例如下面这段示例 SQL，用于统计每个客户最近 3 笔订单金额及排名：

WITH recent_orders AS MATERIALIZED (
  SELECT customer_id, order_id, amount, created_at
  FROM orders
  WHERE created_at >= now() - interval '90 days'
)
SELECT c.customer_name,
       r.order_id,
       r.amount,
       row_number() OVER (
         PARTITION BY r.customer_id
         ORDER BY r.created_at DESC
       ) AS rn
FROM customers c
JOIN recent_orders r ON r.customer_id = c.customer_id
WHERE r.amount > 100
ORDER BY c.customer_name, rn;

该查询同时包含 CTE 物化、连接、窗口函数和过滤。若 recent_orders 被物化且数据量大，窗口排序可能落盘；若 customers 与 recent_orders 连接顺序不佳，中间结果会膨胀。此时分类结论应写成“CTE 物化 + 窗口排序 + 连接放大”，而不是笼统的“查询慢”。

识别复杂查询时，先做结构分类，再做场景分类，最后用执行计划验证代价归属。三者一致时，优化方向最明确；不一致时，优先相信执行计划中的实际行数与缓冲区数据。

四、分类结果如何转化为治理动作

分类不是终点，而是把慢查询映射到可执行动作。多表连接类优先检查连接条件索引与连接顺序；子查询类优先改写为 JOIN 或 LATERAL，并确认是否可消除重复执行；CTE 类确认是否需要 MATERIALIZED 或改为子查询内联；窗口函数类关注分区键与排序键能否走索引，减少排序量；聚合类关注分组基数与 work_mem 是否足够；集合操作类优先用 UNION ALL 替代 UNION，并检查去重是否必要。

复杂查询识别与分类路径 ① SQL 结构识别 多表连接 子查询 / 派生表 CTE（WITH） 窗口函数 聚合 / DISTINCT 集合操作 主形态 + 次形态 避免单一标签 ② 业务场景归类 在线点查 / 详情页 低延迟优先 列表分页 / 筛选 排序与回表控制 报表统计 扫描量与落盘 实时看板 窗口与增量计算 数据导出 / 批处理 并行与资源可控 按场景确定容忍度 与优化优先级 ③ 执行代价定位 actual time 最大节点 真实瓶颈位置 rows 估算 vs 实际 统计信息偏差 Buffers / temp 读写 缓存与落盘判断 loops 放大 相关子查询 / 嵌套循环 输出：主形态 + 场景 + 代价节点 → 映射到治理动作 治理动作：连接顺序与索引 · 子查询改写 · CTE 物化策略 · 窗口排序优化 · 聚合内存与分区 · UNION ALL 替代

把分类结果写成一句话结论，例如“报表类查询，主形态为聚合 + 多表连接，瓶颈在 HashAggregate 落盘”，后续无论是加索引、调 work_mem、改分区还是重写 SQL，都有据可依。识别与分类做得越准，治理动作越少走弯路。

← 上一章 📖 返回目录 下一章 →