全部栏目

首页 操作系统 后端开发 数据库 前端开发 移动端开发
您的当前位置：首页正文

【MySQL】分组排序取每组第一条数据

2024-10-23 来源：个人技术集锦

需求：MySQL根据某一个字段分组，然后组内排序，最后每组取排序后的第一条数据。

准备表：

CREATE TABLE `t_student_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `stu_name` varchar(32) NOT NULL COMMENT '学生姓名',
  `course_name` varchar(32) NOT NULL COMMENT '课程名称',
  `score` int(11) NOT NULL COMMENT '份数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生-分数';

准备数据：

INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (1, '张三', '数学', 90);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (2, '李四', '语文', 94);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (3, '张三', '语文', 98);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (4, '李四', '数学', 97);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (5, '李四', '英语', 99);
INSERT INTO `t_student_score` (`id`, `stu_name`, `course_name`, `score`) VALUES (6, '张三', '英语', 100);

数据如下：

mysql> select * from t_student_score;
+----+----------+-------------+-------+
| id | stu_name | course_name | score |
+----+----------+-------------+-------+
|  1 | 张三     | 数学        |    90 |
|  2 | 李四     | 语文        |    94 |
|  3 | 张三     | 语文        |    98 |
|  4 | 李四     | 数学        |    97 |
|  5 | 李四     | 英语        |    99 |
|  6 | 张三     | 英语        |   100 |
+----+----------+-------------+-------+
6 rows in set (0.08 sec)

要求：查询出各科分数最高的学生姓名。

group by

查询出各科分数最高的学生姓名一开始可能会这样写：

select stu_name,course_name,max(score) from t_student_score group by course_name;

sql中只是简单的按课程进行分组，这样写就会导致一个问题也就是查询出来的各科最高分数可能不是那个学生的，结果如下：

mysql> select stu_name,course_name,max(score) from t_student_score group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 张三     | 数学        |         97 |
| 李四     | 英语        |        100 |
| 李四     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.05 sec)

很明显数学得97分的压根就不是张三，这是为什么呢，group by后的显示的列会只会根据所有组的第一行来显示，张三刚好在数学组的第一行，所以出来的是张三。

group by+子查询order by

既然我们知道group by后的显示的列会只会根据所有组的第一行来显示，那么我们先根据分数进行排序，这样分数最高的肯定是所有组的第一行，然后根据课程进行分组这样是不是就对了？

mysql> select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 张三     | 数学        |         97 |
| 李四     | 英语        |        100 |
| 李四     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.13 sec)

什么情况，以前我怎么记得这么使用是对的呢？然后去查看SQL的执行计划：

mysql> explain select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_student_score | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set (0.06 sec)

执行计划显示只有一个步骤，为什么不是分为两个步骤执行呢？第一步先根据表t_student_score的score字段进行倒序排序，第二步根据第一步生成的临时表t的course_name字段进行分组？？？

而在MySQL5.6中，执行上面的sql会出现不一样的结果：

mysql> select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 李四     | 数学        |         97 |
| 张三     | 英语        |        100 |
| 张三     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.10 sec)

MySQL5.6中返回的结果正是我们想要的。

再来看下MySQL5.6中这个SQL的执行计划：

mysql> explain select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | PRIMARY     | <derived2>      | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using temporary; Using filesort |
|  2 | DERIVED     | t_student_score | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using filesort                  |
+----+-------------+-----------------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.09 sec)

MySQL5.6中这个SQL的执行计划分为两个步骤执行的。

那么为什么切换了版本后就好了呢？

derived_merge

MySQL5.7针对于5.6版本做了一个优化，针对MySQL本身的优化器增加了一个控制优化器的参数叫derived_merge，什么意思呢，“派生类合并”。

官方文档介绍：https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html

使用合并或实现来优化派生表和视图引用优化器可以使用两种策略(也适用于视图引用)处理派生表引用:

  • 将派生表合并到外部查询块中
  • 将派生表实现为内部临时表

例如：

SELECT * FROM (SELECT *FROM t1) AS derived_t1

通过合并派生表derived_t1，该查询的执行类似于：

SELECT * FROM t1;

原来是派生类合并在作怪，通过对MySQL官方使用手册的了解，MySQL5.7对derived_merge参数默认设置为on，也就是开启状态，我们在MySQL5.7中把这个特性关闭使用就行了，如下命令：

# 针对当前session关闭
set session optimizer_switch="derived_merge=off";

# 全局关闭
set global optimizer_switch="derived_merge=off";

这样如果from中查询出来的的结果就不会与外部查询块合并了，sql执行结果如下：

mysql> set session optimizer_switch="derived_merge=off";
Query OK, 0 rows affected (0.01 sec)

mysql> select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----------+-------------+------------+
| stu_name | course_name | max(score) |
+----------+-------------+------------+
| 李四     | 数学        |         97 |
| 张三     | 英语        |        100 |
| 张三     | 语文        |         98 |
+----------+-------------+------------+
3 rows in set (0.07 sec)

mysql> explain select stu_name,course_name,max(score) from (select * from t_student_score order by score desc) t group by course_name;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2>      | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | t_student_score | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort                  |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
2 rows in set (0.10 sec)

其实修改derived_merge参数得谨慎而行之，因为MySQL5.7版本有了这个优化的机制是有它的道理的，之所以去除派生类与外部块合并，是因为减少查询开销，派生类是个临时表，开辟一个临时表的同时还要维护和排序或者分组，都会影响效率，所以尽量不要去修改此参数。

其实也有多种办法不需要修改derived_merge参数而使合并派生类失效，具体做法可参考官方使用手册，可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响并不明确。

防止合并的构造对于派生表和视图引用是相同的：

  • 聚合函数（SUM()，MIN()，MAX()，COUNT()等）
  • DISTINCT
  • GROUP BY
  • HAVING
  • LIMIT
  • UNION或UNION ALL
  • 选择列表中的子查询
  • 分配给用户变量
  • 仅引用文字值（在这种情况下，没有基础表）

下面通过在子查询中使用distinct关键字来禁用derived_merge：

mysql> explain select stu_name,course_name,max(score) from (select distinct(id) tid,s.* from t_student_score s order by score desc) t group by course_name;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | s          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort                  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
2 rows in set (0.08 sec)

子查询order by失效的场景

因为临时表（派生表derived table）中使用order by且使其生效，必须满足三个条件：

  • 外部查询禁止分组或者聚合
  • 外部查询未指定having， order by
  • 外部查询将派生表或者视图作为from句中唯一指定源

不满足这三个条件，order by会被忽略。

一旦外部表使用了group by，那么临时表（派生表 derived table）将不会执行filesort操作（即order by 会被忽略）。
显示全文