百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

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

wptr33 2025-02-15 01:24 18 浏览


??在 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
复制代码

??可以通过下面两种方式解决：

  1. 重新设置 sql_mode，去掉ONLY_FULL_GROUP_BY即可
  2. 使用 any_value() 或 group_concat()
  3. any_value()：将分到同一组的数据里第一条数据的指定列值作为返回数据
  4. 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： 表示需要排序

??这个语句的执行流程是这样的：

  1. 创建一个临时表。表里有两个字段 age 和 count(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":""  
}
复制代码

??关于 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通常用于以下场景：

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

原文链接：
https://juejin.cn/post/7217665415710261305

相关推荐

MySQL进阶五之自动读写分离mysql-proxy

自动读写分离目前，大量现网用户的业务场景中存在读多写少、业务负载无法预测等情况，在有大量读请求的应用场景下，单个实例可能无法承受读取压力，甚至会对业务产生影响。为了实现读取能力的弹性扩展，分担数据库压...

Postgres vs MySQL_vs2022连接mysql数据库

...

3分钟短文 | Laravel SQL筛选两个日期之间的记录，怎么写?

引言今天说一个细分的需求，在模型中，或者使用laravel提供的EloquentORM功能，构造查询语句时，返回位于两个指定的日期之间的条目。应该怎么写？本文通过几个例子，为大家梳理一下。学习时...

一文由浅入深带你完全掌握MySQL的锁机制原理与应用

本文将跟大家聊聊InnoDB的锁。本文比较长，包括一条SQL是如何加锁的，一些加锁规则、如何分析和解决死锁问题等内容，建议耐心读完，肯定对大家有帮助的。为什么需要加锁呢？...

验证Mysql中联合索引的最左匹配原则

后端面试中一定是必问mysql的，在以往的面试中好几个面试官都反馈我Mysql基础不行，今天来着重复习一下自己的弱点知识。在Mysql调优中索引优化又是非常重要的方法，不管公司的大小只要后端项目中用到...

MySQL索引解析(联合索引/最左前缀/覆盖索引/索引下推)

目录1.索引基础...

你会看 MySQL 的执行计划(EXPLAIN)吗?

SQL执行太慢怎么办？我们通常会使用EXPLAIN命令来查看SQL的执行计划，然后根据执行计划找出问题所在并进行优化。用法简介...

MySQL 从入门到精通(四)之索引结构

索引概述索引(index)，是帮助MySQL高效获取数据的数据结构(有序)，在数据之外，数据库系统还维护者满足特定查询算法的数据结构，这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构...

mysql总结——面试中最常问到的知识点

mysql作为开源数据库中的榜一大哥，一直是面试官们考察的重中之重。今天，我们来总结一下mysql的知识点，供大家复习参照，看完这些知识点，再加上一些边角细节，基本上能够应付大多mysql相关面试了（...

mysql总结——面试中最常问到的知识点(2)

首先我们回顾一下上篇内容，主要复习了索引，事务，锁，以及SQL优化的工具。本篇文章接着写后面的内容。性能优化索引优化，SQL中索引的相关优化主要有以下几个方面：最好是全匹配。如果是联合索引的话，遵循最...

MySQL基础全知全解!超详细无废话!轻松上手~

本期内容提醒：全篇2300+字,篇幅较长，可搭配饭菜一同“食”用，全篇无废话(除了这句)，干货满满，可收藏供后期反复观看。注：MySQL中语法不区分大小写，本篇中...

深入剖析 MySQL 中的锁机制原理_mysql 锁详解

在互联网软件开发领域，MySQL作为一款广泛应用的关系型数据库管理系统，其锁机制在保障数据一致性和实现并发控制方面扮演着举足轻重的角色。对于互联网软件开发人员而言，深入理解MySQL的锁机制原理...

Java 与 MySQL 性能优化:MySQL分区表设计与性能优化全解析

引言在数据库管理领域，随着数据量的不断增长，如何高效地管理和操作数据成为了一个关键问题。MySQL分区表作为一种有效的数据管理技术，能够将大型表划分为多个更小、更易管理的分区，从而提升数据库的性能和可...

MySQL基础篇:DQL数据查询操作_mysql 查

一、基础查询DQL基础查询语法SELECT字段列表FROM表名列表WHERE条件列表GROUPBY分组字段列表HAVING分组后条件列表ORDERBY排序字段列表LIMIT...

MySql:索引的基本使用_mysql索引的使用和原理

一、索引基础概念1.什么是索引？索引是数据库表的特殊数据结构（通常是B+树），用于...

一周热门
最近发表
标签列表
赣ICP备2024040476号-12