為什么在語義相同的情況下group by和distinct效率相同呢？

結(jié)論

先說大致的結(jié)論：

在語義相同，有索引的情況下：group by和distinct都能使用索引，效率相同。

在語義相同，無索引的情況下：distinct效率高于group by。原因是distinct 和 group by都會(huì)進(jìn)行分組操作，但group by可能會(huì)進(jìn)行排序，觸發(fā)filesort，導(dǎo)致sql執(zhí)行效率低下。

基于這個(gè)結(jié)論，你可能會(huì)問：

為什么在語義相同，有索引的情況下，group by和distinct效率相同？

在什么情況下，group by會(huì)進(jìn)行排序操作？

帶著這兩個(gè)問題找答案。接下來，我們先來看一下distinct和group by的基礎(chǔ)使用。

基于 Spring Boot + MyBatis Plus + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

distinct的使用

distinct用法

SELECTDISTINCTcolumnsFROMtable_nameWHEREwhere_conditions;

例如：

mysql>selectdistinctagefromstudent;
+------+
|age|
+------+
|10|
|12|
|11|
|NULL|
+------+
4rowsinset(0.01sec)

DISTINCT 關(guān)鍵詞用于返回唯一不同的值。放在查詢語句中的第一個(gè)字段前使用，且作用于主句所有列。

如果列具有NULL值，并且對(duì)該列使用DISTINCT子句，MySQL將保留一個(gè)NULL值，并刪除其它的NULL值，因?yàn)镈ISTINCT子句將所有NULL值視為相同的值。

distinct多列去重

distinct多列的去重，則是根據(jù)指定的去重的列信息來進(jìn)行，即只有所有指定的列信息都相同，才會(huì)被認(rèn)為是重復(fù)的信息。

SELECTDISTINCTcolumn1,column2FROMtable_nameWHEREwhere_conditions;
mysql>selectdistinctsex,agefromstudent;
+--------+------+
|sex|age|
+--------+------+
|male|10|
|female|12|
|male|11|
|male|NULL|
|female|11|
+--------+------+
5rowsinset(0.02sec)

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實(shí)現(xiàn)的后臺(tái)管理系統(tǒng) + 用戶小程序，支持 RBAC 動(dòng)態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

group by的使用

對(duì)于基礎(chǔ)去重來說，group by的使用和distinct類似:

單列去重

語法：

SELECTcolumnsFROMtable_nameWHEREwhere_conditionsGROUPBYcolumns;

執(zhí)行：

mysql>selectagefromstudentgroupbyage;
+------+
|age|
+------+
|10|
|12|
|11|
|NULL|
+------+
4rowsinset(0.02sec)

多列去重

語法：

SELECTcolumnsFROMtable_nameWHEREwhere_conditionsGROUPBYcolumns;

執(zhí)行：

mysql>selectsex,agefromstudentgroupbysex,age;
+--------+------+
|sex|age|
+--------+------+
|male|10|
|female|12|
|male|11|
|male|NULL|
|female|11|
+--------+------+
5rowsinset(0.03sec)

區(qū)別示例

兩者的語法區(qū)別在于，group by可以進(jìn)行單列去重，group by的原理是先對(duì)結(jié)果進(jìn)行分組排序，然后返回每組中的第一條數(shù)據(jù)。且是根據(jù)group by的后接字段進(jìn)行去重的。

例如：

mysql>selectsex,agefromstudentgroupbysex;
+--------+-----+
|sex|age|
+--------+-----+
|male|10|
|female|12|
+--------+-----+
2rowsinset(0.03sec)

distinct和group by原理

在大多數(shù)例子中，DISTINCT可以被看作是特殊的GROUP BY，它們的實(shí)現(xiàn)都基于分組操作，且都可以通過松散索引掃描、緊湊索引掃描(關(guān)于索引掃描的內(nèi)容會(huì)在其他文章中詳細(xì)介紹，就不在此細(xì)致介紹了)來實(shí)現(xiàn)。

DISTINCT和GROUP BY都是可以使用索引進(jìn)行掃描搜索的。例如以下兩條sql（只單單看表格最后extra的內(nèi)容），我們對(duì)這兩條sql進(jìn)行分析，可以看到，在extra中，這兩條sql都使用了緊湊索引掃描Using index for group-by。

所以，在一般情況下，對(duì)于相同語義的DISTINCT和GROUP BY語句，我們可以對(duì)其使用相同的索引優(yōu)化手段來進(jìn)行優(yōu)化。

