文章导读
在MySQL中，我们经常需要用到distinct和group by来处理数据去重。但你知道它们哪个效率更高吗？今天，我们就来聊聊这个问题。
实战分析
首先，我们来看一下没有索引的情况下的测试结果。
mysql> select * from test1;
+----+--------------+
| id | name2 |
+----+--------------+
| 1 | test_data_1 |
| 2 | test_data_2 |
| 3 | test_data_3 |
| 4 | test_data_4 |
| 5 | test_data_5 |
| 6 | test_data_6 |
| 7 | test_data_7 |
| 8 | test_data_8 |
| 9 | test_data_9 |
| 10 | test_data_10 |
+----+--------------+
10 rows in set (0.00 sec)
mysql> explain select distinct(name2) from test1 ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select name2 from test1 group by name2;;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | test1 | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
可以看到，在没有索引的情况下，使用distinct和group by都会使用临时表，并且distinct的效率更高。
接下来，我们来看一下有索引的情况。
mysql> create index idx_name2 on test1(name2);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select name2 from test1 group by name2;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | index | idx_name2 | idx_name2 | 103 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select distinct(name2) from test1 ;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test1 | NULL | index | idx_name2 | idx_name2 | 103 | NULL | 10 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
在有索引的情况下，无论是使用distinct还是group by，都能使用索引，效率相同。
总结与拓展
总结来说，在没有索引的情况下，distinct的效率高于group by。而在有索引的情况下，两者的效率相同。
此外，从MySQL 8.0开始，MySQL删除了隐式排序，因此在无索引的情况下，group by和distinct的执行效率也近乎等价。