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