MySQL分页查询,是用LIMIT m,n,还是先查出所有ID再在前端分页?

用传统的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是分页查询的标准做法唯一途径吗?

阅读 5.6k
评论 更新于 2017-03-09
    6 个回答

    一种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;

    评论 赞赏 2017-03-09

      分页比较慢的情况,主要是第一步慢(取出符合条件记录、排序、选择当前页的行),你说的方法在这一步并没有改进。

      另外一种情况,第一步取符合条件的记录是可以使用少量的表,但取明细行数据需要关联其他多张表,这时候如果数据库选择的执行计划不对,也会很慢。这个时候可以采用@abul的方法,先从小表取出符合条件的记录id,然后再关联其他表。

      注意这些处理都是在数据库内部完成,不需要向前端传递数据,主要有几个原因:
      1、如果符合条件的结果集数据量很大,数据库全部查询出ID和跨网络传输代价很大,你说的最多限制10000条不一定能满足所有的场景。
      2、很多时候用户只会看前几页的内容,一次取出所有ID的消耗其实是浪费了。
      3、如果在第一次和第二次查询中间,数据发生了变化,用户得到的结果集是不准确的,需要根据对查询结果的精确性要求判断是否可行。
      另外,如果查询出的数据公用性较高,可以考虑放到redis类似的缓存中,降低系统的整体负载,只放在前端的话感觉重用率太低了。

      如果非要说一个绝对正确的原则,其实是正确的废话:根据业务场景需求和各方案的优缺点做判断和取舍。

      评论 赞赏 2017-03-10

        1.mysql 为什么不开缓存呢

        2.前端使用同步还是异步获取页面内容?
        如果是同步的,那么你的方式无法满足前端的需求;
        如果是异步的,你的查询方式 初次查询获取全部符合条件的id(假设先获取了10000条),这10000个id如何让前端获取?假如都放页面上,前端js可以直接使用这个数组来发起异步请求,但是如果跳转页码超出了这个范围怎么办,前端肯定还需要请求页码id,这时候你的where查询还是要用的

        所以这个方案目前看效果不是那么明显。
        我也不知道我分析的对不对,你做个参考吧。

        评论 赞赏 2017-03-09

          既然你的思路已经是前端做ID的缓存了,为什么不直接把ID以外的字段也一并在前端缓存了呢

          比如你要取前10页数据,每页50条,那SQL语句就用LIMIT 500取前500条,在这10页之内翻页就不需要任何请求;直到翻下一页的时候,再用LIMIT 500,500的SQL语句去取后500条

          评论 赞赏 2017-03-09
            abul
            • 977

            是否可以尝试两者结合?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
            评论 赞赏 2017-03-09

              这个真的要针对项目来看的:

              1、如果数据量大且只有一个唯一索引ID,那用你后面提出的方法肯定是最快的(当然条数不能太多)

              2、如果有其它字段做了索引且百分百该字段必须作为条件,当然是用普通的 ORDER BY ... LIMIT m, n 分页查询就很快

              3、关于是否使用缓存,也是看应用场景,如果你这个查询不牵扯太多where条件,且数据不是实时更新,这是可以用的

              评论 赞赏 2017-03-10
                撰写回答

                登录后参与交流、获取后续更新提醒