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;

第一页:$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条件，且数据不是实时更新，这是可以用的