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算法有几种”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!