当前位置:首页 > 数据库 > MySQL知识点补充(不同count()的用法、union执行流程、group by语句)

MySQL知识点补充(不同count()的用法、union执行流程、group by语句)

2022年11月07日 15:42:40数据库8

一、MySQL中不同count()的用法

count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加。最后返回累计值

1.对于count(主键id)来说,InnoDB引擎会遍历整张表,把每一行的id值都取出来,返回给server层。server层拿到id后,判断是不可能为空的,就按行累加

2.对于count(1)来说,InnoDB引擎遍历整张表,但不取值。server层对于返回的每一行,放一个数字1进入,判断是不可能为空的,按行累加

3.对于count(字段)来说,如果这个字段是定义为not null的话,一行行地从记录里面读出这个字段,判断不能为null,按行累加;如果这个字段定义允许为null的话,那么执行的时候,判断到有可能是null,还要把值取出来在判断一下,不是null才累加

4.对于count(*)来说,并不会把全部字段取出来,而是专门做了优化。不取值,count(*)肯定不是null,按行累加

按照效率排序 c o u n t ( 字 段 ) &lt; c o u n t ( 主 键 i d ) &lt; c o u n t ( 1 ) ≈ c o u n t ( ∗ ) count(字段)&lt;count(主键id)&lt;count(1)≈count(*) count()<count(id)<count(1)count(),所以尽量使用count(*)

二、union执行流程

为了便于量化分析,以下面表t1来举例

create table t1(id int primary key, a int, b int, index(a));

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=1000)do
    insert into t1 values(i, i, i);
    set i=i+1;
  end while;

END     

分析下面这条SQL语句:

(select 1000 as f) union (select id from t1 order by id desc limit 2);

union的语义是取这两个子查询结果的并集。并集的意思是这两个集合加起来,重复的行只保留一行
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记

  • 第二行的key=PRIMARY,说明第二个子句用到了索引id
  • 第三行的Extra字段,表示在对子查询的结果集做union的时候,使用了临时表

这个语句的执行流程如下:

1.创建一个内存临时表,这个临时表只有一个整型字段f,并且f是主键字段

2.执行第一个子查询,得到1000这个值

3.执行第二个子查询:

  • 拿到第一行id=1000,试图插入临时表中。但由于1000这个值已经存在于临时表了,违反了唯一性约束,所以插入失败,然后继续执行
  • 取到第二行id=999,插入临时表成功

4.从临时表中按行取出数据,返回结果,并删除临时表,结果中包含两行数据分别是1000和999
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
这里的内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键id的唯一性约束,实现了union的语义

如果把上面的语句中union改成union all的话,就没有了去重的语义。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
第二行Extra字段显示的是Using index,表示只使用了覆盖索引,没有用临时表

三、group by语句详解

1、group by执行流程

还是使用上面的表t1,分析下面这条SQL语句:

select id%10 as m, count(*) as c from t1 group by m;

这个语句的逻辑是把表t1里的数据,按照id%10进行分组统计,并按照m的结果排序后输出。explain结果如下:
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
在Extra字段里面,可以看到三个信息:

  • Using index,表示这个语句使用了覆盖索引,选择了索引a,不需要回表
  • Using temporary,表示使用了临时表
  • Using filesort,表示需要排序

这个语句的执行流程如下:

1.创建内存临时表,表里有两个字段m和c,主键是m

2.扫描表t1的索引a,依次取出叶子节点上的id值,计算id%10的结果,记为x

  • 如果临时表中没有主键为x的行,就插入一个记录(x,1)
  • 如果表中有主键为x的行,就将x这一行的c值加1

3.遍历完成后,再根据字段m做排序,得到结果集返回给客户端
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
内存临时表排序流程图:
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
如果并不需要对结果进行排序,在SQL语句末尾增加order by null:

select id%10 as m, count(*) as c from t1 group by m order by null;

MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
由于表t1中的id值是从1开始的,因此返回的结果集中第一行是id=1

这个例子里由于临时表只有10行,内存可以放得下,因此全程只使用了内存临时表。但是,内存临时表的大小是有限的,参数tmp_table_size就是控制整个内存大小的,默认是16M

set tmp_table_size=1024;
select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

把内存临时表的大小限制为最大1024字节,并把语句改成id%100,这样返回结果里有100行数据。但是,这时的内存临时表大小不够存下这100行数据,也就是说,执行过程中会发现内存临时表大小达到了上限。那么,这时候会把内存临时表转成磁盘临时表,磁盘临时表默认使用的引擎是InnoDB

2、group by优化方法——索引

group by的语义逻辑,是统计不同的值的个数。但是,由于每一行的id%100的结果是无序的,所以就需要有一个临时表来记录并统计结果。那么,如果扫描过程中可以保证出现的数据是有序的就可以了

