乐趣区

关于mysql:技术分享-MySQL-改表工单后台逻辑实现

jiezi

作者：莫善

某互联网公司高级 DBA。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

本文目录

一、导读

二、背景

三、剖析

四、注释

五、写在最初

一、导读

DBA【MySQL DBA】，其余 DBA 不在本文探讨范畴。

DDL【ALTER TABLE】，其余操作不在本文探讨范畴。

OMM【Only Modifies Metadata】。

NOMM【Not Only Modifies Metadata】。

二、背景

嘟！嘟！嘟！

你好，我今晚要上线新性能，有个改表帮我解决一下呗。

你好，我有个亿级（十亿）表，须要加个字段 / 索引帮我解决一下呗。

你好，我刚加了一个字段，小表半小时还没加完，而且当初如同写不了数据。

DBA 们应该常常会接到这种需要吧。

DDL 操作可能是 DBA 最头疼的一项工作之一，也是最日常的一项工作了。动不动就要加个字段，扩个长度。如果可怜后期设计不合理的零碎，那前期保护起来就真的是叫爹骂娘的问候。如果又可怜是接盘跑了八百年的业务表须要上线新性能加一两字段或者扩个长度啥的，几乎酸爽到飞。

MySQLL 5.6 开始反对 OnlineDDL，美美地敲了回车，等半天还是没反馈，撸了一把又一把，业务催了一遍又一遍，猥琐发育都二胎了，就是没完结，在三胎之后终于跑完了，而后你就躺下刷了会手机，想找点刺激辞别致力工作的一天，后果发现又是绿油油的一天，怒扔手机睡觉去了，未曾想刚酝酿了一会睡意，麻烦又来了。

业务疯狂的打电话，很不愿意的接通了。业务反馈很多订单返回的数据都有问题。机智的你猜到了必定是改表导致了提早，业务还是一顿吐槽，吧啦吧啦说一大堆，还得起来把读流量切到主库，有没有很酸爽。

为啥会这样呢，咱们就来掰扯掰扯这个问题。

MySQL 的 DDL 操作次要有两种形式 copy 和 inplace。copy 表须要全程锁表，对业务影响极大，inplace 不阻塞 dml 操作。然而 MySQL 5.6 推出 OnlineDDL 以前，根本干啥 copy，干啥啥不行，干啥啥锁表。5.6 版本推出 OnlineDDL，DBA 们几乎爱的不要不要了。然而 inplace？就真的很爱了吗。别忘了，inplace 分为 rebuild 和 no-rebuild，这就能解释了为啥很多操作还是很慢了。起因就是：【Not Only Modifies Metadata】。

既然 OnlineDDL 还是有那么多问题，那咱们该咋整呢？这还只是一个 DDL 操作，如果很多呢，几十上百个 DDL 需要，是不是要疯，枸杞红枣肾宝片是不是得搞起来了。然而别慌，还有后招。生存是很美妙的，比方骑上小电驴就能够送外卖了。

废话工夫完结，当初言归正传，来聊聊改表工单零碎 - 后盾逻辑是怎么实现的。

三、剖析

为什么 DBA 那么排挤 DDL 操作？

1）对生产环境的敬畏心，能不做就不做，多做多错。只有是对线上环境操作，就是有危险的，肯定要有这个意识，任何认为简略的操作都可能暗藏着不可预知的危险，线上环境无小事，一出事就是小事。所以能防止人为操作，就尽量避免人为操作。（是自动化不香吗，还是工作不饱和要体现工作量）

2）大表 DDL 操作老本高，白天容易影响业务，产生提早等等。个别都是凌晨搞，熬夜太伤肾。还有就是，大多数 DDL 开销老本极大，简略来说就是【Not Only Modifies Metadata】开销老本都不小，那么就会带来一些问题，比方下面提到的提早，还有就是 mdl 锁问题，还有磁盘 io 争抢，磁盘空间爆满等等，所以是有诸多危险的。
是否是【Only Modifies Metadata】 请参照下表 ：

Operation 5.6 5.7 8.0
Dropping an index YES YES YES
Renaming an index NULL YES YES
Changing the index type YES YES YES
Renaming a column YES YES YES
Setting a column default value YES YES YES
Extending VARCHAR column size NO YES YES
Dropping the column default value YES YES YES
Modifying the definition of an ENUM or SET column YES YES YES
Adding a VIRTUAL column NULL YES YES
Dropping a VIRTUAL column NULL YES YES
Adding a foreign key constraint YES YES YES
Dropping a foreign key constraint YES YES YES
Setting persistent table statistics YES YES YES
Renaming a table YES YES YES

注：8.0 反对疾速加列（instant 算法），然而有很多限度。常见的就是（1）不能应用 after、first 等地位属性，只能增加在表的最初一列。（2）不能是开启压缩的表。（3）不能是有全文索引的表。

3）重复性工作，大部分就是一个回车的事，而后就是盯监控，关注告警等一小时甚至更久，问题是每天还反复干很多。（dba 是干小事的，想想过后是不是依照内核开发要求招你来的）。

