MySQL——GROUP BY详解与优化

达摩面壁

在 MySQL 中，GROUP BY用于将具有指定列中相同值的行分组在一起。这是在处理大量数据时非常有用的功能，允许对数据进行分类和聚合。

基本使用

语法

以下是GROUP BY子句的基本语法：
"""

1. SELECT col1, col2, ..., aggregate_function(col_name)
2. FROM table_name
3. WHERE condition
4. GROUP BY col1, col2, ...;

复制代码

"""
其中，col1, col2, ...是要分组的列名，aggregate_function是用于聚合数据的函数，如SUM,
AVG, MAX, MIN等。table_name是要从中检索数据的表的名称，condition是可选的查询条
件。
示例

"""

1. SELECT column1, column2, COUNT(*)
2. FROM table_name
3. WHERE condition
4. GROUP BY column1, column2
5. ORDER BY column1, column2;

复制代码

"""
在这个示例中，选择了column1和column2两列，并对它们进行了分组。使用COUNT(*)函
数来计算每个组中的行数。使用ORDER BY子句按column1和column2升序排序结果集。
那怎么查询非分组的列名呢？
一般来讲 SELECT 中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自
于 group by 后面的列。
从MySQL 5.7.5之前默认是支持的，之后的版本默认SQL模式包括ONLY_FULL_GROUP_BY,
"""

1. mysql> select version();
2. +-----------+
3. | version() |
4. +-----------+
5. | 8.0.18    |
6. +-----------+
7. 1 row in set (0.06 sec)
8. mysql> select @@global.sql_mode;
9. +-----------------------------------------------------------------------------------------------------------------------+
10. | @@global.sql_mode                                                                                                     |
11. +-----------------------------------------------------------------------------------------------------------------------+
12. | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
13. +-----------------------------------------------------------------------------------------------------------------------+
14. 1 row in set (0.18 sec)

复制代码

"""
在这种模式下执行 SQL 会报下面的错误
"""

1. mysql> select * from user group by age;
2. 1055 - Expression #1 of SELECT list is not in GROUP BY clause
3. and contains nonaggregated column xxx which is not functionally
4. dependent on columns in GROUP BY clause;
5. this is incompatible with sql_mode=only_full_group_by

复制代码

"""
可以通过下面两种方式解决：

• 重新设置 sql_mode，去掉ONLY_FULL_GROUP_BY即可
  
• 使用 any_value() 或 group_concat()
  

• any_value()：将分到同一组的数据里第一条数据的指定列值作为返回数据
  
• group_concat()：将分到同一组的数据默认用逗号隔开作为返回数据
  

"""

1. mysql> select age, any_value(id) from `user` GROUP BY age;
2. +-----+---------------+
3. | age | any_value(id) |
4. +-----+---------------+
5. |   3 |             0 |
6. |   6 |             3 |
7. |   7 |             5 |
8. |  12 |             1 |
9. |  14 |             2 |
10. |  19 |             7 |
11. +-----+---------------+
12. 6 rows in set (0.15 sec)
14. mysql> select age, group_concat(id) from `user` GROUP BY age;
15. +-----+------------------+
16. | age | group_concat(id) |
17. +-----+------------------+
18. |   3 | 0,4              |
19. |   6 | 3                |
20. |   7 | 5                |
21. |  12 | 1                |
22. |  14 | 2                |
23. |  19 | 7                |
24. +-----+------------------+
25. 6 rows in set (0.05 sec)

复制代码

"""
不同版本的排序

我们以下面这个user表为例，看下在不同版本下有什么区别？
"""

1. mysql>  show create table  user;
2. +-------+---------------------------------+
3. CREATE TABLE `user` (
4.   `id` int(11) NOT NULL,
5.   `name` varchar(255) DEFAULT NULL ,
6.   `age` int(255) DEFAULT NULL,
7.   PRIMARY KEY (`id`),
8.   KEY `nameIndex` (`name`) USING BTREE
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
10. +-------+---------------------------------+
11. mysql> select * from user;
12. +----+------+-----+
13. | id | name | age |
14. +----+------+-----+
15. |  0 | 陈   |   3 |
16. |  1 | 李   |  12 |
17. |  2 | 张   |  14 |
18. |  3 | 陈   |   6 |
19. |  4 | 李   |   3 |
20. |  5 | NULL |   7 |
21. |  7 | 张   |  19 |
22. +----+------+-----+
23. 7 rows in set (0.06 sec)

复制代码

"""
在MySQL 5.7中

在MySQL 8.0中

