如何利用MySQL 8.0的窗口函数优化原本复杂的统计报表查询？-mysql教程-PHP中文网

row_number()比子查询加join更快，因其单次扫描完成排序与编号，避免重复扫描；而子查询需对每行执行独立求值，复杂度近o(n²)，且易触发全表扫描或磁盘临时表。

如何利用mysql 8.0的窗口函数优化原本复杂的统计报表查询？

为什么 `ROW_NUMBER()` 比子查询加 `JOIN` 更快？

因为窗口函数在单次扫描中完成排序和编号，避免了自连接或相关子查询带来的重复扫描。比如统计每个部门薪资前3的员工，用子查询要对每行执行一次 SELECT COUNT(*) 对比，复杂度接近 O(n²)；而 ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) 是单趟扫描 + 内存排序，MySQL 8.0 的优化器还能利用索引加速 PARTITION BY 和 ORDER BY 字段。

实操建议：

确保 PARTITION BY 和 ORDER BY 字段上有联合索引，例如 (dept_id, salary)，否则窗口函数会触发 filesort
避免在 WHERE 中引用窗口函数结果（如 WHERE rn ），必须套一层子查询或 CTE，否则报错 <code>Window function is not allowed in WHERE clause
ROW_NUMBER()、RANK()、DENSE_RANK() 行为不同：相同薪资时，前者强制唯一编号，后两者并列不跳号，选哪个取决于业务对“第3名是否允许并列”的定义

`SUM() OVER (ROWS BETWEEN ...)` 怎么替代自连接算滚动求和？

传统方式用关联查询计算最近7天销售额累计，需要 LEFT JOIN 原表多次，SQL 膨胀且难以维护；窗口函数用 SUM(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 一行搞定，语义清晰，执行计划也更可控。

注意点：

ROWS BETWEEN 必须配合 ORDER BY 使用，否则 MySQL 报错 This type of clause is not allowed when ORDER BY is not specified
如果日期有缺失（比如某天没销售记录），ROWS 是按物理行数滑动，不是按日历天数；真要按自然日滚动，得先用 GENERATE_SERIES（MySQL 8.0.29+）补全日期，再 LEFT JOIN
RANGE 关键字在 MySQL 8.0 中仅支持 UNBOUNDED 或 CURRENT ROW，不能写 RANGE BETWEEN INTERVAL 7 DAY PRECEDING，别被 PostgreSQL 文档误导

用 `LAG()` / `LEAD()` 计算环比时，NULL 怎么处理最安全？

直接 LEAD(sales) - sales 会导致整列变 NULL（只要任意一行 LEAD() 为空，减法结果就是 NULL），报表就断了。正确做法是显式用 COALESCE(LEAD(sales), 0) 或更合理的 COALESCE(LEAD(sales), sales)（假设末期数据用当期值填充）。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

关键细节：

LAG() 默认取前1行，但可以指定偏移量，比如 LAG(sales, 12) OVER (ORDER BY year_month) 算同比，注意字段类型要一致（别拿 VARCHAR 年月去减）
分区边界处（如每个部门首行）的 LAG() 返回 NULL，不是 0，这是设计行为，不是 bug
如果原始数据本身含 NULL，LAG() 会照常返回 NULL，无法区分是“无上期数据”还是“上期值就是 NULL”，业务上需提前清洗或加标记字段

CTE + 窗口函数嵌套太多层，性能反而下降？

MySQL 8.0 对 CTE 的物化策略较保守，多层嵌套（比如 CTE A → CTE B → SELECT + 窗口）可能让优化器放弃下推条件，导致全表扫描。不如把逻辑压平：用单个 SELECT 套多个窗口函数，共享同一份 OVER 子句。

例如同时要编号、累计、环比，别写三层 CTE，直接：

SELECT
  id,
  sales,
  ROW_NUMBER() OVER w AS rn,
  SUM(sales) OVER w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS cumsum,
  sales - LAG(sales) OVER w AS mom_diff
FROM t_sales
WINDOW w AS (PARTITION BY region ORDER BY dt);

这样 MySQL 只做一次分区排序，复用中间结果。另外，WINDOW 子句命名（如 w）能大幅降低出错率——手写三遍 (PARTITION BY region ORDER BY dt) 很容易漏掉一个括号或写错字段名。

真正难的是理解数据分布对窗口行为的影响：比如 PARTITION BY user_id 但某用户有 50 万条记录，内存排序可能溢出到磁盘，这时得考虑加 LIMIT 或分批处理，而不是硬扛。