MySQL——GROUP BY详解与优化

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

基本使用

语法

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

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

"""
其中,col1, col2, ...是要分组的列名,aggregate_function是用于聚合数据的函数,如SUM,

AVG, MAX, MIN等。table_name是要从中检索数据的表的名称,condition是可选的查询条

件。

示例

"""

SELECT column1, column2, COUNT(*)
FROM table_name
WHERE condition
GROUP BY column1, column2
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,
"""

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.06 sec)
mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.18 sec)

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


"""

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

"""


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

  1. 重新设置 sql_mode,去掉ONLY_FULL_GROUP_BY即可

  2. 使用 any_value() 或 group_concat()

  • any_value():将分到同一组的数据里第一条数据的指定列值作为返回数据

  • group_concat():将分到同一组的数据默认用逗号隔开作为返回数据


"""

mysql> select age, any_value(id) from `user` GROUP BY age;
+-----+---------------+
| age | any_value(id) |
+-----+---------------+
|   3 |             0 |
|   6 |             3 |
|   7 |             5 |
|  12 |             1 |
|  14 |             2 |
|  19 |             7 |
+-----+---------------+
6 rows in set (0.15 sec)

mysql> select age, group_concat(id) from `user` GROUP BY age;
+-----+------------------+
| age | group_concat(id) |
+-----+------------------+
|   3 | 0,4              |
|   6 | 3                |
|   7 | 5                |
|  12 | 1                |
|  14 | 2                |
|  19 | 7                |
+-----+------------------+
6 rows in set (0.05 sec)

"""


不同版本的排序

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

"""

mysql>  show create table  user;
+-------+---------------------------------+
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL ,
  `age` int(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `nameIndex` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
+-------+---------------------------------+
mysql> select * from user;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  0 | 陈   |   3 |
|  1 | 李   |  12 |
|  2 | 张   |  14 |
|  3 | 陈   |   6 |
|  4 | 李   |   3 |
|  5 | NULL |   7 |
|  7 | 张   |  19 |
+----+------+-----+
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 时执行排序的开销,可以禁用排序:

"""

GROUP BY column_name ORDER BY NULL

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

工作原理

执行流程

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

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

"""


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

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

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

  1. 创建一个临时表。表里有两个字段 age 和 count(age)、主键为 age
  2. 扫描普通索引nameIndex ,找到 name ='李' 主键 ID;
  3. 通过主键ID,回表找到 age=12 字段值
  4. 判断临时表中有没有主键为 12 的行
  • 没有就插入一个记录(12,1)
  • 就将12这一行的count(age)值加1

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

  1. 初始化sort_buffer, sort_buffer中有两个字段

  2. 从内存临时表中一行一行地取出数据,分别存入sort_buffer中的两个字段里。 这个过程要对内存临时表做全表扫描。

  3. 在sort_buffer中根据age的值进行排序。

  4. 排序完成后,返回给客户端。

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


"""

"filesort_summary":{  
	"rows":2,  
	"examined_rows":2,  
	"number_of_tmp_files":0,  
	"sort_buffer_size":320,  
	"sort_mode":"<sort_key, rowid>"  
}

"""


临时表

内存临时表

由于本例子只有几行数据, 内存可以放得下,因此只使用了内存临时表。 但是内存临时表的

大小是有限制的, 参数 tmp_table_size 表示临时表内存大小, 默认是16M。内存临时表使

用的是memory引擎。

"""

mysql> show  variables like '%tmp_table_size%';
+----------------+---------+
| Variable_name  | Value   |
+----------------+---------+
| tmp_table_size | 2097152 |
+----------------+---------+
1 row in set (0.04 sec)

"""

磁盘临时表

如果临时表大小超过了tmp_table_size, 那么内存临时表就会转成磁盘临时表。磁盘临时表

使用的引擎默认是InnoDB, 是由参数internal_tmp_disk_storage_engine 控制

"""

mysql> show variables like '%internal_tmp_disk_storage_engine%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
1 row in set (0.04 sec)

"""


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

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

"""

mysql> set tmp_table_size=1;
select age,count(age) from user where name ='李'  GROUP BY age ORDER BY age ;
show status like '%Created_tmp%';
Query OK, 0 rows affected (0.02 sec)

+-----+------------+
| age | count(age) |
+-----+------------+
|   3 |          1 |
|  12 |          1 |
+-----+------------+
2 rows in set (0.03 sec)

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 3     |
| Created_tmp_files       | 60    |
| Created_tmp_tables      | 6     |
+-------------------------+-------+
3 rows in set (0.04 sec)

"""


Created_tmp_tables:在内存中创建内部临时表时或在磁盘,服务器将递增此值。

Created_tmp_disk_tables:在磁盘上创建内部临时表时, 服务器递增此值

一般理想的配置是:

"""

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25% 

"""

分组优化

不论是使用内存临时表还是磁盘临时表,group by需要构造一个带唯一索引的表, 执行代价

都是比较高的。如果表的数据量比较大,执行起来就会很慢。

使用索引

如果可以确保输入的数据是有序的,那么 group by的时候, 就只需要从左到右,顺序扫描,

依次累加。那就是InnoDB的索引,对索引列分组不需要临时表,也不需要排序。


增大tmp_table_size

如果group by需要统计的数据量不大, 尽量只使用内存临时表; 可以通过适当调大

tmp_table_size参数, 来避免用到磁盘临时表。

使用SQL_BIG_RESULT

如果一个group by语句中需要放到临时表上的数据量特别大,还是按照上面的逻辑,先放到

内存临时表,插入一部分数据后,发现内存临时表不够用了再转成磁盘临时表,那感觉就没必

要了,那怎么样可以直接使用磁盘临时表呢?

使用SQL_BIG_RESULT, 可以与 SELECT 语句中的GROUP BY或DISTINCT关键字一起使用。

它的作用是告诉MySQL优化器,查询结果集较大,直接用磁盘临时表。MySQL会使用基于磁

盘的临时表进行排序

例如,以下是一个使用SQL_BIG_RESULT的示例:
"""

SELECT SQL_BIG_RESULT col1, col2
FROM my_table
GROUP BY col1;

"""
需要注意的是,使用SQL_BIG_RESULT会增加服务器的内存和CPU使用量,因此应该仔细评

估是否需要使用它。通常情况下,只有在处理大型数据集时才需要使用。

禁用排序

在MySQL 5.7中,如果对group by语句的结果没有排序要求,在语句后面加 order by null,

禁用排序,减少不必要的排序开销。

GROUP BY 和 DISTINCT 的区别

首先是使用方式不同:虽然在某些情况下 DISTINCT 和 GROUP BY 可以实现相同的结果,但

通常情况下,它们用于不同的目的,一个是去重,一个是聚合。

  • DISTINCT 关键字用于返回 SELECT 查询中不同的值,即去重。它会扫描所有的行并去除重复的行。

  • GROUP BY 关键字用于将结果集按照指定列进行分组,并对每个分组执行聚合函数。

再就是在性能上:如果在不需要执行聚合函数时,DISTINCT 和GROUP BY这两条语句的语义

和执行流程是相同的,因此执行性能也相同



使用场景

GROUP BY通常用于以下场景:

  • 对数据进行分类和统计
  • 按特定条件对数据进行分组
  • 进行聚合操作,如计算总数、平均数、最大值、最小值等
  • 生成报表或汇总数据

热门相关:有个人爱你很久   戏精老公今天作死没   闺范   金粉   裙上之臣