当前位置： 首页 > news >正文

SQL 与查询优化（PostgreSQL 篇） 第二期

news 2026/4/24 10:40:56

SQL 与查询优化（PostgreSQL 篇）· 第二期

SQL 与查询优化（PostgreSQL 篇）· 第二期

窗口函数与 CTE 的深度优化

继第一期掌握执行计划与索引基础后，本期聚焦 SQL 的高级能力：窗口函数（Window Functions） 与 公共表表达式（CTE）。
你将学会如何用它们写出更简洁高效的查询，同时避开常见的性能陷阱——包括 CTE 物化屏障、递归 CTE 的优化技巧，以及窗口函数与索引的协作之道。

一、窗口函数 – 不改变行数的聚合利器

1.1 核心概念

窗口函数在保留每一行原始数据的同时，基于一组行（窗口）进行计算。
相比于聚合 GROUP BY（行数减少），窗口函数不压缩结果集。

语法模板：

函数() OVER ([PARTITION BY 分组列] [ORDER BY 排序列] [ROWS/RANGE 窗口帧子句]
)

常见窗口函数：

类别 函数 用途
排名 ROW_NUMBER(), RANK(), DENSE_RANK() 为行分配序号
偏移 LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() 访问同一分区内前后行
分布 NTILE(n), PERCENT_RANK() 分桶与百分位
常规聚合 SUM(), AVG(), COUNT(), MAX(), MIN() 移动或累积聚合

1.2 典型高效场景

(1) 每组取 Top N（如每个客户最近 3 笔订单）

WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rnFROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

优化要点：

  • PARTITION BY customer_id 配合索引 (customer_id, order_date DESC) 可实现 仅索引扫描，避免显式排序。
  • 若表巨大，可加 INCLUDE 覆盖其他输出列。

(2) 计算环比/同比（LAG 替代自连接）

❌ 低效的自连接写法：

SELECT o1.date, o1.amount, o2.amount AS prev_amount
FROM sales o1
LEFT JOIN sales o2 ON o1.date = o2.date + interval '1 day';

✅ 窗口函数高效写法：

SELECT date, amount,LAG(amount, 1) OVER (ORDER BY date) AS prev_amount
FROM sales;
  • 自连接会产生嵌套循环或合并连接，而窗口函数只需一次扫描，按序计算。
  • 若 date 字段有唯一索引，可进一步走 Index Only Scan。

(3) 移动窗口聚合（7 日移动平均）

SELECT date, amount,AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM sales;
  • ROWS 基于物理行号，适合日期连续场景；若日期有缺失，建议用 RANGE 基于实际时间间隔（需配合 ORDER BY date 且 date 类型可加减）。

1.3 窗口函数的执行计划与优化

执行计划中窗口函数体现为 WindowAgg 节点。

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, order_date,ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders;

计划可能显示：

WindowAgg (actual time=... rows=...)->  Sort (actual time=... rows=...)Sort Key: customer_id, order_date->  Seq Scan on orders
  • 排序代价：如果表巨大且没有合适索引，WindowAgg 必须先排序。
  • 优化方法：建立复合索引 (customer_id, order_date)，使数据天然按分区+排序顺序存储，消除显式 Sort。
  • 对于 LAG 或 LEAD 如果 PARTITION BY 列少，也可以受益于索引。

验证索引效果：

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
-- 再次 EXPLAIN，观察 Sort 节点消失，WindowAgg 直接从索引扫描获取有序数据。

二、CTE（WITH 查询） – 优雅但暗藏玄机

2.1 CTE 两种执行模式

自 PostgreSQL 12 开始，优化器可以选择是否物化 CTE。

模式 行为 适用场景
物化（MATERIALIZED） 先执行 CTE 并存储结果到临时表，外部查询再读取 CTE 被多次引用、开销大且中间结果较小
内联（NOT MATERIALIZED） 将 CTE 像子查询一样展开到主查询，合并优化 CTE 只用一次，且合并后能利用更好的索引或连接顺序

默认策略：

  • 若 CTE 无副作用（非 VOLATILE 函数）、只被引用一次且结果不大，倾向于 NOT MATERIALIZED。
  • 如果 CTE 被多次引用，或包含 DISTINCT / GROUP BY / 窗口函数等昂贵操作，倾向于 MATERIALIZED。

手动控制：

