分頁顯示 - MySQL分頁查詢，是用LIMIT m,n，還是先查出所有ID再在前端分頁？
問題描述
用傳統的LIMIT m, n做分頁查詢需要這么幾步：
用SELECT COUNT(*) FROM table WHERE condition ORDER BY ...查到總數并算出有多少頁；
用SELECT columns FROM table WHERE condition ORDER BY ... LIMIT 0, 100顯示第一頁(假設每頁有100行)，如果用SQL_CALC_FOUND_ROWS這個參數的話，可以跟前一條合并成一條SQL；
點“下一頁”時，用SELECT columns FROM table WHERE condition ORDER BY ... LIMIT 100, 100查；
...
這樣做往往花費很大，因為WHERE condition有可能是全表掃描。如果MySQL沒開緩存的話，每翻一頁可能非常慢。
因此我就用一種新的辦法：
用SELECT id FROM table WHERE condition ORDER BY ...得到所有相符的ID，如果數據量太大(比如表中有1,000,000行)，我們就限制一下行數(比如限制最多查10,000，就用LIMIT 10000)，于是這些ID就通過動態頁面或Ajax(以JS代碼或JSON的形式)被傳到了前端；
前端JS選取前100個ID作為第一頁，發送一個帶這100個ID的查詢請求，后端其實處理SELECT columns FROM table WHERE id IN (id_0, id_1, ..., id_99)這么一個查詢；
點“下一頁”時，查詢是SELECT columns FROM table WHERE id IN (id_100, id_101, ..., id_199)；
...
這種方法只需要做一次條件查詢(慢)，列表數據其實都是主鍵查詢(快)。
我在一個業余項目中用了這個辦法，詳見：(http://) www.chess-wizard.com/base/ (第一頁數據被寫在JSP頁面里，有利于SEO).
我要求團隊成員都用這種方式來處理分頁，他們卻并不認同 :-(
難道LIMIT m, n是分頁查詢的標準做法或唯一途徑嗎？
問題解答
回答1：一種id>$id limit $limit;傳遞參數$offset,$limit=100;
第一頁:$offset = 0
select id ,name from table order by id limit $limit;
第二頁:$offset為第一頁返回的id
select id ,name from table where id>$offset order by id limit $limit;
回答2：分頁比較慢的情況，主要是第一步慢（取出符合條件記錄、排序、選擇當前頁的行），你說的方法在這一步并沒有改進。
另外一種情況，第一步取符合條件的記錄是可以使用少量的表，但取明細行數據需要關聯其他多張表，這時候如果數據庫選擇的執行計劃不對，也會很慢。這個時候可以采用@abul的方法，先從小表取出符合條件的記錄id，然后再關聯其他表。
注意這些處理都是在數據庫內部完成，不需要向前端傳遞數據，主要有幾個原因：1、如果符合條件的結果集數據量很大，數據庫全部查詢出ID和跨網絡傳輸代價很大，你說的最多限制10000條不一定能滿足所有的場景。2、很多時候用戶只會看前幾頁的內容，一次取出所有ID的消耗其實是浪費了。3、如果在第一次和第二次查詢中間，數據發生了變化，用戶得到的結果集是不準確的，需要根據對查詢結果的精確性要求判斷是否可行。另外，如果查詢出的數據公用性較高，可以考慮放到redis類似的緩存中，降低系統的整體負載，只放在前端的話感覺重用率太低了。
如果非要說一個絕對正確的原則，其實是正確的廢話：根據業務場景需求和各方案的優缺點做判斷和取舍。
回答3：1.mysql 為什么不開緩存呢
2.前端使用同步還是異步獲取頁面內容？如果是同步的，那么你的方式無法滿足前端的需求;如果是異步的，你的查詢方式 初次查詢獲取全部符合條件的id(假設先獲取了10000條)，這10000個id如何讓前端獲取？假如都放頁面上，前端js可以直接使用這個數組來發起異步請求，但是如果跳轉頁碼超出了這個范圍怎么辦，前端肯定還需要請求頁碼id，這時候你的where查詢還是要用的
所以這個方案目前看效果不是那么明顯。我也不知道我分析的對不對，你做個參考吧。
回答4：既然你的思路已經是前端做ID的緩存了，為什么不直接把ID以外的字段也一并在前端緩存了呢
比如你要取前10頁數據，每頁50條，那SQL語句就用LIMIT 500取前500條，在這10頁之內翻頁就不需要任何請求；直到翻下一頁的時候，再用LIMIT 500,500的SQL語句去取后500條
回答5：是否可以嘗試兩者結合？limit的時候只是取出id，具體字段再關聯出來。
SELECT columns FROM table t1 inner join (SELECT id FROM table WHERE condition ORDER BY ... LIMIT m, n)t2 on t1.id=t2.id回答6：
這個真的要針對項目來看的：
1、如果數據量大且只有一個唯一索引ID，那用你后面提出的方法肯定是最快的（當然條數不能太多）
2、如果有其它字段做了索引且百分百該字段必須作為條件，當然是用普通的 ORDER BY ... LIMIT m, n 分頁查詢就很快
3、關于是否使用緩存，也是看應用場景，如果你這個查詢不牽扯太多where條件，且數據不是實時更新，這是可以用的
網公網安備