mysql中order by实现与优化

在MySQL 中，ORDER BY 的实现有如下两种类型：

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

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

举例说明：如果有两张表：退款表（id，退款id，退款订单号，退款金额），主键为id，普通索引为orderId； 退款明细表（id，关联id（来源于退款表），第三方退款id），主键为id，外键为referid。

• 当通过索引来进行查询的执行计划如下：

   解释：sql语句中明明有order by字段，但是在上面的extra（备注）中只有用到了索引，没有“useing sort buffer”，说明不需要额外的排序。换句话说，order by的内容在查询过程中已经完成排序操，不需要在进行排序。

实现原理：

       图中的Table A 和Table B 分别为上面Query 中的refund和refund_detail这两个表。这种利用索引实现数据排序的方法是MySQL 中实现结果集排序的最佳做法，可以完全避免因为排序计算所带来的资源消耗。所以，优化Query 语句中的ORDER BY 的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY 操作的性能。

• 去掉退款明细表中id字段的主键，执行计划如下：

解释：在refund表的Extra 信息中，多了一个“Using filesort”的信息，实际上这就是MySQL QueryOptimizer 在告诉我们，他需要进行排序操作才能按照客户端的要求返回有序的数据。

实现原理：

         MySQL 在取得第一个表的数据之后，先根据排序条件将数据进行了一次filesort，也就是排序操作。然后再利用排序后的结果集作为驱动结果集来通过Nested Loop Join 访问第二个表。

sort buffer的两种方式：

•  将需要字段和行指针在sort_buffer中进行排序，之后去表中取出数据在返回给客户端。

•  取出所有数据，将不需要排序的数据放到内存，之后再sort_buffer中将排下字段和指针进行排序，之后指针和内存数据匹配，返回结果。
  
  

  当我们无法避免排序操作的时候，我们应该尽可能让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 不得不使用临时表来进行交换排序。