MySQL技巧:結(jié)合相關(guān)參數(shù) 做好Limit優(yōu)化

　　我們?cè)诓樵?xún)數(shù)據(jù)時(shí)，往往需要指定返回幾行數(shù)據(jù)。如現(xiàn)在有一個(gè)B/S架構(gòu)的應(yīng)用程序，其每一頁(yè)可能只顯示30條記錄。此時(shí)為了提高顯示的效率，一般就要求數(shù)據(jù)庫(kù)一次只返回三十條紀(jì)錄。等用戶(hù)按下一頁(yè)的時(shí)候，再?gòu)臄?shù)據(jù)庫(kù)中返回30條記錄，以此類(lèi)推。這可以縮短數(shù)據(jù)顯示的時(shí)間。當(dāng)查詢(xún)的基表比較大時(shí)，這個(gè)措施非常有效。此時(shí)可以使用Limit關(guān)鍵字來(lái)實(shí)現(xiàn)這個(gè)需求。Limit子句可以被用于強(qiáng)制Select查詢(xún)語(yǔ)句返回指定的記錄數(shù)量。

　　通常情況下，Limit關(guān)鍵字可以接受一個(gè)或者兩個(gè)數(shù)字參數(shù)。需要注意的是，這個(gè)參數(shù)必須是一個(gè)整數(shù)常量。如果用戶(hù)給定兩個(gè)參數(shù)，則第一個(gè)參數(shù)表示第一個(gè)返回記錄行的偏移量，第二個(gè)參數(shù)則表示返回記錄行的最大數(shù)據(jù)。另外需要提醒的是，初始記錄行的偏移量是0，而不是1。不少用戶(hù)會(huì)在這里犯錯(cuò)誤。

　　雖然使用了Limit語(yǔ)句來(lái)限制返回的記錄數(shù)，從而可以提高應(yīng)用程序的工作效率。但是其也會(huì)給系統(tǒng)的性能帶來(lái)一些負(fù)面影響。如可能會(huì)導(dǎo)致全表掃描等等。為此筆者給出一些Limit關(guān)鍵字的優(yōu)化的建議，以供大家參考。

　　建議一：靈活使用Limit0子句

　　MySQL根據(jù)Limit關(guān)鍵字的定義，如果參數(shù)為0的話(huà)，則其返回的是空記錄。這看起來(lái)好像沒(méi)有多少的意義。其實(shí)不然。在實(shí)際工作中，靈活使用這個(gè)0參數(shù)，能夠給我們帶來(lái)很大的收獲。

　　如現(xiàn)在數(shù)據(jù)庫(kù)工程師想要確認(rèn)一下某個(gè)查詢(xún)語(yǔ)句的有效性，如果直接運(yùn)行這個(gè)查詢(xún)語(yǔ)句，需要等待其返回的記錄。如果涉及的紀(jì)錄數(shù)量比較多，或者運(yùn)算邏輯比較復(fù)雜，那么需要等到比較長(zhǎng)的時(shí)間。此時(shí)就可以在Select查詢(xún)語(yǔ)句中，使用Limit0子句。只要查詢(xún)語(yǔ)句沒(méi)有語(yǔ)法上的錯(cuò)誤，這就可以讓數(shù)據(jù)庫(kù)快速的返回一個(gè)空集合。從而幫助數(shù)據(jù)庫(kù)設(shè)計(jì)人員迅速的判斷查詢(xún)語(yǔ)句的有效性。另外這個(gè)空集和中還會(huì)返回某個(gè)表的各個(gè)字段的數(shù)據(jù)類(lèi)型。即通過(guò)這個(gè)Limit0子句還可以查詢(xún)某個(gè)表的表結(jié)構(gòu)。

　　可見(jiàn)靈活應(yīng)用Limir0子句，確實(shí)能夠給我們帶來(lái)不小的收益。不過(guò)需要注意的是，在某些特定的場(chǎng)合下，這個(gè)子句可能不會(huì)奏效。如通常情況下，在Monitor工作環(huán)境中不支持這個(gè)Limit0子句。此時(shí)結(jié)果只會(huì)顯示EmptySet，而不是我們所需要的結(jié)果。

