专栏首页Java技术栈新来的领导下令升级 MySQL 8.0,完美掉坑…

新来的领导下令升级 MySQL 8.0,完美掉坑…

你在使用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的话,就需要特别留意这个问题了。

点击关注公众号,Java干货及时送达

二、隐式排序 - 起源(一个优美的BUG)

最初为什么要用隐式排序呢?

我们知道,要对一组数据进行分组,MySQL优化器会选择不同的方法。其中最有效的一种是分组之前对数据排序,降低数据复杂度,使得连续分组变得很容易。

另外,如果可以Group by 一个索引字段来用于获取排序的数据,那么使用它的成本就非常低了(因为BTree索引是天然有序的)。而在实际操作中,Group by用到索引的频率很高。这么看,这确实是个很棒的主意!也可以说是留了一个优美的BUG。

如下查询语句,用到了appName_idx索引,因此group by查询不需要排序,直接分组,高效。

推荐阅读:MySQL数据库开发的 36 条军规

-- 有索引: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的步履维艰,到深入理解各知识点的实现思路,也算顺道吃了杯隐排的践行酒。

莫泊桑说:“生活可能不像你想象的那么好,但是,也不会你想象的那么糟”。人的脆弱和坚强都超乎了自己的想,有时候可能脆弱的一句话,就泪流满面。

有时候你会发现,自己咬着牙走过很长的一段路。在外漂泊打工人不易,为了家人父母过上好日子,加油!另外,关注公众号Java技术栈,在后台回复:面试,可以获取我整理的 MySQL 系列面试题和答案,非常齐全。

本文分享自微信公众号 - Java技术栈(javastack)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2021-07-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 数据库升级

    今天晚上去看服务器,发现数据库的版本是5.7的,看起来挺新的。但是MySQL已经出了8.0了,受不了心中的渴望,所以就直接把源切到8.0新版本了。中国有一些坑,...

    魔王卷子
  • 从库mysqldump会导致复制中断

    ERROR NO 是1756,而且只是 Slave_SQL_Running 停了。

    老叶茶馆
  • JOOX Music & targetversion25 爬坑指南

    文章提到的问题都是一点一点慢慢爬出来的,虽然说网上的适配文章很多,但是大部分内容都是相同的,当遇到一些比较偏的问题只能说是自己慢慢爬了,写这篇文章也是为了方便大...

    IBG JOOX
  • MySQL 8.0与MariaDB 10.4,谁更易于填坑补锅?

    贺春旸,凡普金科DBA团队负责人,《MySQL管理之道:性能调优、高可用与监控》第一、二版作者,曾任职于中国移动飞信、安卓机锋网。致力于MariaDB、Mong...

    jeanron100
  • Python第十二章-多进程和多线程02-多线程

    MySQL被Sun收购后,搞了个过渡的6.0版本,没多久就下线了(有一次居然听说有人在线上用6.0版本,我惊得下巴都掉了)。被Oracle收购后,终于迎来了像样...

    不会飞的小鸟
  • MySQL 8.0.22 源码编译安装全过程

    墨墨导读: Mysql的8.0版本出来已经有一段时间了,近期研究下源码调试。整个编译过程越来越复杂了。

    数据和云
  • Intel PAUSE指令变化影响到MySQL的性能,该如何解决?

    MySQL得益于其开源属性、成熟的商业运作、良好的社区运营以及功能的不断迭代与完善,已经成为互联网关系型数据库的标配。可以说,X86服务器、Linux作为基础设...

    美团技术团队
  • Facebook将MySQL升级至8.0

    Facebook 使用了大量的MySQL以支持他们最重要的工作。并且他们积极开发了许多MySQL 中的新功能,以支持不断发展的需求。这些更改特性发生在 MyS...

    MySQLSE
  • MySQL 8.0 数据字典有哪些变化?

    墨墨导读:MySQL8.0 数据字典(Data Dictionary)也在进化中。MyISAM系统表全部换成InnoDB表 ,支持原子DDL。复杂度增加了。考虑...

    数据和云
  • SonarQube升级踩坑记录

    目前使用的是SonarQube 6.7,已经有超过100个项目在使用。近期开发同学反馈,IDEA+SonarLint结合使用非常好用,可以在代码编写和问题产生的...

    Antony
  • 汇总:MySQL 8.0 运维便捷命令

    墨墨导读:有人说目前为止8.0是最好的版本,我们来看看在运维方面MySQL 8.0带来了哪些便捷命令。

    数据和云
  • MySQL 8.0.11 (2018-04-19, General Availability)

    仅支持通过使用 in-place 方式从 MySQL 5.7 升级到 MySQL 8.0 升级; 不支持从 MySQL 8.0 降级到 MySQL 5....

    MySQL轻松学
  • 鹅厂DB的硬件新探索

    再精妙的程序,最终都要由计算机系统的底层来承载,这个底层就是由各种真实可见的材料组成的硬件,而硬件技术的不断发展也是数据库系统演进的持续推动力。 近些年,在互...

    腾讯云数据库 TencentDB
  • Ken的杂谈从ASP.NET Core 3.1迁移到5.0

    2016年,微软发布了 .NET Core 1.0,作为 .NET Framework的跨平台版本,并抽象除了 .NET Standard 作为公共的类库,自此...

    KenTalk
  • MySQL 8 第一个正式版发布:更好的性能

    MySQL 8.0 系列的首个正式版 8.0.11 已发布,官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍,还带来了大量的改进和更快的性能!

    Debian中国
  • 手把手教你在centos上安装MySQL(避坑必备)

    在上篇文章中手把手教你在centos上配置Django项目(超详细步骤) 已经非常详细的讲解了centos部署Django的过程。

    Python进击者
  • Nacos持久化mysql8.0(Linux CentOS)

    Nacos是这两天才开始学的,自己的阿里云数据库是8.0的版本,Nacos是GitHub上拉的最新版本1.2.0,所以集成的时候花了挺多时间在坑里面,有很多bu...

    Ssss
  • MySQL 8.0新特性 — 密码管理

    作为世界上最流行的开源数据库,MySQL各方面的功能都在不断完善,比如密码管理这一块,从一开始最简单的用户名密码、到5.7版本的validate_passwor...

    brightdeng@DBA
  • Android 8.0中一些坑以及对应的解决方法

    但是迟到好过不到,因此基于此这边还是记录一下项目中遇到的 Android 8.0 的坑及对应解决方法。

    砸漏

扫码关注云+社区

领取腾讯云代金券