MySQL Group by的优化详解
一个标准的Groupby 语句包含排序、分组、聚合函数，比如 selecta,count(*)fromtgroupbya; 这个语句默认使用a 进行排序。如果a 列没有索引，那么就会创建临时表来统计a和count(*)，然后再通过sort_buffer 按a 进行排序。
标准的执行流程
结构：
createtablet1(idintprimarykey,aint,bint,index(a)); delimiter;; createprocedureidata() begin declareiint; seti=1; while(i<=1000)do insertintot1values(i,i,i); seti=i+1; endwhile; end;; delimiter; callidata();
函数就是向t1 中插入1000条语句，从(1,1,1) 到(1000,1000,1000)。
执行 selectid%10asm,count(*)ascfromt1groupbym;
解析：
Usingindex，表示这个语句使用了覆盖索引，选择了索引a，不需要回表；
Usingtemporary，表示使用了临时表；
Usingfilesort，表示需要排序。
过程：
1、创建内存临时表，表里有两个字段m和c，主键是m；
2、扫描表t1的索引a，依次取出叶子节点上的id值，计算id%10的结果，记为x；
1）如果临时表中没有主键为x的行，就插入一个记录(x,1);
2）如果表中有主键为x的行，就将x这一行的c值加1；
第2 步如果发现内存临时表存储的总字段长度到达参数 tmp_table_size 设置的大小，那么就会将内存临时表升级为磁盘临时表，然后重新开始遍历计算。
3、遍历完成后，再根据字段m做排序，得到结果集返回给客户端。
最后的排序就是下图虚线框中的操作，如果sort_buffer 设置的大小不够大，那么就会使用临时表来辅助排序。
优化
未优化（也就是分组列没有索引）的groupby 的总过程可以概括为：因为数据是无序的，所以需要创建临时表，然后一个一个判断属于哪个分组，最后再根据分组列进行排序。所以，优化可以有两个思路：
去掉排序
在明确返回的数据不需要排序的情况下，可以禁止排序，也就是将上面的语句改成 selecta,count(*)fromtgroupbyaorderbynull。
顺序排列
如果记录都按照排序字段排序，那么数据就变成了下面的结构：
这样在实际获取要返回的字段或计算聚合函数时，只需要按顺序依次访问，等到列值变成下一个就知道当前组访问结束，将之前统计的数据直接返回。这样就避免了创建临时表，同时排序也不需要使用sort_buffer 进行额外排序。这样就极大地提高了执行的效率。
实现
1、如果分组字段适合创建索引就直接为分组字段创建索引。
MySQL5.7版本支持了generatedcolumn机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列z，然后在z列上创建一个索引（如果是MySQL5.6及之前的版本，你也可以创建普通列和索引，来解决这个问题）
altertablet1addcolumnzintgeneratedalwaysas(id%100),addindex(z);
然后解析：
这时没有用到临时表和额外排序，所以性能提升。
2、如果分组字段不适合（使用率很低），那么可以使用 SQL_BIG_RESULT 来尝试优化。
在groupby语句中加入SQL_BIG_RESULT这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。MySQL的优化器一看，磁盘临时表是B+树存储，存储效率不如数组来得高。所以，既然使用SQL_BIG_RESULT来说明数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。所以在使用 SQL_BIG_RESULT 后优化器会使用数组结构的磁盘临时表。
但是如果在未达到磁盘临时表的使用条件是不会使用磁盘临时表的，也就是在sort_buffer 空间能够存储要返回和排序的总字段长度时，就使用数组结构的sort_buffer，如果总字段超过sort_buffer 大小，那么就再加上数组结构的磁盘临时表来帮助排序。
那么在sort_buffer 空间足够的情况下，sort_buffer 内部就会对数据进行排序，这样也就起到了索引的作用，
还是以上面的例子来看，使用 SQL_BIG_RESULT
altertablet1addcolumnzintgeneratedalwaysas(id%100),addindex(z);
具体过程如下：
1、初始化sort_buffer，确定放入一个整型字段，记为m；
2、扫描表t1的索引a，依次取出里面的id值,将id%10的值存入sort_buffer中；
3、扫描完成后，对sort_buffer的字段m做排序（如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序）；
4、排序完成后，就得到了一个有序数组。
解析：
可以看到此时就没有使用临时表了，而是直接使用sort_buffer 进行排序，这样就省去了使用临时表带来的性能消耗。
总结
1、如果对groupby语句的结果没有排序要求，要在语句后面加orderbynull；那么一般情况就不需要使用临时表了（上面两个优化都是在要求排序的前提下提出的优化方式）
2、尽量让groupby过程用上表的索引，确认方法是explain结果里没有Usingtemporary和Usingfilesort；
3、如果groupby需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
4、如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到groupby的结果。
以上就是详解MySQLGroupby优化的详细内容，更多关于MySQLGroupby优化的资料请关注毛票票其它相关文章！
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。