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"""
可以通过下面两种方式解决:
[*]重新设置 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 tableuser;
+-------+---------------------------------+
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>"
}"""
临时表
内存临时表
由于本例子只有几行数据, 内存可以放得下,因此只使用了内存临时表。 但是内存临时表的
大小是有限制的, 参数 tmp_table_size 表示临时表内存大小, 默认是16M。内存临时表使
用的是memory引擎。
"""
mysql> showvariables 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%
页:
[1]