当然，在日常工作中，DBA 解决 DDL 操作，个别都是应用 gh-ost 或者 pt-osc 来进行操作，然而这两款工具就没问题了吗？上面我列几个问题：

  1. pt-osc，有触发器限度，产生死锁，且触发概率显著比 ghost 高很多。这是一个危险点，很容易被疏忽。当 pt-osc 异样退出的时候，触发器还是会留在表上，这时候如果先删除长期表（xx_new）而没有先删除触发器，那肯定很酸爽，业务肯定会把你电话打爆。
  2. gh-ost，对网络要求比拟高，如果网络提早比较严重可能会导致改表始终不会实现。对 binlog format 要求是 row，binlog image 要求是 full。还有不反对外键表。
  3. 最重要的一个问题，这类工具都比拟笨，只会 copy 数据。就是说即使是【Only Modifies Metadata】也会傻傻的 copy。对于大表，或者 TPS 比拟大的业务，会写很多 binlog，容易将磁盘打爆。
  4. 对于有慢查问环境，还是容易呈现 mdl 期待。gh-ost 略微好点，然而 pt-osc 的话就比拟爽了，会始终锁。直到人为干涉。

综上，会发现，DDL 真的很烦人，理论工作中遇到的痛点预计比上述的更多。（在此也倡议业务，后期肯定要做好数据库设计，有条件的肯定要让 dba 参加设计，没有条件的肯定要创立条件）

四、注释

先上一个流程图：

提醒：原图链接

https://gitee.com/mo-shan/myo…

次要做如下几点介绍：

  1. 格式化 SQL，须要将业务 SQL 做对立格式化解决，不便前面做 SQL 解析。
  2. 第一次 SQL 解析。依据用户提交的 DDL 做预约义用法判断，查看所有操作是否是满足预约义的。这块逻辑就是把控工单的操作，做到所有操作都可控。啥能做，啥不能做。
  3. 第二次 SQL 解析。判断单条 DDL 是否满足【Only Modifies Metadata】如果满足就间接 OnlineDDL 了，如果不满足就应用 ghost/ptosc 工具。抉择工具的时候会针对上述的优缺点问题做各自的判断，而后优先选择 ghost。说白了就是工单自适应，可依据以后操作，及不同环境做出判断，并调整相应配置生成一个平安正当又高效的执行打算。
  4. 提供 DDL 审核性能，并不是说业务提交什么就都能通过。比如说，是否容许 null 值，是否要求有正文，是否要求标准索引名等等。都能够通过规定开关进行审核。（这点跟上述得预约义用法判断有区别，一个是限度操作，一个是管制操作是否标准正当）
  5. 加上监控，和对异样解决。比方对 mdl 的监控，连接数，沉闷连接数，磁盘空间等等，触发告警会有邮件提醒。
  6. 尽可能的保障每个工单的原子性。就是尽可能的保障每个工单里的每条 DDL 都胜利。要么就都不做，要做就都要胜利，须要留神，这里说的是尽可能，有点回味无穷。
  7. 通过 API 去实现动静配置改表工单，比方正在执行的改表须要暂停，复原，终止，又或者须要获取改表进度。
  8. 开头工作。这点没什么好说的，擦干屁股很重要，防止下次踩坑。

当然要实现上述性能，最大的难度就是如何对业务的 DDL 操作进行解析。只有实现了 SQL 解析，能力打消上痛点实现性能。大家也能够想想怎么实现 SQL 解析这个性能。

上面列几个我在实现的时候遇到问题，供大家思考一下：

  1. SQL 格式化的时候，正文会对你的实现会产生很大的妨碍，那么该如何解决正文的呢。（“/ / comment ” # –”等等这些正文）
  2. SQL 解析的时候要思考【Only Modifies Metadata】分 change、modify，varchar 扩长度（又有局限性）等场景。如何鉴定这些是否满足【OMM】，如果是 modify 的组合模式（modify varchar and not varchar）又该怎么鉴定。
  3. 如何鉴定是否满足 8.0 的疾速加列。
  4. SQL 审核的性能，如何把控 DDL 操作是正当的。
  5. 保留字问题怎么解决呢？不解决，间接返回谬误？那用户体验太差了。有些人可能就会说，用反引号引起来就好了，那么问题来了，用户不晓得这是关键字，他写的 SQL 没有用反引号，那须要工单零碎二次解决给加上，那应该怎么加呢？哪些须要加呢？

以上几点，大家能够想想怎么实现，可能用脑子一过感觉难度如同不大，然而真正落实到代码，应该还是有些难度的。毕竟 ALTER TABLE 语法变幻无穷，组合十分复杂，对于我是如何实现的，有趣味的小伙伴能够看一下 git，欢送吐槽改良。（装置部署及测试都有）

git：https://gitee.com/mo-shan/myo…

须要理解测试流程，能够间接浏览【README.md】测试局部

五、写在最初

日拱一卒，终有一日成车。就是干！

退出移动版