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

又是被 mysql 加字段搞郁闷的一天

  •  
  •   fanyingmao · 195 天前 · 5377 次点击
    这是一个创建于 195 天前的主题,其中的信息可能已经有所发展或是发生改变。
    表一变大加字段就慢,关联查询就慢。
    策划不能一开始想好要什么数据,总是后面加。
    mysql 就不能像 mongo 一样字段想怎么加就怎么加,表一大就加不了字段了。
    像这关联查询加了索引还是一样慢,明明数据就在那里就是取不出来。
    SELECT equip_id, equip_name, COUNT(1) num,SUM(cmp) win FROM (SELECT * FROM a WHERE happend_time>=0 and happend_time<=1704211199) a LEFT JOIN b ON a.role_id = b.role_id and a.unique = b.unique GROUP BY equip_id order by num DESC;
    最后估计还是加扩展表,代码和数据都那么冗余,代码写得都膈应。
    啥时候能把这种脏活丢给 ai 或者工具人干啊。
    第 1 条附言  ·  195 天前
    后面和策划说把旧数据备份下,清空表加字段了,反正是日志表和策划脑子一热的需求。
    48 条回复    2024-01-08 12:03:39 +08:00
    Maboroshii
        1
    Maboroshii  
       195 天前   ❤️ 1
    explain 不贴一下吗?
    yunye
        2
    yunye  
       195 天前   ❤️ 1
    别闹 你就是工具人
    Morii
        3
    Morii  
       195 天前
    升到 5.8 试试呢?
    adoal
        4
    adoal  
       195 天前
    怪选型的人
    Rache1
        5
    Rache1  
       195 天前   ❤️ 4
    MySQL 8 ,腾讯团队贡献了一个 feature ,加字段可以很快呀(符合要求的情况下)

    MySQL · 引擎特性 · 8.0 Instant Add Column 功能解析
    http://mysql.taobao.org/monthly/2020/03/01/
    Rache1
        6
    Rache1  
       195 天前
    而且,MySQL 5.7.8 开始就支持 JSON 字段了,你也可以创建一个 JSON 字段呐
    pkoukk
        7
    pkoukk  
       195 天前   ❤️ 1
    mongo 不是早都没人用了么
    hucw21750
        8
    hucw21750  
       195 天前
    唯一不变的就是需求永远在变 🐶
    maocat
        9
    maocat  
       195 天前 via iPhone
    @pkoukk 兄弟真是张口就来啊
    lerosua
        10
    lerosua  
       195 天前
    @Rache1 这种叫管杀不管埋
    Morii
        11
    Morii  
       195 天前
    @pkoukk 最起码我们还在用,n 亿的数据
    lsk569937453
        12
    lsk569937453  
       195 天前
    fanyingmao
        13
    fanyingmao  
    OP
       195 天前
    fanyingmao
        14
    fanyingmao  
    OP
       195 天前
    mysql 用的是 5.7.36 ,估计是不会去升级了。
    pkoukk
        15
    pkoukk  
       195 天前
    @maocat #9 不好意思,我身边统计学了。这三年来用 mongo 的项目逐渐迁移走了,公司的运维团队也不维护 mongo 了。很久也没在技术文章上看到它的影子了,再加上当时用它的时候体验确实不佳,以为已经被普遍淘汰了。
    june4
        16
    june4  
       195 天前
    mongodb 几年前是火过一波,然而这一点好处远大于缺点
    awalkingman
        17
    awalkingman  
       195 天前
    @lerosua 开发一时爽 维护火葬场
    awalkingman
        18
    awalkingman  
       195 天前
    @lerosua 或者叫 开发一时爽 维护火葬场
    changnet
        19
    changnet  
       195 天前
    你这是游戏服务器的表?一般也不大的吧,顶多就几千万条,不会耗多少时间。大了都会有分布式来拆分。

    想要需求不变是不可能的,一般也不会频繁改表。而且游戏服务器都有缓存,一般不会直接从数据库查,查得慢问题也不大。

    还是说你这是游戏后台?
    trio
        20
    trio  
       195 天前
    pt-online-schema-change
    wonderfulcxm
        21
    wonderfulcxm  
       195 天前 via iPhone
    @Rache1 op 面临的是查询性能的问题,腾讯这个只是提高了增加字段的效率,并没有提高查询的效率。
    Rache1
        22
    Rache1  
       195 天前
    @wonderfulcxm #21 他的标题说的是加字段,正文前半部分也是在说加字段不方便
    wonderfulcxm
        23
    wonderfulcxm  
       195 天前 via iPhone
    @Rache1 不好意思,你说得对,再看了一下,加字段慢也是一个问题,这也算解决了一个痛点。
    ser3w
        24
    ser3w  
       195 天前
    无锁变更整起来啊,我们记录表上亿的,无锁变更加字段要几小时
    fanyingmao
        25
    fanyingmao  
    OP
       195 天前
    @changnet 是游戏后台,我在测试服 8000 多条,ALTER 语句都用了 9 秒,线上几千万条不知要多久,而且停服时处理不会有太多时间。
    guofushan2903
        26
    guofushan2903  
       195 天前
    如果是 5.7mysql 用 gh-ost 工具加字段不影响生产; 8 版本 mysql 直接加字段
    guofushan2903
        27
    guofushan2903  
       195 天前
    需不需要一个 dba 我可以 狗头 jianzhi
    8355
        28
    8355  
       195 天前
    @Rache1 #5 按照常理来说这个域名应该是阿里
    Rache1
        29
    Rache1  
       195 天前   ❤️ 1
    @8355 #28 😂 我只是随手贴的这个文章地址,在 MySQL 官方也有相关的介绍。

    MySQL :: MySQL 8.0: InnoDB now supports Instant ADD COLUMN
    https://dev.mysql.com/blog-archive/mysql-8-0-innodb-now-supports-instant-add-column/
    brader
        30
    brader  
       195 天前
    你这活很大,喊公司招个 dba 啊,这岗位不就来了吗
    adoal
        31
    adoal  
       195 天前
    趁着 5.7 EoL 的机会,渲染一下恐怖气氛,做一次业务系统大改版……
    bigboss519
        32
    bigboss519  
       195 天前
    在线表变更,这不 dba 基本操作吗
    leimu012
        33
    leimu012  
       195 天前
    @Rache1 #6 现在做的项目就疯狂用 json 字段,用的真的想吐,mysql 存 json ,接口文档的字段也是 json ,嵌套好几层
    nice2cu
        34
    nice2cu  
       195 天前
    这种联表统计查询在大数据表肯定是不让的,改成 es 或者 ck 。。还有我们这几亿的表也是可以扩的,找个深夜客户不活跃的时间点执行
    deorth
        35
    deorth  
       195 天前 via Android
    json+虚拟列
    buchikoma
        36
    buchikoma  
       195 天前
    @pkoukk #7 mongo 还是用的多的,公有云数据库三大主力营收就是 mysql 、redis 、mongo
    BQsummer
        37
    BQsummer  
       195 天前
    无锁 ddl 确实是 dba 基本操作, 低版本 mysql 一般是复制一张表, 数据追上后通过重命名方式切换, 只有切的一瞬间有感
    est
        38
    est  
       195 天前
    用 json
    buaacss
        39
    buaacss  
       195 天前
    如果不用阿里云的 dms 之类的东西,可以用 gh-ost 。监听 binlog ,开始复制表,apply binlog, lock + rename table 。一切都是无感的非常好用。

    分享一下之前写做的关于 gh-ost 的笔记。

    对于数据库运维人员来说,MySQL 的大表表结构变更一直都是个麻烦事,为了尽量不影响业务,业内常用的解决方案无外乎三种,

    一是利用 Percona 的 pt-online-schema-change,Facebook 的 OSC 等三方工具;

    二是在备库修改通过切换实现滚动变更;

    三则是升级 MySQL 到 5.6/5.7 通过官方 Online DDL 实现部分变更。

    然而,引入触发器带来的锁竞争问题,主备切换带来的附加成本以及 Online DDL 的局限性都不让 DBA 省心。

    gh-ost 的设计号称无触发器,可监控,可动态调整暂停等,更重要的是切换方案的优秀设计。下面就介绍下其实现原理和 cut-over(新旧表切换)的详细过程。

    原理
    gh-ost 不依赖于触发器,是因为他是通过模拟从库,在 row binlog 中获取增量变更,再异步应用到 ghost 表的。

    有三种功能模式:

    a.连接从库间接应用到主库/c 在从库上进行修改

    连接从库
    校验完后,在主库创建新表
    迁移原表数据到新表
    模拟从库的从库,拉取解析增量 binlog 应用到主库
    cut-over 阶段,用新表替换掉原表

    b.连接主库直接修改

    直连主库
    主库上创建 ghost 表
    新表(ghost 表)上直接 alter 修改表结构
    迁移原表数据到新表
    拉取解析 binlog 事件,应用到新表
    cut-over 阶段,用新表替换掉原表

    两者不同的点就在于,通过连接从库来进行变更,对主库的性能影响最小

    变更流程
    以直连主库修改为例,详细介绍 gh-ost 做了哪些操作:

    校验
    测试 db 是否可连通,并且验证 database 是否存在
    确认连接实例是否正确
    权限验证 show / gh-ost / grants for current_user()
    binlog 验证,包括 row 格式验证和修改 binlog 格式后的重启 replicate
    原表存储引擎,外键,触发器检查,行数预估等

    初始化
    初始化 stream 的连接,添加 binlog 的监听
    初始化 applier 连接,创建 ghosttable 和 changelogtable
    判断是否符合迁移条件,写入结果到 tablesInPlace channel

    迁移
    迁移过程中,row copy 和 binlog apply 是同时进行,其中原则是 binlog apply 的优先级一定大于 row copy 操作的优先级。

    状态展示
    Copy: 9451000/10000060 94.5%; Applied: 31; Backlog: 0/100; Time: 8m26s(total), 8m26s(copy); streamer: mysql-bin.000040:68321839; ETA: 29s

    cut-over
    尝试 lock 原表
    成功后,进行 rename 原子性操作,被 block 住
    unlock 原表,rename 完成切换
    后续中间表清理工作

    迁移和切换的细节实现
    关于 gh-ost 的实现,这里只挑了 rowcopy 和 binlog apply 的顺序问题和 rename 过程做了详细解析。

    在数据迁移的过程中,数据变量有三个,暂且分为,A:来自原表的 rowcopy ,B:binlog 的 apply ,C:对原表的 dml 操作。

    C 操作会记录 binglog 从而触发 B 操作,所以 B 操作一定在 C 操作的后面,因此一般情况下,会有 ACB ,CBA 两种组合,同时特殊情况如 binlog apply 延迟,则会有 CAB 这种组合。

    分析三种组合之前要先了解 gh-ost 在 sql 改写方面是如何映射的:

    RowCopy select insert ignore into 复制数据
    BinlogApply insert replace into 复制期间增加数据

    update update 复制期间变更数据

    delete delete 复制期间删除数据

    在上述原则的基础上,我们再来逐个分析不同顺序组合的影响:

    insert 操作
    binlog 是最权威的,gh-ost 的原则是以 binlog 优先。

    假设一段数据正在复制到新表时产生了插入,我们来分情况看一下如何保证数据一致

    复制需要先从原表 select 再插入到新表,假设插入( binlog )发生在 select 之前

    那么新表在数据到达之前会先从 binlog 插入将数据插入,复制的数据到达后,因为有主键的原因,所以 insert ignore into 会忽略这行数据

    如果插入( binlog )发生在 select 之后

    那么 binlog 在到达新表后会使用 replace into 覆盖之前复制过来的数据

    无论是先还是后都可以保证以 binlog 为准

    update/delete 操作
    一般情况下:

    ACB 组合,即对已经 rowcopy 过的数据,出现对原表的 update/delete 操作。这时候会全部通过 binlog apply 执行,注意 binlog apply 的 update 是对某一条记录的全部列覆盖更新,所以不会有累加的问题。

    CBA 组合,即对尚未迁移的数据,出现对原表的 update/delete 操作。这时候对新表的 binlog apply 会是空操作,具体数据由 rowcopy 迁移。

    特殊情况下:

    CAB 组合,即先对原表更新完以后,rowcopy 在 binlog apply 之前把数据迁移了过去,而在 binlog event 过来以后,会再次应用。这里看似有问题,但是结合 gh-ost 的 binlog aplly 的 sql 映射规则,insert 操作会被 replace 重新替换掉,update 会更新对应记录全部行,delete 会是空操作。最终数据还是一致的状态。

    cut-over 过程
    在 pt-osc 或者 online ddl 中,最后的 rename 操作一般是耗时比较短,但如果表结构变更过程中,有大查询进来,那么在 rename 操作的时候,会触发 MDL 锁的等待,如果在高峰期,这就是个严重的问题。所以 gh-ost 是怎么做的呢?

    gh-ost 利用了 MySQL 的一个特性,就是原子性的 rename 请求,在所有被 blocked 的请求中,优先级永远是最高的。gh-ost 基于此设计了该方案:一个连接对原表加锁,另启一个连接尝试 rename 操作,此时会被阻塞住,当释放 lock 的时候,rename 会首先被执行,其他被阻塞的请求会继续应用到新表。

    migrator.go:iterateChunks() 函数来确定何时开始 cut - over

    具体切换流程如下
    会话 A START

    CREATE table tbl_old
    防止 rename 过早执行

    LOCK TABLES tbl WRITE, tbl_old WRITE
    通过 lock_wait_timeout 设置为 2s 控制超时,超时失败会重试次数为配置 default-retries ,默认 60 次

    新的请求进来,关于原表的请求被 blocked
    RENAME TABLE tbl TO tbl_old, ghost TO tbl , 同样被 blocked

    新的请求进来,关于原表的请求被 blocked
    检查是否有 blocked 的 RENAME 请求,通过 show processlist
    DROP TABLE tbl_old
    UNLOCK TABLES
    END

    不同阶段失败后如何处理
    如果第一步失败,退出程序
    如果会话 A 建表成功,加锁失败,退出程序,未加锁
    rename 请求来的时候,会话 A 死掉,lock 会自动释放,同时因为 tbl_old 的存在 rename 也会失败,所有请求恢复正常
    rename 被 blocked 的时候,会话 A 死掉,lock 会自动释放,同样因为 tbl_old 的存在,rename 会失败,所有请求恢复正常
    rename 死掉,gh-ost 会捕获不到 rename ,会话 A 继续运行,释放 lock ,所有请求恢复正常
    lovelylain
        40
    lovelylain  
       195 天前 via Android
    @awalkingman protobuf 维护转 json 存储 DB ,维护成本还好吧,比加 db 字段方便多了,缺点是不支持检索,但很多后加字段都没有检索需求
    dog82
        41
    dog82  
       194 天前
    用土方法:新建表-迁移数据-重命名
    billylam
        42
    billylam  
       194 天前
    下次设计数据库时预留一些字段
    littlewing
        43
    littlewing  
       194 天前
    MySQL 早就支持 Instant add column 了,升级 MySQL 吧
    1018ji
        44
    1018ji  
       194 天前
    这个 sql 不用 gpt 优化下?
    cheng6563
        45
    cheng6563  
       194 天前
    MySQL 不是早支持加字段不影响读写了。。。
    StarkWhite
        46
    StarkWhite  
       194 天前
    你写的 sql 例子,又有 join 又有子查询,mongodb 不好实现吧
    iv8d
        47
    iv8d  
       194 天前
    很早前是预留部分字段改名用,现在升级 8 了基本不用关注这个
    awalkingman
        48
    awalkingman  
       190 天前
    @lovelylain 如果只是存储,那甚至可以用 text 类型存了,取出来再解析。就是怕 where 怕 join
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1030 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 43ms · UTC 19:26 · PVG 03:26 · LAX 12:26 · JFK 15:26
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.