《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》,点击传送门,即可获取!
官方文档MySQL 5.7 Reference Manual中的“2.1.14 ORDER BY Optimization”章节有如下介绍:
GROUP BY implicitly sorts by default (that is, in the absence of ASC or DESC designators for GROUP BY columns). However, relying on implicit GROUP BY sorting (that is, sorting in the absence of ASC or DESC designators) or explicit sorting for GROUP BY (that is, by using explicit ASC or DESC designators for GROUP BY columns) is deprecated. To produce a given sort order, provide an ORDER BY clause.
google翻译
:默认情况下GROUP BY隐式排序(即,缺少GROUP BY列的ASC或DESC指示符)。但是,不推荐依赖于隐式GROUP BY排序(即,在没有ASC或DESC指示符的情况下排序)或GROUP BY的显式排序(即,通过对GROUP BY列使用显式ASC或DESC指示符)。要生成给定的排序 ORDER,请提供ORDER BY子句。
从MySQL 8.0开始,GROUP BY字段不再支持隐式排序. 官方文档MySQL 8.0 Reference Manual中“8.2.1.16 ORDER BY Optimization”章节有如下介绍:
Previously (MySQL 5.7 and lower), GROUP BY sorted implicitly under certain conditions. In MySQL 8.0, that no longer occurs, so specifying ORDER BY NULL at the end to suppress implicit sorting (as was done previously) is no longer necessary. However, query results may differ from previous MySQL versions. To produce a given sort order, provide an ORDER BY clause.
google翻译
:以前(MySQL 5.7及更低版本),GROUP BY在某些条件下隐式排序。 在MySQL 8.0中,不再发生这种情况,因此不再需要在末尾指定ORDER BY NULL来抑制隐式排序(如前所述)。
但是,查询结果可能与以前的MySQL版本不同。 要产生给定的排序顺序,请提供ORDER BY子句
陈哈哈:“哦,这么看来开发老版本的同事是没用Order by,直接用了隐式排序。年轻人,不讲武德啊!!”
小王(小声):“哈哥,这模块之前好像是你负责的。”
陈哈哈(老脸一红):???
陈哈哈:“咳咳,这MySQL8.0团队不讲武德,给我挖坑!”
好了,接下来我们用测试数据演示一下
下面是表T测试数据,无序
mysql> SELECT pid,appName from T;
±-------±------------------------+
| pid | appName |
±-------±------------------------+
| 1 | Dock Sound Redirector |
| 2 | Blues Music station |
| 3 | usb tether TRIAL |
| 4 | Il vero test del QI |
| 5 | FlightTime Calculator |
| 6 | ZX Spectrum Emulator |
| 7 | The City Dress Up |
±-------±------------------------+
7 rows in set (0.00 sec)
实验1:(MySQL版本:5.7.24)
– 隐式排序
mysql> SELECT pid,appName from T group by appName;
±-------±------------------------+
| pid | appName |
±-------±------------------------+
| 2 | Blues Music station |
| 1 | Dock Sound Redirector |
| 5 | FlightTime Calculator |
| 4 | Il vero test del QI |
| 7 | The City Dress Up |
| 3 | usb tether TRIAL |
| 6 | ZX Spectrum Emulator |
±-------±------------------------+
7 rows in set (0.00 sec)
– 如上述隐式排序,相当于SELECT pid,appName from T group by appName asc 或 SELECT pid,appName from T group by appName order by appName asc;
– 显式排序,相当于SELECT pid,appName from T group by appName order by appName desc;
mysql> SELECT pid,appName from T group by appName desc;
±-------±------------------------+
| pid | appName |
±-------±------------------------+
| 6 | ZX Spectrum Emulator |
| 3 | usb tether TRIAL |
| 7 | The City Dress Up |
| 4 | Il vero test del QI |
| 5 | FlightTime Calculator |
| 1 | Dock Sound Redirector |
| 2 | Blues Music station |
±-------±------------------------+
7 rows in set (0.00 sec)
实验2:(MySQL版本:8.0.16)
mysql> SELECT pid,appName from T group by appName;
±-------±------------------------+
| pid | appName |
±-------±------------------------+
| 1 | Dock Sound Redirector |
| 2 | Blues Music station |
| 3 | usb tether TRIAL |
| 4 | Il vero test del QI |
| 5 | FlightTime Calculator |
| 6 | ZX Spectrum Emulator |
| 7 | The City Dress Up |
±-------±------------------------+
7 rows in set (0.00 sec)
mysql> SELECT pid,appName from T group by appName DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘DESC’ at line 1
如上所示,在MySQL 8.0中,GROUP BY隐式排序不支持了,上面测试例子是无序的。GROUP BY显示排序则直接报错。所以如果有数据库从MySQL 5.7或之前的版本,迁移升级到MySQL 8的话,就需要特别留意这个问题了。
====================================================================================
最初为什么要用隐式排序呢?我们知道,要对一组数据进行分组,MySQL优化器会选择不同的方法。其中最有效的一种是分组之前对数据排序,降低数据复杂度,使得连续分组变得很容易。另外,如果可以Group by 一个索引字段来用于获取排序的数据,那么使用它的成本就非常低了(因为BTree索引是天然有序的)
。而在实际操作中,Group by用到索引的频率很高。这么看,这确实是个很棒的主意!也可以说是留了一个优美的BUG
。
如下查询语句,用到了appName_idx
索引,因此group by查询不需要排序,直接分组,高效。
– 有索引:appName_idx
mysql> EXPLAIN SELECT appName from 0122_csj_demo GROUP BY appName \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: 0122_csj_demo
partitions: NULL
type: index
possible_keys: appName_idx
key: appName_idx
key_len: 515
ref: NULL
rows: 28
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
如果没有索引,MySQL优化器仍然可以决定在分组之前用外部临时表进行filesort
排序,从效率上讲,和无序分组差不多。当用户指定Order by时,是MySQL最希望看到的,这样就不会让排序工作白费,这也是让MySQL团队始终默认隐式排序存在的原因之一。
mysql> EXPLAIN SELECT appName from 0122_csj_demo GROUP BY appName \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: 0122_csj_demo
partitions: NULL
type: ALL
最后
学习视频:
大厂面试真题:
《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》,点击传送门,即可获取!
ect_type: SIMPLE
table: 0122_csj_demo
partitions: NULL
type: ALL
最后
学习视频:
[外链图片转存中…(img-rIADaWq1-1714426037422)]
大厂面试真题:
[外链图片转存中…(img-GlvBZhg4-1714426037423)]
《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》,点击传送门,即可获取!