同样的SQL在MySQL 5.7中与MySQL 8.0中执行结果是不一样的，在MySQL 5.7中数据默认
按照分组列升序展示，在MySQL 8.0中则没有排序，所以在MySQL 5.7中执行计划里面的
Extra 这个字段的多了一个 Using filesort。
因为在MySQL 5.7中，GROUP BY 默认隐式排序，按GROUP BY列按升序排序。如果不想在
执行 GROUP BY 时执行排序的开销，可以禁用排序：
"""

1. GROUP BY column_name ORDER BY NULL

复制代码

"""
然而，在MySQL 8.0中，GROUP BY默认不会使用排序功能，除非使用了ORDER BY语句。
工作原理

执行流程

我们先来看下下面这条sql语句在MySQL 5.7中的执行计划:
"""

1.   explain select age,count(age) from user where name ='李'  GROUP BY age;

复制代码

"""

在Extra字段里面， 我们可以看到三个信息：

• Using index condition： 表示这个语句使用了索引来过滤；
  
• Using temporary： 表示使用了临时表；
  
• Using filesort： 表示需要排序
  

这个语句的执行流程是这样的：

• 创建一个临时表。表里有两个字段 age 和 count(age)、主键为 age
  
• 扫描普通索引nameIndex ，找到 name ='李' 主键 ID；
  
• 通过主键ID，回表找到 age=12 字段值
  
• 判断临时表中有没有主键为 12 的行
  

• 没有就插入一个记录(12,1)
  
• 就将12这一行的count(age)值加1
  

遍历完成后， 需要根据字段 age 做排序

• 初始化sort_buffer， sort_buffer中有两个字段
  
• 从内存临时表中一行一行地取出数据，分别存入sort_buffer中的两个字段里。 这个过程要对内存临时表做全表扫描。
  
• 在sort_buffer中根据age的值进行排序。
  
• 排序完成后，返回给客户端。
  

内存临时表排序的时候使用了rowid排序方法。
"""

1. "filesort_summary":{  
2.         "rows":2,  
3.         "examined_rows":2,  
4.         "number_of_tmp_files":0,  
5.         "sort_buffer_size":320,  
6.         "sort_mode":"<sort_key, rowid>"  
7. }

复制代码

"""
临时表

内存临时表

由于本例子只有几行数据， 内存可以放得下，因此只使用了内存临时表。 但是内存临时表的
大小是有限制的， 参数 tmp_table_size 表示临时表内存大小， 默认是16M。内存临时表使
用的是memory引擎。
"""

1. mysql> show  variables like '%tmp_table_size%';
2. +----------------+---------+
3. | Variable_name  | Value   |
4. +----------------+---------+
5. | tmp_table_size | 2097152 |
6. +----------------+---------+
7. 1 row in set (0.04 sec)

复制代码

"""
磁盘临时表

如果临时表大小超过了tmp_table_size， 那么内存临时表就会转成磁盘临时表。磁盘临时表
使用的引擎默认是InnoDB， 是由参数internal_tmp_disk_storage_engine 控制
"""

1. mysql> show variables like '%internal_tmp_disk_storage_engine%';
2. +----------------------------------+--------+
3. | Variable_name                    | Value  |
4. +----------------------------------+--------+
5. | internal_tmp_disk_storage_engine | InnoDB |
6. +----------------------------------+--------+
7. 1 row in set (0.04 sec)

复制代码

"""

为了复现生成磁盘临时表，把 tmp_table_size设置小一点，通过查

Created_tmp_disk_tables值，查看对应的磁盘临时表数量

"""

1. mysql> set tmp_table_size=1;
2. select age,count(age) from user where name ='李'  GROUP BY age ORDER BY age ;
3. show status like '%Created_tmp%';
4. Query OK, 0 rows affected (0.02 sec)
6. +-----+------------+
7. | age | count(age) |
8. +-----+------------+
9. |   3 |          1 |
10. |  12 |          1 |
11. +-----+------------+
12. 2 rows in set (0.03 sec)
14. +-------------------------+-------+
15. | Variable_name           | Value |
16. +-------------------------+-------+
17. | Created_tmp_disk_tables | 3     |
18. | Created_tmp_files       | 60    |
19. | Created_tmp_tables      | 6     |
20. +-------------------------+-------+
21. 3 rows in set (0.04 sec)

复制代码

"""
Created_tmp_tables：在内存中创建内部临时表时或在磁盘，服务器将递增此值。
Created_tmp_disk_tables：在磁盘上创建内部临时表时， 服务器递增此值
一般理想的配置是：
"""
[code]Created_tmp_disk_tables / Created_tmp_tables * 100%