3K字详解MySQL——GROUP BY优化

100人浏览 2024-08-09 11:18:22

在 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
本文链接：https://www.fuwuqi.cn/mysql/Q4jYZ9wW1g.html

上一篇 > Python 之 MySql“未解之谜”05--表示金钱的用什么类型？
下一篇 > canal解析mysql的binlog实时推送到kafka