mysql中提高Order by語句查詢效率的兩個思路分析

因為可能需要對資料庫的記錄進行重新排序。在這篇文章中，筆者就談談提高Order By語句查詢效率的兩個思路，以供大家參考。

在MySQL資料庫中，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條件陳述式中所使用的參數值是常數，而不是變數。如果使用變數的話，這個方法就不奏效了。

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條件陳述式所匹配的行資訊;第二步對於每個行、記錄構成排序關鍵字和行位置的一系列值，並且記錄查詢所需要的列;第三步根據排序關鍵字排序元祖;第四步按排序的順序檢索行，不過此時是直接從排序的元祖讀取所需要的列(使用的是第三個步驟中的結果)，而不會重新訪問表中的資料。顯然使用檔案排序最佳化的思路，可以避免重複訪問表，從而提高查詢的效率。

原文連結：http://publish.itpub.net/a2011/0225/1160/000001160766.shtml