MySQL——GROUP BY详解与优化

你的名字 2023-10-13 22:22 143阅读 0赞

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

## 基本使用 ##

### 语法 ###

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

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>SELECT col1, col2, ..., aggregate_function(col_name)</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>FROM table_name</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>WHERE condition</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>GROUP BY col1, col2, ...;</code></td> 
  </tr> 
 </tbody> 
</table>

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

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

件。

### 示例 ###

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>SELECT column1, column2, COUNT(*)</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>FROM table_name</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>WHERE condition</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>GROUP BY column1, column2</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>ORDER BY column1, column2;</code></td> 
  </tr> 
 </tbody> 
</table>

"""  
在这个示例中，选择了column1和column2两列，并对它们进行了分组。使用COUNT(\*)函

数来计算每个组中的行数。使用ORDER BY子句按column1和column2升序排序结果集。

**那怎么查询非分组的列名呢？**

一般来讲 SELECT 中的值要么是来自于聚合函数（sum、avg、max等）的结果，要么是来自

于 group by 后面的列。

从MySQL 5.7.5之前默认是支持的，之后的版本默认SQL模式包括ONLY\_FULL\_GROUP\_BY,  
"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; select version();</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| version() |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 8.0.18 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>1 row in set (0.06 sec)</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; select @@global.sql_mode;</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----------------------------------------------------------------------------------------------------------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| @@global.sql_mode |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----------------------------------------------------------------------------------------------------------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----------------------------------------------------------------------------------------------------------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>1 row in set (0.18 sec)</code></td> 
  </tr> 
 </tbody> 
</table>

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

--------------------

"""

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

"""

--------------------

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

1.  重新设置 sql\_mode，去掉ONLY\_FULL\_GROUP\_BY即可
2.  使用 any\_value() 或 group\_concat()

*  any\_value()：将分到同一组的数据里第一条数据的指定列值作为返回数据
 *  group\_concat()：将分到同一组的数据默认用逗号隔开作为返回数据

--------------------

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; select age, any_value(id) from `user` GROUP BY age;</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+---------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| age | any_value(id) |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+---------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 3 | 0 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 6 | 3 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 7 | 5 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 12 | 1 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 14 | 2 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 19 | 7 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+---------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>6 rows in set (0.15 sec)</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; select age, group_concat(id) from `user` GROUP BY age;</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| age | group_concat(id) |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 3 | 0,4 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 6 | 3 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 7 | 5 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 12 | 1 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 14 | 2 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 19 | 7 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>6 rows in set (0.05 sec)</code></td> 
  </tr> 
 </tbody> 
</table>

"""

--------------------

### 不同版本的排序 ###

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

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; show create table user;</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-------+---------------------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>CREATE TABLE `user` (</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>`id` int(11) NOT NULL,</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>`name` varchar(255) DEFAULT NULL ,</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>`age` int(255) DEFAULT NULL,</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>PRIMARY KEY (`id`),</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>KEY `nameIndex` (`name`) USING BTREE</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>) ENGINE=InnoDB DEFAULT CHARSET=utf8 </code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-------+---------------------------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; select * from user;</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----+------+-----+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| id | name | age |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----+------+-----+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 0 | 陈 | 3 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 1 | 李 | 12 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 2 | 张 | 14 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 3 | 陈 | 6 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 4 | 李 | 3 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 5 | NULL | 7 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 7 | 张 | 19 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----+------+-----+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>7 rows in set (0.06 sec)</code></td> 
  </tr> 
 </tbody> 
</table>

"""

--------------------

在MySQL 5.7中

--------------------

![image][]

--------------------

在MySQL 8.0中

--------------------

![image][image 1]

--------------------

同样的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 时执行排序的开销，可以禁用排序：

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>GROUP BY column_name ORDER BY NULL</code></td> 
  </tr> 
 </tbody> 
</table>

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

## 工作原理 ##

### 执行流程 ###

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

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>explain select age,count(age) from user where name ='李' GROUP BY age;</code></td> 
  </tr> 
 </tbody> 
</table>

"""

