MySQL 大表添加一列的实现

问题参考自：，mysql中，一张表里有3亿数据，未分表，要求是在这个大表里添加一列数据。数据库不能停，并且还有增删改操作。请问如何操作？答案为个人原创

以前老版本 mysql 添加一列的方式：

alter table 你的表 add column 新列 char(128);

会造成锁表，简易过程如下：

新建一个和 table1 完全同构的 table2
对表 table1 加写锁
在表 table2 上执行 alter table 你的表 add column 新列 char(128)
将 table1 中的数据拷贝到 table2
将 table2 重命名为 table1 并移除 table1，释放所有相关的锁

如果数据量特别特别大，那么锁表时间很长，期间所有表更新都会阻塞，线上业务不能正常执行。

针对 mysql 5.6（不包含）之前的版本，通过触发器将一个表的更新在另一个表上重复，并进行数据同步，当数据同步完成时，业务上修改表名为新表并发布。业务不会暂停。触发器设置类似于：

create trigger person_trigger_update after update on 原有表 for each row 
begin set @x = "trigger update";
replace into 新表 select * from 原有表 where 新表.id = 原有表.id;
end if;
end;

mysql 5.6（包含） 以后的版本引入了在线 ddl 的功能:

alter table 你的表 , algorithm [=] {default|instant|inplace|copy}, lock [=] { default| none| shared| exclusive }

其中的参数：

algorithm：

default：默认方式，在 mysql 8.0中，如果未显示指定 algorithm，那么会优先选择 instant 算法，如果不行再使用 inplace 算法，如果不支持 inplace 算法则使用 copy 的方式完成
instant：8.0 中新添加的算法，添加列是立即返回。但是不能是虚拟列。这个原理很简单，对于新建一列，表所有原有数据并不是立刻发生变化，只是在表字典里面记录下这个列和默认值，对于默认的 dynamic 行格式（其实就是 compressed 的变种），如果更新了这一列则原有数据标记为删除在末尾追加更新后的记录。这样做就是没有提前预留出列空间，之后更新可能经常会发生行记录空间变动。但是对于大多数业务，都是最近的时间的记录才会修改，所以问题不大。
inplace：在原表上直接进行修改，不会拷贝临时表，可以逐条记录修改，不会产生大量的 undolog 以及 redolog，不会占用很多 buffer。可以避免重建表带来的io和cpu消耗，保证期间依然良好的性能和并发。
copy：拷贝到临时新表上进行修改。由于记录拷贝，会产生大量的 undolog 以及 redolog，并占用很多 buffer，对业务性能有影响。

lock：

default：和 algorithm 的 default 类似
none：无锁，允许并发读取和更新表
shared：共享锁，允许读取不允许更新
exclusive：不允许读取和更新

各个版本支持的在线 ddl 修改使用的算法的对比：

参考文档：

mysql 5.6:https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.htmlmysql

5.7：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.htmlmysql

8.0：

可以通过：

alter table 你的表 add column 新列 char(128), algorithm=instant, lock=none;

类似的语句，实现在线增加字段。最好还是明确 algorithm 以及 lock，这样执行 ddl 的时候能明确知道到底会对线上业务有多大影响。

同时，执行在线 ddl 的过程大概是：

可以看出，在开始阶段需要 metadata lock，metadata lock 是在 5.5 才引入到mysql，之前也有类似保护元数据的机制，只是没有明确提出 metadata lock 概念而已。但是 5.5 之前版本(比如5.1)与5.5之后版本在保护元数据这块有一个显著的不同点是，5.1对于元数据的保护是语句级别的，5.5对于metadata的保护是事务级别的。所谓语句级别，即语句执行完成后，无论事务是否提交或回滚，其表结构可以被其他会话更新；而事务级别则是在事务结束后才释放 metadata lock。

引入 metadata lock 后，主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话a在2次查询期间，会话b对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话a执行了多条更新语句期间，另外一个会话b做了表结构变更并且先提交，就会导致 slave 在重做时，先重做 alter，再重做 update 时就会出现复制错误的现象。

如果当前有很多事务在执行，并且有那种包含大查询的事务，例如：

start transaction;
select count(*) from 你的表

这样类似的会执行较长时间的事务，也会阻塞。

所以，原则上：

避免大事务
在业务低峰去做表结构变化

到此这篇关于mysql 大表添加一列的实现的文章就介绍到这了,更多相关mysql 大表添加一列内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

MySQL 大表添加一列的实现

相关推荐