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

mysql 的一张表超过 1000w 后,如何优化

  •  1
     
  •   ginux · 2019-04-11 14:26:50 +08:00 · 6419 次点击
    这是一个创建于 2039 天前的主题,其中的信息可能已经有所发展或是发生改变。

    现状: 1.超过 1000w 的表较多; 2.每个表之间会有较多的联表查; 3.一个 select 的 sql 可能需要查处几万条数据; 大伙有啥好方法么?

    48 条回复    2019-07-11 14:52:45 +08:00
    kukumao
        1
    kukumao  
       2019-04-11 14:42:07 +08:00
    分表。。。。
    gz911122
        2
    gz911122  
       2019-04-11 14:45:01 +08:00
    索引 ,尽量别连表
    分区
    rockyou12
        3
    rockyou12  
       2019-04-11 14:49:08 +08:00
    如果是统计类型的业务还是直接用 spark 这些来做吧,反正也可以写 sql
    ggicci
        4
    ggicci  
       2019-04-11 14:52:07 +08:00
    ## 自己扛

    因为业务影响会比较大,所以都需要根据业务按顺序考虑以下思路:

    1. 索引优化
    2. 分区优化
    3. 分库
    4. SQL 查询优化,比如把某些联表查询改造成多次单表查询

    ## 云服务扛

    换个超屌的关系型数据库产品
    reus
        5
    reus  
       2019-04-11 14:58:01 +08:00
    换 PostgreSQL.
    linxb
        6
    linxb  
       2019-04-11 15:00:46 +08:00
    增加冗余字段,减少连表操作,水平分表
    yangxin0
        7
    yangxin0  
       2019-04-11 15:01:37 +08:00
    删库重来
    babedoll
        8
    babedoll  
       2019-04-11 15:02:13 +08:00
    分表
    jusalun
        9
    jusalun  
       2019-04-11 15:32:06 +08:00
    放 clickhouse,十几亿的量单机都能秒查
    love
        10
    love  
       2019-04-11 15:43:09 +08:00 via Android
    啥叫一条 sql 查几万,需要一次性取出几万条记录?
    goodleixiao
        11
    goodleixiao  
       2019-04-11 16:04:31 +08:00
    先优化业务规则,在考虑怎么优化数据库。

    建索引、删外键,分表分库,减少连表操作,多点冗余字段
    wedoub
        12
    wedoub  
       2019-04-11 16:06:47 +08:00 via iPhone
    大表拆分成小表,以空间换取时间
    luozic
        13
    luozic  
       2019-04-11 16:06:55 +08:00
    錢多不多? 錢多去買牛逼的服務器; 錢少,那就修代碼,改架構,改查詢。
    14march
        14
    14march  
       2019-04-11 16:11:26 +08:00
    @yangxin0 暴躁程序员 我喜欢
    alamaya
        15
    alamaya  
       2019-04-11 16:14:05 +08:00
    才 1KW,我觉得 mysql 完全木问题呀
    qiyuey
        16
    qiyuey  
       2019-04-11 16:16:57 +08:00
    不要在数据库写 join,全部放到代码侧,这样对缓存和分库分表都比较友好。
    janus77
        17
    janus77  
       2019-04-11 16:18:10 +08:00
    超过 1000w 的表较多,就这一句话我觉得你们当初的架构就有问题
    要么在之前预测的时候就开始做分表
    要么优化业务和表结构,要知道多个 1000w 数量级的查询不是什么常见的场景。
    simapple
        18
    simapple  
       2019-04-11 16:19:40 +08:00
    看具体业务
    看具体瓶颈操作卡在哪里
    看优化预算

    所以没有一个固定的范式去解决


    也许预算是第一个要先搞清楚的
    loading
        19
    loading  
       2019-04-11 16:23:31 +08:00 via Android
    一次查几万?瓶颈可能会在磁盘 io 或者网络 io 上了。
    ginux
        20
    ginux  
    OP
       2019-04-11 16:24:31 +08:00
    @love 是的
    ginux
        21
    ginux  
    OP
       2019-04-11 16:25:28 +08:00
    @alamaya 性能问题很大,因为都是大表
    ginux
        22
    ginux  
    OP
       2019-04-11 16:34:29 +08:00
    @ggicci 索引、分库、分表都做了,例如经常会在表里面执行 count ()或者 in(几千个参数)操作,性能低
    9684xtpa
        23
    9684xtpa  
       2019-04-11 16:53:06 +08:00
    @ginux #22 count in?
    houshengzi
        24
    houshengzi  
       2019-04-11 17:46:50 +08:00
    我们也有这样的表,有七八个字段都是 0/1/2 这些值,但是业务需求基本上都用到这几个字段做条件

    对于这种优化也在头疼着。
    kiddult
        25
    kiddult  
       2019-04-11 17:48:42 +08:00
    @ginux 几千个参数。。。。。还是先拿板砖把开发拍死再说吧
    ducklyl
        26
    ducklyl  
       2019-04-11 18:09:16 +08:00
    千万级索引优化好,也还行。
    接着分表,拆成 N 张子表,再不行的话,再分库,分成多个库。
    opengps
        27
    opengps  
       2019-04-11 18:14:40 +08:00
    大表做运算真的很麻烦,这个方面没经验,只能提示下分表或者分段 sum 试下
    vmskipper
        28
    vmskipper  
       2019-04-11 18:42:46 +08:00
    推到重来 做个容量规划 重新设计
    Antihank
        29
    Antihank  
       2019-04-11 19:26:13 +08:00
    @qiyuey 大佬,我的习惯就是这样,但是问题是这样会很容易内存溢出,而且如果项目有 dubbo 或类似的 Service 层分布式架构,然后从各个服务去拉取数据,数据量变大,往往需要通过中间件来传递。。。到现在我都没有很好的解决办法。。。
    Raymon111111
        30
    Raymon111111  
       2019-04-11 20:21:59 +08:00
    查几万的数据单纯用一个表没有什么办法

    建议换存储引擎, 比如 ES
    tomczhen
        31
    tomczhen  
       2019-04-11 23:54:25 +08:00 via Android   ❤️ 3
    抛开一些低级错误,在 OLTP 数据库上做 OLAP 业务怎么优化都会难受。

    对于时效要求不高的分析就没必要每次都在数据库统计实时数据,定时执行保存结果就好。实效性要求高的高读写数据(热点)只能用缓存解决,业务代码需要处理一致性问题。

    还有就是一些需求本身不适合在关系数据库上做,可以利用 CDC 之类的把数据同步到其他类型数据库。
    redsonic
        32
    redsonic  
       2019-04-12 02:47:39 +08:00
    库表是防在 SSD 上面吗? 不是的话先迁移到 SSD 再说,如果不是频繁写表的话.
    jbiao520
        33
    jbiao520  
       2019-04-12 07:57:13 +08:00 via Android
    in 几千个参数是什么鬼
    0xABCD
        34
    0xABCD  
       2019-04-12 08:12:58 +08:00 via Android
    @ginux 看出来了,问题出在 SQL 写得太渣,架构再牛也没用
    leonme
        35
    leonme  
       2019-04-12 08:52:07 +08:00 via Android
    @tomczhen 看了一圈,这个回答比较靠谱,也是大厂通用的解决方式
    lsongiu
        36
    lsongiu  
       2019-04-12 09:01:50 +08:00
    tidb?
    daodao116
        37
    daodao116  
       2019-04-12 09:40:19 +08:00
    如果是事实性要求较高的需求
    1、索引(尽量保证 join 是索引字段,当然这些字段本身是否建索引还值得商榷)
    2、分区

    如果是统计形需求,就像楼上 @tomczhen 说的,定时统计好了,慢一点没事,单线程不会拖垮数据库。

    再复杂一点就是设计和代码层面的改动了,要看具体业务需求和表结构设计了,可能就不是一句两句能说清楚的了。
    HarryQu
        38
    HarryQu  
       2019-04-12 10:28:10 +08:00
    @tomczhen 实效性要求高的高读写数据(热点)只能用缓存解决,业务代码需要处理一致性问题。

    在这句话中, 缓存是在哪里做 ? 直接使用 Mysql 数据库缓存,还是在业务层做缓存, 如 Redis。
    HarryQu
        39
    HarryQu  
       2019-04-12 10:34:30 +08:00
    @tomczhen
    在实时性要求高的情况下, 如果数据库数据频繁更新,是否意味着缓存时常 miss ? 这样的话缓存也不能起到很好的效果。
    那么如何处理时效性要求高且数据庞大且数据频繁更新的问题 ?
    90928yao
        40
    90928yao  
       2019-04-12 11:20:43 +08:00
    clickhouse 强烈推荐
    90928yao
        41
    90928yao  
       2019-04-12 11:21:27 +08:00
    没看题 太多关联 clickhouse 可能性能不太好
    whp1473
        42
    whp1473  
       2019-04-12 14:57:14 +08:00
    1.分表、分库。横向、纵向切分。
    纵向:将数据分为易变更、高热度数据和不易变更、低热度数据,把 N 列,切分成 M 个表,关联。降低单表的数据储量
    横向:可以考虑用数据库中间件 mycat 之类的,将原有的数据按照 ID 分片到不同库。查询时数据库中间件会自动聚合返回。但要考虑事务问题、使用 join 困难问题。
    2.数据异构
    mysql 数据库对于以 T 为单位的数据本身就存在瓶颈,可以考虑通过 binlog 或者消息的方式同步数据到其他存储引擎(例如:es、hbase),通过实时计算或定时计算将要查询、join 的数据的结果回流到 mysql、es,然后通过该存储进行查询。
    3.热点数据缓存
    对于敏感的数据,需要考虑缓存问题,可以使用 Redis+本地缓存+ZK 的方式做多级缓存。

    异构数据同步快慢,依赖于消息的消费速度,速度慢可以扩容 mq 机器、消费者机器,基本可以保障基本同步。
    whp1473
        43
    whp1473  
       2019-04-12 15:04:26 +08:00
    或者换 Oracle ?单表几亿数据应该没问题吧,写存储过程、优化 Sql、索引,应该能撑很长时间
    whp1473
        44
    whp1473  
       2019-04-12 15:07:24 +08:00
    一次性查几万数据,这个还好吧。一次实时取几万数据,那就只能打产品了。
    ggicci
        45
    ggicci  
       2019-04-12 15:56:59 +08:00
    @ginux 把这些你认为是瓶颈的查询往死里优化,实在优化到头了就得考虑其它可替代的解决方案了。你可以看看是否有其它数据库系统能更好地来处理你的瓶颈部分,想想把这部分数据外挂给这些系统处理能否解决你的需求。
    wind3110991
        46
    wind3110991  
       2019-04-12 16:33:09 +08:00
    ( 1 )订单信息、运营报表数据,建议分表分库,或者更换成 ES 搜索引擎;
    ( 2 )时序数据换成 Hbase、openTSDB ;
    ( 3 )建议检查优化索引,重建表然后瘦身、迁移;
    ( 4 )实时性不强的数据一定要加缓存;
    太多大表联表,说明你的业务逻辑需要优化了,尽量用空间换时间,把业务逻辑尽量放在代码端而不是 mysql,
    mysql 就是个最傻瓜的东西来用就对了,不要 XJB 用,尽量不要在大表间做 join ;
    Ja1
        47
    Ja1  
       2019-04-16 09:49:36 +08:00
    千万级别数据库优化,按照从简到繁:
    1.sql 语句以及索引优化
    2.加缓存
    3.主从复制,主主复制,读写分离
    4.分区
    5.垂直切分
    6.水平切分,(迫不得已才使用,会增加难度)
    lolizeppelin
        48
    lolizeppelin  
       2019-07-11 14:52:45 +08:00
    介绍你个东西 maxwell

    可以读取 mysql 的 binglog 以 json 形式发到卡夫卡 /redia/rabiitmq....
    这样你可以在不改业务代码的情况下,把数据转发到其他的位置做 olap 啦

    把要做分析的数据丢 pg 里比 mysql 里分析快多了...

    不过 maxwell 的代码好像有点简单..有资源可以考虑 debezium
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1662 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 16:49 · PVG 00:49 · LAX 08:49 · JFK 11:49
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.