mysql中提高Order by語句查詢效率的兩個思路分析
在MySQL數(shù)據(jù)庫中，Order by語句的使用頻率是比較高的。但是眾所周知，在使用這個語句時，往往會降低數(shù)據(jù)查詢的性能。因為可能需要對數(shù)據(jù)庫的記錄進行重新排序。在這篇文章中，筆者就談?wù)勌岣逴rder By語句查詢效率的兩個思路，以供大家參考。
一、建議使用一個索引來滿足Order By子句。
在條件允許的情況下，筆者建議最好使用一個索引來滿足Order By子句。如此的話，就可以避免額外的排序工作。這里筆者需要強調(diào)的一點是及時Order By子句不確切匹配索引，但是只要Where子句中所有未使用的索引部分和所有額外的 Order by子句中的列為常數(shù)，此時就可以使用索引。具體的來說，推薦如下的查詢語句。
1、select * from ad_user where is_active='Y' order by value;
在這條查詢語句中，使用了兩個列。在Where查詢語句中，查詢表中活動的記錄。此時使用的是一個常數(shù)的條件。而在Order By子句中，則根據(jù)Value列的值來進行排序。如果在表設(shè)計中，為這個字段設(shè)置一個索引。此時使用這條語句來進行查詢，則查詢結(jié)果就不需要進行額外的排序工作，從而可以提高數(shù)據(jù)的查詢效率。
這也就是說，如果Where條件語句與Order By條件語句一起使用，如果需要通過一個索引來提高查詢效率的話，那么就必須滿足一個條件，及where條件語句中所使用的參數(shù)值是常數(shù)，而不是變量。如果使用變量的話，這個方法就不奏效了。
2、注意有些情況下不能夠使用索引來提高Order By語句的查詢性能。
這里需要注意的是，并不是任何情況下都能夠通過使用索引來提高Order Byz子句的查詢效率。如對不同的關(guān)鍵字使用這個語句、混合使用ASC模式和DESC模式、用于查詢條件的關(guān)鍵字與Order By語句中所使用的關(guān)鍵字不同、對關(guān)鍵字的非連續(xù)元素使用Order By子句、在同一條語句中使用不同的Order BY 和Group BY表達式、使用的表索引的類型不能夠按順序來保存行等情況，就無法通過使用索引來解決Order By語句的排序問題。此時就需要另想他法。如可以重新調(diào)整表結(jié)構(gòu)或者查詢語句，以滿足使用這個特性的特定條件。
其實這里就遇到一個均衡的問題。如在查詢時，Where條件語句中往往使用的是一個變量，這主要是為了提高語句的靈活性。這個變量接受前端用戶傳遞過來的參數(shù)。此時如果用戶同時有排序的需求，根據(jù)上面介紹的規(guī)則，就無法使用索引來提高查詢的效率。此時作為開發(fā)人員，就需要評估，需要語句的靈活性還是需要查詢的性能。通常情況下，對于記錄量比較大的查詢，同時其查詢的格式比較固定，如大容量的月報與年報，此時就會傾向于查詢語句的性能。而對于記錄量比較少的查詢，如日報表，或者使用頻率比較高的查詢語句，此時會更加的傾向于查詢的靈活性。作為開發(fā)人員，現(xiàn)在需要關(guān)注的就是根據(jù)用戶實際的情況，來選擇合適的解決方式。
通常情況下，為了避免使用Order By語句導(dǎo)致的查詢速度變慢的問題，先是需要考慮使用索引來解決問題。如果不能夠通過索引來解決問題，那么可以通過緩存在一定程度來緩解。如可以增加soft_buffer_size變量的大小、根據(jù)實際情況調(diào)整Read_buffer_size變量的大小、更改tmpdir目錄將其指向具有大量空閑空間的專用文件系統(tǒng)等等。有時候管理員可以使用這個特性將負(fù)載均勻分布到多個目錄中去。
二、使用Explain關(guān)鍵字來確認(rèn)是否可以通過索引來解決Order BY速度問題。
如果用戶無法確定是否可以通過索引來提高Order By語句的查詢效率，那么就可以憑借Explain關(guān)鍵字來幫助關(guān)鍵員進行判斷。如可以通過使用explain select * from ad_user where is_active='Y' order by value(即在常規(guī)的查詢語句前面加上一個explain關(guān)鍵字)，用來判斷是否可以使用索引來提高查詢的效率。判斷的方法是：如果這個查詢語句中，有一個using filesort這個字段，那么就非常的抱歉，無法通過使用索引來提高這個語句的查詢效率。反之，沒有這個字段，則說明可以通過索引來提高查詢效率。
這里需要說明的是，通常情況下文件排序優(yōu)化不僅僅可以用于記錄排序關(guān)鍵字和行的位置，并且還會記錄查詢所需要的列。如此的話，就可以避免多次讀取行的信息。為了讓大家更加明白其中的道理筆者簡單說明一下這工作的過程。通常情況下，文件排序優(yōu)化包括四 個步驟。第一步讀取與Where條件語句所匹配的行信息;第二步對于每個行、記錄構(gòu)成排序關(guān)鍵字和行位置的一系列值，并且記錄查詢所需要的列;第三步根據(jù)排序關(guān)鍵字排序元祖;第四步按排序的順序檢索行，不過此時是直接從排序的元祖讀取所需要的列(使用的是第三個步驟中的結(jié)果)，而不會重新訪問表中的數(shù)據(jù)。顯然使用文件排序優(yōu)化的思路，可以避免重復(fù)訪問表，從而提高查詢的效率。
原文鏈接：http://publish.itpub.net/a2011/0225/1160/000001160766.shtml
相關(guān)文章
MySQL數(shù)據(jù)庫存儲引擎和分支現(xiàn)狀分析
在MySQL經(jīng)歷了2008年Sun的收購和2009年Oracle收購Sun的過程中，基本處于停滯發(fā)展的情況，在可以預(yù)見的未來，MySQL是肯定會被Oracle擱置并且逐步雪藏消滅掉的。2011-03-03
這篇文章主要給大家介紹了關(guān)于MySQL優(yōu)化教程之超大分頁查詢的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
MySQL報錯?:Error?writing?file?‘/tmp/XXXX‘?(Errcode:?28?
這篇文章主要給大家介紹了MySQL報錯解決：Error?writing?file?‘/tmp/XXXX‘?(Errcode:?28?-?No?space?left?on?device),文中通過代碼示例和圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-10-10
MYSQL的隨機抽取實現(xiàn)方法。舉個例子，要從tablename表中隨機提取一條記錄，大家一般的寫法就是：SELECT * FROM tablename ORDER BY RAND() LIMIT 1。2009-05-05
在進行更新和刪除操作的時候,條件語句里面有子查詢語句,此時會報1093錯誤,本文就來介紹一下1093錯誤的解決,感興趣的可以了解一下2024-02-02
在主外鍵表存在關(guān)系的時候如果加上"lazy=true"的話，則表明延遲，即只查詢主表中的內(nèi)容，而不查詢外鍵表中的內(nèi)容。2009-09-09
MySQL數(shù)據(jù)庫中提供了很豐富的函數(shù)。MySQL函數(shù)包括數(shù)學(xué)函數(shù)、字符串函數(shù)、日期和時間函數(shù)、條件判斷函數(shù)、系統(tǒng)信息函數(shù)、加密函數(shù)、格式化函數(shù)等。通過這些函數(shù)，可以簡化用戶的操作。本期將帶你總結(jié)常用函數(shù)都有哪些2021-11-11
這篇文章主要為大家詳細(xì)介紹了Ubuntu下MySQL安裝及配置遠程登錄教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-10-10
MySQL中IF()、IFNULL()、NULLIF()、ISNULL()函數(shù)的使用詳解
在MySQL中可以使用IF()、IFNULL()、NULLIF()、ISNULL()函數(shù)進行流程的控制。本文就詳細(xì)的介紹這幾種方法，感興趣的可以了解一下2021-06-06
這篇文章主要介紹了關(guān)于mysql的時區(qū)問題,具有很好的參考價值,希望對大家有所幫助,以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家,2023-11-11