　　GroupBy關(guān)鍵字主要用來(lái)對(duì)數(shù)據(jù)進(jìn)行分類(lèi)匯總。不過(guò)在分類(lèi)匯總之前，往往需要對(duì)數(shù)據(jù)先進(jìn)性排序。而Limit語(yǔ)句用來(lái)指定顯示的結(jié)果數(shù)量時(shí)，往往也需要涉及到紀(jì)錄的分類(lèi)匯總與排序的問(wèn)題。如現(xiàn)在一個(gè)學(xué)校成績(jī)管理系統(tǒng)中，需要對(duì)學(xué)生的總分進(jìn)行排序。即先對(duì)學(xué)生各科成績(jī)進(jìn)行匯總，然后顯示其排名為前50的紀(jì)錄。此時(shí)就需要同時(shí)用到GroupBy子句和Limit子句。其實(shí)從這個(gè)案例中我們也可以看出，這兩個(gè)子句相互依賴(lài)的特性。正是因?yàn)檫@種特性(經(jīng)常相互結(jié)合使用)，為此結(jié)合GroupBy子句可以提高Limit的查詢(xún)效率。

　　這主要是因?yàn)閮烧呷绻黄鹗褂玫脑?huà)，Limit關(guān)鍵字將不會(huì)再重復(fù)計(jì)算任何不必要的GroupBy的值。換句話(huà)說(shuō)，在某些情況下，GroupBy子句能夠通過(guò)順序來(lái)讀取鍵或者在鍵上做排序來(lái)解決分類(lèi)匯總時(shí)的排序問(wèn)題，然后再計(jì)算摘要直到關(guān)鍵字的值的改變?yōu)橹埂Ｈ绱说脑?huà)，兩個(gè)子句所需要做的一些共同性的工作，只要做一次即可。這就可以從另外一次角度用來(lái)提高應(yīng)用系統(tǒng)的性能。相比先做一個(gè)視圖對(duì)數(shù)據(jù)進(jìn)行分類(lèi)匯總的運(yùn)算，再使用一個(gè)查詢(xún)語(yǔ)句來(lái)抽取特定數(shù)量的記錄，效率就要高一點(diǎn)。因?yàn)楹笳呤菍蓚�(gè)子句分開(kāi)來(lái)使用，就無(wú)法享受到結(jié)合使用所體現(xiàn)的優(yōu)勢(shì)。

　　默認(rèn)情況下，Limit子句返回用戶(hù)所指定的記錄行數(shù)。只要數(shù)據(jù)庫(kù)已經(jīng)發(fā)送了用戶(hù)所需要的行數(shù)，則數(shù)據(jù)庫(kù)系統(tǒng)會(huì)放棄剩余的查詢(xún)。即上面這個(gè)學(xué)生成績(jī)的案例中，如果用戶(hù)只需要返回總分成績(jī)排名前50的學(xué)生，則數(shù)據(jù)庫(kù)只返回50條記錄，然后終止查詢(xún)作業(yè)。

　　但是在某些特定的情況下，用戶(hù)可能仍然需要繼續(xù)后續(xù)的查詢(xún)呢?如用戶(hù)出了查詢(xún)某些特定的記錄，還需要知道總的記錄數(shù)量，此時(shí)該如何處理?如現(xiàn)在用戶(hù)需要知道排名前50的學(xué)生信息，同時(shí)需要知道總分在500分以上的總?cè)藬?shù)。此時(shí)單獨(dú)使用Limit子句可能無(wú)法滿(mǎn)足用戶(hù)的需求，因?yàn)槠渲魂P(guān)心前面50條記錄。如果要實(shí)現(xiàn)這個(gè)需求的話(huà)，往往需要結(jié)合SQL_calc_found_rows關(guān)鍵字。

　　這個(gè)關(guān)鍵字的主要用途就是能夠在查詢(xún)時(shí)為數(shù)據(jù)庫(kù)管理員事先準(zhǔn)備好符合Where條件語(yǔ)句的記錄數(shù)目。然后用戶(hù)只要在隨后執(zhí)行一條SelectFound_ROWS語(yǔ)句之后，就可以獲得符合條件的記錄總數(shù)。不過(guò)需要注意的是，使用這個(gè)關(guān)鍵字會(huì)帶來(lái)一定的副作用。即帶有這個(gè)關(guān)鍵字的查詢(xún)語(yǔ)句，是無(wú)法使用數(shù)據(jù)緩存的。故在某些情況下會(huì)降低數(shù)據(jù)查詢(xún)的性能。故一般情況下，這個(gè)關(guān)鍵字只用于Where條件語(yǔ)句比較復(fù)雜的情況。當(dāng)然這只是一個(gè)出于性能考慮的建議，而并不是技術(shù)上的限制。即即使Where條件語(yǔ)句不復(fù)雜，也可以使用這個(gè)關(guān)鍵字，不會(huì)出現(xiàn)語(yǔ)法上的錯(cuò)誤。只是其在性能上并不是很理想。

