MySQL 8.0不講武德，給我挖坑！

原創

作者：陳哈哈 2021-01-27 09:19:44

數據庫 MySQL

你在使用 MySQL 的 Group by 分組時，是否發現分組后的數據都是有序的?

【51CTO.com原創稿件】你在使用 MySQL 的 Group by 分組時，是否發現分組后的數據都是有序的?

[[378654]]

圖片來自 Pexels

其實，在 MySQL 8.0 ，優化器在分組查詢時都會進行隱式排序。那既然隱式排序為什么還要保留 Order by?隱式排序的目的又是什么呢?讓我們一起來看看。

背景小故事

年前我們換了領導，俗話說，新官上任總愛干傻事兒...

這不，領導要擁抱新事物，要求我們更新項目 MySQL 版本，從 MySQL 5.7 更新到 MySQL 8.0。不知是 MySQL 5.7 不香了，還是領導你眼光高了?

我把這個任務交給同事小王，小王不以為然，說換就換。遷完庫，在代碼基本不改的情況下自信上線。

上線后卻發現原本一些有序的列表變無序了，最后臨時回退了版本。

[[378655]]

核對代碼時我們發現，老版本 Select 語句中只是用到了 Group by 分組，也沒有用到 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 團隊不講武德，給我挖坑!”

[[378656]]

好了，接下來我們用測試數據演示一下。

數據測試

下面是表 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 
*************************** 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 的步履維艱，到深入理解各知識點的實現思路，也算順道吃了杯隱排的踐行酒。

莫泊桑說："生活可能不像你想象的那么好，但是，也不會你想象的那么糟"。

人的脆弱和堅強都超乎了自己的想，有時候可能脆弱的一句話，就淚流滿面。

[[378657]]

有時候你會發現，自己咬著牙走過很長的一段路。在外漂泊打工人不易，為了家人父母過上好日子，加油!

作者：陳哈哈

簡介：MySQL 社區的非著名貢獻者，一個愛笑的程序員，善于白嫖知識;陪伴 MySQL 五年，致力于高性能 SQL、事務鎖優化方面的研究;長路漫漫，希望通過自己的分享讓大家少踩一些坑。

編輯：陶家龍

征稿：有投稿、尋求報道意向技術人請聯絡小編微信：gordonlonglong

【51CTO原創稿件，合作站點轉載請注明原文作者和出處為51CTO.com】

責任編輯：武曉燕來源： 51CTO技術棧

MySQL 數據優化器