MySQL技巧:做好Limit優化

我們在查詢資料時，往往需要指定返回幾行資料。 如現在有一個B/S架構的應用程式，其每一頁可能只顯示30條記錄。 此時為了提高顯示的效率，一般就要求資料庫一次只返回三十條紀錄。 等使用者按下一頁的時候，再從資料庫中返回30條記錄，以此類推。 這可以縮短資料顯示的時間。 當查詢的基表比較大時，這個措施非常有效。 此時可以使用Limit關鍵字來實現這個需求。 Limit子句可以被用於強制Select查詢語句返回指定的記錄數量。

通常情況下，Limit關鍵字可以接受一個或者兩個數字參數。 需要注意的是，這個參數必須是一個整數常量。 如果使用者給定兩個參數，則第一個參數表示第一個返回記錄行的偏移量，第二個參數則表示返回記錄行的最大資料。 另外需要提醒的是，初始記錄行的偏移量是0，而不是1。 不少使用者會在這裡犯錯誤。

雖然使用了Limit語句來限制返回的記錄數，從而可以提高應用程式的工作效率。 但是其也會給系統的性能帶來一些負面影響。 如可能會導致全資料表掃描等等。 為此筆者給出一些Limit關鍵字的優化的建議，以供大家參考。

建議一：靈活使用Limit 0子句

根據Limit關鍵字的定義，如果參數為0的話，則其返回的是空記錄。 這看起來好像沒有多少的意義。 其實不然。 在實際工作中，靈活使用這個0參數，能夠給我們帶來很大的收穫。

如現在資料庫工程師想要確認一下某個查詢語句的有效性，如果直接運行這個查詢語句，需要等待其返回的記錄。 如果涉及的紀錄數量比較多，或者運算邏輯比較複雜，那麼需要等到比較長的時間。 此時就可以在Select查詢語句中，使用Limit 0子句。 只要查詢語句沒有語法上的錯誤，這就可以讓資料庫快速的返回一個空集合。 從而説明資料庫設計人員迅速的判斷查詢語句的有效性。 另外這個空集和中還會返回某個表的各個欄位的資料類型。 即通過這個Limit 0子句還可以查詢某個表的表結構。

可見靈活應用Limir 0子句，確實能夠給我們帶來不小的收益。 不過需要注意的是，在某些特定的場合下，這個子句可能不會奏效。 如通常情況下，在Monitor工作環境中不支援這個Limit 0子句。 此時結果只會顯示Empty Set，而不是我們所需要的結果。

建議二：Limit與Group By結合使用

Group By關鍵字主要用來對資料進行分類匯總。 不過在分類匯總之前，往往需要對資料先進性排序。 而Limit語句用來指定顯示的結果數量時，往往也需要涉及到紀錄的分類匯總與排序的問題。 如現在一個學校成績管理系統中，需要對學生的總分進行排序。 即先對學生各科成績進行匯總，然後顯示其排名為前50的紀錄。 此時就需要同時用到Group By子句和Limit子句。 其實從這個案例中我們也可以看出，這兩個子句相互依賴的特性。 正是因為這種特性(經常相互結合使用)，為此結合Group By子句可以提高Limit的查詢效率。

這主要是因為兩者如果一起使用的話，Limit關鍵字將不會再重複計算任何不必要的Group By的值。 換句話說，在某些情況下，Group By子句能夠通過順序來讀取鍵或者在鍵上做排序來解決分類匯總時的排序問題，然後再計算摘要直到關鍵字的值的改變為止。 如此的話，兩個子句所需要做的一些共同性的工作，只要做一次即可。 這就可以從另外一次角度用來提高應用系統的性能。 相比先做一個視圖對資料進行分類匯總的運算，再使用一個查詢語句來抽取特定數量的記錄，效率就要高一點。 因為後者是將兩個子句分開來使用，就無法享受到結合使用所體現的優勢。

建議三：使用SQL_calc_found_rows來提高子句的靈活性

