mysql中 distinct 和 group by 哪个效率更高?

wangms大约 4 分钟

省流

首先，给出一个大致的结论（完整解释请继续阅读）：

有索引时，GROUP BY 和 DISTINCT 的效率几乎相同，都可以利用索引优化。
没有索引时，DISTINCT 的效率通常更高。原因在于 GROUP BY 会进行排序，可能会触发 filesort，导致 SQL 执行变慢。

那么，接下来的问题是：

为什么在有索引的情况下，GROUP BY 和 DISTINCT 会有相同的效率？
为什么 GROUP BY 会进行排序操作，如何影响性能？

这些问题将通过本文逐一解析。首先，让我们快速了解一下 DISTINCT 和 GROUP BY 的基本使用。

`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 用来筛选唯一不同的值。如果某个字段包含 NULL，它会将所有 NULL 视为相同，保留一个 NULL，而其他的 NULL 会被去掉。

多列去重

SELECT DISTINCT column1, column2 FROM table_name;

例如：

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

在多列去重时，只有所有列的组合相同，才会被视为重复。

`GROUP BY` 的使用

`GROUP BY` 的基本用法

GROUP BY 的语法与 DISTINCT 类似，用来对结果进行分组：

SELECT columns FROM table_name GROUP BY columns;

例如：

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

多列去重

SELECT columns FROM table_name GROUP BY columns;

例如：

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.01 sec)

语法差异

GROUP BY 可以更灵活地进行分组，不仅仅是单列，还可以组合多列，分组后可以对每一组进行复杂的计算、聚合等处理。而 DISTINCT 只是做简单的去重。

例如：

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

`DISTINCT` 和 `GROUP BY` 的原理

在大多数情况下，DISTINCT 和 GROUP BY 本质上做的是同一件事：分组操作。这两者都可以使用索引扫描来优化查询性能。例如，以下两条 SQL 语句执行的计划几乎相同：

mysql> explain select int1_index from test_distinct_groupby group by int1_index;
mysql> explain select distinct int1_index from test_distinct_groupby;

在 EXPLAIN 结果中的 Extra 列，我们可以看到两者都使用了 Using index for group-by，表示它们都在利用索引进行分组操作。

`GROUP BY` 的隐式排序

不过，需要注意的是，MySQL 8.0 之前的版本，GROUP BY 会进行隐式排序，这就意味着它会对分组的列进行排序，甚至可能会触发 filesort 操作，这通常会影响性能，尤其是在没有合适索引时。比如以下的 SQL：

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 |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+

上面我们看到 Extra 中出现了 Using temporary; Using filesort，意味着 MySQL 在执行 GROUP BY 时，创建了临时表并进行了排序，导致性能下降。

MySQL 8.0 的优化

从 MySQL 8.0 开始，GROUP BY 不再默认进行隐式排序，这减少了不必要的性能消耗。如果没有索引可用来排序，MySQL 会尽量避免使用 filesort，并尝试优化查询性能。因此，在 MySQL 8.0 及之后版本，GROUP BY 和 DISTINCT 的效率更加相似。

总结

有索引时，DISTINCT 和 GROUP BY 都能有效地利用索引，效率差不多。
没有索引时，DISTINCT 通常会比 GROUP BY 更高效，因为 GROUP BY 可能会进行隐式排序，导致触发 filesort ，增加了额外的计算开销。
从 MySQL 8.0 开始，GROUP BY 不再进行隐式排序，因此在没有索引的情况下，GROUP BY 和 DISTINCT 的性能也变得相似。