liuhaotian
V2EX  ›  问与答

php+mysql 如何优雅的分页

  •  
  •   liuhaotian · Mar 1, 2015 · 3422 views
    This topic created in 4282 days ago, the information mentioned may be changed or developed.

    现在使用的是一个对 MyISAM 表的 SELECT COUNT(*) 获取总数 total,然后除以每页数量 y 之后得到总页数,对于请求的页面 x 计算出到第 x 页的文章数,然后构建子查询(伪代码):

    select * from ? where id>(select id from ? order by id limit x*y,1) limit y
    

    上面算是一个伪代码,大概就是这个思路,不知道有没有优雅一点的分页的方式?想要自己把这个弄清楚。如果有好的思路/轮子,万分感谢!

    16 replies  •  2015-03-01 21:02:58 +08:00
    mcfog
    mcfog
    Mar 1, 2015 via Android
    1
    没看懂这里的子查询有啥好处…直接一个limit不就好了?
    laoyuan
    laoyuan
    Mar 1, 2015❤️1
    2
    limit (x - 1) * y, y
    liuhaotian
    liuhaotian
    OP
    Mar 1, 2015❤️1
    3
    @laoyuan @mcfog 我脑残了。。。说的极是
    coolwind
    coolwind
    Mar 1, 2015
    4
    用一个limit吧
    select * from ? where id in (select id from ? order by id limit (x-1)*y,y)
    liuhaotian
    liuhaotian
    OP
    Mar 1, 2015❤️1
    5
    @coolwind @laoyuan
    问一下这样子的两个效率哪个高?
    是不是直接limit会全表
    coolwind
    coolwind
    Mar 1, 2015❤️2
    6
    数据量大的话 select * from ? where id in (select id from ? order by id limit (x-1)*y,y) 效率高

    小的话,直接limit要高点
    frankzeng
    frankzeng
    Mar 1, 2015
    7
    jquery datatable可以前端进行分页,就是第一次进的时候比较慢一点。
    coolwind
    coolwind
    Mar 1, 2015
    8
    数据越大,取越后面的记录,差异越大(mysql 5.1不支持上面的带limit的子查询,可以改为innder join)

    select count(*) from piwigo_history;
    +----------+
    | count(*) |
    +----------+
    | 6783731 |
    +----------+
    1 row in set (0.00 sec)

    mysql> select a.* from piwigo_history as a inner join (select id from piwigo_history order by id limit 6783400,10) as b on a.id=b.id order by a.id;
    10 rows in set (1.24 sec)

    mysql> select a.* from piwigo_history as a inner join (select id from piwigo_history order by id limit 6783410,10) as b on a.id=b.id order by a.id;
    10 rows in set (1.32 sec)

    mysql> select a.* from piwigo_history as a inner join (select id from piwigo_history order by id limit 6783420,10) as b on a.id=b.id order by a.id;
    10 rows in set (1.34 sec)


    直接读取
    mysql> select * from piwigo_history order by id limit 6783400,10;
    10 rows in set (12.76 sec)

    mysql> select * from piwigo_history order by id limit 6783410,10;
    10 rows in set (14.33 sec)

    mysql> select * from piwigo_history order by id limit 6783420,10;
    10 rows in set (13.13 sec)
    coolwind
    coolwind
    Mar 1, 2015
    9
    您这种方式,速度也不错
    mysql> select * from piwigo_history where id>=(select id from piwigo_history order by id limit 6783400,1) order by id limit 10;
    10 rows in set (1.25 sec)

    mysql> select * from piwigo_history where id>=(select id from piwigo_history order by id limit 6783410,1) order by id limit 10;
    10 rows in set (1.33 sec)

    mysql> select * from piwigo_history where id>=(select id from piwigo_history order by id limit 6783420,1) order by id limit 10;
    10 rows in set (1.37 sec)
    liuhaotian
    liuhaotian
    OP
    Mar 1, 2015❤️1
    10
    @coolwind 实际上我感觉我自己用的跟你的基本是类似的。直接limit的话跟我们是不一样的。
    时间上差了1个数量级,是因为直接limit会全表?
    tabris17
    tabris17
    Mar 1, 2015
    11
    万一不按id排序岂不是傻眼了
    laoyuan
    laoyuan
    Mar 1, 2015
    12
    所以还是用in好
    cevincheung
    cevincheung
    Mar 1, 2015
    13
    换PGSQL大法保平安
    coolwind
    coolwind
    Mar 1, 2015
    14
    没有用到索引,确实是全表扫描,然后用文件排序(这种速度很慢)
    mysql> explain select * from piwigo_history order by id limit 100000,10;
    +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------+
    | 1 | SIMPLE | piwigo_history | ALL | NULL | NULL | NULL | NULL | 6784937 | Using filesort |
    +----+-------------+----------------+------+---------------+------+---------+------+---------+----------------+
    1 row in set (0.00 sec)

    mysql> select @@version;
    +------------+
    | @@version |
    +------------+
    | 5.1.73-log |
    +------------+
    1 row in set (0.00 sec)

    当然,如果您不需要排序的话,直接limit速度还是不错
    mysql> select * from piwigo_history limit 5000000,10;
    10 rows in set (1.50 sec)

    mysql> select * from piwigo_history limit 6000000,10;

    10 rows in set (1.84 sec)
    liuhaotian
    liuhaotian
    OP
    Mar 1, 2015❤️1
    15
    @coolwind 我试试看啊谢谢 确实没有必要排序
    otakustay
    otakustay
    Mar 1, 2015
    16
    数据量大的时候,in会比较快,同时还要看avg row size,avg row size越大,用limit查询越慢,如果有几个text字段的话会慢到不忍直视
    比较快的方法是另建一张表,只存id,先从id表里取到id,再用in去找完整的数据
    id表也可以转为Mongo等更适合此类场景的存储,甚至在系统启动时丢进内存(做好同步)
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   2878 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 52ms · UTC 04:15 · PVG 12:15 · LAX 20:15 · JFK 23:15
    ♥ Do have faith in what you're doing.