注意了，MySQL 8.0开始Group by不再排序

最新推荐文章于 2024-08-05 07:15:00 发布

骑龟的兔子2018

最新推荐文章于 2024-08-05 07:15:00 发布

阅读量5.3k

点赞数 1

分类专栏： SQL基础

本文链接：https://blog.csdn.net/qidan3500/article/details/84563296

版权

SQL基础专栏收录该内容

7 篇文章 0 订阅

订阅专栏

如题所示，mysql 8.0 开始 group by 默认是没有排序的
那mysql 8.0 之前和 8.0 就有可能结果出现不同需要警惕

查看版本信息 

root@mysql3306.sock>[employees]>show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.14                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.14-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+

运行如下SQL

表信息 

root@mysql3306.sock>[employees]>select * from t_group ;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  22744 | d006    | 1986-12-01 | 9999-01-01 |
|  24007 | d005    | 1986-12-01 | 9999-01-01 |
|  30970 | d005    | 1986-12-01 | 2017-03-29 |
|  31112 | d002    | 1986-12-01 | 1993-12-10 |
|  40983 | d005    | 1986-12-01 | 9999-01-01 |
|  49554 | d008    | 1986-12-01 | 1992-05-27 |
|  48317 | d008    | 1986-12-01 | 1989-01-11 |
|  49667 | d007    | 1986-12-01 | 9999-01-01 |
|  50449 | d005    | 1986-12-01 | 9999-01-01 |
|  10004 | d004    | 1986-12-01 | 9999-01-01 |
+--------+---------+------------+------------+
10 rows in set (0.00 sec)

查看执行 计划  发现extra 部分含有 usisng fiesort 

root@mysql3306.sock>[employees]>desc select dept_no ,count(*) from t_group group by dept_no ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

运行结果 是按照 dept_no 排序 输出 

root@mysql3306.sock>[employees]>select dept_no ,count(*) from t_group group by dept_no ;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d002    |        1 |
| d004    |        1 |
| d005    |        4 |
| d006    |        1 |
| d007    |        1 |
| d008    |        2 |
+---------+----------+
6 rows in set (0.00 sec)

下面是8.0 版本

root@mysql3308.sock>[test]>show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 8.0.13                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 8.0.13                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+


root@mysql3308.sock>[test]>select * from t_group ;
+--------+---------+------------+------------+
| emp_no | dept_no | from_date  | to_date    |
+--------+---------+------------+------------+
|  22744 | d006    | 1986-12-01 | 9999-01-01 |
|  24007 | d005    | 1986-12-01 | 9999-01-01 |
|  30970 | d005    | 1986-12-01 | 2017-03-29 |
|  31112 | d002    | 1986-12-01 | 1993-12-10 |
|  40983 | d005    | 1986-12-01 | 9999-01-01 |
|  49554 | d008    | 1986-12-01 | 1992-05-27 |
|  48317 | d008    | 1986-12-01 | 1989-01-11 |
|  49667 | d007    | 1986-12-01 | 9999-01-01 |
|  50449 | d005    | 1986-12-01 | 9999-01-01 |
|  10004 | d004    | 1986-12-01 | 9999-01-01 |
+--------+---------+------------+------------+

运行相同的sql

执行 计划  可以看出 extra 部分 已经没有 using filesort 

root@mysql3308.sock>[test]>desc select dept_no ,count(*) from t_group group by dept_no ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t_group | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using temporary |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

执行结果中 发现 不是按照 dept_no 排序 输出的

root@mysql3308.sock>[test]>select dept_no ,count(*) from t_group group by dept_no ;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d006    |        1 |
| d005    |        4 |
| d002    |        1 |
| d008    |        2 |
| d007    |        1 |
| d004    |        1 |
+---------+----------+
6 rows in set (0.00 sec)

如果这种情况下进行分页

5.7 版本

root@mysql3306.sock>[employees]>select dept_no ,count(*) from t_group group by dept_no  limit 1;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d002    |        1 |
+---------+----------+
1 row in set (0.00 sec)

8.0版本

root@mysql3308.sock>[test]>select dept_no ,count(*) from t_group group by dept_no  limit 1;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d006    |        1 |
+---------+----------+
1 row in set (0.00 sec)

可以发现两个结果是不一样的，原因就是8.0之前是group by 包含using filesort
而 8.0开始是没有的
为了避免这种问题，8.0开始除了索引替换排序的优化思路之外的相关排序我们必须使用order by来进行

5.7
root@mysql3306.sock>[employees]>select dept_no ,count(*) from t_group group by dept_no order by dept_no limit 1;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d002    |        1 |
+---------+----------+
1 row in set (0.00 sec)

8.0
root@mysql3308.sock>[test]>select dept_no ,count(*) from t_group group by dept_no order by dept_no limit 1;
+---------+----------+
| dept_no | count(*) |
+---------+----------+
| d002    |        1 |
+---------+----------+
1 row in set (0.00 sec)

我是知数堂SQL 优化班老师~ ^^
如有关于SQL优化方面疑问和一起交流的请加并且 @兔子@知数堂SQL优化
高性能MySQL,SQL优化群有叶金荣，吴炳锡两位大神坐镇： 579036588
欢迎加入知数堂大家庭