mysqlgroupby用法(MySQL Group by的优化详解)
mysqlgroupby用法
MySQL Group by的优化详解一个标准的 group by 语句包含排序、分组、聚合函数,比如 select a,count(*) from t group by a ; 这个语句默认使用 a 进行排序。如果 a 列没有索引,那么就会创建临时表来统计 a和 count(*),然后再通过 sort_buffer 按 a 进行排序。
标准的执行流程
结构:
|
create table t1(id int primary key , a int , b int , index (a)); delimiter ;; create 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 ;; delimiter ; call idata(); |
函数就是向 t1 中插入1000条语句,从(1,1,1) 到(1000,1000,1000)。
执行 select id%10 as m, count(*) as c from t1 group by m;
解析:
using index,表示这个语句使用了覆盖索引,选择了索引 a,不需要回表;
using temporary,表示使用了临时表;
using filesort,表示需要排序。
过程:
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 设置的大小不够大,那么就会使用临时表来辅助排序。
优化
未优化(也就是分组列没有索引)的 group by 的总过程可以概括为:因为数据是无序的,所以需要创建临时表,然后一个一个判断属于哪个分组,最后再根据分组列进行排序。所以,优化可以有两个思路:
去掉排序
在明确返回的数据不需要排序的情况下,可以禁止排序,也就是将上面的语句改成 select a,count(*) from t group by a order by null。
顺序排列
如果记录都按照排序字段排序,那么数据就变成了下面的结构:
这样在实际获取要返回的字段或计算聚合函数时,只需要按顺序依次访问,等到列值变成下一个就知道当前组访问结束,将之前统计的数据直接返回。这样就避免了创建临时表,同时排序也不需要使用 sort_buffer 进行额外排序。这样就极大地提高了执行的效率。
实现
1、如果分组字段适合创建索引就直接为分组字段创建索引。
mysql 5.7 版本支持了 generated column 机制,用来实现列数据的关联更新。你可以用下面的方法创建一个列 z,然后在 z 列上创建一个索引(如果是 mysql 5.6 及之前的版本,你也可以创建普通列和索引,来解决这个问题)
|
alter table t1 add column z int generated always as (id % 100), add index (z); |
然后解析:
这时没有用到临时表和额外排序,所以性能提升。
2、如果分组字段不适合(使用率很低),那么可以使用 sql_big_result 来尝试优化。
在 group by 语句中加入 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
|
alter table t1 add column z int generated always as (id % 100), add index (z); |
具体过程如下:
1、初始化 sort_buffer,确定放入一个整型字段,记为 m;
2、扫描表 t1 的索引 a,依次取出里面的 id 值, 将 id%10 的值存入 sort_buffer 中;
3、扫描完成后,对 sort_buffer 的字段 m 做排序(如果 sort_buffer 内存不够用,就会利用磁盘临时文件辅助排序);
4、排序完成后,就得到了一个有序数组。
解析:
可以看到此时就没有使用临时表了,而是直接使用 sort_buffer 进行排序,这样就省去了使用临时表带来的性能消耗。
总结
1、如果对 group by 语句的结果没有排序要求,要在语句后面加 order by null;那么一般情况就不需要使用临时表了(上面两个优化都是在要求排序的前提下提出的优化方式)
2、尽量让 group by 过程用上表的索引,确认方法是 explain 结果里没有 using temporary 和 using filesort;
3、如果 group by 需要统计的数据量不大,尽量只使用内存临时表;也可以通过适当调大 tmp_table_size 参数,来避免用到磁盘临时表;
4、如果数据量实在太大,使用 sql_big_result 这个提示,来告诉优化器直接使用排序算法得到 group by 的结果。
以上就是详解mysql group by 优化的详细内容,更多关于mysql group by 优化的资料请关注开心学习网其它相关文章!
原文链接:https://www.cnblogs.com/mengxinJ/p/14341315.html
- mysql新增字段并添加备注(mysql添加备注信息的实现)
- mysql url连接参数配置(浅谈Mysql连接数据库时host和user的匹配规则)
- centos安装mysql8.0教程(Centos7 安装 Mysql8教程)
- mysql数据库导入错误怎么处理(MySQL数据库导入导出数据之报错解答实例讲解)
- mysql日期用法(MySQL DATE_ADD和ADDDATE函数实现向日期添加指定时间间隔)
- mysql开启事务没有提交(MySQL 事务autocommit自动提交操作)
- mysql模糊匹配语句(MySQL 数据库 like 语句通配符模糊查询小结)
- mysql 索引怎么实现(Mysql中索引和约束的示例语句)
- mysql limit越大越慢(MySQL limit分页大偏移量慢的原因及优化方案)
- MYSQL中GROUP BY不包含所有的非聚合字段时的注意事项
- mysql并发控制的重要方式(Mysql服务添加 iptables防火墙策略的方案)
- mysql服务器端安装步骤(windows下jsp+mysql网站环境配置方法)
- 对mysql索引的理解(详解MySQL 8.0 之不可见索引)
- mysql中的默认mysql数据库作用(MySQL安装后默认自带数据库的作用详解)
- 查询mysql 死锁(MySQL线上死锁分析实战)
- mysqlbinlog怎么分析(MySQL中使用binlog时格式该如何选择)
- 官宣 全椒籍明星许海峰 奚秀兰 方芳 王璐瑶携手回家 走太平(全椒籍明星许海峰)
- 以前全椒人是怎么过冬的 满满都是回忆(以前全椒人是怎么过冬的)
- NVIDIA显卡份额冲上88 A饭发愁 游戏优化恐没A卡份了(NVIDIA显卡份额冲上88A饭发愁)
- AMD YES A卡还是N卡 A卡和N卡的区别(AMDYESA卡还是N卡)
- 以后显卡多了一个新选择,N卡和A卡外又多了个I卡(以后显卡多了一个新选择)
- 读卖乐园的彩灯(读卖乐园的彩灯)
热门推荐
- phpredis消息队列(PHP+redis实现微博的推模型案例分析)
- python编程面试常见问题(分析经典Python开发工程师面试题)
- pythonredis列表(Python redis操作实例分析连接、管道、发布和订阅等)
- python中读取文件怎么操作(Python实现的读取文件内容并写入其他文件操作示例)
- 如何启动wampserver数据库服务(wampserver更改默认站点目录www方法教程)
- thinkphp控制器怎么设置自定义(Thinkphp5.0 框架实现控制器向视图view赋值及视图view取值操作示例)
- ReturnUrl带多个参数,返回时只返回一个参数的解决方法
- vuekeep-alive源码(vue中keepAlive组件的作用和使用方法详解)
- docker安装部署及使用(docker在linux上的安装部署实例讲解)
- sqlserver查看创建的索引(浅述SQL Server的聚焦强制索引查询条件和Columnstore Index)