MySQL高级 之 order by、group by 优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL高级 之 order by、group by 优化,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4056字,纯文字阅读大概需要6分钟。
内容图文
![MySQL高级 之 order by、group by 优化](/upload/InfoBanner/zyjiaocheng/509/b64c583972e24482a736fc25ea383736.jpg)
order by示例
示例数据:
Case 1
Case 2
Case 3
Case 4
结论:order by子句,尽量使用Index方式排序,在索引列上遵循索引的最佳左前缀原则。
复合(联合)索引形如 key (‘A1’,’A2’,’A3’ ),排序的思路一般是,先按照A1来排序,A1相同,然后按照A2排序,以此类推,这样对于(A1),(A1,A2), (A1,A2,A3)的索引都是有效的,但是对于(A2,A3)这样的索引就无效了。尽量避免因索引字段的缺失 或 索引字段顺序的不同 引起的FileSort排序。
order by 总结
FileSort排序算法
算法一:双路排序算法
只利用ORDERBY子句中包括的列对象进行排序(适用于有BLOB、TEXT类型的列对象参与的排序)
MySQL4.1之前的排序算法,完整实现过程如下:
1) 按索引键或全表扫描的方式,读取所有的元组,不匹配WHERE子句的元组被跳过;第一步需要从存储读入数据,引发I/O操作。
2) 对于每一行,在缓冲区中存储一对值(对值,包括排序关键字和元组指针)。缓冲区的大小是系统变量的sort_buffer_size设定的值。
3) 当缓冲区已满,运行快排算法(快速排序,qsort)对一个块中的数据进行排序,将结果存储在一个临时文件。保存一个指向排序后的块的指针(如果第二步所说的对值都能被缓冲区容纳,则不会创建临时文件)。
4) 重复上述步骤,直到所有的行已经被读取。
5) 执行一个多路归并操作(操作对象是第三步生成的每一个有序的块)汇集到“MERGEBUFF域”,然后存放到在第二个临时文件中。重复操作,直到第一个文件的所有块归并后存入到第二个文件;“MERGEBUFF域”是代码sql_sort.h中定义的宏,值为7。
6) 重复以下操作(第7步和第8步),直到留下少于“MERGEBUFF2域”标明的块数为止;“MERGEBUFF2域”是代码sql_sort.h中定义的宏,值为15。
7) 在最后一次多路归并操作中,把元组的指针(排序关键字的最后部分)写入到一个结果文件。
8) 在结果文件中,按照排列的顺序使用元组指针读取元组(为了优化这项操作,MySQL读入元组指针进入一个大的块,对块中元组指针进行排序而不是直接对数据排序,然后再用有序的元组指针获取元组到元组缓存,元组缓冲区的大小由read_rnd_buffer_size参数控制)。第8步需要从存储读入数据,引发I/O操作。
算法二:单路排序算法
除利用ORDERBY子句中包括的列对象外,还利用查询目标列中的所有列对象进行排序(适用于除BLOB、TEXT类型外的所有的其他类型的排序)
MySQL4.1之后出现的改进算法,减少一次I/O,需要增加缓冲区大小容纳更多信息。其具体实现过程如下:
1) 获取与WHERE子句匹配的元组。这一步需要从存储读入数据,引发I/O操作。
2) 对于每一个元组,记录排序键值、行的位置值、查询所需的列。这一步记录更多内容,需要更大缓存,内存存储一条元组的信息的长度比算法一的“对值”大许多,这可能引发排序速度问题(排序对象的长度变长,但是内存有限,所以就需把一次内存排序变为多次,进而影响排序的速度),为了控制这个问题,MySQL引入一个参数“max_length_for_sort_data”,如果这一步得到的元组长度大于这个值,则不使用算法二。需要MySQL的使用者特别注意的是,在排序中,如果存在“很高磁盘I/O和很低的CPU利用率”的现象,则需要考虑调整“max_length_for_sort_data”的大小以变更换排序算法。
3) 按照排序的键值,对元组(元组是第二步的结果)进行排序。
算法二直接从缓冲区中的排序的元组中获取有序的列信息等(查询的目的对象),而不是第二次访问该表读取所需的列。相比算法一减少一次I/O。
FileSort优化策略
当无法使用索引列排序时,为了提高Order By的速度,应该尝试一下优化:
1、避免使用 “select * ” 。查询的字段越多导致元组长度总合可能
超过max_length_for_sort_data的设置,导致无法使用单路排序算法,只能用双路排序算法。
超过sort_buffer_size的设置,超出后会创建tmp文件进行合并,导致多次IO
2、适当增大sort_buffer_size参数的设置
3、适当增大max_length_for_sort_data参数的设置
group by 示例
示例:
group by 总结
group by与order by的索引优化基本一样,group by实质是先排序后分组,也就是分组之前必排序,遵照索引的最佳左前缀原则可以大大提高group by的效率。
当无法使用索引列排序时,适当增大sort_buffer_size参数 + 适当增大max_length_for_sort_data参数可以提高filesort排序的效率。注意:可能会出现Using temporary,也就是说mysql在对查询结果排序时使用了临时表。
where高于having,能写在where限定条件中的就尽量写在where中。
---------------------
作者:走慢一点点
来源:CSDN
原文:https://blog.csdn.net/wuseyukui/article/details/72627667
版权声明:本文为博主原创文章,转载请附上博文链接!
MySQL高级 之 order by、group by 优化
标签:log bsp 调整 变量 原创 实现 排序 作者 步骤
本文系统来源:https://www.cnblogs.com/maohuidong/p/10983096.html
内容总结
以上是互联网集市为您收集整理的MySQL高级 之 order by、group by 优化全部内容,希望文章能够帮你解决MySQL高级 之 order by、group by 优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。