Mysql中order by語句的優化詳解

order by語句的優化的技巧以前小編有介紹過一些相關的教程，在這裡我今天再為各位整理一篇關於Mysql中order by語句的優化的一些方法。

在某些情況中，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序。where條件和order by使用相同的索引，並且order by的順序和索引順序相同，並且order by的字段都是升序或者都是降序。

一、建議使用一個索引來滿足Order By子句。

在條件允許的情況下，筆者建議最好使用一個索引來滿足Order By子句。如此的話，就可以避免額外的排序工作。這裡筆者需要強調的一點是及時Order By子句不確切匹配索引，但是只要Where子句中所有未使用的索引部分和所有額外的 Order by子句中的列為常數，此時就可以使用索引。具體的來說，推薦如下的查詢語句。

 代碼如下 復制代碼

1、select * from ad_user where is_active=’Y’ order by value;

在這條查詢語句中，使用了兩個列。在Where查詢語句中，查詢表中活動的記錄。此時使用的是一個常數的條件。而在Order By子句中，則根據Value列的值來進行排序。如果在表設計中，為這個字段設置一個索引。此時使用這條語句來進行查詢，則查詢結果就不需要進行額外的排序工作，從而可以提高數據的查詢效率。

這也就是說，如果Where條件語句與Order By條件語句一起使用，如果需要通過一個索引來提高查詢效率的話，那麼就必須滿足一個條件，及where條件語句中所使用的參數值是常數，而不是變量。如果使用變量的話，這個方法就不奏效了。

例如：下列sql可以使用索引。

 

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

 
但是以下情況不使用索引。

 代碼如下 復制代碼

1) SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

--order by的字段混合ASC和DESC

2) SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

--用於查詢行的關鍵字與ORDER BY中所使用的不相同

3) SELECT * FROM t1 ORDER BY key1, key2；

--對不同的關鍵字使用ORDER BY

2、注意有些情況下不能夠使用索引來提高Order By語句的查詢性能。

這裡需要注意的是，並不是任何情況下都能夠通過使用索引來提高Order Byz子句的查詢效率。如對不同的關鍵字使用這個語句、混合使用ASC模式和DESC模式、用於查詢條件的關鍵字與Order By語句中所使用的關鍵字不同、對關鍵字的非連續元素使用Order By子句、在同一條語句中使用不同的Order BY 和Group BY表達式、使用的表索引的類型不能夠按順序來保存行等情況，就無法通過使用索引來解決Order By語句的排序問題。此時就需要另想他法。如可以重新調整表結構或者查詢語句，以滿足使用這個特性的特定條件。

其實這裡就遇到一個均衡的問題。如在查詢時，Where條件語句中往往使用的是一個變量，這主要是為了提高語句的靈活性。這個變量接受前端用戶傳遞過來的參數。此時如果用戶同時有排序的需求，根據上面介紹的規則，就無法使用索引來提高查詢的效率。此時作為開發人員，就需要評估，需要語句的靈活性還是需要查詢的性能。通常情況下，對於記錄量比較大的查詢，同時其查詢的格式比較固定，如大容量的月報與年報，此時就會傾向於查詢語句的性能。而對於記錄量比較少的查詢，如日報表，或者使用頻率比較高的查詢語句，此時會更加的傾向於查詢的靈活性。作為開發人員，現在需要關注的就是根據用戶實際的情況，來選擇合適的解決方式。

通常情況下，為了避免使用Order By語句導致的查詢速度變慢的問題，先是需要考慮使用索引來解決問題。如果不能夠通過索引來解決問題，那麼可以通過緩存在一定程度來緩解。如可以增加soft_buffer_size變量的大小、根據實際情況調整Read_buffer_size變量的大小、更改tmpdir目錄將其指向具有大量空閒空間的專用文件系統等等。有時候管理員可以使用這個特性將負載均勻分布到多個目錄中去。

二、使用Explain關鍵字來確認是否可以通過索引來解決Order BY速度問題。

如果用戶無法確定是否可以通過索引來提高Order By語句的查詢效率，那麼就可以憑借Explain關鍵字來幫助關鍵員進行判斷。如可以通過使用explain select * from ad_user where is_active=’Y’ order by value(即在常規的查詢語句前面加上一個explain關鍵字)，用來判斷是否可以使用索引來提高查詢的效率。判斷的方法是：如果這個查詢語句中，有一個using filesort這個字段，那麼就非常的抱歉，無法通過使用索引來提高這個語句的查詢效率。反之，沒有這個字段，則說明可以通過索引來提高查詢效率。

