本篇文章带大家了解一下count()、union()和group by语句,补充一下MySQL知识点(不同count()的用法、union执行流程、group by语句)。
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,按行累加
为了便于量化分析,以下面表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的语义是取这两个子查询结果的并集。并集的意思是这两个集合加起来,重复的行只保留一行
这个语句的执行流程如下:
1.创建一个内存临时表,这个临时表只有一个整型字段f,并且f是主键字段
2.执行第一个子查询,得到1000这个值
3.执行第二个子查询:
4.从临时表中按行取出数据,返回结果,并删除临时表,结果中包含两行数据分别是1000和999
这里的内存临时表起到了暂存数据的作用,而且计算过程还用上了临时表主键id的唯一性约束,实现了union的语义
如果把上面的语句中union改成union all的话,就没有了去重的语义。这样执行的时候,就依次执行子查询,得到的结果直接作为结果集的一部分,发给客户端。因此也就不需要临时表了
第二行Extra字段显示的是Using index,表示只使用了覆盖索引,没有用临时表
还是使用上面的表t1,分析下面这条SQL语句:
select id%10 as m, count(*) as c from t1 group by m;
这个语句的逻辑是把表t1里的数据,按照id进行分组统计,并按照m的结果排序后输出。explain结果如下:
在Extra字段里面,可以看到三个信息:
这个语句的执行流程如下:
1.创建内存临时表,表里有两个字段m和c,主键是m
2.扫描表t1的索引a,依次取出叶子节点上的id值,计算id的结果,记为x
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
group by的语义逻辑,是统计不同的值的个数。但是,由于每一行的id%100的结果是无序的,所以就需要有一个临时表来记录并统计结果。那么,如果扫描过程中可以保证出现的数据是有序的就可以了
假设,现在有一个类似下图的这么一个数据结构
如果可以确保输入的数据是有序的,那么计算group by的时候,就只需要从左到右,顺序扫描,依次累加。也就是下面这个流程:
按照这个逻辑执行的话,扫描到整个输入的数据结束,就可以拿到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字段可以看到,这个语句的执行不再需要临时表,也不需要排序了
在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语句的详细内容。更多信息请关注PHP中文网其他相关文章!