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

  在 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;

  在这个示例中,选择了column1column2两列,并对它们进行了分组。运用COUNT(*)函数来核算每个组中的行数。运用ORDER BY子句按column1column2升序排序成果集。

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

  一般来讲 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——GROUP BY详解与优化

MySQL 8.0中,

MySQL——GROUP BY详解与优化
  相同的SQLMySQL 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;

MySQL——GROUP BY详解与优化
  在Extra字段里边, 咱们能够看到三个信息:

  • Using index condition: 表明这个句子运用了索引来过滤;
  • Using temporary: 表明运用了暂时表;
  • Using filesort: 表明需求排序

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

  1. 创建一个暂时表。表里有两个字段 agecount(age)、主键为 age
  2. 扫描普通索引nameIndex ,找到 name ='李' 主键 ID
  3. 经过主键ID,回表找到 age=12 字段值
  4. 判断暂时表中有没有主键为 12 的行
    • 没有就刺进一个记录(12,1)(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>"  
}

  关于 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的索引,对索引列分组不需求暂时表,也不需求排序。

MySQL——GROUP BY详解与优化

增大tmp_table_size

  假如group by需求核算的数据量不大, 尽量只运用内存暂时表; 能够经过适当调大 tmp_table_size参数, 来避免用到磁盘暂时表。

运用SQL_BIG_RESULT

  假如一个group by句子中需求放到暂时表上的数据量特别大,仍是依照上面的逻辑,先放到内存暂时表,刺进一部分数据后,发现内存暂时表不行用了再转成磁盘暂时表,那感觉就没必要了,那怎么样能够直接运用磁盘暂时表呢?

   运用SQL_BIG_RESULT, 能够与 SELECT 句子中的GROUP BYDISTINCT关键字一起运用。它的作用是告知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 BYDISTINCT 的差异

  首先是运用方法不同:虽然在某些情况下 DISTINCTGROUP BY 能够实现相同的成果,但通常情况下,它们用于不同的意图,一个是去重,一个是聚合。

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

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

  再就是在性能上:假如在不需求履行聚合函数时,DISTINCTGROUP BY这两条句子的语义和履行流程是相同的,因而履行性能也相同

MySQL——GROUP BY详解与优化

运用场景

  GROUP BY通常用于以下场景:

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

参考

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