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

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

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

?

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

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

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】大数据量表添加字段

上一篇:基于Mariadb 10.6.4在CentOS 7环境下配置Galera Cluster集群


下一篇:Docker安装Oracle数据库(二)