group by的理解和优化

因为最近接触到数据分析的一些东西,会经常用到group by.


1 group by的字段加索引是否有效?
 explain select sum(lastweekcomratio),sum(thisweekcomratio) from 
 zjf_channel_comparedappid group by comratiogap  ;
 如果comratiogap 是索引,时间1.6-3,执行计划:
 

 如果comratiogap 不是索引,执行时间3-6s,执行计划:

 group by字段是索引,则不会导致Using filesort,并且查询几乎快了一倍。

2 Using filesort的理解   
如果mysql在排序的时候没有使用到索引那么就会输出using filesort。那上面的sql没有order by,为啥group by会排序呢?
考虑下group by的含义,按照某列的值来分组,这里分组的含义就是把值相等的放到一起,所以在group by的时候一定会先order by然后再进行分组的操作。

MySQL根据sort_buffer_size来判断是否使用磁盘临时文件,如果需要排序的数据能放入sort_buffer_size的内存,则无需使用磁盘临时文件,仅仅使用内存就可以了,此时explain只会输出using filesort 否则需要使用磁盘临时文件explain会输出using temporary;using filesort;这里需要指出:如果使用了Using tempory(磁盘临时文件),那么就需要磁盘IO(很慢),肯定会拉慢sql的执行效率,必须避免;如果使用了Using filesort,此时会做内存中的排序,也会一定程度影响效率。


3 sql改写
如果SQL的条件查询和分组都只是一个表的,可以采取子查询的方式
这SQL的条件查询和分组都只是一个表的,内联后数据就变得臃肿了,这时候再进行条件查询和分组是否太吃亏了,我们可以尝试一下提前进行分组和条件查询,实现方法就是子查询联合内联查询。 
实例:SELECT
    attack_ip,
    country,
    province,
    city,
    line,
    info_update_time AS attack_time,
    sum( attack_count ) AS attack_times 
FROM
    `blacklist_attack_ip`
    INNER JOIN `blacklist_ip_count_date` ON `blacklist_attack_ip`.`attack_ip` = `blacklist_ip_count_date`.`ip` 
WHERE
    `attack_count` > 0 
    AND `date` BETWEEN '2017-10-13 00:00:00' 
    AND '2017-10-13 23:59:59' 
GROUP BY
    `ip`
    LIMIT 10 OFFSET 1000
 改写成:
 (SELECT
    attack_ip,
    country,
    province,
    city,
    line,
    info_update_time AS attack_time,
    sum( attack_count ) AS attack_times 
FROM
    `blacklist_attack_ip` 
WHERE
    `attack_count` > 0 
    AND `date` BETWEEN '2017-10-13 00:00:00' 
    AND '2017-10-13 23:59:59' 
GROUP BY
    `ip`) A     INNER JOIN `blacklist_ip_count_date` ON A.`attack_ip` = `blacklist_ip_count_date`.`ip` 
        LIMIT 10 OFFSET 1000

4 说明下面语句mysql执行顺序       
select t.a,t.b,sum(t1.c) as num from t left join t1 on t.name=t1.name where t.a='a' group by t.b having num>3 order by t.a limit 2;

先执行 where t.a='a'来筛选,然后select内容,然后进行group by和having,最后orderby limit;

参考:https://blog.csdn.net/Tim_phper/article/details/78344444

https://blog.csdn.net/xinyuan_java/article/details/91435745

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值