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
SJH0402
V2EX  ›  MySQL

mysql 分表能带来哪些显著的,可见的提升?

  •  
  •   SJH0402 · 60 天前 · 3444 次点击
    这是一个创建于 60 天前的主题,其中的信息可能已经有所发展或是发生改变。
    前提:
    1 、未分库
    2 、表 A 年数据量 1000w ,表 B 年数据量 5000w
    3 、原业务中的 sql 涉及到 left join 查询,总是超时

    两个表都使用 create_time 字段按月份分表 12 个,
    在分表后,left join 的查询效率没有丝毫提升,
    单表查询效率略微下降 (0.02 秒 > 0.05 秒)?

    分表工具使用的是 mycat 以及 sharding-proxy (都有尝试)。

    因为是第一次尝试 mysql 分表,所以很疑惑,分表带来的究竟是哪方面的提升?
    还是说我的分表字段或者 sql 有问题
    33 条回复    2024-03-15 16:10:43 +08:00
    dobelee
        1
    dobelee  
       60 天前
    啥场景?干掉 join ,关键字段冗余存储,或者组装数据。从业务的角度优化一下。
    linauror
        2
    linauror  
       60 天前
    查询时带上时间条件了没,如果没带可能比单表效率还差。另外直接去数据库 explain 分析一下命中了索引没
    fkdog
        3
    fkdog  
       60 天前
    所以你有确定查询性能已经无任何可优化的手段后才决定分表,
    还是瞄了了面经然后感性判断数据量大需要分表然后稀里糊涂的就拆了?
    SJH0402
        4
    SJH0402  
    OP
       60 天前
    @linauror 带了,时间条件是范围索引,甚至有时候还会添加等值查询之类的条件进一步命中索引。哪怕是这样速度也是慢的出奇,几十上百秒是经常的
    SJH0402
        5
    SJH0402  
    OP
       60 天前
    @dobelee 正在往这个方向尝试
    SJH0402
        6
    SJH0402  
    OP
       60 天前
    @fkdog 目前是单表 join 太慢,领导让分表试试,技术这块我只有执行权没有决定权
    boks
        7
    boks  
       60 天前
    单表查询 0.05 秒,left join 后几十上百秒?你用啥字段关联的,确定都有索引吗
    SJH0402
        8
    SJH0402  
    OP
       60 天前
    @boks 一般来说是 left join 后使用 bigint 类型的 id 类字段进行关联,然后用 create_time 搭配 where 关键字进行范围过滤

    select xxx from a left join b
    on a.id = b.aid
    where create_time between ... and ...
    SJH0402
        9
    SJH0402  
    OP
       60 天前
    @boks 使用 explain 看过,能触发索引
    openliucongbx
        10
    openliucongbx  
       60 天前
    最好是冗余一下,或者做个新表速度杠杠的
    MetalCore
        11
    MetalCore  
       60 天前
    分表需要围绕索引来, 如果分表前的索引(explain)和分表后的索引是一模一样的, 那肯定不能带来什么提升;
    其次分表数据可能不均衡, 使分表没起效果
    再次分表可以多分细一些, 每个片要很多大, 建议多测试测试
    themostlazyman
        12
    themostlazyman  
       60 天前
    left join 也是扫 A 的全表吧。
    SJH0402
        13
    SJH0402  
    OP
       60 天前
    @themostlazyman 对,扫 A 表全表。我是以 1000w 条数据作的测试
    SJH0402
        14
    SJH0402  
    OP
       60 天前
    @MetalCore 在页面上的条件查询的两个重要组成是手机号和记录的生成时间,考虑到手机号太多,就用的 create_time,我再查一下看换一个字段会不会更好
    themostlazyman
        15
    themostlazyman  
       60 天前
    你为啥不先用条件把 A 表的范围缩小再去 left join 。
    opengps
        16
    opengps  
       60 天前
    分表当然是因为索引本身足够大,减小索引才变快
    thevita
        17
    thevita  
       60 天前
    分表的作用就是:控制 b+tree 的深度, 让每个 tree 的规模在硬件/系统/业务 能接受的范围内.

    方案: 看看 vitess?
    SJH0402
        18
    SJH0402  
    OP
       60 天前
    @themostlazyman 前端页面是个 table 页,默认显示全部数据的前 10 条
    yingqiuQAQ
        19
    yingqiuQAQ  
       60 天前
    1. 大表 Join 小表。2. Join 列类型一致并建立索引。

    如果搜索的数据量大,慢是无可厚非的。 但是建议先贴下 explain 的结果,看下 SQL 是不是全是走的索引
    ZephyrW
        20
    ZephyrW  
       60 天前
    两个分表规则一样的话,设置下 binding-tables ,连表查避免笛卡尔积
    GeekGao
        21
    GeekGao  
       60 天前
    是使用基于日期的 RANGE 分片吗?
    bazingaterry
        22
    bazingaterry  
       59 天前
    述职 ppt 的内容肯定能有可见的提升……
    leonme
        23
    leonme  
       59 天前
    这么大数据量,还 join ?
    laminux29
        24
    laminux29  
       59 天前   ❤️ 2
    什么年代了,还分库分表,给自己添麻烦,直接高主频满血 CPU + DDR5 高主频内存 + PCIE-5 NVME SSD 。

    特别是 PCIE-5 NVME SSD ,多线程 4k 随机读写速度,直接拉满 6GB/s 。贵司什么业务还能超过这个性能?

    自从 SSD 与 Redis 普及后,各种 BBS 、交流群中,以前经常讨论的数据库性能问题,已经很少出现了。
    gerefoxing
        25
    gerefoxing  
       59 天前
    分表就算了吧,给自己和以后找麻烦,单表查询,其他关联信息单独查询+缓存,还有一些可以字段冗余。统计做统计表,定时时时更新
    luoyou1014
        26
    luoyou1014  
       59 天前
    先用 mysql 自带的分区功能,可以达到分表的效果,但完全不用改动业务
    zhaozs1
        27
    zhaozs1  
       59 天前
    # 新增分区 类似下面这样的分区,然后用分区查询看看 优化目标是缩小数据量
    echo "alter table t_car_gps reorganize partition p230 into ( PARTITION p$pend_date2 VALUES LESS THAN ('$pend_date'), partition p230 VALUES LESS THAN (MAXVALUE));"
    dog82
        28
    dog82  
       59 天前
    大表 filter 数据量超过 5%,索引就会失效
    数据库架构设计时要考虑到将来的使用场景
    MoYi123
        29
    MoYi123  
       59 天前
    @SJH0402 你要是能直接把查询优化好, 有人会不听你的方案吗?
    kuituosi
        30
    kuituosi  
       59 天前
    分库分表并不是银弹,还是需要考虑需求。尤其是分表的作用更加有限
    分表的实质是减少数据规模,改善索引 io 效率,但是如果全表扫描的话还不如不分
    分库的实质是采用多节点的计算和 io 资源分摊,来提高查询性能
    oltp 的一个特点就是查询的结果集不大,否则你用任何优化方法效果都不理想
    tikazyq
        31
    tikazyq  
       59 天前
    上 OLAP 或者 PowerBI 吧
    kanepan19
        32
    kanepan19  
       49 天前
    分库分表 是解决写入不问题,不是解决查询问题。
    yuyang1992test
        33
    yuyang1992test  
       35 天前
    一般数据量大才分表吧
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2877 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 14:28 · PVG 22:28 · LAX 07:28 · JFK 10:28
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.