mysql中order by 語句的用法 索引優化

mysql中Order By可以用來做很多的用法，以前在一些手冊可參考到關於order by 的用法，但都是想當簡單的用法，這是本文章的第一節，下面我們來一行行來介紹關於它的具體用法。

MySQL Order By keyword是用來給記錄中的數據進行分類的。

MySQL Order By Keyword根據關鍵詞分類

ORDER BY keyword是用來給記錄中的數據進行分類的。

MySQL Order By語法

 代碼如下 復制代碼 SELECT column_name(s)
FROM table_name
ORDER BY column_name

注意：SQL語句是“字母大小寫不敏感”的語句（它不區分字母的大小寫），即：“ORDER BY”和“order by”是一樣的。

ORDER BY 關鍵詞用於對記錄集中的數據進行排序。

例子
下面的例子選取 "Persons" 表中的存儲的所有數據，並根據 "Age" 列對結果進行排序：

 代碼如下 復制代碼

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Persons ORDER BY age");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'];
  echo " " . $row['LastName'];
  echo " " . $row['Age'];
  echo "<br />";
  }

mysql_close($con);
?>

以上代碼的輸出：

 代碼如下 復制代碼 Glenn Quagmire 33
Peter Griffin 35

升序或降序的排序
如果您使用 ORDER BY 關鍵詞，記錄集的排序順序默認是升序（1 在 9 之前，"a" 在 "p" 之前）。

請使用 DESC 關鍵詞來設定降序排序（9 在 1 之前，"p" 在 "a" 之前）：

注:

如果我們在執行select語句的時候使用ORDER BY （DESC），那麼它首先會對所有記錄按照關鍵字有一個排序，然後依次讀取所需的記錄，而不是先選出記錄再進行降序排列

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 的操作中，難道只是為讓執行計劃的輸出少一行

MySQL Order By索引優化方法

盡管 ORDER BY 不是和索引的順序准確匹配，索引還是可以被用到，只要不用的索引部分和所有的額外的 ORDER BY 字段在 WHERE 子句中都被包括了。

使用索引的MySQL Order By
下列的幾個查詢都會使用索引來解決 ORDER BY 或 GROUP BY 部分：

 代碼如下 復制代碼 SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 WHERE key_part1=constant GROUP BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

不使用索引的MySQL Order By
在另一些情況下，MySQL無法使用索引來滿足 ORDER BY，盡管它會使用索引來找到記錄來匹配 WHERE 子句。這些情況如下：
* 對不同的索引鍵做 ORDER BY ：

 代碼如下 復制代碼 SELECT * FROM t1 ORDER BY key1, key2;

* 在非連續的索引鍵部分上做 ORDER BY：

 代碼如下 復制代碼 SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

* 同時使用了 ASC 和 DESC：

 代碼如下 復制代碼 SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

* 用於搜索記錄的索引鍵和做 ORDER BY 的不是同一個：

 代碼如下 復制代碼 SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

* 有很多表一起做連接，而且讀取的記錄中在 ORDER BY 中的字段都不全是來自第一個非常數的表中（也就是說，在 EXPLAIN 分析的結果中的第一個表的連接類型不是 const）。
* 使用了不同的 ORDER BY 和 GROUP BY 表達式。
* 表索引中的記錄不是按序存儲。例如，HASH 和 HEAP 表就是這樣。

通過執行 EXPLAIN SELECT ... ORDER BY，就知道MySQL是否在查詢中使用了索引。如果 Extra 字段的值是 Using filesort，則說明MySQL無法使用索引。詳情請看"7.2.1 EXPLAIN Syntax (Get Information About a SELECT)"。當必須對結果進行排序時，MySQL 4.1以前 它使用了以下 filesort 算法：
復制代碼 代碼如下:
1. 根據索引鍵讀取記錄，或者掃描數據表。那些無法匹配 WHERE 分句的記錄都會被略過。
2. 在緩沖中每條記錄都用一個‘對'存儲了2個值（索引鍵及記錄指針）。緩沖的大小依據系統變量 sort_buffer_size 的值而定。
3. 當緩沖慢了時，就運行 qsort（快速排序）並將結果存儲在臨時文件中。將存儲的塊指針保存起來（如果所有的‘對'值都能保存在緩沖中，就無需創建臨時文件了）。
4. 執行上面的操作，直到所有的記錄都讀取出來了。
5. 做一次多重合並，將多達 MERGEBUFF（7）個區域的塊保存在另一個臨時文件中。重復這個操作，直到所有在第一個文件的塊都放到第二個文件了。
6. 重復以上操作，直到剩余的塊數量小於 MERGEBUFF2 (15)。
7. 在最後一次多重合並時，只有記錄的指針（排序索引鍵的最後部分）寫到結果文件中去。
8. 通過讀取結果文件中的記錄指針來按序讀取記錄。想要優化這個操作，MySQL將記錄指針讀取放到一個大的塊裡，並且使用它來按序讀取記錄，將記錄放到緩沖中。緩沖的大小由系統變量 read_rnd_buffer_size 的值而定。這個步驟的代碼在源文件 `sql/records.cc' 中。

這個逼近算法的一個問題是，數據庫讀取了2次記錄：一次是估算 WHERE 分句時，第二次是排序時。盡管第一次都成功讀取記錄了（例如，做了一次全表掃描），第二次是隨機的讀取（索引鍵已經排好序了，但是記錄並沒有）。在MySQL 4.1 及更新版本中，filesort 優化算法用於記錄中不只包括索引鍵值和記錄的位置，還包括查詢中要求的字段。這麼做避免了需要2次讀取記錄。改進的 filesort 算法做法大致如下：
1. 跟以前一樣，讀取匹配 WHERE 分句的記錄。
2. 相對於每個記錄，都記錄了一個對應的；‘元組'信息信息，包括索引鍵值、記錄位置、以及查詢中所需要的所有字段。
3. 根據索引鍵對‘元組'信息進行排序。
4. 按序讀取記錄，不過是從已經排序過的‘元組'列表中讀取記錄，而非從數據表中再讀取一次。

使用改進後的 filesort 算法相比原來的，‘元組'比‘對'需要占用更長的空間，它們很少正好適合放在排序緩沖中（緩沖的大小是由 sort_buffer_size 的值決定的）。因此，這就可能需要有更多的I/O操作，導致改進的算法更慢。為了避免使之變慢，這種優化方法只用於排序‘元組'中額外的字段的大小總和超過系統變量 max_length_for_sort_data 的情況（這個變量的值設置太高的一個表象就是高磁盤負載低CPU負載）。想要提高 ORDER BY 的速度，首先要看MySQL能否使用索引而非額外的排序過程。如果不能使用索引，可以試著遵循以下策略：
* 增加 sort_buffer_size 的值。
* 增加 read_rnd_buffer_size 的值。
* 修改 tmpdir，讓它指向一個有很多剩余空間的專用文件系統。

好了，關於mysql 用法，索引用法，優化等待一系列的都有介紹了，大家可參考一下。