• 技术文章 >数据库 >mysql教程

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

    青灯夜游青灯夜游2021-09-08 09:58:54转载268
    本篇文章带大家了解一下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,按行累加

    1.png

    二、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的语义是取这两个子查询结果的并集。并集的意思是这两个集合加起来,重复的行只保留一行

    在这里插入图片描述

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

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

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

    3.执行第二个子查询:

    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字段里面,可以看到三个信息:

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

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

    2.扫描表t1的索引a,依次取出叶子节点上的id值,计算id%10的结果,记为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

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

    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字段可以看到,这个语句的执行不再需要临时表,也不需要排序了

    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语句的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:csdn,如有侵犯,请联系admin@php.cn删除
    专题推荐:MySQL count() union group by
    上一篇:详细了解MySQL中的主备、主从和读写分离 下一篇:浅谈Mysql5.7中怎么搭建主从复制?
    线上培训班

    相关文章推荐

    • 带你聊聊MySQL中的事务隔离• 深入了解MySQL中的锁(全局锁、表级锁、行锁)• 如何解决php emoji mysql 错误问题• 解析mysql的安装与使用(收藏)• 详细了解MySQL中的主备、主从和读写分离

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网