● 如何正确得给线上表加字段？

1. 问题：怎么给线上表加字段？

工作中最常遇到的问题，怎么给线上频繁使用的大表添加字段？ 比如：给下面的用户表（user）添加年龄（age）字段。

ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年龄';

如果在线上数据库这样操作，修改表结构的时候，MySQL会自动添加表锁，并且是写锁，会阻塞后续的所有读写请求，造成非常严重的后果，整个服务都有宕机的风险：

2. 线上服务宕机的原因

为什么会出现这种情况呢？

原因是在执行查询语句的时候，MySQL自动加了MDL锁（metadata lock，即元数据锁）。

不行的话，我们可以再执行一下show processlist命令，查看有哪些正在执行的进程：

可以清楚的看到Session2和Session3的语句正在等待MDL锁， Waiting for table metadata lock。

MDL锁的作用是什么？

为了保证并发操作下数据的一致性。如果一个事务正在执行中，另一个在这时修改了表结构，不但可能导致当前事务出现不可重复读的问题，还有可能连事务都无法提交。

3. 如何优雅的给线上表加字段

从MySQL5.6版本开始增加了Online DDL，作用就是在执行DDL的时候，允许并发执行DML。简单翻译就是修改表结构的时候，也能同时支持并发执行增删查改操作。

从MySQL8.0版本开始又优化了Online DDL，支持快速添加列，可以实现给大表秒级加字段。

具体用法就是在DDL语句后面增加两个参数ALGORITHM和LOCK。比如：

ALTER TABLE `user` ADD `age` int NOT NULL DEFAULT '0' COMMENT '年龄', 
ALGORITHM=Inplace, 
LOCK=NONE;

这两个参数分别是干嘛用的？有哪些选项呢？

ALGORITHM可以指定使用哪种算法执行DDL，可选项有：

  • Copy：

    拷贝方式，MySQL5.6 之前 DDL 的执行方式，过程就是先创建新表，修改新表结构，把旧表数据复制到新表，删除旧表，重命名新表。执行过程非常耗时，产生大量的磁盘IO和占用CPU，还有使Buffer poll失效，而且需要锁住旧表，性能较差，现在基本很少使用。

  • Inplace：

    原地修改，MySQL5.6开始引入的，优点是不会在Server层发生表数据拷贝，过程中允许并发执行DML操作。过程就是先添加MDL写锁，执行初始化操作，然后降级为MDL读锁，执行DDL操作（比较耗时，允许并发执行DML操作），升级为MDL写锁，完成DDL操作。

  • Instant：

    快速修改，MySQL8.0开始引入的，可以实现快速给大表添加字段。

性能依次是，Instant > Inplace > Copy。

LOCK可以指定执行过程中，是否加锁，可选项有：

NONE

不加锁，允许DML操作。

SHARED

加读锁，允许读操作，禁止DML操作。

DEFAULT

默认锁模式，在满足DDL操作前提下，默认锁模式会允许尽可能多的读操作和DML操作。

EXCLUSIVE

加写锁，禁止读操作和DML操作。

Online DDL并不是支持所有DDL操作，看一下到底支持哪些操作？

操作 Instant Inplace Rebuilds Table 允许并发DML 仅修改元数据
添加列 Yes Yes No Yes No
删除列 No Yes Yes Yes No
重命名列 No Yes No Yes Yes
更改列顺序 No Yes Yes Yes No
设置列默认值 Yes Yes No Yes Yes
更改列数据类型 No No Yes No No
设置VARCHAR列大小 No Yes No Yes Yes
删除列默认值 Yes Yes No Yes Yes
更改自动增量值 No Yes No Yes No
设置列为null No Yes Yes Yes No
设置列not null No Yes Yes Yes No

像最常见的添加列就可以使用Instant，而像删除列、重命名列、更改列数据类型就只能使用Inplace了。

来源： 《线上服务宕机，码农试用期被毕业，原因竟是给MySQL加个字段》