WITH t AS MATERIALIZED ( SELECT ... ) SELECT * FROM t;      -- 强制物化
WITH t AS NOT MATERIALIZED ( SELECT ... ) SELECT * FROM t;  -- 强制内联

2.2 防范 CTE 物化陷阱

经典陷阱：CTE 产生巨大中间结果，而外部查询只需少量数据，但因 CTE 被物化导致全量计算。

例：查询最近 3 个月有订单的活跃客户详情（与另一张小表关联）。

❌ 错误写法：

WITH recent_orders AS (SELECT * FROM orders WHERE order_date > now() - interval '3 months'   -- 可能很大
)
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM recent_orders ro WHERE ro.customer_id = c.id);
  • 即使 customers 只有少数符合条件，recent_orders 依然会被完整物化（扫描三个月所有订单）。

✅ 优化写法（强制内联或直接使用子查询）：

-- 方法1: 强制 NOT MATERIALIZED (PG 12+)
WITH recent_orders AS NOT MATERIALIZED (SELECT * FROM orders WHERE order_date > now() - interval '3 months'
)
SELECT c.* FROM customers c WHERE EXISTS (...);-- 方法2: 直接用子查询（最可靠）
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.order_date > now() - interval '3 months'
);

判断是否需要物化：

  • 若 CTE 被多次引用 → 通常物化更好（避免重复计算）。
  • 若 CTE 只被引用一次，但内部有昂贵的去重/排序，且外部过滤性极强 → 强制 NOT MATERIALIZED，让优化器将外部条件下推。

2.3 CTE + 窗口函数组合优化

案例：每个类别下取销售额最高的前 5 个产品，且需要关联产品详情表。

WITH ranked_products AS (SELECT p.category_id, p.id, p.name, s.amount,ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY s.amount DESC) AS rnFROM products pJOIN sales s ON p.id = s.product_id
)
SELECT rp.*, c.category_name
FROM ranked_products rp
JOIN categories c ON rp.category_id = c.id
WHERE rp.rn <= 5;

执行计划中可能看到两个关键点：

  1. ranked_products 内先做 Hash Join（product 与 sales）然后排序并计算 ROW_NUMBER()。
  2. 如果 ranked_products 被物化，则其临时结果会被保存，再与 categories 连接。

优化思路：

  • 索引支持：sales(product_id, amount DESC)，products(category_id, id)。
  • 考虑将 categories 的连接移到 CTE 内部吗？不一定，可能会导致 ROW_NUMBER() 在更大数据集上计算。
  • 更佳方案：CTE 内部只产出 (category_id, product_id, amount, rn)，外部再关联类别表，利用物化或内联均可。

三、递归 CTE – 处理树与图

3.1 基础递归模型

递归 CTE 常用于组织层级、BOM 展开、路径枚举等。
结构：锚点成员（非递归） + UNION ALL + 递归成员（引用自身）。

示例：员工层级查询（表 employees(id, manager_id, name)）

WITH RECURSIVE emp_tree AS (-- 锚点：顶层员工SELECT id, name, manager_id, 1 AS level, ARRAY[id] AS pathFROM employees WHERE manager_id IS NULLUNION ALL-- 递归：下属SELECT e.id, e.name, e.manager_id, t.level + 1, t.path || e.idFROM employees eJOIN emp_tree t ON e.manager_id = t.id
)
SELECT * FROM emp_tree;

3.2 递归 CTE 的性能优化

常见瓶颈：

  1. 索引缺失：递归成员中的连接条件 e.manager_id = t.id 必须在 employees(manager_id) 上建立索引，否则每一层都会全表扫描。

    CREATE INDEX idx_emp_mgr ON employees(manager_id);

  2. 循环风险：若图中有环（例如 manager_id 指向已遍历节点），递归会无限循环。解决方法：

    • 使用 path 数组并在递归条件中加入 NOT e.id = ANY(t.path)。
    • 或设置递归深度限制 WHERE level < 20。

  3. 重复访问：对于多路径树（DAG），一个节点可能通过不同路径被多次访问。若需要去重，可以在递归成员中使用 DISTINCT 或最终 GROUP BY，但可能损失性能。可借助临时表或使用 UNION（而非 UNION ALL），但请注意 UNION 会造成额外的排序去重开销。

  4. 控制搜索方向：

    • 自上而下（从根到叶）：使用 manager_id 索引。
    • 自下而上（从叶到根）：需要 id 索引，并且递归成员改为 JOIN ... ON t.manager_id = e.id。

