Group by隱式排序，一個(gè)優(yōu)美的BUG

小伙伴想精準(zhǔn)查找自己想看的MySQL文章？喏 →

MySQL江湖路 | 專欄目錄

你在使用MySQL的Group by分組時(shí)，是否發(fā)現(xiàn)分組后的數(shù)據(jù)都是有序的？其實(shí)，在MySQL8.0版本前，優(yōu)化器在分組查詢時(shí)都會(huì)進(jìn)行隱式排序。那既然隱式排序?yàn)槭裁催€要保留Order by？隱式排序的目的又是什么呢？讓我們一起來(lái)看看。

目錄

一、背景小故事

官方文檔

數(shù)據(jù)測(cè)試

二、隱式排序 - 起源（一個(gè)優(yōu)美的BUG）

三、隱式排序 - 宿命

一、背景小故事

年前我們換了領(lǐng)導(dǎo)。俗話說(shuō)，新官上任干總愛(ài)干傻事兒，這不，領(lǐng)導(dǎo)要擁抱新事物，要求我們更新項(xiàng)目MySQL版本，從MySQL5.7更新到MySQL8.0。不知是MySQL5.7不香了，還是領(lǐng)導(dǎo)你眼光高了？

我把這個(gè)任務(wù)交給同事小王，小王不以為然，說(shuō)換就換。遷完庫(kù)，在代碼基本不改的情況下自信上線。上線后卻發(fā)現(xiàn)原本一些有序的列表變無(wú)序了，最后臨時(shí)回退了版本。

核對(duì)代碼時(shí)我們發(fā)現(xiàn)，老版本Select語(yǔ)句中只是用到了Group by分組，也沒(méi)有用到order by排序，有點(diǎn)蒙，為啥沒(méi)用order by卻排了序？查資料后得知，在MySQL8.0版本前是存在Group by隱式排序的！ 就是說(shuō)在我們使用分組（Group by）時(shí)，如：select * from T group by appName; 會(huì)默認(rèn)按照appName正序排序，相當(dāng)于select * from T group by appName order by appName;，倒排同理：select * from T group by appName desc; 可見(jiàn)，MySQL在8.0版本前的分組查詢中，偷偷加上了排序操作。

納尼？MySQL還有這種操作？快找一下官方文檔對(duì)Group by隱式排序的介紹：

官方文檔

官方文檔MySQL 5.7 Reference Manual中的“2.1.14 ORDER BY Optimization”章節(jié)有如下介紹：

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翻譯：默認(rèn)情況下GROUP BY隱式排序(即，缺少GROUP BY列的ASC或DESC指示符)。但是，不推薦依賴于隱式GROUP BY排序(即，在沒(méi)有ASC或DESC指示符的情況下排序)或GROUP BY的顯式排序(即，通過(guò)對(duì)GROUP BY列使用顯式ASC或DESC指示符)。要生成給定的排序 ORDER，請(qǐng)?zhí)峁㎡RDER BY子句。

從MySQL 8.0開(kāi)始，GROUP BY字段不再支持隱式排序. 官方文檔MySQL 8.0 Reference Manual中“8.2.1.16 ORDER BY Optimization”章節(jié)有如下介紹：

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中，不再發(fā)生這種情況，因此不再需要在末尾指定ORDER BY NULL來(lái)抑制隱式排序（如前所述）。 但是，查詢結(jié)果可能與以前的MySQL版本不同。 要產(chǎn)生給定的排序順序，請(qǐng)?zhí)峁㎡RDER BY子句

陳哈哈：“哦，這么看來(lái)開(kāi)發(fā)老版本的同事是沒(méi)用Order by，直接用了隱式排序。年輕人，不講武德啊！！”

小王（小聲）：“哈哥，這模塊之前好像是你負(fù)責(zé)的。”

陳哈哈（老臉一紅）：？？？

