窗口函数可将N次表扫描压缩为1次,但需正确使用ORDER BY、PARTITION BY及联合索引,否则性能不升反降;ROWS BETWEEN按物理行滑动,RANGE易因重复值导致结果失真。

直接结论:用 SUM() OVER、ROW_NUMBER() OVER 等窗口函数替代自连接,能把原本 N 次表扫描压成 1 次,前提是 OVER 子句写对、索引跟上。
为什么自连接在统计类查询里特别慢
比如算“每个部门最近7天销售额滚动累计”,传统写法常靠 LEFT JOIN 原表多次,或用相关子查询逐行计算。这类逻辑导致 MySQL 对同一张表反复扫描,执行计划里常见 Using temporary; Using filesort,数据量一过百万,响应就卡顿。
窗口函数不依赖关联,它在单趟扫描中按指定顺序和范围完成计算,避免重复读盘。但这个优势只在写法和索引都到位时才生效。
- 没加
ORDER BY就用ROWS BETWEEN→ 报错This type of clause is not allowed when ORDER BY is not specified -
PARTITION BY dept_id ORDER BY sale_date字段没联合索引 → 触发filesort,性能不升反降 - 日期字段有缺失(如某天无销售记录),用
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW是按物理行滑动,不是自然日,结果会偏移
SUM() OVER 替代滚动求和的正确写法
核心是帧定义必须匹配业务语义,且能走索引:
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 安全写法:
SUM(sales) OVER (ORDER BY sale_date, id ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)—— 补id防止同一天多条记录导致窗口膨胀 - 别用
RANGE:MySQL 的RANGE按值匹配,遇到重复sale_date会把当天所有行全拉进窗口,结果失真 - 真要按日历天数滚动?先用
GENERATE_SERIES()(MySQL 8.0.29+)补全日期,再LEFT JOIN原表,最后套窗口函数
ROW_NUMBER() 替代 TOP-N 过滤时的坑
想查“每个部门薪资前3的员工”,不能直接写 WHERE ROW_NUMBER() OVER (...) ,MySQL 会报错 <code>Window function is not allowed in WHERE clause。
- 必须套一层子查询:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees) t WHERE t.rn -
PARTITION BY dept_id ORDER BY salary DESC必须有对应联合索引(dept_id, salary),否则EXPLAIN里一定出现Using filesort - 别写
ROW_NUMBER() OVER ()—— 全表无序编号,每次执行顺序可能不同,业务不可控
LAG/LEAD 计算环比时 NULL 处理不牢,整列就断
LEAD(sales) - sales 看似简洁,但只要有一行 LEAD() 返回 NULL,整列结果就是 NULL,报表直接空白。
- 稳妥做法:
COALESCE(LEAD(sales), sales)(末期用当期值填充)或COALESCE(LEAD(sales), 0)(末期归零) - 同比场景用
LAG(sales, 12) OVER (ORDER BY year_month),注意year_month类型要是INT或DATE,别混用字符串,否则排序错乱 - 偏移量不写默认是 1,但显式写出来更易维护,比如
LAG(sales, 1) OVER (...)
窗口函数本身不慢,慢的是没索引支撑的 PARTITION BY 和 ORDER BY 字段;真正容易被忽略的,是写完 SQL 后忘了跑 EXPLAIN 确认是否走了索引、有没有 filesort —— 这一步跳过,优化就等于白做。


















