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

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

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

• 為什么在語(yǔ)義相同，有索引的情況下，group by和 distinct 效率相同？
• 在什么情況下，group by會(huì)進(jìn)行排序操作？

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

distinct的使用

distinct用法

SELECT DISTINCT columns FROM table_name WHERE where_conditions;

例如：

mysql> select distinct age from student;
+------+
| age  |
+------+
|   10 |
|   12 |
|   11 |
| NULL |
+------+
4 rows in set (0.01 sec)

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

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

distinct 多列去重

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

SELECT DISTINCT column1,column2 FROM table_name WHERE where_conditions;
mysql> select distinct sex,age from student;
+--------+------+
| sex    | age  |
+--------+------+
| male   |   10 |
| female |   12 |
| male   |   11 |
| male   | NULL |
| female |   11 |
+--------+------+
5 rows in set (0.02 sec)

group by的使用

對(duì)于基礎(chǔ)去重來(lái)說(shuō)，group by 的使用和 distinct 類(lèi)似。

單列去重

語(yǔ)法：

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

執(zhí)行：

mysql> select age from student group by age;
+------+
| age  |
+------+
|   10 |
|   12 |
|   11 |
| NULL |
+------+
4 rows in set (0.02 sec)

多列去重

語(yǔ)法：

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

執(zhí)行：

mysql> select sex,age from student group by sex,age;
+--------+------+
| sex    | age  |
+--------+------+
| male   |   10 |
| female |   12 |
| male   |   11 |
| male   | NULL |
| female |   11 |
+--------+------+
5 rows in set (0.03 sec)

區(qū)別示例

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

例如：

mysql> select sex,age from student group by sex;
+--------+-----+
| sex    | age |
+--------+-----+
| male   |  10 |
| female |  12 |
+--------+-----+
2 rows in set (0.03 sec)

distinct和group by原理

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

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

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

mysql> explain select int1_index from test_distinct_groupby group by int1_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 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)
mysql> explain select distinct int1_index from test_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 | Using index for group-by |
+----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set (0.05 sec)

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

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

mysql> explain select int6_bigger_random from test_distinct_groupby GROUP BY int6_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 | Using temporary; Using filesort |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set (0.04 sec)

隱式排序

對(duì)于隱式排序，我們可以參考 MySQL 官方的解釋?zhuān)?/p>

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

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.

大致解釋一下：

GROUP BY 默認(rèn)隱式排序（指在 GROUP BY 列沒(méi)有 ASC 或 DESC 指示符的情況下也會(huì)進(jìn)行排序）。然而，GROUP BY 進(jìn)行顯式或隱式排序已經(jīng)過(guò)時(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)無(wú)法利用索引排序時(shí)，MySQL 優(yōu)化器就不得不選擇通過(guò)使用臨時(shí)表然后再排序的方式來(lái)實(shí)現(xiàn)GROUP BY了。

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

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

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html

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.

大致解釋一下：

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

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

• 在語(yǔ)義相同，有索引的情況下： group by和 distinct 都能使用索引，效率相同。因?yàn)?code>group by和distinct近乎等價(jià)，distinct 可以被看做是特殊的group by。
• 在語(yǔ)義相同，無(wú)索引的情況下： distinct效率高于group by。原因是distinct 和 group by都會(huì)進(jìn)行分組操作，但group by在 MySQL8.0 之前會(huì)進(jìn)行隱式排序，導(dǎo)致觸發(fā) filesort，sql 執(zhí)行效率低下。但從 MySQL8.0 開(kāi)始，MySQL 就刪除了隱式排序，所以，此時(shí)在語(yǔ)義相同，無(wú)索引的情況下，group by和distinct的執(zhí)行效率也是近乎等價(jià)的。

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

到此這篇關(guān)于MySQL中的distinct與group by比較使用方法的文章就介紹到這了,更多相關(guān)MySQL distinct與group by內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論