想测试下在单表千万级数据量情况下,修改表结构增加字段需要多久,锁表多久,因此在windows本地下载了mysql5.7版本进行测试,使用下面存储过程向MySQL写入一千万条数据,再进行表修改操作,得出的结果是增加字段这种修改表结构还是会走copy方式,创建个临时表,再改表结构,再把原数据复制到临时表,再删除原表并重命名临时表到原表去,此处数据简单,且MySQL锁定了一分多钟时间,因此可见在5.7版本不建议对大表做表结构修改去增加字段,会影响线上业务,尤其是主从库,从库监听binlog会同步修改后的所有数据行,机器要是规格低,慢的很。因此这种情况下我们的解决方案是
- SQL定时到凌晨三四点执行(买的云数据库不阻塞的话,一两个小时能执行完还行,数据量大不太建议用这种方式)
- 提前留扩展字段,json格式。
- 加扩展表,给扩展表留备用字段和json扩展字段
- 使用云数据库提供的解决方案,可以定速同步,不阻塞数据库,先执行主库修改(会短暂锁表),然后定速同步从库。
- 使用MySQL 8.0,支持instant模式,直接修改元数据,不修改表数据。
- 使用大表修改工具pt-osc工具、gh-ost
下面这个执行是一条条插入,会比较慢,建议批量生成数据SQL字符串执行,一次一千条,或者使用数据生成工具,例如评论里说的navicat16,另外大量数据插入的时候建议先移除索引,只保留主键,其他的等数据插入完再添加索引,否则在添加数据时还需要去维护索引B+树,写入,是没必要的,插入完成后再构建索引即可。插入完成后这个表的数据ibd文件达到了800M(行数据和索引数据)
create table member
(
id int unsigned auto_increment
primary key,
phone char(11) not null comment '手机号码',
age int unsigned not null comment '年龄'
)
comment '会员';
drop procedure if exists test;
create procedure test()
begin
declare i bigint;
set i = 16660564665;
while i < 16670564665 do
insert into member (phone, age) VALUES (convert(i,char),23);
set i = i +1;
end while;
end
;
call test();
create index idx_age
on member (age);
create index idx_phone
on member (phone);