MySQL中Order By实现原理分析

下面将通过实例分析两种排序实现方式及实现图解：

假设有 Table A 和 B 两个表结构分别如下：

1 sky@localhost : example 01:48:21> show create table A\G
2 
3 *************************** 1. row ***************************
4 
5 Table: A
6 
7 Create Table: CREATE TABLE `A` (
8 
9 `c1` int(11) NOT NULL default '0',
10 
11 `c2` char(2) default NULL,
12 
13 `c3` varchar(16) default NULL,
14 
15 `c4` datetime default NULL,
16 
17 PRIMARY KEY (`c1`)
18 
19 ) ENGINE=InnoDB DEFAULT CHARSET=utf8
20 
21 sky@localhost : example 01:48:32> show create table B\G
22 
23 *************************** 1. row ***************************
24 
25 Table: B
26 
27 Create Table: CREATE TABLE `B` (
28 
29 `c1` int(11) NOT NULL default '0',
30 
31 `c2` char(2) default NULL,
32 
33 `c3` varchar(16) default NULL,
34 
35 PRIMARY KEY (`c1`),
36 
37 KEY `B_c2_ind` (`c2`)
38 
39 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

1、利用有序索引进行排序，实际上就是当我们Query 的ORDER BY 条件和Query 的执行计划中所利用的Index的索引键(或前面几个索引键)完全一致，且索引访问方式为rang、ref 或者index的时候，MySQL可以利用索引顺序而直接取得已经排好序的数据。这种方式的ORDER BY 基本上可以说是最优的排序方式了，因为MySQL不需要进行实际的排序操作。

假设我们在Table A和B上执行如下SQL：

1 sky@localhost : example 01:44:28> EXPLAIN SELECT A.* FROM A,B
2 
3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c1\G
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
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

我们通过执行计划可以看出，MySQL实际上并没有进行实际的排序操作，实际上其整个执行过程如下图所示：

2、通过相应的排序算法，将取得的数据在内存中进行排序方式，MySQL 比需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。

第二种方式在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.c2\G
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

MySQL 从 Table A 中取出了符合条件的数据，由于取得的数据并不满足ORDER BY 条件，所以MySQL进行了 filesort 操作，其整个执行过程如下图所示：

在MySQL 中，filesort 操作还有一个比较奇怪的限制，那就是其数据源必须是来源于一个Table，所以，如果我们的排序数据如果是两个(或者更多个) Table 通过Join所得出的，那么 MySQL 必须通过先创建一个临时表(Temporary Table)，然后再将此临时表的数据进行排序，如下例所示：

1 sky@localhost : example 02:46:15> explain select A.* from A,B
2 
3 -> where A.c1 > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\G
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 temporary; 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