本文正在参与「金石方案」
在
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,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