先说大致的结论(完整结论在文末):
- 在语义相同,有索引的情况下:
group by
和distinct都能使用索引,效率相同。- 在语义相同,无索引的情况下:distinct效率高于
group by
。原因是distinct 和group by
都会进行分组操作,但group by
可能会进行排序,触发filesort,导致sql执行效率低下。
基于这个结论,你可能会问:
- 为什么在语义相同,有索引的情况下,
group by
和distinct效率相同? - 在什么情况下,
group by
会进行排序操作?
带着这两个问题找答案。接下来,我们先来看一下distinct和group by
的基础使用。
推荐一个开源免费的 Spring Boot 最全教程:
https://github.com/javastacks/spring-boot-best-practice
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值,并且对该列使用DISTINCT
子句,MySQL将保留一个NULL值,并删除其它的NULL值,因为DISTINCT
子句将所有NULL值视为相同的值。
distinct多列去重
distinct多列的去重,则是根据指定的去重的列信息来进行,即只有所有指定的列信息都相同,才会被认为是重复的信息。
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的使用
对于基础去重来说,group by
的使用和distinct类似:
单列去重
语法:
SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;
执行:
mysql> select age from student group by age;
+------+
| age |
+------+
| 10 |
| 12 |
| 11 |
| NULL |
+------+
4 rows in set (0.02 sec)
多列去重
语法:
SELECT columns FROM table_name WHERE where_conditions 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.03 sec)
区别示例
两者的语法区别在于,group by
可以进行单列去重,group by
的原理是先对结果进行分组排序,然后返回每组中的第一条数据。且是根据group by
的后接字段进行去重的。
例如:
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原理
在大多数例子中,DISTINCT
可以被看作是特殊的GROUP BY
,它们的实现都基于分组操作,且都可以通过松散索引扫描、紧凑索引扫描(关于索引扫描的内容会在其他文章中详细介绍,就不在此细致介绍了)来实现。
DISTINCT
和GROUP BY
都是可以使用索引进行扫描搜索的。例如以下两条sql(只单单看表格最后extra的内容),我们对这两条sql进行分析,可以看到,在extra中,这两条sql都使用了紧凑索引扫描Using index for group-by
。
所以,在一般情况下,对于相同语义的DISTINCT
和GROUP BY
语句,我们可以对其使用相同的索引优化手段来进行优化。
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;
+----+-------------+-----------------------+------------+-------+---------------+---------+---