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

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

按照效率排序count()<count(id)<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的语义是取这两个子查询结果的并集。并集的意思是这两个集合加起来,重复的行只保留一行
在这里插入图片描述

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

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

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

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

3.执行第二个子查询:

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

4.从临时表中按行取出数据,返回结果,并删除临时表,结果中包含两行数据分别是1000和999
在这里插入图片描述
这里的内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键id的唯一性约束,实现了union的语义

如果把上面的语句中union改成union all的话,就没有了去重的语义。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了
在这里插入图片描述
第二行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结果如下:
在这里插入图片描述
在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做排序,得到结果集返回给客户端
在这里插入图片描述
内存临时表排序流程图:
在这里插入图片描述在这里插入图片描述
如果并不需要对结果进行排序,在SQL语句末尾增加order by null:

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

在这里插入图片描述
由于表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的结果是无序的,所以就需要有一个临时表来记录并统计结果。那么,如果扫描过程中可以保证出现的数据是有序的就可以了

假设,现在有一个类似下图的这么一个数据结构
在这里插入图片描述
如果可以确保输入的数据是有序的,那么计算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;

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

原文链接:加载失败,请重新获取