更新時(shí)間：2012-07-01
下面將通過實(shí)例分析兩種排序?qū)崿F(xiàn)方式及實(shí)現(xiàn)圖解：
假設(shè)有 Table A 和 B 兩個(gè)表結(jié)構(gòu)分別如下：
1 sky@localhost : example 01:48:21> show create table A\G2 3 *************************** 1. row ***************************4 5 Table: A6 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=MyISAM DEFAULT CHARSET=utf820 21 sky@localhost : example 01:48:32> show create table B\G22 23 *************************** 1. row ***************************24 25 Table: B26 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=MyISAM DEFAULT CHARSET=utf8
1、利用有序索引進(jìn)行排序，實(shí)際上就是當(dāng)我們Query 的ORDER BY 條件和Query 的執(zhí)行計(jì)劃中所利用的Index的索引鍵(或前面幾個(gè)索引鍵)完全一致，且索引訪問方式為rang、ref 或者index的時(shí)候，MySQL可以利用索引順序而直接取得已經(jīng)排好序的數(shù)據(jù)。這種方式的ORDER BY 基本上可以說是最優(yōu)的排序方式了，因?yàn)镸ySQL不需要進(jìn)行實(shí)際的排序操作。
假設(shè)我們在Table A和B上執(zhí)行如下SQL：
1 sky@localhost : example 01:44:28> EXPLAIN SELECT A.* FROM A,B2 3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c1\G4 5 *************************** 1. row ***************************6 7 id: 18 9 select_type: SIMPLE10 11 table: A12 13 type: range14 15 possible_keys: PRIMARY16 17 key: PRIMARY18 19 key_len: 420 21 ref: NULL22 23 rows: 324 25 Extra: Using where26 27 *************************** 2. row ***************************28 29 id: 130 31 select_type: SIMPLE32 33 table: B34 35 type: ref36 37 possible_keys: B_c2_ind38 39 key: B_c2_ind40 41 key_len: 742 43 ref: example.A.c244 45 rows: 246 47 Extra: Using where; Using index
我們通過執(zhí)行計(jì)劃可以看出，MySQL實(shí)際上并沒有進(jìn)行實(shí)際的排序操作，實(shí)際上其整個(gè)執(zhí)行過程如下圖所示：
2、通過相應(yīng)的排序算法，將取得的數(shù)據(jù)在內(nèi)存中進(jìn)行排序方式，MySQL 比需要將數(shù)據(jù)在內(nèi)存中進(jìn)行排序，所使用的內(nèi)存區(qū)域也就是我們通過sort_buffer_size 系統(tǒng)變量所設(shè)置的排序區(qū)。這個(gè)排序區(qū)是每個(gè)Thread 獨(dú)享的，所以說可能在同一時(shí)刻在MySQL 中可能存在多個(gè) sort buffer 內(nèi)存區(qū)域。
第二種方式在MySQL Query Optimizer 所給出的執(zhí)行計(jì)劃(通過 EXPLAIN 命令查看)中被稱為filesort。在這種方式中，主要是由于沒有可以利用的有序索引取得有序的數(shù)據(jù)，MySQL只能通過將取得的數(shù)據(jù)在內(nèi)存中進(jìn)行排序然后再將數(shù)據(jù)返回給客戶端。在MySQL中filesort 的實(shí)現(xiàn)算法實(shí)際上是有兩種的，一種是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位行數(shù)據(jù)的行指針信息，然后在sort buffer 中進(jìn)行排序。另外一種是一次性取出滿足條件行的所有字段，然后在sort buffer中進(jìn)行排序。
在MySQL4.1版本之前只有第一種排序算法，第二種算法是從MySQL4.1開始的改進(jìn)算法，主要目的是為了減少第一次算法中需要兩次訪問表數(shù)據(jù)的 IO 操作，將兩次變成了一次，但相應(yīng)也會耗用更多的sort buffer 空間。當(dāng)然，MySQL4.1開始的以后所有版本同時(shí)也支持第一種算法，MySQL主要通過比較我們所設(shè)定的系統(tǒng)參數(shù) max_length_for_sort_data的大小和Query 語句所取出的字段類型大小總和來判定需要使用哪一種排序算法。如果 max_length_for_sort_data更大，則使用第二種優(yōu)化后的算法，反之使用第一種算法。所以如果希望 ORDER BY 操作的效率盡可能的高，一定要主義 max_length_for_sort_data 參數(shù)的設(shè)置。曾經(jīng)就有同事的數(shù)據(jù)庫出現(xiàn)大量的排序等待，造成系統(tǒng)負(fù)載很高，而且響應(yīng)時(shí)間變得很長，最后查出正是因?yàn)镸ySQL 使用了傳統(tǒng)的第一種排序算法而導(dǎo)致，在加大了max_length_for_sort_data 參數(shù)值之后，系統(tǒng)負(fù)載馬上得到了大的緩解，響應(yīng)也快了很多。
我們再看看 MySQL 需要使用filesort 實(shí)現(xiàn)排序的實(shí)例。
假設(shè)我們改變一下我們的Query，換成通過A.c2來排序，再看看情況：
1 sky@localhost : example 01:54:23> EXPLAIN SELECT A.* FROM A,B2 3 -> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c2\G4 5 *************************** 1. row ***************************6 7 id: 18 9 select_type: SIMPLE10 11 table: A12 13 type: range14 15 possible_keys: PRIMARY16 17 key: PRIMARY18 19 key_len: 420 21 ref: NULL22 23 rows: 324 25 Extra: Using where; Using filesort26 27 *************************** 2. row ***************************28 29 id: 130 31 select_type: SIMPLE32 33 table: B34 35 type: ref36 37 possible_keys: B_c2_ind38 39 key: B_c2_ind40 41 key_len: 742 43 ref: example.A.c244 45 rows: 246 47 Extra: Using where; Using index
MySQL 從 Table A 中取出了符合條件的數(shù)據(jù)，由于取得的數(shù)據(jù)并不滿足ORDER BY 條件，所以MySQL進(jìn)行了 filesort 操作，其整個(gè)執(zhí)行過程如下圖所示：
在MySQL 中，filesort 操作還有一個(gè)比較奇怪的限制，那就是其數(shù)據(jù)源必須是來源于一個(gè)Table，所以，如果我們的排序數(shù)據(jù)如果是兩個(gè)(或者更多個(gè)) Table 通過Join所得出的，那么 MySQL 必須通過先創(chuàng)建一個(gè)臨時(shí)表(Temporary Table)，然后再將此臨時(shí)表的數(shù)據(jù)進(jìn)行排序，如下例所示：
1 sky@localhost : example 02:46:15> explain select A.* from A,B2 3 -> where A.c1 > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\G4 5 *************************** 1. row ***************************6 7 id: 18 9 select_type: SIMPLE10 11 table: A12 13 type: range14 15 possible_keys: PRIMARY16 17 key: PRIMARY18 19 key_len: 420 21 ref: NULL22 23 rows: 324 25 Extra: Using where; Using temporary; Using filesort26 27 *************************** 2. row ***************************28 29 id: 130 31 select_type: SIMPLE32 33 table: B34 35 type: ref36 37 possible_keys: B_c2_ind38 39 key: B_c2_ind40 41 key_len: 742 43 ref: example.A.c244 45 rows: 246 47 Extra: Using where
個(gè)執(zhí)行計(jì)劃的輸出還是有點(diǎn)奇怪的，不知道為什么，MySQL Query Optimizer 將 “Using temporary” 過程顯示在第一行對Table A 的操作中，難道只是為讓執(zhí)行計(jì)劃的輸出少一行?
實(shí)際執(zhí)行過程應(yīng)該是如下圖所示：
