【.com原创稿件】当你使用MySQL的Groupbygrouping时，你是否发现分组后的数据是有序的？图片来自Pexels其实在MySQL8.0中，优化器会进行隐式排序。那么为什么要保留隐式排序后的Orderby呢？隐式排序的目的是什么？让我们来看看。背景故事几年前，我们换了领导。俗话说，新官上任总爱干蠢事……这不，领导要拥抱新事物，让我们更新项目的MySQL版本，从MySQL5.7升级到MySQL8.0。不知道是MySQL5.7不流行了，还是领导眼光高？我把这个任务交给了同事小王，小王却不以为然，说完就改了。搬库后，不用改代码，放心上线。上线后发现有些原本有序的列表变得乱了，最后临时回滚了版本。在查看代码时，我们发现老版本的Select语句只使用了Groupby分组，并没有使用Orderby排序。这有点令人困惑。为什么不使用Orderby而是排序？查了资料才知道，在MySQL8.0版本之前，是有Groupby隐式排序的！也就是说，当我们使用Groupby时，如：select*fromTgroupbyappName;默认按照appName的正序排序，相当于：select*fromTgroupbyappNameorderbyappName；reverse也是一样：select*fromTgroupbyappNamedesc;可以看出MySQL在8.0版本之前偷偷在分组查询中加入了排序操作。纳尼？MySQL还有这种操作？赶紧找找官方文档中Groupbyimplicitsorting的介绍：官方文档MySQL5.7ReferenceManual官方文档中的“2.1.14ORDERBYOptimization”一章有如下介绍：GROUPBY默认隐式排序（即，在GROUPBY列没有ASC或DESC指示符的情况下）。但是，不推荐依赖隐式GROUPBY排序（即在没有ASC或DESC指示符的情况下进行排序）或GROUPBY的显式排序（即通过对GROUPBY列使用显式ASC或DESC指示符）。要生成给定的排序顺序，请提供ORDERBY子句。ASC或DESC指示符）。但是，不建议依赖隐式GROUPBY排序（即，不使用ASC或DESC指示符进行排序）或显式GROUPBY排序（即，对GROUPBY列使用显式ASC或DESC指示符）。要生成给定的排序ORDER，请提供ORDERBY子句。从MySQL8.0开始，GROUPBY字段不再支持隐式排序。官方文档MySQL8.0ReferenceManual中的“8.2.1.16ORDERBYOptimization”章节有如下介绍：以前（MySQL5.7及更低版本），GROUPBY在某些条件下隐式排序。在MySQL8.0中，不再发生这种情况，因此不再需要在末尾指定ORDERBYNULL来抑制隐式排序（如之前所做的那样）。但是，查询结果可能与以前的MySQL版本不同。要生成给定的排序顺序，请提供ORDERBY子句。谷歌翻译：之前（MySQL5.7及更早版本），GROUPBY在特定条件下隐式排序。在MySQL8.0中，这不再发生，因此不再需要在末尾指定ORDERBYNULL来抑制隐式排序（如前所述）。但是，查询结果可能与以前的MySQL版本不同。要生成给定的排序顺序，请提供ORDERBY子句。陈哈哈：“哦，原来那个开发老版本的同事好像没有用Orderby，直接用隐式排序，年轻人，别谈武功！！”小王（低声道）：“哈哥，这个模块以前好像是你负责的。”哈哈陈（老脸通红）：？？？哈哈陈：“咳咳，这MySQL8.0团队不谈武功，给我挖个坑！”好了，接下来我们就用测试数据来演示一下。数据测试下面是表T的测试数据，无序：mysql>SELECTpid,appNamefromT;+--------+-------------------------+|pid|appName|+--------+------------------------+|1|码头声音重定向器||2|BluesMusicstation||3|usbtetherTRIAL||4|IlverotestdelQI||5|FlightTimeCalculator||6|ZXSpectrumEmulator||7|TheCityDressUp|+-------+-------------------------+7rowsinset(0.00sec)实验一：（MySQL版本：5.7.24）--隐式排序mysql>SELECTpid,appNamefromTgroupbyappName;+------+------------------------+|pid|appName|+--------+------+|pid|appName|+--------+-----------------------+|2|BluesMusicstation||1|DockSoundRedirector||5|飞行时间计算器||4|IlverotestdelQI||7|TheCityDressUp||3|usbtetherTRIAL||6|ZXSpectrumEmulator|+------+------------------------+7rowsinset(0.00sec)--如上隐式sorting,equivalenttoSELECTpid,appNamefromTgroupbyappNameascorSELECTpid,appNamefromTgroupbyappNameorderbyappNameasc;--显式排序，相当于SELECTpid,appNamefromTgroupbyappNameorderbyappNamedesc;mysql>SELECTpid,appNamefromTgroupbyappNamedesc;+--------+----------------------+|pid|appName|+--------+------------------------+|6|ZXSpectrumEmulator||3|usbtetherTRIAL||7|TheCityDressUp||4|IlverotestdelQI||5|飞行时间计算器||1|DockSoundRedirector||2|BluesMusicstation|+--------+----------------------+7rowsinset(0.00sec)实验二：(MySQL版本：8.0.16)mysql>SELECTpid,appNamefromTgroupbyappName;+--------+------------------------+|pid|appName|+--------+-----------------------+|1|DockSoundRedirector||2|BluesMusicstation||3|usbtetherTRIAL||4|IlverotestdelQI||5|FlightTimeCalculator||6|ZXSpectrumEmulator||7|TheCityDressUp|+--------+------------------------+7行inset(0.00sec)mysql>SELECTpid,appNamefromTgroupbyappNameDESC;ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'DESC'atline1As如上所示，在MySQL8.0中，不支持GROUPBY的隐式排序。上面的测试例子是一个无序的GROUPBY显示排序，会直接报错。因此，如果数据库从MySQL5.7或更早版本迁移到MySQL8，需要特别注意这个问题。隐式排序：起源（一个美丽的错误）为什么首先使用隐式排序？我们知道，要对一组数据进行分组，MySQL优化器会选择不同的方法。最有效的方法是在分组前先对数据进行排序，这样可以降低数据的复杂性，使连续分组变得容易。另外，如果Groupby一个索引字段可以得到排序后的数据，那么使用成本就很低了（因为BTree索引是天然有序的）。实际上，Groupby经常使用索引。这样看来，这确实是个好主意！也可以说是留下了美丽的虫子。下面的查询语句使用了appName_idx索引，所以Groupby查询不需要排序，直接分组，效率高。--带索引：appName_idxmysql>EXPLAINSELECTappNamefrom0122_csj_demoGROUPBYappName\G******************************1.row**************************id:1select_type:SIMPLEtable:0122_csj_demopartitions:NULLtype:indexpossible_keys:appName_idxkey:appName_idxkey_len:515ref:NULLrows:28filtered:100.00Extra:Usingindex1rowinset,1warning(0.00sec)ifWithout一个索引，MySQL优化器仍然可以决定在分组之前使用外部临时表进行filesort排序，这在效率上类似于无序分组。当用户指定Orderby时，是MySQL最希望看到的，这样排序工作才不会白费，这也是MySQL团队一直默认隐式排序的原因之一。mysql>EXPLAINSELECTappNamefrom0122_csj_demoGROUPBYappName\G******************************1.row******************************id:1select_type:SIMPLEtable:0122_csj_demopartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:28filtered:100.00Extra:Usingtemporary;Usingfilesort1rowinset,1warning(0.00sec)另外，用户可以显式指定ORDERBYNULL让MySQL知道GROUPBY不需要排序。所以需要一个非标准的（ORDERBYNULL）语法来抵消另一个非标准扩展（GROUPBYsort）的影响。mysql>EXPLAINSELECTappNamefrom0122_csj_demoGROUPBYappNameORDERBYnull\G******************************1.row******************************id:1select_type:SIMPLEtable:0122_csj_demopartitions:NULLtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:28filtered:100.00Extra:Usingtemporary1rowinset,1warning(0.00sec)隐式排序：Destinyfor为了解决这个优雅的bug，MySQL团队在8.0版本中引入了倒排索引。正负索引排序的优化思想，对于隐式排序来说，已经得出了一个像样的结论。从此删除了Groupby的隐式排序功能，分组排序必须使用Orderby。该分组算法仍然可以延续之前基于正负指标分组的效率。好了，这篇文章基本就结束了。隐式排序是MySQL角落里一个比较冷门的知识点，却是认识四年的老朋友了。在北京漂泊四年，时光荏苒。从第一次接触MySQL的艰难，到对各个知识点的实现思路的深入理解，也算是顺便练了一杯酒。莫泊桑说：“生活也许没有你想象的那么好，但也没有你想象的那么糟糕。”人的脆弱和坚强是超乎想象的，有时候一句脆弱的话，可能会让人泪流满面。有时候你会发现自己咬着牙走了很远的路。打工不易，但为了家人父母过上好日子，加油！作者：陈哈哈简介：MySQL社区非知名贡献者，爱笑程序员，擅长淫学知识；加入MySQL五年，致力于高阶开发研究性能SQL和事务锁优化；任重而道远，希望通过自己的分享，让大家少走弯路。编辑：陶佳龙征稿：如有意投稿或寻求报道，请联系编辑微信：gordonlonglong【原创稿件，合作网站转载请注明原作者和出处为.com】
