MySQL中Order By实现原理分析

核心提示： 第二种方式在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为filesort，在这种方式中，MySQL中Order By实现原理分析(3)，主要是由于没有可以利用的有序索引取得有序的数据，MySQL只能通过将取得的数据在内存中进行排序然

第二种方式在MySQL Query Optimizer 所给出的执行计划(通过 EXPLAIN 命令查看)中被称为filesort。在这种方式中，主要是由于没有可以利用的有序索引取得有序的数据，MySQL只能通过将取得的数据在内存中进行排序然后再将数据返回给客户端。在MySQL中filesort 的实现算法实际上是有两种的，一种是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。另外一种是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

在MySQL4.1版本之前只有第一种排序算法，第二种算法是从MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sort buffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法，MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。所以如果希望 ORDER BY 操作的效率尽可能的高，一定要主义 max_length_for_sort_data 参数的设置。曾经就有同事的数据库出现大量的排序等待，造成系统负载很高，而且响应时间变得很长，最后查出正是因为MySQL 使用了传统的第一种排序算法而导致，在加大了max_length_for_sort_data 参数值之后，系统负载马上得到了大的缓解，响应也快了很多。

我们再看看 MySQL 需要使用filesort 实现排序的实例。

假设我们改变一下我们的Query，换成通过A.c2来排序，再看看情况：

1　sky@localhost　:　example　01:54:23>　EXPLAIN　SELECT　A.*　FROM　A,B
2　
3　->　WHERE　A.c1　>　2　AND　A.c2　<　5　AND　A.c2　=　B.c2　ORDER　BY　A.c2G
4　
5　***************************　1.　row　***************************
6　
7　id:　1
8　
9　select_type:　SIMPLE
10　
11　table:　A
12　
13　type:　range
14　
15　possible_keys:　PRIMARY
16　
17　key:　PRIMARY
18　
19　key_len:　4
20　
21　ref:　NULL
22　
23　rows:　3
24　
25　Extra:　Using　where;　Using　filesort
26　
27　***************************　2.　row　***************************
28　
29　id:　1
30　
31　select_type:　SIMPLE
32　
33　table:　B
34　
35　type:　ref
36　
37　possible_keys:　B_c2_ind
38　
39　key:　B_c2_ind
40　
41　key_len:　7
42　
43　ref:　example.A.c2
44　
45　rows:　2
46　
47　Extra:　Using　where;　Using　index