MySQL中filesort算法有几种-创新互联

这篇文章主要介绍了MySQL中filesort算法有几种，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

创新互联公司是网站建设专家,致力于互联网品牌建设与网络营销，专业领域包括成都网站制作、网站设计、电商网站制作开发、小程序制作、微信营销、系统平台开发，与其他网站设计及系统开发公司不同，我们的整合解决方案结合了恒基网络品牌建设经验和互联网整合营销的理念，并将策略和执行紧密结合，且不断评估并优化我们的方案，为客户提供全方位的互联网品牌整合方案！

一.filesort算法介绍
MySQL有两种filesort算法:two-pass和single-pass。
(1).two-pass
列长度之和超过max_length_for_sort_data字节时就使用这个算法，其原理是:先按照where筛选条件读取数据行，并存储每行的排序字段和行指针到排序缓冲区(sort buffer)。如果排序缓冲大小不够，就在内存中运行一个快速排序操作，把排序结果存储到一个临时文件里，
用一个指针指向这个已经排序好了的块，然后继续读取数据，直到所有行都读取完毕为止。这是第一次读取记录。以上第一次读取记录时，可以按照索引排序或表扫描，可以做到顺序读取。但第二次读取记录时，虽然排序字段是有序的，行缓存里存储的行指针是有序的，
但所指向的物理记录需要随机读，所以这个算法可能会带来很多随机读，从而导致效率很低。
优点是：排序的数据量小，一般在内存里即可完成。
缺点是：需要读取记录两次，第二次读取时候可能会产生许多随机IO，成本可能会比较高。


(2).single-pass
MySQL一般使用这种算法。其原理是：按照筛选条件把SQL中涉及的字段全部读入排序缓冲区(sort buffer)里，然后依据排序字段进行排序，
如果排序缓冲不够，会将临时排序结果写入到一个临时文件里，最后合并临时排序文件，直接返回已经排序好的结果集.
优点是：不需要读取记录两次，相对于two-pass，可以减少I/O开销。
缺点是：由于要读入所有字段，排序缓冲可能不够，需要额外的临时文件协助进行排序，导致增加额外的I/O成本。


二.排序相关的参数设置和优化
相关参数如下:
max_length_for_sort_data:如果各列长度之和(包括选择列、排序列)超过了max_length_for_sort_data字节，那么就使用two-pass算法。
如果排序BLOB、Text字段，使用的也是two-pass算法，那么这个值设置的太高会导致系统I/O上升和CPU下降，所以建议不要将max_length_for_sort_data
的值设置的太高。
max_sort_length:如果排序BLOB、TEXT字段，则仅排序前max_sort_length个字节。
可以考虑的优化方向如下：
(1).增大sort_buffer_size
一般情况下使用默认的single-pass算法即可。可以考虑增大sort_buffer_size以减少磁盘I/O。
需要留意的是字段长度之和不要超过max_length_for_sort_data,只查询所需要的列，注意列的长度和类型。 MySQL目前读取和计算列的长度是按照定义的大的度进行的，所以在设计表结构的时候，不要将varchar类型的字段设置得过大，虽然对于varchar类型来说，在物理磁盘中实际
存储可以做到紧凑，但在排序的时候是会分配大定义长度的，有时候排序阶段所产生的临时文件甚至比原始表还要大。MySQL 5.7版本在这方面做了一些优化。
(2).对于two-pass算法，可以考虑增大read_rnd_buffer_size，但由于这个全局变量是对所有连接都生效，因此建议只在会话级进行设置，以加速一些特殊的大操作。
(3).在操作系统层面，优化临时文件的读写。

感谢你能够认真阅读完这篇文章，希望小编分享的“MySQL中filesort算法有几种”这篇文章对大家有帮助，同时也希望大家多多支持创新互联，关注创新互联-成都网站建设公司行业资讯频道，更多相关知识等着你来学习!

当前文章：MySQL中filesort算法有几种-创新互联
分享路径：http://scjbc.cn/article/djiphe.html