当前位置: 首页 > 数据库 > 数据库综合 >正文

详解MySQL中的count()、union()和group by语句

来源:互联网时间:2021-09-03 19:00:27编辑:网友分享
本篇文章带大家了解一下count()、union()和group by语句,补充一下MySQL知识点(不同count()的用法、union执行流程、group by语句)。
本篇文章带大家了解一下count()、union()和group by语句,补充一下MySQL知识点(不同count()的用法、union执行流程、group by语句)。

详解MySQL中的count()、union()和group by语句

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

count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加。最后返回累计值。【相关推荐:mysql视频教程】

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,按行累加

详解MySQL中的count()、union()和group by语句

分析下面这条SQL语句:

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

union的语义是取这两个子查询结果的并集。并集的意思是这两个集合加起来,重复的行只保留一行

详解MySQL中的count()、union()和group by语句

这里的内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键id的唯一性约束,实现了union的语义

如果把上面的语句中union改成union all的话,就没有了去重的语义。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了

详解MySQL中的count()、union()和group by语句
在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语句

详解MySQL中的count()、union()和group by语句

由于表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语句
从这个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语句
这个语句的执行没有再使用临时表,而是直接用了排序算法

更多编程相关知识,请访问:编程入门!!

以上就是详解MySQL中的count()、union()和group by语句的详细内容,更多请关注web开发者其它相关文章!

上一篇: 一份程序员的正版清单,这些正版软

下一篇:浅谈Mysql5.7中怎么搭建主从复制

您可能感兴趣的文章

相关阅读