如何在MySQL 8.0中使用窗口函数避免自连接性能损耗

小静同学_8959

小静同学_8959

2026-11-17

923人浏览

原创

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

如何在mysql 8.0中使用窗口函数避免自连接性能损耗

直接结论:用 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 替代滚动求和的正确写法

核心是帧定义必须匹配业务语义,且能走索引:

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 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 会报错 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 —— 这一步跳过,优化就等于白做。

相关文章

数码产品性能查询
数码产品性能查询

该软件包括了市面上所有手机CPU,手机跑分情况,电脑CPU,电脑产品信息等等,方便需要大家查阅数码产品最新情况,了解产品特性,能够进行对比选择最具性价比的商品。

下载

相关标签:

mysql 窗口函数 mysql 8.0

本站声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5063

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1329

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7141

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3783

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7120

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

9981

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1350

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1172

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程