本文正在参与「金石方案」

在 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

能够经过下面两种方法处理：

重新设置 sql_mode，去掉ONLY_FULL_GROUP_BY即可
运用 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：表明需求排序

这个句子的履行流程是这样的：

创建一个暂时表。表里有两个字段 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排序办法。

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

关于 MySQL 排序这块内容，我们能够先看下这篇文章：MySQL排序优化与作业原理

暂时表

内存暂时表

由于本例子只要几行数据，内存能够放得下，因而只运用了内存暂时表。但是内存暂时表的巨细是有约束的，参数 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通常用于以下场景：

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

参考

MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.15 GROUP BY Optimization

MySQL——GROUP BY详解与优化

根本运用

语法

示例

不同版别的排序

作业原理

履行流程

暂时表

内存暂时表

磁盘暂时表

分组优化

运用索引

增大`tmp_table_size`

运用`SQL_BIG_RESULT`

禁用排序

`GROUP BY` 和 `DISTINCT` 的差异

运用场景

参考

作者信息

MySQL——GROUP BY详解与优化

根本运用

语法

示例

不同版别的排序

作业原理

履行流程

暂时表

内存暂时表

磁盘暂时表

分组优化

运用索引

增大tmp_table_size

运用SQL_BIG_RESULT

禁用排序

GROUP BY 和 DISTINCT 的差异

运用场景

参考

相关文章

软考高项第 17 章 – 项目干系人管理

需求评审图什么？

面向对象的思考

IOS OpenGL ES GPUImage 图像混合 GPUImageNormalBlendFilter

作者信息

增大`tmp_table_size`

运用`SQL_BIG_RESULT`

`GROUP BY` 和 `DISTINCT` 的差异