V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
aries910
V2EX  ›  问与答

limit mysql 取最后_分页场景(limit,offset)特别慢,有什么办法可以优化?

  •  
  •   aries910 · 353 天前 · 1961 次点击
    这是一个创建于 353 天前的主题,其中的信息可能已经有所发展或是发生改变。

    目前的场景就是 单表大概 20 万左右的数据,需要分页,排序(浏览量、新增时间,点赞等等) laravel 生成的 sql select id, logo, title, supports, collections, created_at, views, user_id, summary from table where status > 0 order by supports desc, created_at desc limit 20 offset 164920;

    因为 offset 就执行的很慢 求大牛给点解决方案加速下,最好就是通过修改 sql 的方式

    PS:机器是单核小主机。。。

    39 条回复    2021-06-09 09:38:40 +08:00
    loophole12
        1
    loophole12  
       353 天前 via Android   ❤️ 1
    可以先把拉取的 id 都取出来,再根据 id 用 in 拉取整行数据
    sheeta
        2
    sheeta  
       353 天前   ❤️ 3
    取上次分页最后的结果 id, > id limit 20 。缺点,不能跳到指定的页。
    ksedz
        3
    ksedz  
       353 天前
    加索引试试
    emeab
        4
    emeab  
       353 天前
    Select * From table_name Where id in (Select id From table_name where status > 0) order by supports desc, created_at desc limit 20 offset 164920;
    emeab
        5
    emeab  
       353 天前
    不过 20 万就干趴下 看看慢日志
    aries910
        6
    aries910  
    OP
       353 天前
    @emeab 上面的 sql 就是从慢日志里找出来的啊。。。
    还有你上面的 sql 一样慢,没用
    aries910
        7
    aries910  
    OP
       353 天前
    @sheeta 感谢提供思路,但是指定页的功能还是需要的
    aries910
        8
    aries910  
    OP
       353 天前
    @loophole12 试过了,没啥用
    aries910
        9
    aries910  
    OP
       353 天前
    @ksedz 上面的 status 和 supports 都加了单独的索引,没啥用给,我看网上有人说 mysql 查询的时候,where 和 order 只会用一个索引,所以就算都有索引也没用,我去尝试了也确实没用(我不太考虑复合索引,毕竟小机器,怕后面数据更大了更新索引占资源)
    emeab
        10
    emeab  
       353 天前
    @aries910 SELECT * FROM table INNER JOIN( SELECT id FROM table status > 0 order by supports desc, created_at desc LIMIT 10000,100 ) b USING(id) 试下? 我这边单表快 40 万, 也就 0.1 秒
    Jooooooooo
        11
    Jooooooooo  
       353 天前
    大数分页是无解问题

    一个妥协的方案是用 id 当游标
    dqzcwxb
        12
    dqzcwxb  
       353 天前
    @sheeta #2 正解
    aries910
        13
    aries910  
    OP
       353 天前
    @emeab 感谢再次回复,这边不能上传图片,我这边执行了还是十几秒
    emeab
        14
    emeab  
       353 天前
    @aries910 我这边二次执行就快了.
    emeab
        15
    emeab  
       353 天前
    SELECT *
    FROM table AS t1
    JOIN (SELECT id FROM table ORDER BY id desc LIMIT 500000, 1) AS t2WHERE t1.id = t2.id ORDER BY t1.id desc LIMIT 2;
    emeab
        16
    emeab  
       353 天前
    这个也行. 因为没完整的表 只能这样了.
    aries910
        17
    aries910  
    OP
       353 天前
    @emeab
    没用,只能借用新浪的图床了
    cnoder
        18
    cnoder  
       353 天前
    楼上方法叫 延迟关联
    notejava
        19
    notejava  
       353 天前
    思路拓宽一点,从查询流程上优化,例如提前然用户填筛选条件筛选掉部分数据,再做分页。
    emeab
        20
    emeab  
       353 天前
    @aries910 explain 看下索引
    lostvincent
        21
    lostvincent  
       353 天前
    相关知识 https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
    #1 方法是可以的,你还是慢可能是哪里没写对

    步骤 1: select id where {condition};
    步骤 2: select * where id in (步骤 1 查出来的);

    最关键是步骤 1 只 select id,顺带 where 相关的字段的索引安排
    aries910
        22
    aries910  
    OP
       353 天前
    emeab
        23
    emeab  
       353 天前
    @aries910 我这边 500 万单表 都没问题. 1s 多.
    emeab
        24
    emeab  
       353 天前
    我半桶水解决不了. 等大牛把.
    JasonLaw
        25
    JasonLaw  
       353 天前
    还是在附言上添加一下 expalin 的结果吧。没有足够的信息,所有人都是靠猜的。
    Amit
        26
    Amit  
       353 天前
    楼上先查出 id 再关联查询数据应该是最好的办法了,limit 写在子查询中只查 id 查的是索引没有回表,小主机应该也是没问题的啊,楼主要不要再检查下
    mikeguan
        27
    mikeguan  
       353 天前 via Android
    取消分页吧。
    我们最终采用分页时最多读取 1 万条数据
    数据量大了,分页真的扛不住
    napsterwu
        28
    napsterwu  
       353 天前 via iPhone
    因为如果有 20w 数据,offset 19w,也会遍历 19w 行。只能是用 id 去过滤
    zpfhbyx
        29
    zpfhbyx  
       353 天前
    #2 正解
    calpes
        30
    calpes  
       353 天前
    这楼里一群人干哈呢,1 楼就是完美解决方案了,查到后边 offset 大了慢是因为你表里有 text,先查 id 再取其他字段就 ok 了
    calpes
        31
    calpes  
       353 天前
    @emeab 二次执行就快了可还行,查完一次就有缓存了
    emeab
        32
    emeab  
       353 天前
    @calpes 所以我说我是半桶水啊 XD
    dawniii
        33
    dawniii  
       353 天前
    楼上很多说先取 id 的,只取 id 的话,是不用 offset 条件了吗?慢的原因不就是 offset 的值太大了,问题不还是存在吗?
    ebingtel
        34
    ebingtel  
       352 天前
    @emeab 有的时候 不一定是 offset 慢,先看看把 orderby 去掉,是不是快了……如果是 orderby 的原因,相关字段加上索引……如果没走索引,再用 FORCE INDEX
    aries910
        35
    aries910  
    OP
       352 天前
    @emeab 不管问题有没有解决,也还是感谢你的建议 :),至少看的来你是真的帮忙去实践了,谢谢!
    aries910
        36
    aries910  
    OP
       352 天前
    @ebingtel 确实是的,上面有很多小伙伴提议先用 id 取区间,再 select 完整数据
    这种方法时好时坏,我替换了 orderby 后也会变快
    关键查资料的时候看到个说法 :
    where status=1 orderby views desc
    这种情况这种会用一个索引,就算 status 和 views 都索引了,也并没啥用
    求确认
    xiaochong0302
        37
    xiaochong0302  
       352 天前
    只能看前 N 页就好了,简单粗暴

    ```
    public function getPage()
    {
    $page = $this->request->getQuery('page', ['trim', 'int'], 1);

    return $page > 100 ? 100 : $page;
    }

    public function getLimit()
    {
    $limit = $this->request->getQuery('limit', ['trim', 'int'], 12);

    return $limit > 100 ? 100 : $limit;
    }
    ```
    ebingtel
        38
    ebingtel  
       351 天前
    @aries910 如果是 orderby 多个字段,可以建立一个复合索引,这样才会快……如果没有,就这用用 FORCE INDEX
    ebingtel
        39
    ebingtel  
       351 天前
    @ebingtel (建立复合索引的基础上)就接着用 FORCE INDEX
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1084 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 21:47 · PVG 05:47 · LAX 14:47 · JFK 17:47
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.