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

关于很多人说“MySQL 1 亿条数据统计轻轻松松”,确实超出我的认知。可以请各位大神发一下你们的“轻松”的标准跟扫描的数据量嘛?

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

    在之前的一个帖子 https://www.v2ex.com/t/1007852 的讨论引申出的另外一个话题

    我说说我的环境:

    • MySQL 8.x
    • 数据量:9000W+ 条 (我的生产环境确实没有 1 亿+数据量的表)
    • 扫描的数据量:全表
    • 运行环境:AWS 的 t3.xlarge (4c 16G) 磁盘类型为 GP3

    统计 SQL:

    select sum(cost)
    from table_name
    

    统计全表的 cost 字段的总和

    反正跑了 20s 是没有跑完的。相同的查询在 Clickhouse 跑了 12s ( clickhouse 的配置也不高,而且也是单机环境,非分布式)

    想看看各位大神的环境与配置,以及统计的 SQL 大概是什么

    如果各位的“轻松”的条件是用索引极大的减少了扫描的数据量,那感觉就不是一个话题了

    85 条回复    2024-04-03 16:47:58 +08:00
    djangovcps
        1
    djangovcps  
       319 天前
    我个人使用一般超过 2000 万条,就 g 了,除非表分区或者分表,另外说快的,可能是锁 id 查询快?
    RangerWolf
        2
    RangerWolf  
    OP
       319 天前
    @djangovcps 差不多,clickhouse 出来之前为了让 MySQL 的统计快一点我都愁死了
    djangovcps
        3
    djangovcps  
       319 天前
    纯分析的要不试下 es ,蛮快的,一亿条 sum 应该 1s 内能搞定,如果内存 32G ,0.5s 内可以跑完
    j1132888093
        4
    j1132888093  
       319 天前   ❤️ 11
    说快的肯定是指走了索引的情况,绝大多数业务都不会有扫描全表的场景,统计这种业务就不应该在 MySQL 中做
    zzNucker
        5
    zzNucker  
       319 天前   ❤️ 3
    一亿条用索引都可能很慢啊。。。。

    我也不知道轻松的标准是啥
    chendy
        6
    chendy  
       319 天前
    隔壁帖子说的是
    > 插入为主,简单的查询和统计

    总量大,但是每次插的查的都是一小部分

    楼主这种上来就全表扫一遍的,除非做缓存要不换啥都慢
    顺便一说现在的机器性能真不错,早年这么大的表一个 count(*) 下去都要一会才能查出来
    RangerWolf
        7
    RangerWolf  
    OP
       319 天前
    @zzNucker 我也不知道,反正我看回复的贴子一堆说“轻轻松松”
    justkfc
        8
    justkfc  
       319 天前
    轻松主要的途径不就是索引和分区表
    awalkingman
        9
    awalkingman  
       319 天前   ❤️ 2
    看了一下原来帖子的发言,大部份说的是简单查询下,我理解的简单查询就是走索引查询数据,没有 join 。mysql count 性能差不是什么冷知识,count ,sum 也算不上简单查询,计算列甚至可能用不上索引。
    我理解楼主的情绪,但是发这个帖子的就显得有些没在点上了。
    Leviathann
        10
    Leviathann  
       319 天前   ❤️ 2
    这不是典型的 olap 需求?所谓的轻轻松松都是基于 mysql 作为 oltp 数据库的前提
    june4
        11
    june4  
       319 天前
    你都搞全表扫描了,根本不是 mysql 的应用场景,mysql 追求的是尽可能走索引不扫表
    zzNucker
        12
    zzNucker  
       319 天前
    @RangerWolf 看了原贴大概是单表 1 亿只按索引简单 select 吧,有需要聚合,计算就不用这个方案了
    iseki
        13
    iseki  
       319 天前   ❤️ 1
    你自己都说了 ClickHouse 需要跑 12s 才能完成的全表求和,你觉得大家的“轻轻松松”能是什么标准呢?反正面对一亿行全表统计,我个人视能在分钟内完成就是可以忍受的,到底是不是轻轻松松要看需求,如果数据再大一点,能完成而不因为各种内存空间不足报错就是可以忍受的。

    @zzNucker 他这是全表扫描,索引并不能提供太多价值。
    adoal
        14
    adoal  
       319 天前
    因为,很久以前的说法是 MySQL 上了 2000 万就要分库分表……所谓轻松 1 亿是针对以前年代的硬件和 MySQL 版本上 OLTP 类系统哪怕做好索引和优化也扛不住这个量的数据来说的。
    liprais
        15
    liprais  
       319 天前
    clickhouse 有事务么?
    akira
        16
    akira  
       319 天前
    mysql 上亿 只是说能用来做跑常规业务,汇总统计还是要别的来做的把
    night98
        17
    night98  
       319 天前   ❤️ 2
    你说这话不就是纯抬杠了吗,有哪个带事务的行数据库能扫一亿数据统计在十秒内出结果的?你光磁盘 io 都不够这个数吧。 不过也可以做,无非所有数据缓存到内存里,统计也还是很快的,加钱可达
    adoal
        18
    adoal  
       319 天前
    刚才忽然好奇测试了一下 PostgreSQL ,在自己陈年老机上,i4-4460 ,32G 内存,没 tune 过参数的 PG16 ,生成了一亿条随机数,全表 sum 一下 1972.434 ms
    xiaofan2
        19
    xiaofan2  
       319 天前
    其实扫描速度跟你表设计大小有很大关系 我们有一个小表余额表 只保存了独立的几个余额相关字段 单表一亿扫描无任何问题
    min
        20
    min  
       319 天前
    ap 场景用 tp 数据库,被 ap 库秒得渣也不剩
    nuk
        21
    nuk  
       319 天前
    所以 mysql 到底跑了多少秒
    ntedshen
        22
    ntedshen  
       319 天前   ❤️ 1
    拿列式库在本地查单列。。。
    拿行式库不允许索引不允许分布式还要放到 io 差了好几倍的 vps 上跑。。。
    得出一个结论至少慢 60%。。。

    讲道理,我认为这属于褒奖 mysql 。。。

    gp3 的 16kio 是 16000 合 256MB/s 。。。
    请注意现在的家用低端固态的 4k io 都能达到 160k 。。。
    est
        23
    est  
       319 天前
    感觉这不是 mysql 的问题,而是 aws 的 IOPS 的锅? LZ 测试 clickhouse 也是 t3.xlarge (4c 16G) 磁盘类型为 GP3 嘛?

    mysql 的话,还得调优一下 InnoDB 才有比较好的性能。我 10 年前测 tokudb ,把 qq 那个 10 亿条导入进去,无聊 sum 了一下群号。。 差不多 10 秒就出来。。
    miniliuke
        24
    miniliuke  
       319 天前   ❤️ 1
    求求了用 pg 吧,实测真的吊打 Mysql (未调优)......
    ShuWei
        25
    ShuWei  
       319 天前
    别拿极端情况反驳,毕竟原贴的绝大部分情况下,都不太可能涉及全表扫描,而且,这种全表扫描,很多时候都可以通过冗余统计字段的方式来温柔对待,因地制宜就好,在需求和成本间实现优雅的平衡
    xmh51
        26
    xmh51  
       319 天前   ❤️ 2
    大哥,我们只是提建议,不是你用来抬杠的对手。
    Clickhouse 没有完整的事务支持。 缺少高频率,低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据,但这符合 GDPR 。Clickhouse 适合海量数据,低频更新,低并发查询。尤其是统计数据。
    mysql 是一个传统的支持完整事务的数据库。
    两者不在一个赛道上,根据自己的需要自己决策选型。
    LeeReamond
        27
    LeeReamond  
       319 天前   ❤️ 1
    听别人说有什么用,你每天混论坛的还不知道大部分人是啥水平。所谓轻松能跑出来就算轻松,一个查询 5 秒,只要用户没 call 过来说慢那就是纯正常响应速度。

    楼上的也不知道有几个试过,一亿级数据就算用唯一索引,单行数据调取性能下降多少,a<索引<b 查询多长时间才能返回,分区后能不能加速。

    反正我试完上述结论没一个显示能用的。
    当然这是单实例,你集群虚拟化数据层当我没说。。
    mysunshinedreams
        28
    mysunshinedreams  
       319 天前
    之前工作经历有过单表上 10e 数据的,不过仅存了包含主键在内的四个字段,也是在不断往上探测系统极限性能的情况下才发现瓶颈的,某些特别场景还是可以抗住的。
    NXzCH8fP20468ML5
        29
    NXzCH8fP20468ML5  
       319 天前 via Android
    v2 的人谈数据库,基本上都是在赛博斗蛐蛐。

    不会有人不知道 mysql 一个查询只能用单线程吧。
    这也是为什么一群人吹嘘 oracle 大表连接很快,又有人吹嘘 pgsql 可以平替 oracle 的原因。
    因为其他数据库的一个查询可以利用多个核来执行,mysql 还在煞笔的用一个核去跑,不慢才怪呢。
    fd9xr
        30
    fd9xr  
       319 天前 via iPhone
    5.5 都随便跑…到底哪里不行了 是什么要让你在里面算 SUM()
    W3Cbox
        31
    W3Cbox  
       319 天前
    什么样的业务会有上亿数据。从事开发年 10 来,很多公司从创业到倒闭了都没有上亿数据,真有上亿数据,公司都可以上市了
    xuanbg
        32
    xuanbg  
       319 天前
    3000 万+,6 表联查,平均 50ms 。
    wudaye
        33
    wudaye  
       319 天前
    @W3Cbox 根本不需要什么上市公司,只要用户量达到 1000w 的公司,核心业务表数据量上亿几乎是必然的事
    june4
        34
    june4  
       319 天前
    @LeeReamond 有索引还会慢?你要不要了解下索引的原理,走索引百亿内无论多少数据都只要固定的小量读硬盘次数。刚在我的亿级表上试了下,无论是取单行还是 100 行,在索引上耗时显示都是 0.000s
    goodryb
        35
    goodryb  
       319 天前   ❤️ 1
    查了下 aws GP3 的性能参数 每个卷的最大 IOPS (16 KiB I/O) 16,000 , 每个卷的最大吞吐量 1,000 MiB/s

    这个速度怕不是连我台式机上面的 pcie3.0 ssd 都打不过,对数据库好一点吧

    https://docs.aws.amazon.com/zh_cn/AWSEC2/latest/UserGuide/ebs-volume-types.html
    hhecoder
        36
    hhecoder  
       319 天前 via Android
    @W3Cbox 很多数据类的 c 端产品,稍微有点成绩的上亿数据都轻轻松松。
    iseki
        37
    iseki  
       319 天前
    @xxfye MySQL 8.x 还没有并行查询能力吗?有点弱啊
    NXzCH8fP20468ML5
        38
    NXzCH8fP20468ML5  
       319 天前
    @iseki innodb 存储引擎在 8.0.14 有过一次升级,支持并行读取了。
    但执行器还是只能一个线程处理。
    可以说 mysql 的并行查询能力聊胜于无。
    FishBear
        39
    FishBear  
       319 天前 via iPhone
    我们公司的表 12 亿了还在跑
    yh5DC6Y7u7TdcT9s
        40
    yh5DC6Y7u7TdcT9s  
       319 天前
    oltp 场景: 典型代表 pg 和 mysql ,使用范围最广,常见 web 业务,日常增删改查,支持事务的 ACID 特性; 通常查一整行数据或者多列,基本不会全表扫描,所以数据底层按照行来存储数据,读一整行时按照局部性原理,会对缓存友好
    olap 场景:典型代表 clickhouse ,doris ,hbase 等;常用于分析统计,需要大量扫描数据,且通常只关注某一列数据,不支持事务/支持的很弱,底层数据是按照列来存储的,因此扫描列的效率远大于扫描整行

    两个不同的赛道, 底层设计都不一样
    jeesk
        41
    jeesk  
       319 天前
    没有相同的条件,然后不同的人会得出不同的结论.

    你说的上亿的数据? 机器什么配置? 磁盘,cpu ,内存, 等等. 我个人觉得网络上的评论多数是不能信的, 还是要自己实践.


    就像 windows 有说卡一样, 1 个人还在用 8 代低压 cpu 的笔记本,另外一个人用上 13 代 i9 cpu. 1 个人说卡的要死, 另外一个说流畅的要死? 怎么评价? 他们说的都是真话.
    iyaozhen
        42
    iyaozhen  
       318 天前
    哥,谁叫你全表 count/sum 呀,不加 where 就是很慢

    数据库场景分两种,oltp 、olap ,简单来说就是 oltp 主要是 select * from table where id = xxx ,1 亿肯定没啥问题。olap 就比较看场景了,比如统计一天的 pv 、uv ,select count(*) as pv, count(distinct user_id) as uv from table where `date_day` = '2024-01-01' 这样也行,好几千 w 的表也可以秒出,如果 where 条件不能命中索引就麻烦了
    MySQL 是通过不同存储引擎来分别支持两种场景,但实际情况是两种场景我们都有,这就出来了 tidb 这种 newsql ,两种都支持的比较好

    我实际搞过,当然公司不缺资源,100C 256G 3T SSD
    allenzhangSB
        43
    allenzhangSB  
       318 天前
    @adoal #18 方便同样的数据用 MySQL 再跑一遍吗
    makerbi
        44
    makerbi  
       318 天前
    @W3Cbox #31 爬虫 + 数据分析的 SaaS 很容易就上亿了
    LeeReamond
        45
    LeeReamond  
       318 天前
    @wudaye 也不至于非得千万级用户,你一个用户一共才产生 10 条数据?你稍微有点详细业务需求,数据量比用户高两三个数量级也正常,这还是单业务。

    @june4 你这写的给我说乐了, 走唯一索引当然索引不耗时,B+树能耗什么时?你整个查询时间什么情况,行数增长和查询时间增长不是线性关系,另外跟你表内数据量也有关系,你所有自我感觉良好的数据结构全都不复杂。我原文写的“没一个能用的”,你要是没看懂你可以再看一遍,还是你最后压测单机 qps 跑个一两百就叫能用了?我写的就是有的程序员对响应速度也没要求,对流量也没要求,业务跑完就叫“够用”,“凯瑞起来毫无压力”
    litguy
        46
    litguy  
       318 天前
    GP3 的性能感觉不太行
    虽然一个 NVME 盘性能都可以爆掉它了
    可能那些用户配置和你不一样
    hefish
        47
    hefish  
       318 天前   ❤️ 1
    php 是最好的语言,没有之一。
    Foxkeh
        48
    Foxkeh  
       318 天前
    请问主题里这个场景里所有的 cost 字段都是动态的吗?有没可能按照时间区间或者其他维度定期计算然后只做累加?
    icy37785
        49
    icy37785  
       318 天前 via iPhone
    @W3Cbox #30 我的个人项目手机百度网盘的分享链接做搜索,数据都是以亿为单位了。以前数据上亿少很多就是因为性能问题比较省,真放开手来,很多类型的数据都是很容易过亿的。
    Terry166
        50
    Terry166  
       318 天前
    既然是在 AWS 上,可以试试 Snowflake ,AWS 上的数据仓库平台,把数据库文件导入 Snowflake ,查询的时候会自动横向扩展提升查询效率,查询包含 1000 亿条数据的表也只需 10 秒左右的时间。
    nnegier
        51
    nnegier  
       318 天前 via Android
    @j1132888093 #4 在这样的环境下为什么索引快呀? 1 亿条
    me1onsoda
        52
    me1onsoda  
       318 天前
    有没有可能你配置太抠了
    phli
        53
    phli  
       318 天前
    @W3Cbox 我这一天就能上亿。
    RangerWolf
        54
    RangerWolf  
    OP
       318 天前
    @Terry166
    @me1onsoda 穷人小项目 RDS 已经嫌贵了
    RangerWolf
        55
    RangerWolf  
    OP
       318 天前
    @miniliuke 也想转试试看,老项目的路径依赖比较强一点,新项目可以试试看
    coinbase
        56
    coinbase  
       318 天前
    pg 是最好的语言,没有之一。
    Narcissu5
        57
    Narcissu5  
       318 天前
    OLAP 的角度来讲,20S 可能真的不算慢,我司之前的大数据开发用 hive ,随便一个查询至少两三分钟
    rickiey
        58
    rickiey  
       318 天前
    云存储应该有 iops 限制
    crazycarry
        59
    crazycarry  
       318 天前
    70 亿的三个表。。只能索引查询。。。
    jowan
        60
    jowan  
       318 天前
    你这里是 count 全表,除非是业务统计需要,我们的投放平台监测链接和媒体回传表几十亿,业务上要做取舍,和淘宝京东类似,你检索结果几百万但最多只给你展示前 N 页。根据那个帖子的介绍,在以插入和简单查询为主的场景下 MySQL 完全够用,确实轻轻松松。而且我们还有复杂的统计查询,不过业务上都是要求带时间范围,比如最大时间跨度不能超过 3 个月,等等。
    jowan
        61
    jowan  
       318 天前
    MySQL 的 count 性能众所周知,几百万以上就特别明显,数据量达到千万级别后要么你用预估统计 TABLE_ROWS 之类的,要么就缓存。而且条件查询一定要命中索引和限制返回行数,要不然崩的概率非常大。
    hallDrawnel
        62
    hallDrawnel  
       318 天前
    同时要看你买的什么配置的 MySQL ,跑在什么硬盘上。我们之前几亿的数据常规业务操作基本 100ms 以内完成。
    keshawnvan
        63
    keshawnvan  
       318 天前
    试试 PolarDB ,开列存节点,实测复杂查询快 30 倍以上。
    lbunderway
        64
    lbunderway  
       318 天前
    我一般是超过 800 万就要分表,但是这个分表时机要看索引长度和树的高度决定
    zvvvvv
        65
    zvvvvv  
       318 天前
    @W3Cbox #31 物联网相关的产品,不是轻松上亿嘛
    javaisthebest
        66
    javaisthebest  
       318 天前   ❤️ 1
    @lbunderway

    阿里巴巴规范典型受害者。。。。
    leonhao
        67
    leonhao  
       318 天前
    V 站数据库的水平怎么这么低啊,讨论其他的头头是道,到了数据库就特别拉胯
    Mrun
        68
    Mrun  
       318 天前
    我现在手上的业务,单表都有 3 亿+的,要看的 sql 的复杂度的。。。
    luckywh
        69
    luckywh  
       318 天前
    一亿五千万 count 主键 平均 5 秒 mysql 生产环境
    laaaaaa
        70
    laaaaaa  
       318 天前
    我们一般上百万就上 es 了- -
    kestrelBright
        71
    kestrelBright  
       318 天前
    看扫描行数,还有行数据大小吧
    yumizhao888
        72
    yumizhao888  
       318 天前 via iPhone
    一看大部分就是理论家,数据库就存储用的,放那么多单遍是想显摆自己技术牛逼吗?一看就是学生党。实际上手就是能 select 就 select ,能分表就分表,索引一下,要什么数据程序判断一下 1ms 不就马上出来了吗。
    数据库很难吗???
    encro
        73
    encro  
       318 天前
    统计 mysql 这类数据库的一个老大难。特别是加了 where 的统计。所以最好的做法是避免统计。

    我就是一个表放几万数据的,但是我不会给他们实时统计的,他们要的报表是每天生成的,不能修改的。
    encro
        74
    encro  
       318 天前
    @encro
    #73

    我就是一个表放几亿数据的
    MaxFang
        75
    MaxFang  
       318 天前
    上 r6 4x 的试试。
    zpfhbyx
        76
    zpfhbyx  
       318 天前
    上 e 的表本身搞统计就是跑不动啊. 轻松的话 一般说的都是主键 id 或者主键 id 偏移+ 区间的扫描啊. 比如 select xxx from test where id > 0 and id <= 100000 这种
    sampeng
        77
    sampeng  
       317 天前   ❤️ 1
    说的轻轻松松是有前提条件的。仅仅是业务查询,非聚合查询。聚合分析别说上亿了,上千万,可能就得考虑上 olap 的分析工具了。

    另一方面,你都上亿的数据了,还是需要分析的。4c 16G 是不太小了点。这个成本是可以计算的。

    1.clickhouse 集群。4*16 集群 x3.
    2.mysql 把硬件往上面拉三倍。

    从哪个角度看都是 2 成本更低。除非你能做到 1 是动态的,但投入的隐形成本又是另一回事。

    换句话说,我个人认为绝大多数的场景,堆机器就够了。除非有数据分析团队资源,全靠研发自己出方案,实在得不偿失
    ewBuyVmLZMZE
        78
    ewBuyVmLZMZE  
       317 天前
    @djangovcps #3 他这种数据需求,不如上 ClickHouse 了,ES 在这块的加成不如 ClickHouse 。

    另如果是 sum 之类的这种 Analytical 的查询,写入量不高,存量高,需要实时更新的话,可以预聚合写入。那种情况下,ClickHouse 加物化视图很不错。
    NoNewWorld
        79
    NoNewWorld  
       317 天前
    全表..... 百万就不行了,上千万的查询只有建立在索引能筛选掉大部分数据的情况。
    j1132888093
        80
    j1132888093  
       317 天前   ❤️ 1
    @nnegier 拿我前公司的业务举例,1 亿条数据的表,根据公司 id 哪怕只走一级索引,过滤出来的数据其实已经是千分之一乃至万分之一了,再拿这几千个 id 去走主键索引,四层的 B+ 树查找在目前普遍用 SSD 的情况下,还是可以接受的
    Vegetable
        81
    Vegetable  
       317 天前
    由于看不惯别人装逼自己发帖结果让别人又装了一遍
    RangerWolf
        82
    RangerWolf  
    OP
       317 天前
    @Vegetable 人艰不拆好吗
    adoal
        83
    adoal  
       316 天前   ❤️ 1
    @allenzhangSB 我上次拿来测试的物理机系统是 Debian 13 ,MySQL 官方 deb 包只到 12 的,库依赖有问题,我又懒得自己编译了。今天找了一台 Debian 12 的虚拟机做了测试。所在的物理机也比较老了,E5-2360 的。虚拟机是 6C 12G ,存储是来自 SSD 加速的 HDD 阵列分的 LUN 。

    安装了 PG 和 MySQL ,PG 来自 PGDG 的 apt repo ,版本 15.5 ,MySQL 来自 Oracle 官方 deb ,版本 8.0.35 。两者都没做任何 tuning 。

    测试数据:先在 PG 上生成,再导出后导入到 MySQL 去。
    数据生成语句:
    create table t(i integer, v integer);
    insert into t (select i, random()*100::integer from generate_series(1, 100000000));

    测试语句:
    select count(1) from t;
    select sum(v) from t;
    select v, count(1) from t group by v;

    测试一个系统时关掉另一个系统。不过没重启服务器。

    测出来的结果
    pg15
    count: 2.746s
    sum: 3.001s
    group: 7.109s

    mysql80
    count: 4.67s
    sum: 76.48s
    group: 100.95s
    woshicixide
        84
    woshicixide  
       316 天前
    可能是指按主键查询吧
    zzmark06
        85
    zzmark06  
       238 天前 via Android
    oltp 业务,时间和扫描数据量基本成正比
    你这个,扫描数据量上亿,咋个快法?
    olap 业务,时间和扫描数据量也是成正比,不过可以通过只扫描必要列、并行扫描多 chunk 并行加速、跳数索引(减少 chunk 数量)、预聚合(无需扫描原始数据)、块压缩(减少 io 时间)等手段来变相超车,减少实际扫描数据量。

    分库分表,若依旧单并行顺序执行,不过是把工作拆分,最终时间不变(不考虑 cache)。
    若并行查分表,那就是上面的多 chunk 并行加速。

    新些的库,比如 polardb ,不开 imci ,只选择并行化,也可以实现相同的效果


    mysql ,一些统计性质的聚合函数可以通过索引,能实现类似于“只扫描必要列”的特性

    至于你举例这个 sql ,除了有列存优化的库,应该谁跑都差不多才是,都慢
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3474 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 04:57 · PVG 12:57 · LAX 20:57 · JFK 23:57
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.