MySQL 8.0窗口函数求均值与Join预求均值的性能差异对比

背景信息 #

使用MySQL 8.0，成绩表sc的字段包含student_id（对应表中SId）、course_id（对应表中CId）、score。测试数据的建表与插入语句如下：

create table sc(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into sc values('01' , '01' , 80);
insert into sc values('01' , '02' , 90);
insert into sc values('01' , '03' , 99);
insert into sc values('02' , '01' , 70);
insert into sc values('02' , '02' , 60);
insert into sc values('02' , '03' , 80);
insert into sc values('03' , '01' , 80);
insert into sc values('03' , '02' , 80);
insert into sc values('03' , '03' , 80);
insert into sc values('04' , '01' , 50);
insert into sc values('04' , '02' , 30);
insert into sc values('04' , '03' , 20);
insert into sc values('05' , '01' , 76);
insert into sc values('05' , '02' , 87);
insert into sc values('06' , '01' , 31);
insert into sc values('06' , '03' , 34);
insert into sc values('07' , '02' , 89);
insert into sc values('07' , '03' , 98);

需求 #

输出所有成绩记录，同时展示每个学生的平均分，并按平均分降序排序。

两种解决方案 #

方案1：窗口函数实现 #

-- Solution 1
SELECT
  sc.*,
  AVG(score) OVER (PARTITION BY sid) AS avg_score
FROM sc
ORDER BY avg_score DESC

方案2：子查询分组+左连接实现 #

-- Solution 2
select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )r 
on sc.sid = r.sid
order by avscore desc;

性能差异分析（大表场景） #

结合提供的EXPLAIN执行计划结果，两种方案在数据量极大时的性能差异非常明显：

1. 表扫描次数 #

• 方案1仅需扫描一次sc表：窗口函数在扫描表的过程中，直接按sid分组计算平均分，无需额外的表访问。
• 方案2需要扫描两次sc表：子查询阶段扫描全表做分组聚合，主查询阶段再次扫描全表与子查询结果做左连接。数据量越大，两次扫描的IO开销差距越显著。

2. 临时表与内存开销 #

• 方案1无需额外存储中间结果：窗口函数的分组计算可以在内存中实时完成，除非分组基数（学生数量）极大到超出内存限制，否则不会产生磁盘临时表。
• 方案2会生成临时表存储子查询的聚合结果：当学生数量众多时，临时表可能会写入磁盘，增加磁盘IO开销；后续的左连接操作也需要基于临时表做匹配，进一步提升内存与IO压力。

3. 排序效率 #

两者都需要按平均分降序排序，但方案1的avg_score是在表扫描阶段直接计算完成的，排序时仅需基于已生成的字段操作；方案2的avscore来自子查询，连接后的数据量与原表一致，但排序阶段需要处理包含连接字段的全量数据，开销更高。

4. 索引优化的影响 #

若为sid建立索引：

• 方案1可直接利用索引按sid顺序扫描，避免分组时的排序操作，进一步降低开销。
• 方案2的子查询可利用索引加速分组，但连接阶段仍需匹配主表的sid，整体仍比方案1多一次表扫描，性能差距依然存在。

结论 #

在数据量极大的场景下，方案1（窗口函数）的性能显著优于方案2（子查询+左连接）。它通过减少表扫描次数、避免额外的连接与临时表开销，大幅提升了执行效率。

内容的提问来源于stack exchange，提问作者union77