3.3 递归 + 聚合优化

需求：计算每个部门（树）的薪资总和。

WITH RECURSIVE dept_tree AS (SELECT id, name, parent_id, salary_sum FROM departments dWHERE parent_id IS NULLUNION ALLSELECT d.id, d.name, d.parent_id, d.salary_sumFROM departments dJOIN dept_tree t ON d.parent_id = t.id
)
SELECT t.id, t.name, sum(t.salary_sum) OVER (PARTITION BY ...) ...

更好的做法是在递归过程中累积，或使用 WITH ORDINALITY + 路径数组，最后用 GROUP BY 路径根节点。但更推荐使用 ltree 扩展或闭包表。

四、实战案例：用窗口函数 + CTE 重写低效查询

原查询（业务逻辑：每个产品取最近 3 条销售记录，并关联产品名）

❌ 低效版本（使用子查询和 LATERAL）：

SELECT p.id, p.name, recent.*
FROM products p
CROSS JOIN LATERAL (SELECT amount, sale_dateFROM sales sWHERE s.product_id = p.idORDER BY s.sale_date DESCLIMIT 3
) recent;
  • 对每个产品执行一个独立的子查询，性能取决于产品数量，若产品多且无索引，会退化为 Nest Loop 全表扫描。

✅ 优化版本（窗口函数 + CTE + 覆盖索引）：

WITH ranked_sales AS (SELECT product_id, amount, sale_date,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date DESC) AS rnFROM sales
)
SELECT p.id, p.name, rs.amount, rs.sale_date
FROM products p
JOIN ranked_sales rs ON p.id = rs.product_id
WHERE rs.rn <= 3;

索引设计：

CREATE INDEX idx_sales_prod_date ON sales(product_id, sale_date DESC) INCLUDE (amount);
-- 使用 INCLUDE 避免回表读取 amount

执行计划分析：

  • 对 sales 表执行 Index Only Scan Backward，数据已按 (product_id, sale_date DESC) 顺序存储，窗口函数仅需一次扫描，无需额外排序。
  • 然后与 products 表 Hash Join。
  • 运行时间从原查询的 2.4 秒（10 万产品，500 万销售记录）降到 45 毫秒。

五、常见误区与避坑指南

误区 正确做法
滥用 DISTINCT 在 CTE 内部 如果需要去重，优先考虑窗口函数 ROW_NUMBER() OVER (PARTITION BY ...) 再过滤 rn=1，效率更高
认为 CTE 一定是优化屏障 PG 12+ 会根据情况内联，也可以手动 NOT MATERIALIZED
递归 CTE 不加访问限制 务必加最大深度或环路检测，否则可能跑死数据库
窗口函数中 ORDER BY 列无索引 导致显式排序；若不能建索引，考虑减少窗口行数或使用 RANGE 而非 ROWS
LAG 和 LEAD 忽略 PARTITION BY 默认全表排序，除非你确定需要；否则务必加上分区列

六、小结与下期预告

本期回顾

  • 窗口函数：在不改变行数的情况下完成排名、偏移、移动聚合，彻底替代低效的自连接和子查询。优化关键在于建立合适索引（分区列 + 排序列）以消除 Sort。
  • CTE：掌握 MATERIALIZED 与 NOT MATERIALIZED 的语义，避免不必要的物化开销；学会利用 CTE 拆分复杂逻辑的同时保持性能。
  • 递归 CTE：处理层级数据，索引是生命线（递归连接列必须有索引），注意环路和深度控制。
  • 结合案例展示了如何用“窗口函数 + CTE”重构低效的 LATERAL 查询，实现数量级提升。

下期预告

第三期：连接优化与统计信息深度调优

  • 多表连接的顺序与连接方法选择（Nested Loop、Hash Join、Merge Join 背后的代价模型）
  • 统计信息的进阶：ndistinct、MCV、histogram_bounds、correlation 如何影响连接基数估算
  • 手动修正统计信息：ALTER TABLE SET STATISTICS、pg_statistic_ext（扩展统计信息）解决多列相关性问题
  • 实战案例：被低估的 Hash Join 导致临时文件落盘，如何通过调参和 SQL 改写解决

敬请期待！欢迎在评论区留下你在窗口函数或 CTE 上遇到过的诡异性能问题，我们会挑选典型案例在下期前进行补充分析。

SQL 优化没有银弹，唯计划与统计不辜负。 我们第三期见。