MySQL 8.0不讲武德，给我挖坑！

【.com原创稿件】你在使用 MySQL 的讲武 Group by 分组时，是挖坑否发现分组后的数据都是有序的?

 

图片来自 Pexels

其实，在 MySQL 8.0 ，讲武优化器在分组查询时都会进行隐式排序。挖坑那既然隐式排序为什么还要保留 Order by?讲武隐式排序的目的又是什么呢?让我们一起来看看。

背景小故事

年前我们换了领导，挖坑俗话说，讲武新官上任总爱干傻事儿...

这不，挖坑领导要拥抱新事物，讲武要求我们更新项目 MySQL 版本，挖坑从 MySQL 5.7 更新到 MySQL 8.0。讲武不知是挖坑 MySQL 5.7 不香了，还是讲武领导你眼光高了?

我把这个任务交给同事小王，小王不以为然，挖坑说换就换。讲武迁完库，在代码基本不改的情况下自信上线。

上线后却发现原本一些有序的列表变无序了，最后临时回退了版本。

 

核对代码时我们发现，老版本 Select 语句中只是用到了 Group by 分组，b2b信息网也没有用到 Order by 排序，有点蒙，为啥没用 Order by 却排了序?

查资料后得知，在 MySQL 8.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.

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，直接用了隐式排序。年轻人，不讲武德啊!!”

小王(小声)：“哈哥，这模块之前好像是你负责的。”

陈哈哈(老脸一红)：???

陈哈哈：“咳咳，这 MySQL 8.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 的话，就需要特别留意这个问题了。

隐式排序：起源(一个优美的 Bug)

最初为什么要用隐式排序呢?我们知道，要对一组数据进行分组，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