陳哈哈：“咳咳，這MySQL8.0團(tuán)隊(duì)不講武德，給我挖坑！”

好了，接下來(lái)我們用測(cè)試數(shù)據(jù)演示一下

數(shù)據(jù)測(cè)試

下面是表T測(cè)試數(shù)據(jù)，無(wú)序

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)

實(shí)驗(yàn)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) -- 如上述隱式排序，相當(dāng)于SELECT pid,appName from T group by appName asc 或 SELECT pid,appName from T group by appName order by appName asc; -- 顯式排序，相當(dāng)于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)

實(shí)驗(yàn)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隱式排序不支持了，上面測(cè)試?yán)邮菬o(wú)序的。GROUP BY顯示排序則直接報(bào)錯(cuò)。所以如果有數(shù)據(jù)庫(kù)從MySQL 5.7或之前的版本，遷移升級(jí)到MySQL 8的話，就需要特別留意這個(gè)問(wèn)題了。

二、隱式排序 - 起源（一個(gè)優(yōu)美的BUG）

最初為什么要用隱式排序呢？我們知道，要對(duì)一組數(shù)據(jù)進(jìn)行分組，MySQL優(yōu)化器會(huì)選擇不同的方法。其中最有效的一種是分組之前對(duì)數(shù)據(jù)排序，降低數(shù)據(jù)復(fù)雜度，使得連續(xù)分組變得很容易。另外，如果可以Group by 一個(gè)索引字段來(lái)用于獲取排序的數(shù)據(jù)，那么使用它的成本就非常低了（因?yàn)锽Tree索引是天然有序的）。而在實(shí)際操作中，Group by用到索引的頻率很高。這么看，這確實(shí)是個(gè)很棒的主意！也可以說(shuō)是留了一個(gè)優(yōu)美的BUG。

如下查詢語(yǔ)句，用到了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)

如果沒(méi)有索引，MySQL優(yōu)化器仍然可以決定在分組之前用外部臨時(shí)表進(jìn)行filesort排序，從效率上講，和無(wú)序分組差不多。當(dāng)用戶指定Order by時(shí)，是MySQL最希望看到的，這樣就不會(huì)讓排序工作白費(fèi)，這也是讓MySQL團(tuán)隊(duì)始終默認(rèn)隱式排序存在的原因之一。

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不需要排序。因此需要一個(gè)非標(biāo)準(zhǔn)（ORDER BY NULL）語(yǔ)法來(lái)抵消另一個(gè)非標(biāo)準(zhǔn)擴(kuò)展（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)

三、隱式排序 - 宿命

為了解決這個(gè)優(yōu)美的BUG，MySQL團(tuán)隊(duì)在8.0版本引入了倒排索引。正負(fù)向索引排序的優(yōu)化思路，給隱式排序體面的落下帷幕。自此Group by隱式排序功能被刪除，分組排序必須用order by來(lái)進(jìn)行，分組的算法依然可以基于正負(fù)向索引延續(xù)之前分組的高效性。

好了，本文到此基本結(jié)束，隱式排序算是MySQL角落里較冷門的知識(shí)點(diǎn)，對(duì)我來(lái)說(shuō)卻是一位結(jié)識(shí)四年的舊友了。北漂四年，時(shí)光匆匆，從初識(shí)MySQL的步履維艱，到深入理解各知識(shí)點(diǎn)的實(shí)現(xiàn)思路，也算順道吃了杯隱排的踐行酒。

莫泊桑說(shuō)：“生活可能不像你想象的那么好，但是，也不會(huì)你想象的那么糟”。人的脆弱和堅(jiān)強(qiáng)都超乎了自己的想，有時(shí)候可能脆弱的一句話，就淚流滿面。有時(shí)候你會(huì)發(fā)現(xiàn)，自己咬著牙走過(guò)很長(zhǎng)的一段路。 在外漂泊打工人不易，為了家人父母過(guò)上好日子，加油！

MySQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。