mysql>explainselectint1_indexfromtest_distinct_groupbygroupbyint1_index;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|1|SIMPLE|test_distinct_groupby|NULL|range|index_1|index_1|5|NULL|955|100.00|Usingindexforgroup-by|
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1rowinset(0.05sec)

mysql>explainselectdistinctint1_indexfromtest_distinct_groupby;
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|1|SIMPLE|test_distinct_groupby|NULL|range|index_1|index_1|5|NULL|955|100.00|Usingindexforgroup-by|
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1rowinset(0.05sec)

但對(duì)于GROUP BY來說，在MYSQL8.0之前，GROUP Y默認(rèn)會(huì)依據(jù)字段進(jìn)行隱式排序。

可以看到，下面這條sql語句在使用了臨時(shí)表的同時(shí)，還進(jìn)行了filesort。

mysql>explainselectint6_bigger_randomfromtest_distinct_groupbyGROUPBYint6_bigger_random;
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
|1|SIMPLE|test_distinct_groupby|NULL|ALL|NULL|NULL|NULL|NULL|97402|100.00|Usingtemporary;Usingfilesort|
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1rowinset(0.04sec)

隱式排序

對(duì)于隱式排序，我們可以參考Mysql官方的解釋

大致解釋一下：

GROUP BY 默認(rèn)隱式排序（指在 GROUP BY 列沒有 ASC 或 DESC 指示符的情況下也會(huì)進(jìn)行排序）。然而，GROUP BY進(jìn)行顯式或隱式排序已經(jīng)過時(shí)（deprecated）了，要生成給定的排序順序，請(qǐng)?zhí)峁?ORDER BY 子句。

所以，在Mysql8.0之前,Group by會(huì)默認(rèn)根據(jù)作用字段（Group by的后接字段）對(duì)結(jié)果進(jìn)行排序。在能利用索引的情況下，Group by不需要額外進(jìn)行排序操作；但當(dāng)無法利用索引排序時(shí)，Mysql優(yōu)化器就不得不選擇通過使用臨時(shí)表然后再排序的方式來實(shí)現(xiàn)GROUP BY了。

且當(dāng)結(jié)果集的大小超出系統(tǒng)設(shè)置臨時(shí)表大小時(shí)，Mysql會(huì)將臨時(shí)表數(shù)據(jù)copy到磁盤上面再進(jìn)行操作，語句的執(zhí)行效率會(huì)變得極低。這也是Mysql選擇將此操作（隱式排序）棄用的原因。

基于上述原因，Mysql在8.0時(shí)，對(duì)此進(jìn)行了優(yōu)化更新：

大致解釋一下：

從前（Mysql5.7版本之前），Group by會(huì)根據(jù)確定的條件進(jìn)行隱式排序。在mysql 8.0中，已經(jīng)移除了這個(gè)功能，所以不再需要通過添加order by null 來禁止隱式排序了，但是，查詢結(jié)果可能與以前的 MySQL 版本不同。要生成給定順序的結(jié)果，請(qǐng)按通過ORDER BY指定需要進(jìn)行排序的字段。

因此，我們的結(jié)論也出來了：

在語義相同，有索引的情況下：

group by和distinct都能使用索引，效率相同。因?yàn)間roup by和distinct近乎等價(jià)，distinct可以被看做是特殊的group by。

在語義相同，無索引的情況下：

distinct效率高于group by。原因是distinct 和 group by都會(huì)進(jìn)行分組操作，但group by在Mysql8.0之前會(huì)進(jìn)行隱式排序，導(dǎo)致觸發(fā)filesort，sql執(zhí)行效率低下。

但從Mysql8.0開始，Mysql就刪除了隱式排序，所以，此時(shí)在語義相同，無索引的情況下，group by和distinct的執(zhí)行效率也是近乎等價(jià)的。

推薦group by的原因

group by語義更為清晰

group by可對(duì)數(shù)據(jù)進(jìn)行更為復(fù)雜的一些處理

相比于distinct來說，group by的語義明確。且由于distinct關(guān)鍵字會(huì)對(duì)所有字段生效，在進(jìn)行復(fù)合業(yè)務(wù)處理時(shí)，group by的使用靈活性更高，group by能根據(jù)分組情況，對(duì)數(shù)據(jù)進(jìn)行更為復(fù)雜的處理，例如通過having對(duì)數(shù)據(jù)進(jìn)行過濾，或通過聚合函數(shù)對(duì)數(shù)據(jù)進(jìn)行運(yùn)算。

審核編輯：劉清