?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
  
MySQL5.7关于order by之后再进行group by 发现order by失效问题(缺少DERIVED)
 
 
# 1. 问题说明: 运行sql,创建测试表,具体说明问题原因
CREATE TABLE `a`  (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`created_time` datetime(0) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
 
INSERT INTO `a` VALUES (1, '小明''2018-09-10 14:08:49');
INSERT INTO `a` VALUES (2, '小小''2018-09-10 14:08:49');
INSERT INTO `a` VALUES (3, '小明''2018-09-10 14:09:34');
INSERT INTO `a` VALUES (4, '小小''2018-09-10 14:09:34');
 
# 2. 执行问题sql
mysql> SELECT * FROM (SELECT * FROM a ORDER BY created_time desc) L GROUP BY username;
+----+----------+---------------------+
| id | username | created_time        |
+----+----------+---------------------+
|  2 | 小小     | 2018-09-10 14:08:49 |
|  1 | 小明     | 2018-09-10 14:08:49 |
+----+----------+---------------------+
2 rows in set (0.04 sec)
 
# 3. 添加limit绕过问题,执行sql
mysql> SELECT * FROM (SELECT * FROM a ORDER BY created_time desc LIMIT 10000) L GROUP BY username;
+----+----------+---------------------+
| id | username | created_time        |
+----+----------+---------------------+
|  4 | 小小     | 2018-09-10 14:09:34 |
|  3 | 小明     | 2018-09-10 14:09:34 |
+----+----------+---------------------+
2 rows in set (0.06 sec)
 
//对比两次结果,发现没添加limit的sql执行查询结果后的created_time并不是我们想要的.下面我们来执行EXPLAIN,寻找一下具体原因
 
# 4. 分析排序失效的sql
mysql> EXPLAIN SELECT * FROM (SELECT * FROM a ORDER BY created_time desc) L GROUP BY username;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set (0.03 sec)
 
# 5. 分析可以正常排序的sql
mysql> EXPLAIN SELECT * FROM (SELECT * FROM a ORDER BY created_time desc LIMIT 10000) L GROUP BY username;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using temporary; Using filesort |
|  2 | DERIVED     | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort                  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
2 rows in set (0.03 sec)
 
//相比第二条sql,第一条sql缺少了DERIVED派生表,应该是5.7被优化了
 
  


---------------------------------------------------------------------------------------------
不忘初心 方得始终!

唯有志存高远,方能风行天下。

道之所存,虽千万人吾往矣! 情之所钟,虽千万里吾念矣~

原创作品,允许转载,转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。