【MySQL】大数据表里面插入字段的操作

最近在面试找工作的时候遇到一些之前没有思考或者接触过的问题，其中一大块是MySQL的相关知识点以及一些实战中遇到的问题
题目：
向一个3000w条数据的表里面新增一个字段，需要如何操作？

分析：
咋一听这个问题，感觉好像完全没有头绪，如果没有提前准备的话，可能真的比较懵。按照知识库的储备应该是直接写sql：alter table xxx add column [column_name] type comment ‘…’;这样就可以了

但是实际上对于大批量的数据直接使用这种做法的话，很容易卡死，因为数据量过大导致的MySQL处理能力上的不足会对于数据库的响应造成一定的影响，同时可能会导致数据库的不可用，然后造成一系列的多米诺骨牌效应。

解决方案：

思路一

那么正确的插入姿势是什么样的呢？
在这里其实我们可以想到的一种方案是新建一个空表，在新建的表里面里面插入字段，然后把旧表里面的输入插入到这个空表里面来，等数据迁移完成这之后，再更改表名即可。
下面是具体的步骤：

1. 新建一个临时表，假设表名为user_info_temp, 之前的表名为：

> create table user_info_temp like user_info;

2.向新的表里面加入我们要加的字段

alter table user_info_temp add column hobby varchar(32) default null comment "爱好";

3. 把旧表的数据插入到新表种

insert into user_info_temp(uid,username,pwd,createtime,updatetime) select uid,username,pwd,createtime,updatetime from user_info;

4.对表进行重命名。

rename table user_info to user_info_bak;
rename table user_info_temp to user_info;

但是实际操作中，会出现卡死的现象，经过仔细分析，我们会发现，新的表除了拷贝了之前表的字段，同时也获取了原表里面的主键和索引，我们知道在有索引和主键的情况下，数据在写入的时候除了写入字段的值以为还会在磁盘上额外写入字段的索引信息，对于3000w条数据，这一项工作同样是一笔巨大的开销，所以就出现了卡死的现象，那么如何改进呢？

方案二：

方案二，我们在方案一的基础上删除主键和索引先，所以实现的顺序如下。

1. 新建临时表
   create table user_info_temp like user_info;

2.删除之前的索引和主键

alter table user_info_temp drop PRIMARY KEY;
alter talbe user_info_temp drop index index_name; // 注意修改这里的索引名称

3.新增新的字段

alter table user_info_temp add column hobby varchar(32) default null comment "爱好"; 

4.向新表迁入数据

insert into table user_info_temp(uid,username,pwd,createtime,updatetime) select  uid,username,pwd,createtime,updatetime from user_info;

5.向新表里面添加对应的主键和索引

alter table user_info_temp add PRIMARY KEY(uid); //这里的uid换成你们自己的字段
alter table user_info_temp add index index_name(column1,column2); //这里你们懂的，index_name,column1,column2 换成你们自己的

6.重命名

rename table user_info to user_info_bak;
rename table user_info_temp to user_info;

在新的方案里面，因为我们先去掉了主键和索引，所以在迁移数据的时候，大大减少了磁盘IO的次数，方案改进之后，从开始到迁移3100万数据卡死，执行了3个小时左右。第二次执行，删除主键和索引，执行insert into … select …语句，全部数据迁移只用了半小时。数据迁移后再把主键和索引加上，添加主键用了20分钟，添加5个索引的耗时都在10分钟以下，一个多小时完成整张表的迁移工作。

你以为这就完了吗？
在实际应用中，我们不需要考虑是否停服的问题吗？
如果是停服更新的话，新旧表数据就一定是一致的，但是也有一些情况是不停服更新的，那么我们就需要关注在迁移的过程中，user_info里面新增或者更新的数据，这又需要分开来看：对于新增的数据批量写入到新的表里面，但是注意不要带上主键id写入，因为新的user_info表可能也写入了新的数据，你直接带上主键id插入到新的user_info表，必然出现主键冲突的现象。
对于更新的数据，我们可以根据表里面的update_time时间来查找哪些是在
我们迁移的过程中发生了变更的数据，然后批量更新到新的user_info表即可。

今天的内容就是这些了，感谢您的阅读！^_