【MySQL】分組排序取每組第一條數據

需求：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)