![image][image 2]

--------------------

**在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排序方法。

--------------------

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>"filesort_summary":{ </code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>"rows":2, </code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>"examined_rows":2, </code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>"number_of_tmp_files":0, </code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>"sort_buffer_size":320, </code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>"sort_mode":"&lt;sort_key,&nbsp;rowid&gt;" </code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>}</code></td> 
  </tr> 
 </tbody> 
</table>

"""

--------------------

### 临时表 ###

##### 内存临时表 #####

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

大小是有限制的， 参数 tmp\_table\_size 表示临时表内存大小， 默认是16M。内存临时表使

用的是memory引擎。

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; show variables like '%tmp_table_size%';</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----------------+---------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| Variable_name | Value |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----------------+---------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| tmp_table_size | 2097152 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----------------+---------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>1 row in set (0.04 sec)</code></td> 
  </tr> 
 </tbody> 
</table>

"""

##### 磁盘临时表 #####

如果临时表大小超过了tmp\_table\_size， 那么内存临时表就会转成磁盘临时表。磁盘临时表

使用的引擎默认是InnoDB， 是由参数internal\_tmp\_disk\_storage\_engine 控制

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; show variables like '%internal_tmp_disk_storage_engine%';</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----------------------------------+--------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| Variable_name | Value |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----------------------------------+--------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| internal_tmp_disk_storage_engine | InnoDB |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+----------------------------------+--------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>1 row in set (0.04 sec)</code></td> 
  </tr> 
 </tbody> 
</table>

"""

--------------------

> 为了复现生成磁盘临时表，把 tmp\_table\_size设置小一点，通过查

> Created\_tmp\_disk\_tables值，查看对应的磁盘临时表数量

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>mysql&gt; set tmp_table_size=1;</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>select age,count(age) from user where name ='李' GROUP BY age ORDER BY age ;</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>show status like '%Created_tmp%';</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>Query OK, 0 rows affected (0.02 sec)</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| age | count(age) |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 3 | 1 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| 12 | 1 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-----+------------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>2 rows in set (0.03 sec)</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-------------------------+-------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| Variable_name | Value |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-------------------------+-------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| Created_tmp_disk_tables | 3 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| Created_tmp_files | 60 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>| Created_tmp_tables | 6 |</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>+-------------------------+-------+</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>3 rows in set (0.04 sec)</code></td> 
  </tr> 
 </tbody> 
</table>

"""

--------------------

Created\_tmp\_tables：在内存中创建内部临时表时或在磁盘，服务器将递增此值。

Created\_tmp\_disk\_tables：在磁盘上创建内部临时表时， 服务器递增此值

**一般理想的配置是：**

"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>Created_tmp_disk_tables / Created_tmp_tables * 100% &lt;= 25% </code></td> 
  </tr> 
 </tbody> 
</table>

"""

## 分组优化 ##

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

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

### 使用索引 ###

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

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

![image][image 3]

--------------------

### 增大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的示例：  
"""

<table> 
 <tbody> 
  <tr> 
   <td></td> 
   <td><code>SELECT SQL_BIG_RESULT col1, col2</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>FROM my_table</code></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td><code>GROUP BY col1;</code></td> 
  </tr> 
 </tbody> 
</table>

"""  
需要注意的是，使用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这两条语句的语义

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

--------------------

![image][image 4]

--------------------

## 使用场景 ##

GROUP BY通常用于以下场景：

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

[image]: https://img-blog.csdnimg.cn/img_convert/6cb16efa9e7cd7f83bdb118712eb66f7.png
[image 1]: https://img-blog.csdnimg.cn/img_convert/46d8c29ab1373446d984a7572446b4b5.png
[image 2]: https://img-blog.csdnimg.cn/img_convert/73a5bd20ea7843c767fe250cf555ede0.png
[image 3]: https://img-blog.csdnimg.cn/img_convert/0931b8d8d5e7c007d652625b7e3f4923.png
[image 4]: https://img-blog.csdnimg.cn/img_convert/9936031aabbb3c9fa8836defd97995a9.png