mysql 8.0 group by 不再排序

结论：

mysql5.7版本 group by默认会按照分组字段来排序，到了8.0版本，就只是分组，没有排序了。

但是如果分组字段是索引列的话，分组时就会使用索引，结果也就相当于做了排序了。

 

实验：

创建测试表并插入数据。

?

1 2 3 4 5 6 7 8

CREATE TABLE `tt` ( `id` int(11) NOT NULL, `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, PRIMARY KEY (`id`) );   insert into tt values(1,2,3),(2,1,4),(3,2,4),(4,6,7)

 

分组字段上没有索引的情况：　

mysql 5.7版本：

mysql> select * from t;
+----+------+
| id | c1 |
+----+------+
| 1 | 10 |
| 2 | 8 |
| 3 | 7 |
| 4 | 9 |
| 5 | 10 |
+----+------+
5 rows in set (0.00 sec)

mysql> explain select c1 from t group by c1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------+
1 row in set, 1 warning (0.00 sec)

执行计划中有排序

mysql> select c1 from t group by c1;
+------+
| c1 |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
+------+
4 rows in set (0.00 sec)

分组结果是排好序的。

mysql8.0版本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

ysql> select * from t; +----+------+ | id | c1   | +----+------+ |  1 |   10 | |  2 |    8 | |  3 |    7 | |  4 |    9 | |  5 |   10 | +----+------+ 5 rows in set (0.00 sec)   mysql> explain select c1 from t group by c1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ |  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 1 row in set, 1 warning (0.00 sec) <br>8.0版本执行计划中没有排序（using filesort） mysql> select c1 from t group by c1; +------+ | c1   | +------+ |   10 | |    8 | |    7 | |    9 | +------+ 4 rows in set (0.00 sec)<br>分组结果没有排序

　　分组列上加上索引之后：

mysql5.7版本

mysql> alter table t add key(c1);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select c1 from t group by c1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | index | c1 | c1 | 5 | NULL | 5 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
执行计划中使用索引
mysql> select c1 from t group by c1;
+------+
| c1 |
+------+
| 7 |
| 8 |
| 9 |
| 10 |
+------+
4 rows in set (0.00 sec)
分组结果是有序的。

Mysql8.0版本，表现与5.7版本相同。

mysql> alter table t add key(c1);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select c1 from t group by c1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | c1            | c1   | 5       | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select c1 from t group by c1;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | index | c1            | c1   | 5       | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select c1 from t group by c1;
+------+
| c1   |
+------+
|    7 |
|    8 |
|    9 |
|   10 |
+------+
4 rows in set (0.00 sec)