這裡需要說明的是，通常情況下文件排序優化不僅僅可以用於記錄排序關鍵字和行的位置，並且還會記錄查詢所需要的列。如此的話，就可以避免多次讀取行的信息。為了讓大家更加明白其中的道理筆者簡單說明一下這工作的過程。通常情況下，文件排序優化包括四 個步驟。第一步讀取與Where條件語句所匹配的行信息;第二步對於每個行、記錄構成排序關鍵字和行位置的一系列值，並且記錄查詢所需要的列;第三步根據排序關鍵字排序元祖;第四步按排序的順序檢索行，不過此時是直接從排序的元祖讀取所需要的列(使用的是第三個步驟中的結果)，而不會重新訪問表中的數據。顯然使用文件排序優化的思路，可以避免重復訪問表，從而提高查詢的效率

一次mysql order by 優化案例

 代碼如下 復制代碼

1.  desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where CreateDate>'0001-01-01 00:00:00 '  and ActionType in(10,9,19,20)  ORDER BY CreateDate DESC limit 93060,20;
   
| id | select_type | table             | type | possible_keys          | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-------------------+------+------------------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | wikiuseractionlog | ALL  | idx_date_type,idx_date | NULL | NULL    | NULL | 91859 | Using where; Using filesort

看執行計劃，沒有用上索引，雖然我建了一個復合索引idx_date_type(createdate,actiontype)和一個列索引idx_date(createdate)，要使上面的語句走索引只要有下面幾點：

1）需要將日期‘0001-01-01 00：00：00’改成2014-01-01 00:00:00  (2013以下都不行，不知道為什麼)！！補充：DBA群有大神說：select 的結果集占全表的20%，就會走全表 ，而不走索引！ 我想可能是MYSQL優化器根據CBO成本估算，如果select數據量大，估計走索引的成本會比走全表的還大，所以才會全表掃描而不走索引！

2）需將createdate和actiontype做復合索引！一般情況下order by的字段如果要走索引優化，就需要將where條件下條件字段與order by的字段做聯合索引！比如： select * from test_0719 order by name;這種條件下沒有where 條件字段，就算name上有索引，也還是會發生文件排序，那這種條件下，要麼是增加一個where 條件字段，然後把這個字段與name做聯合索引！要麼是把name字段也添加到where 條件下！  

3） limit 93060,20  這種語句應該避免，如果此表不是非常大！93060行就已經占據了全表比較大的量，那mysql優化器可能就不會選擇索引，就算有索引也不會走！而是選擇全表掃描！因為要取的數據量非常大！mysql優化器很可能會認為走索引的成本比走全表掃描的成本還大！  同上面第一點！

 代碼如下 復制代碼

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog  ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | wikiuseractionlog | ALL  | NULL          | NULL | NULL    | NULL | 95220 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, CreateDate, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where CreateDate>'2014-01-01 00:00:00 '  ORDER BY CreateDate DESC  limit 93060,20;
+----+-------------+-------------------+-------+------------------------+---------------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys          | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+------------------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | wikiuseractionlog | range | idx_date_type,idx_date | idx_date_type | 8       | NULL | 3737 | Using where

4）此種情況下雖然有單獨的索引idx_date(createdate)，但發現依舊用不上索引，原因是order by的字段也必須出現在where 條件裡，此處的原因大概也就是，排序操作是在最後的，首先提取數據的時候，如果where條件裡的字段有索引，在提取出來的時候就已經做了排序操作了，此後再order by的時候就不用filesort了，不然則，在最後提取出來的數據裡order by的時候，自然就要做filesort了！！

5）下面第一個例子用不上索引！第二個則可以！將ActionType in(10,9,19,20) 改成or 也不行！此處涉及到in 優化，可以嘗試改成union all

 代碼如下 復制代碼

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where ActionType in(10,9,19,20)  ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | wikiuseractionlog | ALL  | idx_type_date | NULL | NULL    | NULL | 90747 | Using where; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> desc SELECT ActionID, UserID, CreateUser, ActionType, ActionName, ActionComment, PointsRulesID,Reason,ObjectID,ByUserID,ByUserName,SubjectID FROM  wikiuseractionlog Where ActionType ='10'  ORDER BY CreateDate;
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
| id | select_type | table             | type | possible_keys | key           | key_len | ref   | rows  | Extra       |
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
|  1 | SIMPLE      | wikiuseractionlog | ref  | idx_type_date | idx_type_date | 3       | const | 45373 | Using where |
+----+-------------+-------------------+------+---------------+---------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)

總結：1

order by 後的字段，如果要走索引，須與where 條件裡的某字段建立復合索引！！或者說orcer by後的字段如果要走索引排序，它要麼與where 條件裡的字段建立復合索引【這裡建立復合索引的時候，需要注意復合索引的列順序為（where字段，order by 字段），這樣才能滿足最左列原則，原因可能是order by字段並能算在where 查詢條件中！】，要麼它自身要在where 條件裡被引用到！

總結：2

表a       id為普通字段,上面建有索引
select * from a order by id   (用不上索引)
select id from a order by id (能用上索引)
select * from a where id=XX order by id  (能用上索引)
意思是說order by 要避免使用文件系統排序，要麼把order by的字段出現在select 後，要麼使用order by字段出現在where 條件裡，要麼把order by字段與where 條件字段建立復合索引！