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

[数据库/PostgreSQL] 真的没有完美的分页方法吗?

  •  
  •   kaifeiji · 2022-03-14 13:26:03 +08:00 · 6043 次点击
    这是一个创建于 962 天前的主题,其中的信息可能已经有所发展或是发生改变。
    因为性能原因,研究了很多分页 SQL 的实现:

    1 、limit-offset 的耗时线性增长;
    2 、keyset 不能跳转指定页;
    3 、xmin 基于事务,可能有“空洞”;
    4 、ctid 基于存储,不能用 where 条件过滤;
    5 、pg_stats 基于统计数据,实时性差,结果不精确。

    具体可参考这篇文章:

    https://kaifeiji.cc/post/do-i-really-know-about-pagination/

    我想问的是,有没有:

    1 、耗时固定
    2 、能跳转指定页
    3 、没有“空洞”
    4 、能用 where 条件过滤
    5 、实时性、结果精确

    的分页方法?
    41 条回复    2022-04-05 09:34:55 +08:00
    nolo
        1
    nolo  
       2022-03-14 13:34:57 +08:00
    bitmap 索引试试?
    wd
        2
    wd  
       2022-03-14 13:48:59 +08:00 via iPhone   ❤️ 7
    直接看最后,这文章是搞笑的...
    cslive
        3
    cslive  
       2022-03-14 13:53:27 +08:00
    同楼上,拉到最后看完你文章有惊喜
    kaifeiji
        4
    kaifeiji  
    OP
       2022-03-14 13:56:02 +08:00
    @nolo 开启 bitmap 扫描,直接这样?

    set enable_bitmapscan =off;
    dzdh
        5
    dzdh  
       2022-03-14 14:04:14 +08:00
    没有。这不是 Pgsql 的问题。所有 SQL 数据库都存在分页到后面越来越慢问题。

    海量数据查询、排序、分页。请直接上搜索引擎。
    3dwelcome
        6
    3dwelcome  
       2022-03-14 14:18:34 +08:00
    我竟然信了。。一直看到了文章最后,差点晕过去。

    话说大数据分页始终是个比较麻烦的问题,不同排序和字段过滤,会导致完全不同的分页结果,连缓存都不太好做。
    luckyrayyy
        7
    luckyrayyy  
       2022-03-14 14:28:45 +08:00
    哈哈哈哈笑死
    kaifeiji
        8
    kaifeiji  
    OP
       2022-03-14 14:40:00 +08:00
    @dzdh 那我可以死心了
    oneisall8955
        9
    oneisall8955  
       2022-03-14 15:19:33 +08:00
    最终,发现问题的根源是索引损坏,导致分页时排序太慢。。。。。。。。。
    hidemyself
        10
    hidemyself  
       2022-03-14 15:20:36 +08:00
    答案是没有,要么上 ES 这种
    Vegetable
        11
    Vegetable  
       2022-03-14 15:26:25 +08:00
    根据我对很多“大厂”项目的观察,真的没有完美的分页方案。
    leoskey
        12
    leoskey  
       2022-03-14 15:27:12 +08:00
    上 ES 后又发生新的问题🐕 https://www.v2ex.com/t/840193
    sfqtsh
        13
    sfqtsh  
       2022-03-14 15:39:44 +08:00 via Android
    用游标呢
    kaifeiji
        14
    kaifeiji  
    OP
       2022-03-14 15:43:32 +08:00
    @sfqtsh 游标和 keyset 类似,都不能跳指定页
    sfqtsh
        15
    sfqtsh  
       2022-03-14 15:53:04 +08:00 via Android
    @kaifeiji 可以 MOVE absolute
    hope4tomorrow
        16
    hope4tomorrow  
       2022-03-14 16:00:58 +08:00
    用 mysql 也出过类似的问题,ID 主键索引不连续了,200w 数据分页一次要 2s ,在 leader 指导下直接对 ID 建索引,再分页查询立马起作用🥱
    MoYi123
        17
    MoYi123  
       2022-03-14 16:17:26 +08:00   ❤️ 1
    @hope4tomorrow 不是很懂, 为什么要给主键再建一次索引?
    westoy
        18
    westoy  
       2022-03-14 16:23:45 +08:00
    淘系你订单多的话, 中间也慢的, 而且经常会排序出错, 缓存上半天不变

    京东我怀疑会定时腾冷热数据, 它家是真的存在不定时丢单的

    这两家我估计已经接近消费领域性能和误差折中的天花板了
    kaifeiji
        19
    kaifeiji  
    OP
       2022-03-14 16:38:55 +08:00
    @sfqtsh 个人理解,MOVE 同样是要一个一个数 offset 的。有更多的线索吗?
    so1n
        20
    so1n  
       2022-03-14 16:40:14 +08:00
    数据库本身就不擅长做分页的
    kaifeiji
        21
    kaifeiji  
    OP
       2022-03-14 16:43:39 +08:00
    @sfqtsh 明白你的意思了,MOVE absolute 确实可以跳转到指定页,但它的实现和性能与 limit-offset 是相同的,所以耗时也是线性增长
    hooopo
        22
    hooopo  
       2022-03-14 16:44:24 +08:00
    还是有的
    3dwelcome
        23
    3dwelcome  
       2022-03-14 16:57:24 +08:00
    @kaifeiji "但它的实现和性能与 limit-offset 是相同的,所以耗时也是线性增长"

    基本上好一点的网站,翻页都是有限制的,用户也不可能无限翻下去。无限翻页就是个伪需求。

    头 100 页内存缓存一下,基本上能应付 95%的情况。剩下的 5%,也不会强制要求高性能。

    坚持说数据库 limit-offset 性能有问题的,不是傻就是坏。
    9c04C5dO01Sw5DNL
        24
    9c04C5dO01Sw5DNL  
       2022-03-14 17:02:23 +08:00
    es 也没有完美分页
    kaifeiji
        25
    kaifeiji  
    OP
       2022-03-14 17:13:07 +08:00
    @3dwelcome 懂了
    Oktfolio
        26
    Oktfolio  
       2022-03-14 19:32:33 +08:00
    搜索引擎一定条数之后不是也只能游标分页吗?
    felixcode
        27
    felixcode  
       2022-03-14 19:45:27 +08:00 via Android
    文章的最后
    ==========
    P.S

    最终,发现问题的根源是索引损坏,导致分页时排序太慢。

    修复索引后,耗时 1 秒以内——千万级的数据分页,LIMIT-OFFSET 还是 HOLD 住的。

    也就是说,我前边整的活儿算是白折腾了。
    ===========
    Sasasu
        28
    Sasasu  
       2022-03-14 20:01:08 +08:00
    有些奇葩 ORM 发出来的查询长这样:

    select count(*), * from (<query>) limit 20

    这种人现在的计算机科学还满足不了他
    hooopo
        29
    hooopo  
       2022-03-14 20:07:53 +08:00
    @Sasasu 不信
    ClarkAbe
        30
    ClarkAbe  
       2022-03-14 21:26:39 +08:00 via Android
    mysql limit page 现在差不多 300 万订单数据了还是很快的啊,毫秒级响应.......
    hope4tomorrow
        31
    hope4tomorrow  
       2022-03-14 23:07:30 +08:00
    @MoYi123 因为原有的主键索引失效了,失效原因是,那个表是开发环境的日志表,组里有同学操作过,删除了一些数据,导致索引也失效了,所以再建一次索引,leader 当时的描述是,建一个二级索引
    ipwx
        32
    ipwx  
       2022-03-15 00:11:42 +08:00
    ES 的底层是 Lucence ,LUCENCE 在我当年学习的时候,分页原理应该是直接用关键词抽出来一些倒排索引,然后用优先队列对倒排索引进行合并,上面的结果打分以后用一个大小为 K * page_size 的堆保存最好的前 K 页结果然后返回第 K 页的内容。
    msg7086
        33
    msg7086  
       2022-03-15 05:51:03 +08:00
    @ipwx 提醒一下,是 Lucene 不是 Lucence 。以前我也读错写错过。
    encro
        34
    encro  
       2022-03-15 09:05:20 +08:00
    分页关键点:

    1 ,数据量大情况下不要 count ;
    2 ,复杂查询用 es 之类;
    3 ,索引排序;
    4 ,组合索引;
    5 ,索引类别;
    6 ,explain ;
    Geekerstar
        35
    Geekerstar  
       2022-03-15 13:55:49 +08:00
    @leoskey 哈哈哈,被老哥鞭尸了
    Sasasu
        36
    Sasasu  
       2022-03-15 14:26:20 +08:00
    @hooopo 随便一个搜索引擎搜 '分页组件是基于 Mybatis 的,它会在你写的 SQL 脚本外面再套一层 SELECT COUNT(*) ROWNUM_ FROM (….) 计算总记录数,同时有另一个嵌套 SELECT * FROM(…) WHERE ROWNUM > 10 AND RONNUM < 10 * 2 这种方式生成分页信息'
    hooopo
        37
    hooopo  
       2022-03-15 14:37:36 +08:00
    @Sasasu 这种方式很高效啊
    Sasasu
        38
    Sasasu  
       2022-03-15 21:51:37 +08:00
    @hooopo 带着 filter 算总页数了
    hooopo
        39
    hooopo  
       2022-03-15 22:20:54 +08:00 via Android
    @Sasasu 并不是总页数 只是判断有没有下一页 盲猜做无限下拉的
    KouShuiYu
        40
    KouShuiYu  
       2022-03-30 17:08:18 +08:00
    limit-offset 还有一个坑必须加上 order 且排序要唯一,不然查出来的顺序会随着 limit-offset 参数不同而变化🐶
    liaohongxing
        41
    liaohongxing  
       2022-04-05 09:34:55 +08:00
    tidb 有个 tiflash 组件, tiflash 据说魔改的 clickhouse, 基于列存储, 实测 count 也快,分页也快 ,最完美的应该就是基于列存储,根据 sql 自动选择, 这样 where x1= ? and x2= ? ,根据列查
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1230 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 23:12 · PVG 07:12 · LAX 16:12 · JFK 19:12
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.