分頁優化的四種方式

轉自：http://www.orczhou.com/index.php/2009/03/four-way-pager-display/

很久以前讀了一篇關于分頁的文章，后來越想越有道理，最近又重新找出來，并做了翻譯，原文參考:Four ways to optimize paginated displays.

翻譯背景：在大數據量的情況下，原本很簡單的分頁如果沒有處理好，你會發現分頁的請求會消耗你大量的數據庫時間。如果你遇到了這個問題，文章給了你幾個很好的解決的方案。當然，初學者若能看完這篇文章，那么它會指導你寫出更具有擴展性的分頁代碼。

全文概述：文中提到了分頁的辦法總結如下：

1. 全部緩存查詢結果。把查詢結果全部緩存起來(例如文件緩存、靜態化結果頁面等)。
2. 不詳細顯示總共有多少分頁。這里有兩個優化的技巧。其一每次在計算總條目的時候，我就固定查詢501條，然后將前500條分頁顯示好，如果第501條確實存在，那么給出按鈕 “查看更多...”（這種情況會很少）。其二，在每次列表本頁面的時候，比如第一頁我要顯示1-20條，那么我查詢出1-21條。如果第21條真的存在，我就給出"下一頁"按鈕，依次類推。
   
   事實上google就是這樣做的。在查看第一頁搜索結果的時候google只會顯示前十頁(共100個條目)，并不顯示搜索結果條目總共有多少:
   
   查看第二頁的時候，僅僅會多顯示一頁
   
3. 通過EXPLAIN的"row"列來估算結果總共有多少條目。文章中稱google是這樣估算結果集的：

---

全文譯文：

?

在實際開發中，分頁顯示是我們最常遇到的優化問題之一。例如搜索結果、積分列表、排行榜等。分頁的一般模型:在一個排序的結果集合(較大)中我們要顯示其中連續20條目;并且需要顯示 “下一頁”、”上一頁”的鏈接;有時候我們還需要顯示，總共有多少個條目，一共分了多少頁。

要給出這樣一個完成顯示，數據庫的代價是很大的，有時候就為了顯示這么一個分頁，需要執行的SQL會比整個頁面顯示其他的全部SQL消耗還要大。
我曾遇到這樣的案例：有一次在為我們的一個客戶做Slow Query LOG分析的時候我們就發現:整個LOG 里面的SQL耗時6300s,其中兩個主要的分頁查詢大約消耗了(2850 + 380)秒,占了整個Slow Query的50%。
分頁沒有處理好就是這么糟糕~.

我們來分析一下分頁的一般情況:

#典型分頁的SQL如下:
SELECT .... FROM ... ORDER BY .... LIMIT X, 20

如果ORDER BY部分沒有能夠用索引的話（這樣的情況還是很多的），MYSQL就會做filesort,（注意這塊的filesort，請參看：http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/）；假想如果如果滿足WHERE 條件的條目共有個百萬的數量級的話，那么MYSQL就會取出這上百萬的結果，臨時存儲、文件排序，然后再刪除大大部分的數據保留其中的20個。當用戶點擊“下一頁”的時候，上面的過程會完全重做一遍，只是取得結果向后偏了一點。要是你還想顯示“總共有多少條目，共分多少頁面”的話，一般是這樣做(1)使用SQL_CALC_FOUND_ROWS?(2)執行一個單獨的SQL去計算行數。如果用戶的每一次請求都執行以上的操作，可以想象當你的數據量越來越大的時候，情況會越來越糟。

事實上，有很多辦法去優化上面的過程的。(關于這一點我之前我寫過的一篇article on optimizing ranked data 。不過那篇文章里面介紹的辦法實施起來比較困難。所以如果不是情況復雜和重要到一定程度，就不值得那樣做。)那一般情況怎么辦呢？除了索引、重組數據、SQL優化,我們還有兩個大的方面可以考慮去做。其一，積極的把SQL的查詢結果緩存起來，從而減少SQL執行;其二就是重新考慮一下你的分頁就架構，在應用中，并不是每次都需要把分頁的各個部分都完整顯示出來的。例如你把從第1到50頁的鏈接都給出來，很多時候用戶根本不會直接去點擊某一頁。我們考慮的思路是指把最重要的部分先展示出來。

這樣考慮的于是就有了下面四個優化的建議來提高性能

1. 首次查詢的時候緩存結果。這樣情況就變得簡單了，無論是結果條目的數量，總共的頁面數量，還是取出其中的部分條目。
2. 不顯示總共有多少條目。Google搜索結果的分頁顯示就用了這個特性。很多時候你可能看了前幾頁，就夠了。那么我可以這樣，每次我都把結果限制在500條（這個數據越大 資源消耗越大）然后你每次查詢的時候，都查詢501條記錄，這樣，如果結果真有501個，那么我們就顯示鏈接 “顯示下500條記錄”。
3. 不顯示總頁面數。只給出“下一頁”的鏈接，如果有下一頁的話。(如果用戶想看上一頁的話，他會通過瀏覽器來回到上一頁的)。那你可能會問我“不顯示總頁面數”怎么知道是不是有下一頁呢？這里有一個很好的小技巧：你在每次顯示你當前頁面條目的時候你都多查詢一條，例如你要顯示第11-20個條目時，你就取出11-21條記錄（多取一條，并不顯示這多取的內容），那么當你發現第21條存在的時候就顯示“下一頁的鏈接”，否則就是末頁了。這樣你就不用每次計算總頁面數量了，特別是在做緩存很困難的時候這樣做效率非常好。
4. 估算總結果數。Google就是這么做的，事實證明效果很好。用EXPLAIN 來解釋你的SQL，然后通過EXPLAIN的結果來估算。EXPLAIN結果有一列”row”會給你一個大概的結果。(這個辦法不是處處都行，但是某些地方效果是很好的)這些辦法可以很大程度上減輕數據庫的壓力，而且對用戶體驗不會有什么影響。

這些辦法可以很大程度上減輕數據庫的壓力，而且對用戶體驗不會有什么影響。