刁钻的面试官！MySQL中DISTINCT和GROUP BY 哪个效率更高？

运维路书 2024-08-15

结论

MySQL8.0以上版本：

无论有无索引，在语义相同的情况下，DISTINCT 和 GROUP BY性能基本相同；

MySQL5.7及以下版本：

有索引的情况下，DISTINCT 和 GROUP BY性能基本相同；

无索引情况下，DISTINCT 效率高于 GROUP BY。原因是GROUP BY存在隐式排序，触发filesort,导致SQL执行效率下降；

以上结论部分为个人理解，可能有不对的地方，希望大家留言讨论交流。

实验验证

先建两张表t_users_idx（有索引）,t_users_no_idx（无索引）

通常DISTINCT可以被看作是特殊的GROUP BY，它们的实现都基于分组操作，且都可以通过松散索引扫描、紧凑索引扫描来实现。

但是GROUP BY 在不同的版本中存在隐式排序，这会触发filesort，进而导致sql性能下降。

一

MySQL5.7

老规矩先看官方手册中的描述

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

Note

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 会隐式排序（即在 GROUP BY 列没有 ASC 或 DESC 指示符的情况下）。然而，依赖 GROUP BY 的隐式排序（即在没有 ASC 或 DESC 指示符的情况下排序）或 GROUP BY 的显式排序（即对 GROUP BY 列使用显式的 ASC 或 DESC 指示符）已被弃用。若要产生给定的排序顺序，请提供 ORDER BY 子句。

接下来我们来证实一下，隐式排序的影响是否存在。

有索引

单列去重

执行计划中显示都是用了紧凑索引，索引字段和扫描行数都一样，因此性能也基本相同。

多列去重

DISTINCT 多列的去重，只有所有指定的列信息都相同，才会被认为是重复的信息。

执行计划同样distinct和 group by一致，性能相同。

无索引

无索引的情况下，从执行计划中可以看出无论是单列去重还是多列去重，GROUP BY都比DISTINC多了一项 “Using filesort”。

文件排序的过程包括将数据读取到内存中进行排序，如果数据量过大无法全部放入内存，还可能会使用临时文件进行辅助排序。这也是导致SQL性能下降的主要原因。

二

MySQL8.0

先看官方手册

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会根据确定的条件进行隐式排序。在mysql 8.0中，已经移除了这个功能，所以不再需要通过添加order by null 来禁止隐式排序了，但是，查询结果可能与以前的 MySQL 版本不同。要生成给定顺序的结果，请按通过ORDER BY指定需要进行排序的字段。

有索引