MySQL,ORDER BY的实现与优化

Anonymity | | 访问(155)

  在MySQL中,ORDER BY的实现有如下两种类型:

  •一种是通过有序索引直接取得有序的数据,这样不用进行任何排序操作即可得到满足客户端要求的有序数据并返回给客户端;

  •另外一种则须通过MySQL的排序算法将存储引擎中返回的数据进行排序后,再将排序后的数据返回给客户端。

  第一种不用排序的实现方式

  利用索引实现数据排序是MySQL中实现结果集排序的最佳方法,可以完全避免因为排序计算所带来的资源消耗。所以,在优化Query语句中的ORDER BY时,尽可能利用已有的索引来避免实际的排序计算,甚至可以增加索引字段,这可以很大幅度地提升ORDER BY操作的性能。在有些Query的优化过程中,即使为了避免实际的排序操作而调整索引字段的顺序,甚至是增加索引字段也是值得的。当然,在调整索引之前,须要评估调整该索引对其他Query造成的影响,以平衡整体得失。

  第二种排序实现方式

  MySQL目前可以通过两种算法来实现数据的排序操作:

  (1)取出满足过滤条件作为排序条件的字段,以及可以直接定位到行数据的行指针信息,在Sort Buffer中进行实际的排序操作,然后利用排好序的数据根据行指针信息返回表中取得客户端请求的其他字段的数据,再返回给客户端;

  (2)根据过滤条件一次取出排序字段及客户端请求的所有其他字段的数据,并将不须要排序的字段存放在一块内存区域中,然后在 Sort Buffer中将排序字段和行指针信息进行排序,最后再利用排序后的行指针与存放在内存区域中和其他字段一起的行指针信息进行匹配、合并结果集,再按照顺序返回给客户端。

  上述第一种排序算法是MySQL一直以来就有的,而第二种则是从MySQL 4.1版本才开始增加的改进版排序算法。第二种算法与第一种相比,其主要优势就是减少了数据的二次访问。在排序之后不须要再一次回到表中取数据,节省了IO操作。当然,第二种算法会消耗更多的内存,这正是一种典型的通过内存空间换取时间的优化方式。

  当无法避免排序操作时,又该如何来优化呢?很显然,应该尽可能让MySQL选择使用第二种算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序工作的效率。

  1. 加大max_length_for_sort_data参数的设置

  在MySQL中,决定使用老式排序算法还是改进版排序算法是通过参数max_length_for_sort_data来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL就会选择改进后的排序算法,反之,则选择老式的算法。所以,如果有充足的内存让MySQL存放须要返回的非排序字段,就可以加大这个参数的值来让MySQL选择使用改进版的排序算法。

  2. 去掉不必要的返回字段

  当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫MySQL去使用改进版的排序算法,否则可能会造成MySQL不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应max_length_for_sort_data参数的限制。

  3. 增大sort_buffer_size参数设置

  增大sort_buffer_size并不是为了让 MySQL选择改进版的排序算法,而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段,因为分段会造成MySQL不得不使用临时表来进行交换排序。