关于java:新来的领导下令升级-MySQL-80完美掉坑…

8次阅读

共计 5756 个字符，预计需要花费 15 分钟才能阅读完成。

你在应用 MySQL 的 Group by 分组时，是否发现分组后的数据都是有序的？

其实，在 MySQL8.0 版本前，优化器在分组查问时都会进行隐式排序。

那既然隐式排序为什么还要保留 Order by？隐式排序的目标又是什么呢？让咱们一起来看看。

年前咱们换了领导。

俗话说，新官上任干总爱干傻事儿，这不，领导要拥抱新事物，要求咱们更新我的项目 MySQL 版本，从 MySQL5.7 更新到 MySQL8.0。

不知是 MySQL5.7 不香了，还是领导你眼光高了？

我把这个工作交给共事小王，小王不以为然，说换就换。迁完库，在代码根本不改的状况下自信上线。上线后却发现本来一些有序的列表变无序了，最初长期回退了版本。

核查代码时咱们发现，老版本 Select 语句中只是用到了 Group by 分组，也没有用到 order by 排序，有点蒙，为啥没用 order by 却排了序？

查资料后得悉，在 MySQL8.0 版本前是存在 Group by 隐式排序的！

就是说在咱们应用分组（Group by)时，如：select * from T group by appName; 会默认依照 appName 正序排序，相当于 select * from T group by appName order by appName;，倒排同理：select * from T group by appName desc。

可见，MySQL 在 8.0 版本前的分组查问中，偷偷加上了排序操作。

纳尼？MySQL 还有这种操作？快找一下官网文档对 Group by 隐式排序的介绍：

官网文档 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.

翻译：

默认状况下 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.

翻译：

以前（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
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 28
     filtered: 100.00
        Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

另外，用户能够显式指定 ORDER BY NULL 就能让 MySQL 晓得 GROUP BY 不须要排序。因而须要一个非标准（ORDER BY NULL）语法来对消另一个非标准扩大（GROUP BY 排序）的影响。

mysql> EXPLAIN SELECT appName from 0122_csj_demo GROUP BY appName ORDER BY null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: 0122_csj_demo
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 28
     filtered: 100.00
        Extra: Using temporary
1 row in set, 1 warning (0.00 sec)

为了解决这个柔美的 BUG，MySQL 团队在 8.0 版本引入了倒排索引。正负向索引排序的优化思路，给隐式排序体面的落下帷幕。自此 Group by 隐式排序功能被删除，分组排序必须用 order by 来进行，分组的算法仍然能够基于正负向索引连续之前分组的高效性。

好了，本文到此根本完结，隐式排序算是 MySQL 角落里较冷门的知识点，对我来说却是一位结识四年的旧友了。北漂四年，时光匆匆，从初识 MySQL 的寸步难行，到深刻了解各知识点的实现思路，也算顺道吃了杯隐排的践行酒。

莫泊桑说：“生存可能不像你设想的那么好，然而，也不会你设想的那么糟”。人的软弱和刚强都超乎了本人的想，有时候可能软弱的一句话，就泪流满面。有时候你会发现，本人咬着牙走过很长的一段路。在外流浪打工人不易，为了家人父母过上好日子，加油！

原文链接：https://blog.csdn.net/qq_3939…

版权申明：本文为 CSDN 博主「_陈哈哈」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿(2021 最新版)

2. 终于靠开源我的项目弄到 IntelliJ IDEA 激活码了，真香！

3. 阿里 Mock 工具正式开源，干掉市面上所有 Mock 工具！

4.Spring Cloud 2020.0.0 正式公布，全新颠覆性版本！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！

正文完

java

发表至： java

2021-07-09

0

如果可能你一定要掌握数据

关于java:深入底层｜JVM源码解读HotSpot的模板解释器

关于java:Java-项目中使用-Resilience4j-框架实现异步超时处理

Map学习一之基本遍历元素的方法一

关于harmonyos:37HarmonyOS鸿蒙开发组件Switch

关于java:新来的领导下令升级-MySQL-80完美掉坑…

一、背景小故事

官网文档

数据测试

二、隐式排序 – 起源（一个柔美的 BUG）

三、隐式排序 – 宿命

站内搜索