MySQL大表变更“字段/索引“可能会引发的锁表问题

参考：

• 亿级大表在线不锁表变更字段与索引_华为云官方博客-CSDN博客
• mysql该字段锁表_Mysql笔记（七）~ 大表加字段不锁表方案_weixin_39871162的博客-CSDN博客

声明：加索引可以不锁表，MySQL 5.6 以上的新特性，可参考：MySQL 5.6版本新特性之Online DDL：在线变更表结构且不阻塞数据库服务

背景：

        大家在日常工作中，往往需要对数据库的表结构做变更，一般涉及到增删字段，修改字段属性等ALTER的操作。然而，在大表场景下，特别是千万级、亿级的大表，如果处理不当，这些操作往往会引发锁表的巨大隐患，特别是在生产环境中，一旦在变更表结构过程中，出现了长时间锁表，会导致用户产生的数据长时间无法正常变更到表中，进而导致服务功能异常，结果将是灾难性的。

        一般执行这种Alter类型的变更，我们可能有以下的想法：

1. 停服，在停服期间做表结构的变更，自然就可以防止对用户产生影响。但是，很多场景是不允许停服的。而且如果表的数据量达到上亿，那么需要停服时间可能需要十几个小时，甚至更长，这是极不现实的；
2. 凌晨执行，在用户较少的时间段内，做变更，尽量减少对用户产生影响。但是如果出现锁表的话，万一有用户使用服务，服务将不可用；
3. 使用临时表，但是缺点是复制数据到新表期间，如果用户在这期间做了update或delete操作，且数据修改发生在之前已经复制完成的部分，那么将无法感知到这部分数据，导致丢失掉用户的操作数据，风险太大；
4. 使用存储过程分批更新，缺点是执行时间会很久，且有可能影响到用户的DDL操作。因为为了防止每次循环修改时，锁住太多数据行，我们需要控制每次更新数据的行数，粒度不能太大，否则很有可能会锁住用户正在操作的数据行。

具体操作步骤：

1. 创建一个临时的新表，首先复制旧表的结构(包含索引)
   create table new_table like old_table;

2. 给新表加上新增的字段

3. 把旧表的数据复制过来（需注意复制过程也需要时间，这时如果有已经复制完的数据又被用户修改后，可能会导致复制后的数据不能保持最新的。所以原表如果有记录了数据的写入时间字段就最好了（比如modify_time），可以找到执行这一步操作之后的数据，并重复导入到新表，直到数据差异很小。不过还是会可能损失极少量的数据。）
   insert into new_table(filed1,filed2…) select filed1,filed2,… from old_table;

4. 删除旧表，重命名新表的名字为旧表的名字

建议：

1. 尽量选择请求流量小的时间执行
2. 执行时先看一下有没有未提交的事务，注意查看事物 information_schema.innodb_trx 表
3. 随时关注服务器日志状况，已有问题要先行解决
4. 后续可先在预发环境或测试环境先行模拟，评估风险