分页显示 - MySQL分页查询，是用LIMIT m,n，还是先查出所有ID再在前端分页？

Question

用传统的LIMIT m, n做分页查询需要这么几步：

1. 用SELECT COUNT(*) FROM table WHERE condition ORDER BY ...查到总数并算出有多少页；

2. 用SELECT columns FROM table WHERE condition ORDER BY ... LIMIT 0, 100显示第一页(假设每页有100行)，如果用SQL_CALC_FOUND_ROWS这个参数的话，可以跟前一条合并成一条SQL；

3. 点“下一页”时，用SELECT columns FROM table WHERE condition ORDER BY ... LIMIT 100, 100查；

4. ...

这样做往往花费很大，因为WHERE condition有可能是全表扫描。如果MySQL没开缓存的话，每翻一页可能非常慢。

因此我就用一种新的办法：

1. 用SELECT id FROM table WHERE condition ORDER BY ...得到所有相符的ID，如果数据量太大(比如表中有1,000,000行)，我们就限制一下行数(比如限制最多查10,000，就用LIMIT 10000)，于是这些ID就通过动态页面或Ajax(以JS代码或JSON的形式)被传到了前端；

2. 前端JS选取前100个ID作为第一页，发送一个带这100个ID的查询请求，后端其实处理SELECT columns FROM table WHERE id IN (id_0, id_1, ..., id_99)这么一个查询；

3. 点“下一页”时，查询是SELECT columns FROM table WHERE id IN (id_100, id_101, ..., id_199)；

4. ...

这种方法只需要做一次条件查询(慢)，列表数据其实都是主键查询(快)。

我在一个业余项目中用了这个办法，详见：(http://) www.chess-wizard.com/base/ (第一页数据被写在JSP页面里，有利于SEO).

我要求团队成员都用这种方式来处理分页，他们却并不认同 :-(

难道LIMIT m, n是分页查询的标准做法或唯一途径吗？

Answer 1

一種id>$id limit $limit;傳遞參數$offset,$limit=100;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;

第一頁:$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;🎜

Answer 2

分頁比較慢的情況，主要是第一步慢（取出符合條件記錄、排序、選擇當前頁的行），你說的方法在這一步驟並沒有改進。

另一種情況，第一步取符合條件的記錄是可以使用少量的表，但取明細行資料需要關聯其他多張表，這時候如果資料庫選擇的執行計劃不對，也會很慢。這時候可以採用@abul的方法，先從小表取出符合條件的記錄id，然後再關聯其他表。

注意這些處理都是在資料庫內部完成，不需要向前端傳遞數據，主要有幾個原因：
1、如果符合條件的結果集數據量很大，數據庫全部查詢出ID和跨網絡傳輸代價很大，你說的最多限制10000條不一定能滿足所有的場景。
2、很多時候使用者只會看前幾頁的內容，一次取出所有ID的消耗其實是浪費了。
3、如果在第一次和第二次查詢中間，資料發生了變化，使用者得到的結果集是不準確的，需要根據對查詢結果的精確性要求判斷是否可行。
另外，如果查詢出的資料公用性較高，可以考慮放到redis類似的快取中，降低系統的整體負載，只放在前端的話感覺重用率太低了。

如果非要說一個絕對正確的原則，其實是正確的廢話：根據業務場景需求和各方案的優缺點做判斷和取捨。

Answer 3

1.mysql 為什麼不開快取呢

2.前端使用同步還是非同步取得頁面內容？
如果是同步的，那麼你的方式無法滿足前端的需求;
如果是異步的，你的查詢方式初次查詢獲取全部符合條件的id(假設先獲取了10000條)，這10000個id如何讓前端獲取？假如都放頁面上，前端js可以直接使用這個數組來發起異步請求，但是如果跳轉頁碼超出了這個範圍怎麼辦，前端肯定還需要請求頁碼id，這時候你的where查詢還是要用的

所以這個方案目前看效果不是那麼明顯。
我也不知道我分析的對不對，你做個參考吧。

Answer 4

既然你的思路已經是前端做ID的快取了，為什麼不直接把ID以外的欄位也一併在前端快取了呢

例如你要取前10頁數據，每頁50條，那SQL語句就用LIMIT 500取前500條，在這10頁之內翻頁就不需要任何請求；直到翻下一頁的時候，再用LIMIT 500,500的SQL語句去取後500條

Answer 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

Answer 6

這個真的要針對專案來看的：

1、如果資料量大且只有一個唯一索引ID，那麼用你後面提出的方法肯定是最快的（當然條數不能太多）

2、如果有其它字段做了索引且百分百該字段必須作為條件，當然是用普通的 ORDER BY ... LIMIT m, n 分頁查詢就很快

3、關於是否使用緩存，也是看應用場景，如果你這個查詢不牽扯太多where條件，且資料不是即時更新，這是可以用的