mysqlfilesort介紹

 　　在MySQL中的ORDER BY有兩種排序實現方式:

　　1、利用有序索引獲取有序數據

　　2、文件排序

　　在使用explain分析查詢的時候，利用有序索引獲取有序數據顯示Using index。而文件排序顯示Using filesort。

　　1.利用有序索引獲取有序數據

　　取出滿足過濾條件作為排序條件的字段，以及可以直接定位到行數據的行指針信息，在 Sort Buffer 中進行實際的排序操作，然後利用排好序的數據根據行指針信息返回表中取得客戶端請求的其他字段的數據，再返回給客戶端.

　　這種方式，在使用explain分析查詢的時候，顯示Using index。而文件排序顯示Using filesort。

　　注意：MySQL在查詢時最多只能使用一個索引。因此，如果WHERE條件已經占用了索引，那麼在排序中就不使用索引了。

　　1.1 按照索引對結果進行排序：order by 使用索引是有條件

　　1) 返回選擇的字段，即只包括在有選擇的此列上(select後面的字段)，不一定適應*的情況)：

　　CREATE TABLE `test` (

　　`id` int(11) NOT NULLAUTO_INCREMENT,

　　`rdate` datetime NOT NULL,

　　`inventid` int(11) NOT NULL,

　　`customerid` int(11) NOT NULL,

　　`staffid` int(11) NOT NULL,

　　`data` varchar(20) NOT NULL,

　　PRIMARY KEY (`id`),

　　UNIQUE KEY `rdate`(`rdate`,`inventid`,`customerid`),

　　KEY `inventid` (`inventid`),

　　KEY `customerid` (`customerid`),

　　KEY `staffid` (`staffid`)

　　) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1

　　mysql>

　　explain select inventid from test where rdate='2011-12-1400:00:00' order by inventid , customerid;

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

　　| id | select_type | table | type | possible_keys |key | key_len |ref | rows |Extra |

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

　　| 1 | SIMPLE | test |ref | rdate |rdate | 8 |const | 10 | Using where; Using index |

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

　　1 row in set (0.00 sec)

　　Select選擇的列使用索引，而下面不使用索引：

　　mysql> explain select * from test where rdate='2011-12-14 00:00:00'order by inventid , customerid ;

　　+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

　　| id | select_type | table | type | possible_keys | key | key_len|ref | rows | Extra |

　　+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

　　| 1 | SIMPLE | test | ALL | rdate | NULL | NULL |NULL | 13 |Using where;Using filesort|

　　+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

　　2) 只有當ORDER BY中所有的列必須包含在相同的索引，並且索引的順序和order by子句中的順序完全一致，並且所有列的排序方向(升序或者降序)一樣才有，(混合使用ASC模式和DESC模式則不使用索引)

　　mysql>

　　xplain select inventid from test order byrdate, inventid ;

　　+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

　　| 1 | SIMPLE | test | index | NULL | rdate |16 | NULL | 13 |Using index|

　　+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+

　　1 row in set (0.00 sec)

　　mysql>

　　explain select inventid from test where rdate="2011-12-16" order by inventid ,staffid;

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------

　　| id | select_type | table | type | possible_keys |key | key_len | ref | rows | Extra |

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------

　　| 1 | SIMPLE | test | ref | rdate | rdate | 8 | const | 1 |Using where;Using filesort

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------

　　1 row in set (0.00 sec)

　　由於rdate, inventid使用了同一個索引。排序使用到了索引。這個也是滿足了前綴索引。但是order by inventid ,staffid;就不是使用了索引，因為staffid和inventid不是同一個索引

　　3) where 語句與ORDER BY語句組合滿足最左前綴:

　　mysql>

　　explain select inventid from test whererdate="2011-12-16" order by inventid ;

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

　　| 1 | SIMPLE | test | ref | rdate | rdate | 8 | const | 1 | Using where;Using index|

　　+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

　　1 row in set (0.00 sec)

　　4) 如果查詢聯接了多個表，只有在order by子句的所有列引用的是第一個表的列才可以。

　　5) 在其他的情況下，mysql使用文件排序 例如：

　　1) where語句與order by語句，使用了不同的索引

　　2) 檢查的行數過多，且沒有使用覆蓋索引

　　3) ORDER BY中的列不包含在相同的索引，也就是使用了不同的索引

　　4) 對索引列同時使用了ASC和DESC

　　5) where語句或者ORDER BY語句中索引列使用了表達式，包括函數表達式

　　6) where 語句與ORDER BY語句組合滿足最左前綴，但where語句中使用了條件查詢。查見第10句,雖然where與order by構成了索引最左有綴的條件，但是where子句中使用的是條件查詢。

　　mysql> explain select inventid from test where rdate>"2011-12-16" order by inventid;

　　+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

　　+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------

　　| 1 |SIMPLE | test | range | rdate | rdate | 8 | NULL | 1 | Using where; Using index;Usingfilesort|

　　+----+-------------+-------+-------+---------------+-------+---------+------+------+----------------

　　1 row in set (0.00sec)

　　7) 當使用left join，使用右邊的表字段排序

　　2.文件排序

　　這個 filesort 並不是說通過磁盤文件進行排序，而只是告訴我們進行了一個排序操作。即在MySQL Query Optimizer 所給出的執行計劃(通過 EXPLAIN 命令查看)中被稱為文件排序(filesort)

　　文件排序是通過相應的排序算法,將取得的數據在內存中進行排序: MySQL需要將數據在內存中進行排序，所使用的內存區域也就是我們通過sort_buffer_size 系統變量所設置的排序區。這個排序區是每個Thread 獨享的，所以說可能在同一時刻在MySQL 中可能存在多個 sort buffer 內存區域。

　　在MySQL中filesort 的實現算法實際上是有兩種：

　　雙路排序：是首先根據相應的條件取出相應的排序字段和可以直接定位行數據的行指針信息，然後在sort buffer 中進行排序。

　　單路排序：是一次性取出滿足條件行的所有字段，然後在sort buffer中進行排序。

　　在MySQL4.1版本之前只有第一種排序算法雙路排序，第二種算法是從MySQL4.1開始的改進算法，主要目的是為了減少第一次算法中需要兩次訪問表數據的 IO 操作，將兩次變成了一次，但相應也會耗用更多的sortbuffer 空間。當然，MySQL4.1開始的以後所有版本同時也支持第一種算法，

　　MySQL主要通過比較我們所設定的系統參數 max_length_for_sort_data的大小和Query 語句所取出的字段類型大小總和來判定需要使用哪一種排