假设,现在有一个类似下图的这么一个数据结构
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
如果可以确保输入的数据是有序的,那么计算group by的时候,就只需要从左到右,顺序扫描,依次累加。也就是下面这个流程:

  • 当碰到第一个1的时候,已经知道累积了X个0,结果集里的第一行就是(0,X)
  • 当碰到第一个2的时候,已经知道累积了Y个1,结果集里的第一行就是(1,Y)

按照这个逻辑执行的话,扫描到整个输入的数据结束,就可以拿到group by的结果,不需要临时表,也需要再额外排序

在MySQL5.7版本支持了generated column机制,用来实现列数据的关联更新。创建一个列z,在z列上创建一个索引

alter table t1 add column z int generated always as(id % 100), add index(z);

这样,索引z上的数据就是有序的了。group by语句就可以改成:

select z, count(*) as c from t1 group by z;

MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
从这个Extra字段可以看到,这个语句的执行不再需要临时表,也不需要排序了

3、group by优化方法——直接排序

在group by语句中加入SQL_BIG_RESULT这个提示,就可以告诉优化器:这个语句涉及的数据量很大,直接用磁盘临时表。因为磁盘临时表是B+树存储,存储效率不如数组来得高。所以MySQL优化器直接用数组来存

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

1.初始化sort_buffer,确定放入一个整型字段,记为m

2.扫描表t1的索引a,依次取出里面的id值,将id%100的值存入sort_buffer中

3.扫描完成后,对sort_buffer的字段m做排序(如果sort_buffer内存不够用,就会利用磁盘临时文件辅助排序)

4.排序完成后,就得到了一个有序数组

根据有序数组,得到数组里面的不同值,以及每个值的出现次数
MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记MySQL知识点补充(不同count()的用法、union执行流程、group by语句) _ JavaClub全栈架构师技术笔记
这个语句的执行没有再使用临时表,而是直接用了排序算法


总结:

MySQL相关内容的梳理告一段落了,这里强烈推荐极客时间中林晓斌老师的《MySQL实战45讲》专栏,让我对MySQL整个知识体系有了新的理解。这篇博客算是对之前没有提到的MySQL相关知识的补充,后面如果学习到新的知识也会继续进行补充的

作者:邋遢的流浪剑客
来源链接:https://blog.csdn.net/qq_40378034/article/details/91128275

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/database/68334.html

标签: group byMySQL
分享给朋友:

“MySQL知识点补充(不同count()的用法、union执行流程、group by语句)” 的相关文章

Linux安装MySQL(超详细)

Linux安装MySQL(超详细)

1 查看是否已经安装 Mysql rpm -qa | grep mysql 我之前装过一次! 如果你查看出来有东西,可以使用下面命令将其删除 rpm -e 文件名   2 下载官方 Mysq...

JDBC如何连接mysql数据库附详细步骤

JDBC如何连接mysql数据库附详细步骤

JDBC连接数据库在学习中是很重要的一个环节,今天给大家详细说明JDBC连接数据库需要的步骤 1.加载驱动 驱动包的下载地址 https://dev.mysql.com/downloads/connector/j/ 到时候要将这个加载到项目中然后输入...

性能优化|Mysql优化之Explain精讲

性能优化|Mysql优化之Explain精讲

Explain详解 本次测试使用的数据库版本为5.7 初始化sql语句: CREATE TABLE `film` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) D...

mysql 查询数据库内存大小

mysql 查询数据库内存大小

查询某个数据库,指定数据库名:打开information_schema,在它之下查询 select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables whe...

mysql 查询两张表 横向合并实现类似full join

mysql 查询两张表 横向合并实现类似full join

由于本人项目要求,统计一张表里用户已读、未读状态的总数,具体业务逻辑比较复杂,所以用简单两张表实现横向合并: A 表示信息未读数量统计: B 表示已读数量统计: 最终结果表: 现在要将A B表合并成一张表,由于本人比较懒,能用一个s...

mysql 查询表 所有字段

复制表结构 CREATE TABLE tb_students_copy LIKE tb_students_info; 查询mysql所有表数据、字段信息 mysql 查询表 所有字段 前2种有重复 select COLUMN_NAME...

mysql数据查询——复杂查询

《实验五 数据查询——复杂查询》实验说明 一、 适用课程:《数据库原理与应用》 实验学时:3 二、实验目的: (1) 掌握SELECT语句的语法格式; (2) 熟练运用SELECT语句进行多表连接查询; (3) 熟练运用SELECT语句进行各种复杂类型的...

mysql 查询数据结果计算百分比

mysql 查询数据结果计算百分比

数量÷总数×100=百分比  SELECT user_id ,COUNT(*) total, CONCAT(CAST(CONVERT(100 * CAST(count(*) AS DECIMAL) / CAST((SELECT COUNT(*) FROM 表名)...

Mysql 查看连接数,状态 最大并发数(赞)

-- show variables like '%max_connections%'; 查看最大连接数 set global max_connections=1000 重新设置 mysql> show...

Mysql查询某字段值重复的数据

查询user表中,user_name字段值重复的数据及重复次数 select user_name,count(*) as count from user group by user_name having count>1;   作者:sot...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。