Mysql千万级大表添加字段锁表？

MySQL 大表数据添加新字段

有时候我们在测试环境给一个表添加字段,但是在线上环境添加一个字段,却极其的慢。原因是线上的数据库一般会存有大量的数据(百万级,千万级),基本的添加字段方式在线上数据库已经不太合适了。

> alter table user add column flag tinyint(1) default 0;

基本添加方式,大量数据的表不推荐。执行加字段操作就会锁表，这个过程可能需要很长时间甚至导致服务崩溃。

解决方案

扩展新表方案

创建一个新表user_ext(id,user_id,flag),将user表的id导入user_ext,然后user表和user_ext通过关联就可以间接实现我们的需求。

通过中间表转换过去

创建一个临时的新表，首先复制旧表的结构（包含索引）

> create table user_new like user;

• 给新表加上新增的字段
• 把旧表的数据复制过来

> insert into user_new(filed1,filed2…) select filed1,filed2,… from user

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

建议是在脱机的情况下执行，避免在执行迁移数据过程中有新数据进来，导致新表数据流失不完整

总结

生产环境MySQL添加或修改字段主要通过如下四种方式进行，实际使用中还有很多注意事项

• 直接添加

如果该表读写不频繁，数据量较小（通常1G以内或百万以内），直接添加即可（可以了解一下online ddl的知识）

• 使用pt_osc添加

如果表较大 但是读写不是太大，且想尽量不影响原表的读写，可以用percona tools进行添加，相当于新建一张添加了字段的新表，再将原表的数据复制到新表中，复制历史数据期间的数据也会同步至新表，最后删除原表，将新表重命名为原表表名，实现字段添加

• 先在从库添加 再进行主从切换

如果一张表数据量大且是热表（读写特别频繁），则可以考虑先在从库添加，再进行主从切换，切换后再将其他几个节点上添加字段

• 将现有MySQL版本5.7升级到8.0.12之后的版本

相关文章

Mysql事务

Mysql中的索引

Mysql通过binlog恢复数据