group by的理解和优化

灰太狼 2024-04-17 06:25 51阅读 0赞

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

1 group by的字段加索引是否有效？  
 explain select sum(lastweekcomratio),sum(thisweekcomratio) from   
 zjf\_channel\_comparedappid group by comratiogap  ;  
 如果comratiogap 是索引，时间1.6-3，执行计划：  
 ![20190826184742609.png][]

如果comratiogap 不是索引，执行时间3-6s，执行计划：![201908261848052.png][]

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

[20190826184742609.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/0c1c2cc769bb4a7fbe8ae0ccf0ddef1e.png
[201908261848052.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/16/70f56d54a8dc41f795d864f65640d8b3.png