MySQL ORDER BY 的完成剖析

MySQL ORDER BY 的完成剖析。本站提示廣大學習愛好者：（MySQL ORDER BY 的完成剖析）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL ORDER BY 的完成剖析正文

上面將經由過程實例剖析兩種排序完成方法及完成圖解：
假定有 Table A 和 B 兩個表構造分離以下：
sky@localhost : example 01:48:21> show create table AG
*************************** 1. row ***************************
Table: A
Create Table: CREATE TABLE `A` (
`c1` int(11) NOT NULL default ‘0′,
`c2` char(2) default NULL,
`c3` varchar(16) default NULL,
`c4` datetime default NULL,
PRIMARY KEY  (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

sky@localhost : example 01:48:32> show create table BG
*************************** 1. row ***************************
Table: B
Create Table: CREATE TABLE `B` (
`c1` int(11) NOT NULL default ‘0′,
`c2` char(2) default NULL,
`c3` varchar(16) default NULL,
PRIMARY KEY  (`c1`),
KEY `B_c2_ind` (`c2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

1、應用有序索引停止排序，現實上就是當我們 Query 的 ORDER BY 前提和 Query 的履行籌劃中所應用的 Index 的索引鍵（或後面幾個索引鍵）完整分歧，且索引拜訪方法為 rang、 ref 或許 index 的時刻，MySQL 可以應用索引次序而直接獲得曾經排好序的數據。這類方法的 ORDER BY 根本上可以說是最優的排序方法了，由於 MySQL 不須要停止現實的排序操作。

假定我們在Table A 和 B 上履行以下SQL：
sky@localhost : example 01:44:28> EXPLAIN SELECT A.* FROM A,B
-> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c1G
*************************** 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
*************************** 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

我們經由過程履行籌劃可以看出，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來排序，再看看情形：
sky@localhost : example 01:54:23> EXPLAIN SELECT A.* FROM A,B
-> WHERE A.c1 > 2 AND A.c2 < 5 AND A.c2 = B.c2 ORDER BY A.c2G
*************************** 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

MySQL 從 Table A 中掏出了相符前提的數據，因為獲得的數據其實不知足 ORDER BY 前提，所以 MySQL 停止了 filesort 操作，其全部履行進程以下圖所示：

在 MySQL 中，filesort 操作還有一個比擬奇異的限制，那就是其數據源必需是起源於一個 Table，所以，假如我們的排序數據假如是兩個（或許更多個） Table 經由過程 Join所得出的，那末 MySQL 必需經由過程先創立一個暫時表（Temporary Table），然後再將此暫時表的數據停止排序，以下例所示：

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

這個履行籌劃的輸入照樣有點奇異的，不曉得為何，MySQL Query Optimizer 將 “Using temporary” 進程顯示在第一行對 Table A 的操作中，豈非只是為讓履行籌劃的輸入少一行？

現實履行進程應當是以下圖所示：