在MySQL數(shù)據(jù)庫(kù)中，Order by語(yǔ)句的使用頻率是比較高的。但是眾所周知，在使用這個(gè)語(yǔ)句時(shí)，往往會(huì)降低數(shù)據(jù)查詢的性能。因?yàn)榭赡苄枰獙?duì)數(shù)據(jù)庫(kù)的記錄進(jìn)行重新排序。在這篇文章中，筆者就談?wù)勌岣逴rder By語(yǔ)句查詢效率的兩個(gè)思路，以供大家參考。

　一、建議使用一個(gè)索引來(lái)滿足Order By子句。

在條件允許的情況下，筆者建議最好使用一個(gè)索引來(lái)滿足Order By子句。如此的話，就可以避免額外的排序工作。這里筆者需要強(qiáng)調(diào)的一點(diǎn)是及時(shí)Order By子句不確切匹配索引，但是只要Where子句中所有未使用的索引部分和所有額外的 Order by子句中的列為常數(shù)，此時(shí)就可以使用索引。具體的來(lái)說(shuō)，推薦如下的查詢語(yǔ)句。

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

在這條查詢語(yǔ)句中，使用了兩個(gè)列。在Where查詢語(yǔ)句中，查詢表中活動(dòng)的記錄。此時(shí)使用的是一個(gè)常數(shù)的條件。而在Order By子句中，則根據(jù)Value列的值來(lái)進(jìn)行排序。如果在表設(shè)計(jì)中，為這個(gè)字段設(shè)置一個(gè)索引。此時(shí)使用這條語(yǔ)句來(lái)進(jìn)行查詢，則查詢結(jié)果就不需要進(jìn)行額外的排序工作，從而可以提高數(shù)據(jù)的查詢效率。

這也就是說(shuō)，如果Where條件語(yǔ)句與Order By條件語(yǔ)句一起使用，如果需要通過(guò)一個(gè)索引來(lái)提高查詢效率的話，那么就必須滿足一個(gè)條件，及where條件語(yǔ)句中所使用的參數(shù)值是常數(shù)，而不是變量。如果使用變量的話，這個(gè)方法就不奏效了。

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

這里需要注意的是，并不是任何情況下都能夠通過(guò)使用索引來(lái)提高Order Byz子句的查詢效率。如對(duì)不同的關(guān)鍵字使用這個(gè)語(yǔ)句、混合使用ASC模式和DESC模式、用于查詢條件的關(guān)鍵字與Order By語(yǔ)句中所使用的關(guān)鍵字不同、對(duì)關(guān)鍵字的非連續(xù)元素使用Order By子句、在同一條語(yǔ)句中使用不同的Order BY 和Group BY表達(dá)式、使用的表索引的類型不能夠按順序來(lái)保存行等情況，就無(wú)法通過(guò)使用索引來(lái)解決Order By語(yǔ)句的排序問(wèn)題。此時(shí)就需要另想他法。如可以重新調(diào)整表結(jié)構(gòu)或者查詢語(yǔ)句，以滿足使用這個(gè)特性的特定條件。

其實(shí)這里就遇到一個(gè)均衡的問(wèn)題。如在查詢時(shí)，Where條件語(yǔ)句中往往使用的是一個(gè)變量，這主要是為了提高語(yǔ)句的靈活性。這個(gè)變量接受前端用戶傳遞過(guò)來(lái)的參數(shù)。此時(shí)如果用戶同時(shí)有排序的需求，根據(jù)上面介紹的規(guī)則，就無(wú)法使用索引來(lái)提高查詢的效率。此時(shí)作為開發(fā)人員，就需要評(píng)估，需要語(yǔ)句的靈活性還是需要查詢的性能。通常情況下，對(duì)于記錄量比較大的查詢，同時(shí)其查詢的格式比較固定，如大容量的月報(bào)與年報(bào)，此時(shí)就會(huì)傾向于查詢語(yǔ)句的性能。而對(duì)于記錄量比較少的查詢，如日?qǐng)?bào)表，或者使用頻率比較高的查詢語(yǔ)句，此時(shí)會(huì)更加的傾向于查詢的靈活性。作為開發(fā)人員，現(xiàn)在需要關(guān)注的就是根據(jù)用戶實(shí)際的情況，來(lái)選擇合適的解決方式。

通常情況下，為了避免使用Order By語(yǔ)句導(dǎo)致的查詢速度變慢的問(wèn)題，先是需要考慮使用索引來(lái)解決問(wèn)題。如果不能夠通過(guò)索引來(lái)解決問(wèn)題，那么可以通過(guò)緩存在一定程度來(lái)緩解。如可以增加soft_buffer_size變量的大小、根據(jù)實(shí)際情況調(diào)整Read_buffer_size變量的大小、更改tmpdir目錄將其指向具有大量空閑空間的專用文件系統(tǒng)等等。有時(shí)候管理員可以使用這個(gè)特性將負(fù)載均勻分布到多個(gè)目錄中去。

二、使用Explain關(guān)鍵字來(lái)確認(rèn)是否可以通過(guò)索引來(lái)解決Order BY速度問(wèn)題。

如果用戶無(wú)法確定是否可以通過(guò)索引來(lái)提高Order By語(yǔ)句的查詢效率，那么就可以憑借Explain關(guān)鍵字來(lái)幫助關(guān)鍵員進(jìn)行判斷。如可以通過(guò)使用explain select * from ad_user where is_active='Y' order by value(即在常規(guī)的查詢語(yǔ)句前面加上一個(gè)explain關(guān)鍵字)，用來(lái)判斷是否可以使用索引來(lái)提高查詢的效率。判斷的方法是：如果這個(gè)查詢語(yǔ)句中，有一個(gè)using filesort這個(gè)字段，那么就非常的抱歉，無(wú)法通過(guò)使用索引來(lái)提高這個(gè)語(yǔ)句的查詢效率。反之，沒有這個(gè)字段，則說(shuō)明可以通過(guò)索引來(lái)提高查詢效率。

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

原文鏈接：http://publish.itpub.net/a2011/0225/1160/000001160766.shtml

最新評(píng)論