預設情況下，Limit子句返回使用者所指定的記錄行數。 只要資料庫已經發送了使用者所需要的行數，則資料庫系統會放棄剩餘的查詢。 即上面這個學生成績的案例中，如果使用者只需要返回總分成績排名前50的學生，則資料庫只返回50條記錄，然後終止查詢作業。

但是在某些特定的情況下，使用者可能仍然需要繼續後續的查詢呢?如使用者出了查詢某些特定的記錄，還需要知道總的記錄數量，此時該如何處理?如現在使用者需要知道排名前50的學生資訊，同時需要知道總分在500分以上的總人數。 此時單獨使用Limit子句可能無法滿足使用者的需求，因為其只關心前面50條記錄。 如果要實現這個需求的話，往往需要結合SQL_calc_found_rows關鍵字。

這個關鍵字的主要用途就是能夠在查詢時為資料庫管理員事先準備好符合Where條件陳述式的記錄數目。 然後使用者只要在隨後執行一條Select Found_ROWS語句之後，就可以獲得符合條件的記錄總數。 不過需要注意的是，使用這個關鍵字會帶來一定的副作用。 即帶有這個關鍵字的查詢語句，是無法使用資料緩存的。 故在某些情況下會降低資料查詢的性能。 故一般情況下，這個關鍵字只用于Where條件陳述式比較複雜的情況。 當然這只是一個出於性能考慮的建議，而並不是技術上的限制。 即即使Where條件陳述式不復雜，也可以使用這個關鍵字，不會出現語法上的錯誤。 只是其在性能上並不是很理想。

建議四：與Distinct關鍵字共同使用時的特殊現象

Distinct關鍵字主要用來過濾重複的記錄。 而Limit關鍵字則主要用來指定記錄所返回的行數。 如果這兩個關鍵字共同使用時，會出現什麼樣的情況呢?如果從字面意思去理解，資料庫會返回指定的不重複的記錄數。 如Limit的參數為50，則資料庫返回50條不重複的記錄數。 然後後續的查詢就會停止。 如果查詢的記錄中有重複記錄，則資料庫查詢的實際數量往往要比Limit關鍵字所指定的數量要多。

在實際工作中，這條語句的作用還是很大的。 如現在有一張員工考勤資訊的表格。 現在資料庫管理員需要統計缺勤次數排名前20的員工人數。 此時為了防止有重複的記錄，就可以在查詢語句中加一個Distinct關鍵字，用來過濾重複的記錄數。 從而可以避免採用多個查詢語句來完成這個需求。

建議五：Limit與索引之間的關係

如果資料庫管理員決定使用Limit子句來指定需要顯示的記錄數，那麼最好能夠最大限度的使用索引，以避免全資料表掃描，提高工作效率。 即當資料庫選擇做完整的資料表掃描時，可以在某些情況下使用索引。

如現在資料庫管理員決定將Limit子句與Order BY子句一起使用。 資料庫一旦找到了排序結果的第一個RowCount行，則系統將會結束排序，而並不會對整個表進行排序。 如果單獨使用Order By子句的話，則會對整個表進行排序。 雖然如此，但是排序必定要浪費一定的時間。 此時資料庫管理員如果決定使用索引，則可以在很大程度上提高這個查詢的效率。

對於這個內容，筆者要強調一個問題。 如果必須要進行檔排序，則必須選擇所有匹配查詢，並且在確定已經找到第一個行之前，必須對他們的大部分內容進行了排序。 特別需要強調的是，在任何情況下，一旦找到了行，則就不需要再排序結果的其他部分，資料庫會自動結束排序。

可見Limit子句其本質的功能是限制使用者的紀錄數量。 但是其還有很多別的用途。 如快速判斷查詢語句的有效性、計算表所需要的空間等等。 不過其也有一定的副作用，可能會帶系統的運行帶來一些負面的影響。 此時最好能夠採取一些措施來提高系統運行的性能。