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

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

  •  
  •   aries910 · 2021-06-07 14:19:21 +08:00 · 2970 次点击
    这是一个创建于 1266 天前的主题,其中的信息可能已经有所发展或是发生改变。

    目前的场景就是 单表大概 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  
       2021-06-07 14:24:34 +08:00 via Android   ❤️ 1
    可以先把拉取的 id 都取出来,再根据 id 用 in 拉取整行数据
    sheeta
        2
    sheeta  
       2021-06-07 14:39:24 +08:00   ❤️ 3
    取上次分页最后的结果 id, > id limit 20 。缺点,不能跳到指定的页。
    ksedz
        3
    ksedz  
       2021-06-07 14:48:01 +08:00
    加索引试试
    2kCS5c0b0ITXE5k2
        4
    2kCS5c0b0ITXE5k2  
       2021-06-07 14:48:53 +08:00
    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;
    2kCS5c0b0ITXE5k2
        5
    2kCS5c0b0ITXE5k2  
       2021-06-07 14:49:27 +08:00
    不过 20 万就干趴下 看看慢日志
    aries910
        6
    aries910  
    OP
       2021-06-07 15:01:21 +08:00
    @emeab 上面的 sql 就是从慢日志里找出来的啊。。。
    还有你上面的 sql 一样慢,没用
    aries910
        7
    aries910  
    OP
       2021-06-07 15:02:06 +08:00
    @sheeta 感谢提供思路,但是指定页的功能还是需要的
    aries910
        8
    aries910  
    OP
       2021-06-07 15:02:18 +08:00
    @loophole12 试过了,没啥用
    aries910
        9
    aries910  
    OP
       2021-06-07 15:05:15 +08:00
    @ksedz 上面的 status 和 supports 都加了单独的索引,没啥用给,我看网上有人说 mysql 查询的时候,where 和 order 只会用一个索引,所以就算都有索引也没用,我去尝试了也确实没用(我不太考虑复合索引,毕竟小机器,怕后面数据更大了更新索引占资源)
    2kCS5c0b0ITXE5k2
        10
    2kCS5c0b0ITXE5k2  
       2021-06-07 15:15:00 +08:00
    @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  
       2021-06-07 15:29:20 +08:00
    大数分页是无解问题

    一个妥协的方案是用 id 当游标
    dqzcwxb
        12
    dqzcwxb  
       2021-06-07 15:33:54 +08:00
    @sheeta #2 正解
    aries910
        13
    aries910  
    OP
       2021-06-07 15:48:14 +08:00
    @emeab 感谢再次回复,这边不能上传图片,我这边执行了还是十几秒
    2kCS5c0b0ITXE5k2
        14
    2kCS5c0b0ITXE5k2  
       2021-06-07 15:52:20 +08:00
    @aries910 我这边二次执行就快了.
    2kCS5c0b0ITXE5k2
        15
    2kCS5c0b0ITXE5k2  
       2021-06-07 15:52:45 +08:00
    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;
    2kCS5c0b0ITXE5k2
        16
    2kCS5c0b0ITXE5k2  
       2021-06-07 15:53:18 +08:00
    这个也行. 因为没完整的表 只能这样了.
    aries910
        17
    aries910  
    OP
       2021-06-07 16:05:05 +08:00
    @emeab
    没用,只能借用新浪的图床了
    cnoder
        18
    cnoder  
       2021-06-07 16:29:01 +08:00
    楼上方法叫 延迟关联
    notejava
        19
    notejava  
       2021-06-07 16:35:06 +08:00
    思路拓宽一点,从查询流程上优化,例如提前然用户填筛选条件筛选掉部分数据,再做分页。
    2kCS5c0b0ITXE5k2
        20
    2kCS5c0b0ITXE5k2  
       2021-06-07 16:47:42 +08:00
    @aries910 explain 看下索引
    lostvincent
        21
    lostvincent  
       2021-06-07 17:03:07 +08:00
    相关知识 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
       2021-06-07 17:19:10 +08:00
    2kCS5c0b0ITXE5k2
        23
    2kCS5c0b0ITXE5k2  
       2021-06-07 17:35:23 +08:00
    @aries910 我这边 500 万单表 都没问题. 1s 多.
    2kCS5c0b0ITXE5k2
        24
    2kCS5c0b0ITXE5k2  
       2021-06-07 17:36:06 +08:00
    我半桶水解决不了. 等大牛把.
    JasonLaw
        25
    JasonLaw  
       2021-06-07 17:41:19 +08:00
    还是在附言上添加一下 expalin 的结果吧。没有足够的信息,所有人都是靠猜的。
    Amit
        26
    Amit  
       2021-06-07 17:42:59 +08:00
    楼上先查出 id 再关联查询数据应该是最好的办法了,limit 写在子查询中只查 id 查的是索引没有回表,小主机应该也是没问题的啊,楼主要不要再检查下
    mikeguan
        27
    mikeguan  
       2021-06-07 17:56:44 +08:00 via Android
    取消分页吧。
    我们最终采用分页时最多读取 1 万条数据
    数据量大了,分页真的扛不住
    napsterwu
        28
    napsterwu  
       2021-06-07 18:04:28 +08:00 via iPhone
    因为如果有 20w 数据,offset 19w,也会遍历 19w 行。只能是用 id 去过滤
    zpfhbyx
        29
    zpfhbyx  
       2021-06-07 18:45:38 +08:00
    #2 正解
    calpes
        30
    calpes  
       2021-06-07 19:20:41 +08:00
    这楼里一群人干哈呢,1 楼就是完美解决方案了,查到后边 offset 大了慢是因为你表里有 text,先查 id 再取其他字段就 ok 了
    calpes
        31
    calpes  
       2021-06-07 19:24:34 +08:00
    @emeab 二次执行就快了可还行,查完一次就有缓存了
    2kCS5c0b0ITXE5k2
        32
    2kCS5c0b0ITXE5k2  
       2021-06-07 19:59:45 +08:00
    @calpes 所以我说我是半桶水啊 XD
    dawniii
        33
    dawniii  
       2021-06-07 20:12:47 +08:00
    楼上很多说先取 id 的,只取 id 的话,是不用 offset 条件了吗?慢的原因不就是 offset 的值太大了,问题不还是存在吗?
    ebingtel
        34
    ebingtel  
       2021-06-08 09:33:14 +08:00
    @emeab 有的时候 不一定是 offset 慢,先看看把 orderby 去掉,是不是快了……如果是 orderby 的原因,相关字段加上索引……如果没走索引,再用 FORCE INDEX
    aries910
        35
    aries910  
    OP
       2021-06-08 09:39:39 +08:00
    @emeab 不管问题有没有解决,也还是感谢你的建议 :),至少看的来你是真的帮忙去实践了,谢谢!
    aries910
        36
    aries910  
    OP
       2021-06-08 09:45:26 +08:00
    @ebingtel 确实是的,上面有很多小伙伴提议先用 id 取区间,再 select 完整数据
    这种方法时好时坏,我替换了 orderby 后也会变快
    关键查资料的时候看到个说法 :
    where status=1 orderby views desc
    这种情况这种会用一个索引,就算 status 和 views 都索引了,也并没啥用
    求确认
    xiaochong0302
        37
    xiaochong0302  
       2021-06-08 15:16:45 +08:00
    只能看前 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  
       2021-06-09 09:37:25 +08:00
    @aries910 如果是 orderby 多个字段,可以建立一个复合索引,这样才会快……如果没有,就这用用 FORCE INDEX
    ebingtel
        39
    ebingtel  
       2021-06-09 09:38:40 +08:00
    @ebingtel (建立复合索引的基础上)就接着用 FORCE INDEX
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3734 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 33ms · UTC 04:20 · PVG 12:20 · LAX 20:20 · JFK 23:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.