V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
decdfgfe
V2EX  ›  数据库

该段 Sql 分页代码能够优化吗?

  •  
  •   decdfgfe · 2015-11-19 09:05:58 +08:00 · 1760 次点击
    这是一个创建于 3290 天前的主题,其中的信息可能已经有所发展或是发生改变。

    代码如下
    $sql="select id,second_level,third_level,title,description,update_time,thumb from p_ed2k where second_level='$cid' and third_level='$scid' order by id DESC limit $offset,$perNum";
    分页越到后面越慢, second_level 和 third_level 已经做索引

    31 条回复    2015-11-20 11:09:41 +08:00
    xiamingchong
        1
    xiamingchong  
       2015-11-19 09:08:10 +08:00
    没有表结构怎么看,有联合索引么
    decdfgfe
        2
    decdfgfe  
    OP
       2015-11-19 09:11:36 +08:00
    @xiamingchong
    表结构如下
    +--------------+---------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +--------------+---------------+------+-----+---------+----------------+
    | id | int(7) | NO | PRI | NULL | auto_increment |
    | page_id | int(7) | NO | | NULL | |
    | page_url | varchar(50) | NO | | NULL | |
    | second_level | varchar(50) | YES | MUL | NULL | |
    | third_level | varchar(50) | YES | MUL | NULL | |
    | title | varchar(500) | NO | | NULL | |
    | resource | longtext | NO | | NULL | |
    | description | text | YES | | NULL | |
    | picture | varchar(1000) | YES | | NULL | |
    | label | varchar(100) | YES | MUL | NULL | |
    | size | varchar(20) | YES | | NULL | |
    | read_count | int(11) | YES | | NULL | |
    | is_finish | varchar(1) | YES | | NULL | |
    | create_time | int(11) | YES | | NULL | |
    | update_time | int(11) | YES | MUL | NULL | |
    | resource_num | int(5) | YES | | NULL | |
    | cover | varchar(100) | YES | | NULL | |
    | thumb | varchar(100) | YES | | NULL | |
    | status | tinyint(1) | NO | | 1 | |
    | s_title | varchar(100) | YES | | NULL | |
    | format | varchar(10) | YES | | NULL | |
    +--------------+---------------+------+-----+---------+----------------+
    msg7086
        3
    msg7086  
       2015-11-19 09:14:11 +08:00
    MySQL 分页本来就慢。先查出 id 列表然后用 id 去查记录吧。
    loveyu
        4
    loveyu  
       2015-11-19 09:15:30 +08:00
    数据量多大
    xiamingchong
        5
    xiamingchong  
       2015-11-19 09:16:07 +08:00
    @decdfgfe 最好是发创建表结构的语句,
    还有多少数据就发现慢了?
    msg7086
        6
    msg7086  
       2015-11-19 09:18:14 +08:00
    Rails 代码供参考

    # MySQL hacks to improve pagination performance
    @postids = Post.where(:category_id => @cat_id).paginate(:page => params[:page], :per_page => 15).order('id DESC').select('id').to_a;
    @posts = Post.where(:id => @postids).order('id DESC');

    比直接分页查数据快得多。
    sheng9632
        7
    sheng9632  
       2015-11-19 09:18:41 +08:00
    where id>$offset limit $pernum
    decdfgfe
        8
    decdfgfe  
    OP
       2015-11-19 09:19:28 +08:00
    @loveyu 数据量只有 50 几 W 条。
    inmyfree
        9
    inmyfree  
       2015-11-19 09:19:39 +08:00
    我数据库比较挫,目前只想到两个东东,
    1 、使用存储过程,可能会有一定的提升效果,但是可能不太明显
    2 、应用层次做查找结果缓存,这样可以大大提升速度,缺点是数据更新可能会不及时
    decdfgfe
        10
    decdfgfe  
    OP
       2015-11-19 09:20:42 +08:00
    @xiamingchong 在程序里面翻页时会很慢。
    在 Mysql 里面 desc 执行时间却很快,本身服务器性能应该不会有这么大差别吧。
    thinkmore
        11
    thinkmore  
       2015-11-19 09:51:25 +08:00
    数据量大,翻页到中间就比较慢了
    hpeng
        12
    hpeng  
       2015-11-19 09:55:50 +08:00
    既然 id 都排序了.你就记录上次查询的最后 id 嘛...然后就 id>lastId (个人理解
    zyue
        13
    zyue  
       2015-11-19 09:59:43 +08:00
    如果出现慢查询 econd_level 和 third_level 考虑加下索引
    realpg
        14
    realpg  
       2015-11-19 10:03:17 +08:00
    mysql limit 的 offset 比较大的情况下就会超慢……
    自行用别的办法
    superbear
        15
    superbear  
       2015-11-19 10:08:10 +08:00
    @hpeng 正解,顺序翻页好使,但是一下翻到第 N 页就不太好弄。
    superbear
        16
    superbear  
       2015-11-19 10:14:56 +08:00
    可以查查 mysql 语句执行顺序, limit 是最后一步执行的
    raysmond
        17
    raysmond  
       2015-11-19 10:22:02 +08:00
    建议把所有 id 放在内存中,可以用 redis/memcached ,分页的时候从内存中获取该页的所有 id ,然后再去数据库中查询
    hpeng
        18
    hpeng  
       2015-11-19 10:22:26 +08:00 via iPhone
    @superbear 对噢。跳页就不行了。看来是个不好的方法
    wmttom
        19
    wmttom  
       2015-11-19 10:35:00 +08:00
    percona 推荐的做法是查 id 分页,然后用查出来的主键 id join 本表来获取其他字段数据。
    sujin190
        20
    sujin190  
       2015-11-19 10:39:12 +08:00
    翻页数据库在做操作时是数的,数据量大了肯定慢, explain 看啊
    akira
        21
    akira  
       2015-11-19 10:40:44 +08:00
    分页是不需要精确的。
    如果 id 是顺序并且连续的话,可以先获得 maxid ,然后通过简单计算,获得对应分页的起始 id 和结束 id 。
    pythoner
        22
    pythoner  
       2015-11-19 10:48:31 +08:00
    对分页的优化基本就是 @raysmond 这个思路,我个人比较推荐用 redis 来搞,用 list 可以很方便地将记录 id 和页数对应起来。

    再极端一点,把列表页中需要显示的字段也通通放到 redis 里,再用 lua 写一个方法,根据页数 id 的对应关系,一次性将当前页的数据拼好取出来。

    再极端一点,用 nginx_lua 实现上一步。
    scalai
        23
    scalai  
       2015-11-19 10:54:46 +08:00
    不知道这样行不:
    建这个索引 second_level , third_level , id, title, description, update_time, thumb
    或 second_level , third_level , id

    刚看了 数据库索引设计与优化 这本书,没有实操过。。。
    raysmond
        24
    raysmond  
       2015-11-19 10:58:10 +08:00
    @pythoner 对啊,基本上你说的第一条就够用, redis 的 range 这些方法太好用了。至于后面两个,除非有高要求,量大,一般不用。量不是很大的,直接把一个对象扔进 redis 里去,懒得拼对象了。
    scalai
        25
    scalai  
       2015-11-19 11:14:00 +08:00
    @scalai 话说, second_level 和 third_level 看名字像是重复率很高。
    hgc81538
        26
    hgc81538  
       2015-11-19 13:04:21 +08:00 via iPhone
    是 second_level 做一個索引, third_level 做一個索引,共兩索引?
    還是 second_level 和 third_level 做同一個索引?

    還有你的 SQL 有注入的問題,小心
    iyaozhen
        27
    iyaozhen  
       2015-11-19 13:36:20 +08:00
    涨见识了,以前就觉得翻页这个事情好蛋疼。每次算总页数还要 count(*)
    wawehi
        28
    wawehi  
       2015-11-19 13:42:19 +08:00
    就这段 SQL ,要想优化得快,还得上缓存,推荐 redis , list, zhash ,结合起来用,非常高效
    otakustay
        29
    otakustay  
       2015-11-19 15:50:02 +08:00
    最好的办法就是把用于查询的字段再单独弄张表,从这个表拿到 id ,再用 in 通过 id 去拿数据……
    xiamingchong
        30
    xiamingchong  
       2015-11-20 09:22:20 +08:00
    @otakustay 才这么点数据没必要的
    otakustay
        31
    otakustay  
       2015-11-20 11:09:41 +08:00
    @xiamingchong 不仅仅是数据, avg row size 也有很大的影响
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1314 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 17:49 · PVG 01:49 · LAX 09:49 · JFK 12:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.