【MySQL】大数据量表添加字段

本文主要是介绍【MySQL】大数据量表添加字段，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

前几天因为需求调整，需要在某张表中添加一个新的字段，而要添加字段的表，正好是我们库中最大的一张表，表中大约有3300万条记录，占用空间32.5G，直接在表上添加字段，很容易卡死。

​

网上找了一下大数据量表添加字段的帖子，帖子数量还是很多的，套路也都差不多，主要就是两种方案：

方案一：通过创建备份表的方式，步骤如下：

1.创建临时表account_bill_temp

create table account_bill_temp like account_bill;

2.在新表中添加字段

alter table account_bill_temp add columu bill_id varchar(64) comment '账单id' after bill_amount;

3.把旧表中的数据迁移到新表中

insert into account_bill_temp (column1,column2,...) select column1,column2,... from account_bill;

4.修改两张表的表名

rename table account_bill to account_bill_bak;
rename table account_bill_temp to account_bill;

方案二：通过复制数据文件的方式，步骤如下：

1.将表中的数据放到数据文件中

select * from account_bill into outfile '/mysql-files/account.txt'; 

2.创建临时表account_bill_temp

create table account_bill_temp like account_bill;

3.把旧表中的数据迁移到新表中

load data infile '/mysql-files/account.txt' into table account_bill_temp;

大家可能发现以上两种方案是有区别的，方法二中并没有添加字段的操作。其实最开始，我是按照方案一添加的字段，但是在执行第三步insert into ... select ...语句的时候，数据库卡死了。然后我就又去网上扒拉，发现了方案二。但是方案二有个问题，就是要求临时表的表结构和原表一致，无法添加字段，只能用于备份数据等操作，所以方案二对我们的问题其实是没啥意义的。之所以把方案二也列出来，是因为如果只是备份数据的话，方案二的执行效率要比方案一高很多（据说能达到20倍以上）。

方案二走不通，于是我们只能又回到了方案一。方案一的主要问题就是执行第三步insert into ... select ...语句的时候会卡死，数据迁移到3100万左右的时候，临时表中的数据不再发生变化，数据容量也卡在了25.9G不再增加。卡死的原因没有定位到，我们只是试着去寻找效率更高的方案。其实最终的方案也比较简单，就是account_bill表存在5个索引，其中一个索引由6个字段组成，我们在方案一的第一步后面加了一步，删除主键和所有索引。

第一次按方案一执行的时候，带着主键和索引执行insert into ... select ...语句，从开始到迁移3100万数据卡死，执行了3个小时左右。第二次执行，删除主键和索引，执行insert into ... select ...语句，全部数据迁移只用了半小时。数据迁移后再把主键和索引加上，添加主键用了20分钟，添加5个索引的耗时都在10分钟以下，一个多小时完成整张表的迁移工作。总结最终方案如下：

1.创建临时表account_bill_temp

create table account_bill_temp like account_bill;

2.删除临时表的主键和索引

alter table account_bill_temp drop PRIMARY KEY;

alter table account_bill_temp drop index index_name; //注意修改索引名

3.在新表中添加字段

alter table account_bill_temp add columu bill_id varchar(64) comment '账单id' after bill_amount;

4.把旧表中的数据迁移到新表中

insert into account_bill_temp (XXX,XXX,...) select XXX,XXX,... from account_bill;

5.添加主键和索引

alter table account_bill_temp add primary key (id);
alter table account_bill_temp add index index_name (column1,column2);

6.修改两张表的表名

rename table account_bill to account_bill_bak;
rename table account_bill_temp to account_bill;

​

这篇关于【MySQL】大数据量表添加字段的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！