V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Features
V2EX  ›  MySQL

MySQL 数据上亿以后,查询分页问题

  •  
  •   Features · 344 天前 · 4907 次点击
    这是一个创建于 344 天前的主题,其中的信息可能已经有所发展或是发生改变。
    查询分页一般要最少要执行两条 SQL 语句

    ` SELECT COUNT(*) FROM tablename WHERE columnName = 'xx' `
    ` SELECT * FROM tablename WHERE columnName = 'xx' limit 0,10`

    因为查询条件是动态的,所以不太可能做缓存
    这种情况应该是必须要分表了吧?
    有什么数据库能在不分表,走索引的情况下实现良好的体验呢?
    40 条回复    2024-01-21 21:17:33 +08:00
    changdy
        1
    changdy  
       344 天前   ❤️ 3
    https://juejin.cn/post/7207410405786484796

    https://juejin.cn/post/7323570678690185242

    我自己做过一个订单查询的优化 ..场景复杂得多 .

    不知道楼主是已经有了具体的场景,还是只是猜想将来的维护情况
    Pythoner666666
        2
    Pythoner666666  
       344 天前   ❤️ 1
    瓶颈在你这个 count(*),下面一条走索引不会慢,所以解决办法就是跟 PM 沟通下,要么把时间筛选加上,要么就加缓存允许一定的延迟。
    vacuitym
        3
    vacuitym  
       344 天前
    不然做一张表单纯存数据的 count ,然后去定时刷新
    nice2cu
        4
    nice2cu  
       344 天前
    limit 页数大了也会慢的, 试试业务上能不能处理下,比如必填条件数增多,分页量大时不然看 1000 页之后的
    tonghuashuai
        5
    tonghuashuai  
       344 天前
    数据量大以后使用 limit offset, size 会有性能问题,可以使用 id 作为 cursor 。
    june4
        6
    june4  
       344 天前
    先确定有没有准确实时的大数据集总数这个需求吧
    opengps
        7
    opengps  
       344 天前   ❤️ 1
    我们这里要求的是分两步走:先执行第二句,有信息返回再执行第一句,否则第一句直接就用返回值 0 代替
    JackCh3ng
        8
    JackCh3ng  
       344 天前
    如果你的查询条件不复杂,数据量基本固定不会再增加,可以用缓存计数的方式,否则就乖乖上 es 吧,而且就算上 es 也还是解决不了深度分页的问题( es 只能查询一万条数据,也就是说 10 条一页,只能 1000 页,后面的也查不出来了)。如果觉得 es 成本太高,在没有复杂查询条件的情况下也可以尝试列式存储,但具体效率要看数据量和查询字段,使用列式存储仍然需要一个事务数据库,同样是需要数据同步的方案,或者在代码里进行双写。
    silentsky
        9
    silentsky  
       344 天前
    如果是内部使用 换 olap 数据库,否则不要 count
    zhuoyue100
        10
    zhuoyue100  
       344 天前
    数据量比较大的时候,这类查询一般不走业务表,可以异步写一份到分析型数据库来实现
    ShuA1
        11
    ShuA1  
       344 天前   ❤️ 4
    别用 count 用 EXPLAIN ,快很多,测试一下吧
    iyiluo
        12
    iyiluo  
       344 天前
    最好和业务商量一下,看看能不能限制一下查询范围,例如对外展示的数据限制前面 100 页
    FreeEx
        13
    FreeEx  
       344 天前
    1. 非要有总数量的话就得加上一个时间范围的选择。
    2. 不要总数量的话就可以用游标分页,这种需要数据结构中有一个字段是带顺序且唯一。

    第二种可以参考 https://bojithapiyathilake.medium.com/pagination-offset-vs-cursor-in-mysql-92cbf1a02cfa
    realNewBee
        14
    realNewBee  
       344 天前
    如果想通过技术手段来解决,那就不能使用 MySQL ,得使用其他的数据库。否则只能通过业务方案来解决。
    数据量超过百万,count 和 limit 都有性能问题。所以肯定不能用。在不改变数据库的情况下,我能想到的就是用 ID 来做滚动分页+索引搜索的业务方案来解决。
    coderzhangsan
        15
    coderzhangsan  
       344 天前   ❤️ 7
    上亿数据分页查询,楼上已经说明了存在的问题,我帮你简单汇总下:

    1. innodb 引擎,count 查询会很慢,优化的重点将是这里。
    a. 统计精度不精准的前提下,可以使用 explain select count(*) from tablename where columnName = 'xx' .
    b. 统计精度要求精准的话,由于存在各类场景的查询,innodb 引擎不适合去做这类聚合统计查询的业务,冗余一张 myisam 引擎的表去查询或者更换数据库

    2.分页语句,limit 偏移量越大,查询愈慢,可以使用唯一索引做偏移量查询,常见的就是自增 id ,示例
    SELECT id FROM tablename WHERE id > 10000 order by id desc limit 10.
    me1onsoda
        16
    me1onsoda  
       344 天前
    游标?
    encro
        17
    encro  
       344 天前
    我们就是默认只差最近 3 天的,时间建立一个索引。
    catamaran
        18
    catamaran  
       344 天前
    列数据库性能是真的快,比如 clickhouse ,但是对于数据的修改很不友好,不知道 oceanbase 怎么样,正在了解中。
    Features
        19
    Features  
    OP
       344 天前
    @ShuA1 用 explain 确实快很多,但是返回的 rows 应该是有问题的
    我这里测试,mysql5.7 ,设置 WHERE 条件以后,这个 rows 最大只能到 52618055
    不知道是索引问题还是什么情况
    Pastsong
        20
    Pastsong  
       344 天前
    用 cursor
    sch1111878
        21
    sch1111878  
       344 天前
    最近也遇到了这个问题, mysql 已经分表, 单表 2000 万数据, 聚合查询要 20 多分钟, 中间改用过 es, 但是不熟, 其他同事写的也乱, 实在不行就准备换 clickhouse 了,

    上面也有说业务表和聚合查询应该要分开的, 不然锁表或者慢 sql 都会业务影响比较大
    thinkershare
        22
    thinkershare  
       344 天前
    @Features explain 完全不能用来统计行数量,高频更新下返回的数据属于基本不可用垃圾数据。如果需要精确的分页。而且有复杂的查询,我的使用经验是 2000W+后就需要分表(如果是机械硬盘 500w ,有高频写入就需要分表了).
    如果数据库是只读的(包括软删除)模式,使用自增 id 是一个解。其它模式我也没找到好办法再 mysql 中实现靠谱的分页。
    sampeng
        23
    sampeng  
       344 天前   ❤️ 1
    1.cursor 法,上亿的数据,就算你 20 一页吧,产品经理麻烦你来给我翻到第 1234567 页谢谢。。。其实都这么多数据了。total 返回一个固定值估计都没人发现。。哈哈哈哈哈
    2.分区。哦。不好意思。。窜台了。mysql 没分区。那就只能分表了。
    3.外置表。也就是其他的解决方案,这就可以随意脑洞大开的想方案了。问题是很容易成另一坨屎山。任何新技术的引进都会导致复杂度的上升,我相信绝大多数用 mysql 到几亿了才想到分页怎么搞的 team ,玩 clickhouse 好,玩 es 也罢,都是会踩你没踩过的其他的雷的。
    sampeng
        24
    sampeng  
       344 天前
    如果是可以迁移库。。有一说一,迁移 pg 保平安。。最少一个分区下去就能解决你大部分问题。。
    ShuA1
        25
    ShuA1  
       344 天前
    @Features 看业务吧,一般业务用 explain 就够了, 不需要太精确
    NickX
        26
    NickX  
       344 天前
    如果数据量大,第一条 count 做实时其实没太大必要(目测只是展示作用,可以接受一段时间的延迟),可以缓存起来定时查询。第二条,columnName 字段加索引一下子能查出来。
    fiveStarLaoliang
        27
    fiveStarLaoliang  
       344 天前
    可以 count 上缓存,再上分表,旧数据分表存储,还可以加个冗余表,存储对应时间范围的主键,尽量降低表数据量,尽量走主键索引
    ninjashixuan
        28
    ninjashixuan  
       344 天前
    百万以上大 offset 就很慢了吧
    edk24
        29
    edk24  
       344 天前
    用 es 查询关联索引,分页结果拿到后再取数据库结果补充完整信息

    用 es 的话, 会存在 count 不准确的情况 好像是超过 10w 条还是多少就不准确, 但这个不重要 提高搜索精度比如手机号,身份证类似的减少这种情况的发生


    数据量大一点的系统都没有去搞 count 这个事情,都是直接偏移游标查下一个位置的数据, 那玩意太耗时了
    edk24
        30
    edk24  
       344 天前
    @edk24 这个是我们维护贵州核酸系统总结来的经验,用 es 来查索引很快

    你每页也就大不了 100 条数据, 用 id 去数据库查出来补充其他展示数据 很快的
    egqpwU3F2beQ8V77
        31
    egqpwU3F2beQ8V77  
       344 天前
    @ShuA1 可以啊 这骚操作学会了
    heliotrope
        32
    heliotrope  
       344 天前
    我是先执行第二条 SQL 分页前端异步查
    场景是 亿级别的数据量 自用的 有各种筛选条件要过滤
    目前是查列表
    第一页几乎无感 count 要 16 秒左右
    用户能接受 先显示出列表 主要是看列表总数用户愿意等
    主要是他不愿意花钱上 ES
    业务不允许切割的情况下几乎无解 缓存之类的 用户输入的搜索词每次都不一样根本就没办法缓存
    EXPLAIN 完全不准 不加任何条件都不准 能差出一个数量级
    illbehere
        33
    illbehere  
       344 天前
    搞个 mpp 数据库做从库吧
    dzdh
        34
    dzdh  
       344 天前
    zincsearch 、manticoresearch 、sphinx 、solr 、es 、opensearch
    akinoowari
        35
    akinoowari  
       344 天前
    @edk24 "track_total_hits":true 用这个不会不准,不然默认最大 10000
    MineDog
        36
    MineDog  
       344 天前
    记得大学时候还是用 mysql SQL_CALC_FOUND_ROWS 返回总计行,当时理解查一遍开销应该更小才对,后来在工作中就好像都是查两遍了 。 看了文档,原来 8.0 都废弃了 https://dev.mysql.com/worklog/task/?id=12615
    edk24
        37
    edk24  
       343 天前
    @akinoowari 学到了大佬
    jowan
        38
    jowan  
       343 天前
    这个问题很简单 你搜索淘宝和京东的时候看看最多给你多少数据就知道了 业务端可以显示 1W+ 10W
    上亿条数据 一页一页的去分页 可以重新考虑一下这个业务是否合理
    zoharSoul
        39
    zoharSoul  
       343 天前
    无需
    dyv9
        40
    dyv9  
       338 天前 via Android
    @sampeng 很多产品经理只知道要实现的功能点,但他们不具备设计能力,喜欢把不同用户角色的功能混在一个页面上做,导致一个查询包办很多种不同的细分功能,难以优化,就像这个返回说有几千页时明显在提示你这功能设计就有问题,没有对数据做限制,甚至用户啥条件都不输入也允许他们仅用隐含的状态之类的条件查所有数据,这是荒唐的软件设计。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2968 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 13:52 · PVG 21:52 · LAX 05:52 · JFK 08:52
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.