MySQL order by實現(xiàn)原理分析和Filesort優(yōu)化方式
在MySQL中的ORDER BY有兩種排序實現(xiàn)方式：
1. 利用有序索引獲取有序數(shù)據(jù)
2. 文件排序
在使用explain分析查詢的時候，利用有序索引獲取有序數(shù)據(jù)顯示Using index。而文件排序顯示Using filesort。
1.利用有序索引獲取有序數(shù)據(jù)
取出滿足過濾條件、作為排序條件的字段，以及可以直接定位到行數(shù)據(jù)的行指針信息，在 Sort Buffer 中進行實際的排序操作，然后利用排好序的數(shù)據(jù)根據(jù)行指針信息返回表中取得客戶端請求的其他字段的數(shù)據(jù)，再返回給客戶端。
這種方式，在使用explain分析查詢的時候，顯示Using index。而文件排序顯示Using filesort。
注意：
MySQL在查詢時最多只能使用一個索引。
因此，如果WHERE條件已經(jīng)占用了索引，那么在排序中就不使用索引了。（現(xiàn)在已經(jīng)可以使用多個索引了）
1.1 按照索引對結果進行排序：order by 使用索引時有條件
1) 返回選擇的字段，即只包括在有選擇的此列上（select后面的字段），不一定適應*的情況)：
CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_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
如：
root@mysql5601 14:58: [lijk]> 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 | 5 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+ 1 row in set, 5 warnings (0.00 sec)
Select選擇的列使用索引，而下面不使用索引：
root@mysql5601 15:00: [lijk]> 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 | ref | rdate | rdate | 5 | const | 1 | Using where | +----+-------------+-------+------+---------------+-------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
2) 只有當ORDER BY中所有的列必須包含在相同的索引，并且索引的順序和order by子句中的順序完全一致，并且所有列的排序方向（升序或者降序）一樣才有，（混合使用ASC模式和DESC模式則不使用索引）
root@mysql5601 15:00: [lijk]> explain select inventid from test order by rdate, inventid ; +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ | 1 | SIMPLE | test | index | NULL | rdate | 13 | NULL | 1 | Using index | +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ 1 row in set (0.00 sec) root@mysql5601 15:01: [lijk]> 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 | 5 | 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語句組合滿足最左前綴：
root@mysql5601 15:02: [lijk]> 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 | ref | rdate | rdate | 5 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec)
4) 如果查詢聯(lián)接了多個表，只有在order by子句的所有列引用的是第一個表的列才可以。
5) 在其他的情況下，mysql使用文件排序 例如：
- 1） where語句與order by語句，使用了不同的索引
- 2） 檢查的行數(shù)過多，且沒有使用覆蓋索引
- 3） ORDER BY中的列不包含在相同的索引，也就是使用了不同的索引
- 4） 對索引列同時使用了ASC和DESC
- 5） where語句或者ORDER BY語句中索引列使用了表達式，包括函數(shù)表達式
- 6） where 語句與ORDER BY語句組合滿足最左前綴，但where語句中使用了條件查詢。查見第10句,雖然where與order by構成了索引最左有綴的條件，但是where子句中使用的是條件查詢。
root@mysql5601 15:02: [lijk]> 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 | index | rdate | rdate | 13 | NULL | 1 | Using where; Using index; Using filesort | +----+-------------+-------+-------+---------------+-------+---------+------+------+------------------------------------------+ 1 row in set (0.00 sec)
- 7） 當使用left join，使用右邊的表字段排序
2.文件排序
這個 filesort 并不是說通過磁盤文件進行排序，而只是告訴我們進行了一個排序操作。
即在MySQL Query Optimizer 所給出的執(zhí)行計劃(通過 EXPLAIN 命令查看)中被稱為文件排序（filesort）
文件排序是通過相應的排序算法,將取得的數(shù)據(jù)在內(nèi)存中進行排序: MySQL需要將數(shù)據(jù)在內(nèi)存中進行排序，所使用的內(nèi)存區(qū)域也就是我們通過sort_buffer_size 系統(tǒng)變量所設置的排序區(qū)。這個排序區(qū)是每個Thread 獨享的，所以說可能在同一時刻在MySQL 中可能存在多個 sort buffer 內(nèi)存區(qū)域。
在MySQL中filesort 的實現(xiàn)算法實際上是有兩種：
雙路排序：是首先根據(jù)相應的條件取出相應的排序字段和可以直接定位行數(shù)據(jù)的行指針信息，然后在sort buffer 中進行排序。
單路排序：是一次性取出滿足條件行的所有字段，然后在sort buffer中進行排序。
在MySQL4.1版本之前只有第一種排序算法雙路排序，第二種算法是從MySQL4.1開始的改進算法，主要目的是為了減少第一次算法中需要兩次訪問表數(shù)據(jù)的 IO 操作，將兩次變成了一次，但相應也會耗用更多的sortbuffer 空間。
當然，MySQL4.1開始的以后所有版本同時也支持第一種算法。
MySQL主要通過比較我們所設定的系統(tǒng)參數(shù) max_length_for_sort_data的大小和Query 語句所取出的字段類型大小總和來判定需要使用哪一種排序算法。
如果 max_length_for_sort_data更大，則使用第二種優(yōu)化后的算法，反之使用第一種算法。
所以如果希望 ORDER BY 操作的效率盡可能的高，一定要主義max_length_for_sort_data 參數(shù)的設置。
曾經(jīng)就有同事的數(shù)據(jù)庫出現(xiàn)大量的排序等待，造成系統(tǒng)負載很高，而且響應時間變得很長，最后查出正是因為MySQL 使用了傳統(tǒng)的第一種排序算法而導致，在加大了max_length_for_sort_data 參數(shù)值之后，系統(tǒng)負載馬上得到了大的緩解，響應也快了很多。
2.1 MySQL 需要使用filesort 實現(xiàn)排序的實例
假設有 Table A 和 B 兩個表結構分別如下：
# mysql >show create table A\G *************************** 1. row *************************** Table: A Create Table: CREATE TABLE `A` ( `id` int(11) NOT NULL default '0', `c2` char(2) default NULL, `c3` varchar(16) default NULL, `c4` datetime default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 #:mysql > show create table B\G *************************** 1. row *************************** Table: B Create Table: CREATE TABLE `B` ( `id` int(11) NOT NULL default '0', `c2` char(2) default NULL, `c3` varchar(16) default NULL, PRIMARY KEY (`id`), KEY `B_c2_ind` (`c2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8
A.c2不是索引將使用：
root@mysql5601 15:02: [lijk]> EXPLAIN SELECT A.* FROM A,B WHERE A.id >2 AND A.c2 <5 AND A.c2 = B.c2 ORDER BY A.c2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: A type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: B type: ref possible_keys: B_c2_ind key: B_c2_ind key_len: 7 ref: example.A.c2 rows: 2 Extra: Using where; Using index
2.2 MySQL 需要使用Using temporary 臨時表來filesort
如果order by的子句只引用了聯(lián)接中的第一個表，MySQL會先對第一個表進行排序，然后進行聯(lián)接。
也就是expain中的Extra的Using Filesort.否則MySQL先把結果保存到臨時表(Temporary Table),然后再對臨時表的數(shù)據(jù)進行排序.此時expain中的Extra的顯示Using temporary Using Filesort.
例如如果我們的排序數(shù)據(jù)如果是兩個(或者更多個) Table 通過 Join所得出的,如下例所示：
root@mysql5601 15:02: [lijk]> explain select A.* from A,B where A.id > 2 and A.c2 < 5 and A.c2 = B.c2 order by B.c3\G *************************** 1. row*************************** id: 1 select_type: SIMPLE table: A type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where; Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: B type: ref possible_keys: B_c2_ind key: B_c2_ind key_len: 7 ref: example.A.c2 rows: 2 Extra: Using where
實際執(zhí)行過程應該是如下圖所示：
3.優(yōu)化Filesort
當無法避免排序操作時，又該如何來優(yōu)化呢？很顯然，應該盡可能讓 MySQL 選擇使用第二種單路算法來進行排序。這樣可以減少大量的隨機IO操作，很大幅度地提高排序工作的效率。
1. 加大 max_length_for_sort_data 參數(shù)的設置
在 MySQL 中，決定使用老式排序算法還是改進版排序算法是通過參數(shù) max_length_for_ sort_data 來決定的。
當所有返回字段的最大長度小于這個參數(shù)值時，MySQL 就會選擇改進后的排序算法，反之，則選擇老式的算法。
所以，如果有充足的內(nèi)存讓MySQL 存放須要返回的非排序字段，就可以加大這個參數(shù)的值來讓 MySQL 選擇使用改進版的排序算法。
2. 去掉不必要的返回字段
當內(nèi)存不是很充裕時，不能簡單地通過強行加大上面的參數(shù)來強迫 MySQL 去使用改進版的排序算法，否則可能會造成 MySQL 不得不將數(shù)據(jù)分成很多段，然后進行排序，這樣可能會得不償失。
此時就須要去掉不必要的返回字段，讓返回結果長度適應 max_length_for_sort_data 參數(shù)的限制。
3. 增大 sort_buffer_size 參數(shù)設置
增大 sort_buffer_size 并不是為了讓 MySQL選擇改進版的排序算法，而是為了讓MySQL盡量減少在排序過程中對須要排序的數(shù)據(jù)進行分段，因為分段會造成 MySQL 不得不使用臨時表來進行交換排序。
總結
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
這篇文章主要介紹了最全50個數(shù)據(jù)庫查詢練習題，Mysql數(shù)據(jù)庫版本，全部都驗證過2020-12-12
MySQL group by對單字分組序和多字段分組的方法講解
今天小編就為大家分享一篇關于MySQL group by對單字分組序和多字段分組的方法講解，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-03-03
MySQL?8.0版本配置my.cnf?/?my.ini各項配置示例詳解
在MySQL 8.0版本之前,MySQL使用的是名為my.ini的配置文件來配置數(shù)據(jù)庫服務器,這篇文章主要介紹了MySQL?8.0版本配置my.cnf?/?my.ini各項配置的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-11-11
修改數(shù)據(jù)SQL中,可以使用UPDATE語句來修改、更新一個或多個表的數(shù)據(jù),下面這篇文章主要給大家介紹了關于在SQL中修改數(shù)據(jù)的基礎語句,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-02-02
這篇文章主要介紹MYSQL存儲的過程，本文圍繞MYSQL存儲詳細展開文章內(nèi)容，并附上詳細代碼解析,需要的下伙伴可以參考一下，希望對你有所幫助2021-10-10
這篇文章主要介紹了MySQL5.7.20解壓版安裝和修改root密碼的教程，非常不錯，具有參考借鑒價值,需要的朋友可以參考下2018-04-04
本文介紹了在終端操作命令以及處理隱藏文件夾的兩種方法：一種是直接在終端輸入命令啟動、停止和重啟；另一種是通過拖拽文件到終端并添加命令如start或stop,同時,介紹了如何通過命令顯示隱藏的usr文件夾并重新啟動Finder以訪問2024-10-10
mysql binlog如何恢復數(shù)據(jù)到某一時刻
這篇文章主要介紹了mysql binlog如何恢復數(shù)據(jù)到某一時刻問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-06-06
這篇文章主要為大家詳細介紹了MySQL數(shù)據(jù)庫中表常用的一些操作方法，文中的示例代碼講解詳細， 對我們學習MySQL有一定幫助，需要的可以參考一下2022-09-09
在 MySQL 生產(chǎn)運維里,主從復制是數(shù)據(jù)備份、讀寫分離、高可用架構的核心,但配置、網(wǎng)絡、磁盤、數(shù)據(jù)一致性、日志清理等問題,總能讓復制突然中斷,因此本文給大家介紹了MySQL主從復制故障排查及解決方案,需要的朋友可以參考下2026-03-03