　　建議四：與Distinct關(guān)鍵字共同使用時(shí)的特殊現(xiàn)象

　　Distinct關(guān)鍵字主要用來(lái)過(guò)濾重復(fù)的記錄。而Limit關(guān)鍵字則主要用來(lái)指定記錄所返回的行數(shù)。如果這兩個(gè)關(guān)鍵字共同使用時(shí)，會(huì)出現(xiàn)什么樣的情況呢?如果從字面意思去理解，數(shù)據(jù)庫(kù)會(huì)返回指定的不重復(fù)的記錄數(shù)。如Limit的參數(shù)為50，則數(shù)據(jù)庫(kù)返回50條不重復(fù)的記錄數(shù)。然后后續(xù)的查詢(xún)就會(huì)停止。如果查詢(xún)的記錄中有重復(fù)記錄，則數(shù)據(jù)庫(kù)查詢(xún)的實(shí)際數(shù)量往往要比Limit關(guān)鍵字所指定的數(shù)量要多。

　　在實(shí)際工作中，這條語(yǔ)句的作用還是很大的。如現(xiàn)在有一張員工考勤信息的表格。現(xiàn)在數(shù)據(jù)庫(kù)管理員需要統(tǒng)計(jì)缺勤次數(shù)排名前20的員工人數(shù)。此時(shí)為了防止有重復(fù)的記錄，就可以在查詢(xún)語(yǔ)句中加一個(gè)Distinct關(guān)鍵字，用來(lái)過(guò)濾重復(fù)的記錄數(shù)。從而可以避免采用多個(gè)查詢(xún)語(yǔ)句來(lái)完成這個(gè)需求。

　　建議五：Limit與索引之間的關(guān)系

　　如果數(shù)據(jù)庫(kù)管理員決定使用Limit子句來(lái)指定需要顯示的記錄數(shù)，那么最好能夠最大限度的使用索引，以避免全表掃描，提高工作效率。即當(dāng)數(shù)據(jù)庫(kù)選擇做完整的表掃描時(shí)，可以在某些情況下使用索引。

　　如現(xiàn)在數(shù)據(jù)庫(kù)管理員決定將Limit子句與OrderBY子句一起使用。數(shù)據(jù)庫(kù)一旦找到了排序結(jié)果的第一個(gè)RowCount行，則系統(tǒng)將會(huì)結(jié)束排序，而并不會(huì)對(duì)整個(gè)表進(jìn)行排序。如果單獨(dú)使用OrderBy子句的話(huà)，則會(huì)對(duì)整個(gè)表進(jìn)行排序。雖然如此，但是排序必定要浪費(fèi)一定的時(shí)間。此時(shí)數(shù)據(jù)庫(kù)管理員如果決定使用索引，則可以在很大程度上提高這個(gè)查詢(xún)的效率。

　　對(duì)于這個(gè)內(nèi)容，筆者要強(qiáng)調(diào)一個(gè)問(wèn)題。如果必須要進(jìn)行文件排序，則必須選擇所有匹配查詢(xún)，并且在確定已經(jīng)找到第一個(gè)行之前，必須對(duì)他們的大部分內(nèi)容進(jìn)行了排序。特別需要強(qiáng)調(diào)的是，在任何情況下，一旦找到了行，則就不需要再排序結(jié)果的其他部分，數(shù)據(jù)庫(kù)會(huì)自動(dòng)結(jié)束排序。

　　可見(jiàn)Limit子句其本質(zhì)的功能是限制用戶(hù)的紀(jì)錄數(shù)量。但是其還有很多別的用途。如快速判斷查詢(xún)語(yǔ)句的有效性、計(jì)算表所需要的空間等等。不過(guò)其也有一定的副作用，可能會(huì)帶系統(tǒng)的運(yùn)行帶來(lái)一些負(fù)面的影響。此時(shí)最好能夠采取一些措施來(lái)提高系統(tǒng)運(yùn)行的性能。

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]