MySQL Query 的优化— ORDER BY 的实现与优化

在 MySQL 中，ORDER BY 的实现有如下两种类型：
◆ 一种是通过有序索引而直接取得有序的数据，这样不用进行任何排序操作即可得到满足客户端要求的有序数据返回给客户端；
◆ 另外一种则需要通过 MySQL 的排序算法将存储引擎中返回的数据进行排序然后再将排序后的数据返回给客户端。

1、在我们优化 Query 语句中的 ORDER BY 的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升 ORDER BY 操作的性能。在有些 Query 的优化过程中，即使为了避免实际的排序操作而调整索引字段的顺序，甚至是增加索引字段也是值得的。当然，在调整索引之前，同时还需要评估调整该索引对其他 Query 所带来的影响，平衡整体得失。

2、如果没有索引利用的时候，MySQL 又如何来实现排序呢？
MySQL 目前可以通
过两种算法来实现数据的排序操作。
◆ 取出满足过滤条件的用于排序条件的字段以及可以直接定位到行数据的行指针信息，在 Sort Buffer 中进行实际的排序操作，然后利用排好序之后的数据根据行指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端；

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

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

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

◆ 加大 max_length_for_sort_data 参数的设置
在 MySQL 中，决定使用第一种老式的排序算法还是新的改进算法的依据是通过参数max_length_for_sort_data 来决定的。当我们所有返回字段的最大长度小于这个参数值的时候，MySQL 就会选择改进后的排序算法

2. 去掉不必要的返回字段
当我们的内存并不是很充裕的时候，我们不能简单的通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法，因为如果那样可能会造成 MySQL 不得不将数据分成很多段然后进行排使用序，这样的结果可能会得不偿失。在这种情况下，我们就需要去掉不必要的返回字段，让我们的返回结果长度适应 max_length_for_sort_data 参数的限制。

3. 增大 sort_buffer_size 参数设置
增大 sort_buffer_size 并不是为了让 MySQL 可以选择改进版的排序算法，而是为了让 MySQL可以尽量减少在排序过程中对需要排序的数据进行分段，因为这样会造成 MySQL 不得不使用临时表来进行交换排序。