亲宝软件园·资讯

展开

MySQL 大表添加一列 MySQL 大表添加一列的实现

干货满满张哈希 人气:0
想了解MySQL 大表添加一列的实现的相关内容吗,干货满满张哈希在本文为您仔细讲解MySQL 大表添加一列的相关知识和一些Code实例,欢迎阅读和指正,我们先划重点:MySQL,大表添加一列,下面大家一起来学习吧。

问题参考自: https://www.zhihu.com/question/440231149 ,mysql中,一张表里有3亿数据,未分表,要求是在这个大表里添加一列数据。数据库不能停,并且还有增删改操作。请问如何操作?答案为个人原创

以前老版本 MySQL 添加一列的方式:

ALTER TABLE 你的表 ADD COLUMN 新列 char(128);

会造成锁表,简易过程如下:

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

针对 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:

LOCK:

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

image

参考文档:

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:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

可以通过:

ALTER TABLE 你的表 ADD COLUMN 新列 char(128), ALGORITHM=INSTANT, LOCK=NONE;

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

同时,执行在线 DDL 的过程大概是:

image

可以看出,在开始阶段需要 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 你的表

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

所以,原则上:

加载全部内容

相关教程
猜你喜欢
用户评论