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

[fanyingmao]

表一变大加字段就慢，关联查询就慢。
策划不能一开始想好要什么数据，总是后面加。
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 或者工具人干啊。

后面和策划说把旧数据备份下，清空表加字段了，反正是日志表和策划脑子一热的需求。

[Maboroshii]

explain 不贴一下吗？

[yunye]

别闹 你就是工具人

[Morii]

升到 5.8 试试呢？

[adoal]

怪选型的人

[Rache1]

MySQL 8 ，腾讯团队贡献了一个 feature ，加字段可以很快呀（符合要求的情况下）

MySQL · 引擎特性 · 8.0 Instant Add Column 功能解析
http://mysql.taobao.org/monthly/2020/03/01/

[Rache1]

而且，MySQL 5.7.8 开始就支持 JSON 字段了，你也可以创建一个 JSON 字段呐

[pkoukk]

mongo 不是早都没人用了么

[hucw21750]

唯一不变的就是需求永远在变 🐶

[maocat]

@pkoukk 兄弟真是张口就来啊

[lerosua]

@Rache1 这种叫管杀不管埋

[Morii]

@pkoukk 最起码我们还在用，n 亿的数据

[lsk569937453]

https://imgur.com/a/NNn8cau

[fanyingmao]

@Maboroshii
explain
https://sm.ms/image/nMkZO4IBsTqNDfr

[fanyingmao]

mysql 用的是 5.7.36 ，估计是不会去升级了。

[pkoukk]

@maocat #9 不好意思，我身边统计学了。这三年来用 mongo 的项目逐渐迁移走了，公司的运维团队也不维护 mongo 了。很久也没在技术文章上看到它的影子了，再加上当时用它的时候体验确实不佳，以为已经被普遍淘汰了。

[june4]

mongodb 几年前是火过一波，然而这一点好处远大于缺点

[awalkingman]

@lerosua 开发一时爽 维护火葬场

[awalkingman]

@lerosua 或者叫 开发一时爽 维护火葬场

[changnet]

你这是游戏服务器的表？一般也不大的吧，顶多就几千万条，不会耗多少时间。大了都会有分布式来拆分。

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

还是说你这是游戏后台？

[trio]

pt-online-schema-change

[wonderfulcxm]

@Rache1 op 面临的是查询性能的问题，腾讯这个只是提高了增加字段的效率，并没有提高查询的效率。

[Rache1]

@wonderfulcxm #21 他的标题说的是加字段，正文前半部分也是在说加字段不方便

[wonderfulcxm]

@Rache1 不好意思，你说得对，再看了一下，加字段慢也是一个问题，这也算解决了一个痛点。

[ser3w]

无锁变更整起来啊，我们记录表上亿的，无锁变更加字段要几小时

[fanyingmao]

@changnet 是游戏后台，我在测试服 8000 多条，ALTER 语句都用了 9 秒，线上几千万条不知要多久，而且停服时处理不会有太多时间。

[guofushan2903]

如果是 5.7mysql 用 gh-ost 工具加字段不影响生产； 8 版本 mysql 直接加字段

[guofushan2903]

需不需要一个 dba 我可以 狗头 jianzhi

[8355]

@Rache1 #5 按照常理来说这个域名应该是阿里

[Rache1]

@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]

你这活很大，喊公司招个 dba 啊，这岗位不就来了吗

[adoal]

趁着 5.7 EoL 的机会，渲染一下恐怖气氛，做一次业务系统大改版……

[bigboss519]

在线表变更，这不 dba 基本操作吗

[leimu012]

@Rache1 #6 现在做的项目就疯狂用 json 字段，用的真的想吐，mysql 存 json ，接口文档的字段也是 json ，嵌套好几层

[nice2cu]

这种联表统计查询在大数据表肯定是不让的，改成 es 或者 ck 。。还有我们这几亿的表也是可以扩的，找个深夜客户不活跃的时间点执行

[deorth]

json+虚拟列

[buchikoma]

@pkoukk #7 mongo 还是用的多的，公有云数据库三大主力营收就是 mysql 、redis 、mongo

[BQsummer]

无锁 ddl 确实是 dba 基本操作, 低版本 mysql 一般是复制一张表, 数据追上后通过重命名方式切换, 只有切的一瞬间有感

[est]

用 json

[buaacss]

如果不用阿里云的 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]

@awalkingman protobuf 维护转 json 存储 DB ，维护成本还好吧，比加 db 字段方便多了，缺点是不支持检索，但很多后加字段都没有检索需求

[dog82]

用土方法：新建表-迁移数据-重命名

[billylam]

下次设计数据库时预留一些字段

[littlewing]

MySQL 早就支持 Instant add column 了，升级 MySQL 吧

[1018ji]

这个 sql 不用 gpt 优化下？

[cheng6563]

MySQL 不是早支持加字段不影响读写了。。。

[StarkWhite]

你写的 sql 例子，又有 join 又有子查询，mongodb 不好实现吧

[iv8d]

很早前是预留部分字段改名用，现在升级 8 了基本不用关注这个

[awalkingman]

@lovelylain 如果只是存储，那甚至可以用 text 类型存了，取出来再解析。就是怕 where 怕 join