2025年mysql窗口函数排序（mysql5.7窗口函数）

大家好，我是讯享网，很高兴认识大家。

一、什么是窗口函数

二、用窗口函数实现分组内排序

三、基于窗口函数的高效分页批处理方案

一、什么是窗口函数

窗口函数(Window Function)又叫开窗函数，是一种常见的 OLAP 函数，与聚合函数不同，窗口函数可以按多个维度分别做排序，简化了复杂分析场景的 SQL 逻辑。常见的单机数据库一般都支持窗口函数，TiDB v3.0，MySQL 8.0 版本也开始支持窗口函数功能。

二、用窗口函数实现分组内排序

分组并对组内排序是使用窗口函数的常见场景。

首先我们制作一张学生成绩表，包含学生姓名，学号，科目，以及科目成绩字段，并写入一些数据：

业务需求 1：计算出每科成绩的前两名的姓名、学号和成绩

这是一个难以用聚合函数实现的需求，由于长期不支持窗口函数，MySQL 社区普遍推荐使用用户变量的方式来实现，具体实现方式如下：

26 rows in set (0.01 sec)

通过定义两个用户变量，一个用于切换到下一组，另一个用来发放行号，以此来通过嵌套循环的方式来实现为每组单独发放行号。缺点是不能处理相同分数名次并列的情况，并且嵌套太多，逻辑比较复杂，每次计算都要为变量重新赋值。

来看一下窗口函数的实现方式，仅需要一条 SQL，一个子查询就可以得出各科成绩的前两名，注意这里使用的 rank() 函数可以识别相同分数名次并列的情况，也就是说假如一科出现了两人并列第一，使用下面的 SQL 可以公平的把并列第一的情况展现出来，这是用户变量难以实现的。

业务需求 2：计算出每科成绩第一名与第二名之间的分差

TiDB 提供 lead() 与 lag() 函数来获取组内数据排序后的下一行或上一行的列值，此处正是使用了 lead() 函数来获取下一行的列值，通过子查询的方式即可计算出第一名与第二名之间的分差：

三、基于窗口函数的高效分页批处理方案

窗口函数作为数据库的高级分析功能，它的应用场景不仅限于分组内排序，我们还可以利用窗口函数做很多有意思的事情，比如本案例用窗口函数来大幅优化跑批中的分页处理效率。

我们用 sysbench 创建一张表并加载一些数据，用这张表来模拟批量处理逻辑。

首先初始化一张表 sbtest1，其表结构如下，其中 id 字段为整型主键：

初始化时加载了 100 万行数据，之后我们删除掉其中一部分，通过这样的方式使 id 值不再连续，弱化分页时对于 id 值的依赖。当前表中剩余数据有 90 万行左右：

表内数据预览：

6 rows in set (0.01 sec)

常见的分页更新 SQL 一般使用主键/唯一索引进行排序，以确保相邻的两页之间没有空隙或重叠，配合 MySQL limit 语法中非常好用的 offset 功能来按固定行数拆分页面，拆分后的页面被包装在独立的事务中，可以灵活的进行逐页或批量对数据进行更新。

begin;

update sbtest1 set pad=‘new_value’ where id in (select id from sbtest1 order by id limit 0,10000);

commit;

begin;

update sbtest1 set pad=‘new_value’ where id in (select id from sbtest1 order by id limit 10000,10000);

commit;

begin;

update sbtest1 set pad=‘new_value’ where id in (select id from sbtest1 order by id limit 20000,10000);

commit;

这种方案逻辑清晰，SQL 易于编写，但它有着明显的劣势，由于需要对主键/唯一索引进行排序，越靠后的页面需要参与排序的行数越多，TiKV 中扫描数据的压力也越大，批量整体处理效率就越低，当批量的整体数据量比较大时，很可能会占用过多计算资源，甚至触发性能瓶颈，影响联机业务。

下面案例是一种改进方案，通过灵活运用窗口函数 row_number() 将数据按照主键排序后赋予行号，再通过聚合函数按照设置好的页面大小对行号进行分组，以计算出每页的最大值和最小值。

将这个结果集作为批量处理的元信息，这样在批量处理阶段只需要使用 between…and… 来圈定好每个页面的数据，多个页面并发的进行批量更新即可，由于元信息的计算阶段使用主键/唯一索引进行排序，并用 row_number() 函数赋予了唯一序号，因此也可以避免在两个相邻的页面中出现空隙或重叠。

使用这种方案可以显著避免由于频繁，大量的排序造成的性能损耗，进而大幅提升批量处理的整体效率。

四、复合主键分页案例

制作元信息表

操作分页的案例

另外可以使用隐藏字段 _tidb_rowid 做分页使用。