MySQL中Order By實現原理分析

作者：簡朝陽 2009-03-26 13:43:59

在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

這個執行計劃的輸出還是有點奇怪的，不知道為什么，MySQL Query Optimizer 將 “Using temporary” 過程顯示在第一行對Table A 的操作中，難道只是為讓執行計劃的輸出少一行?

實際執行過程應該是如下圖所示：

【編輯推薦】

詳解MySQL分組查詢Group By實現原理
解決MySQL 5數據庫連接超時問題
MySQL百萬級高并發網站實戰攻略

責任編輯：彭凡來源： IT168

實現 Order By MySQL