mysql 8.0 group by 不再排序

结论：

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

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

 

实验：

创建测试表并插入数据。

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版本

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)

8.0版本执行计划中没有排序（using filesort）
mysql> select c1 from t group by c1;
+------+
| c1   |
+------+
|   10 |
|    8 |
|    7 |
|    9 |
+------+
4 rows in set (0.00 sec)
分组结果没有排序

　　分组列上加上索引之后：

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)