mysql 修改表字段优化方案,要求:现在需要要改一下字段名,增加一个字段,在删除另一个字段,改一个字段类型.
要求:现在需要要改一下字段名,增加一个字段,在删除另一个字段,改一个字段类型.
建立如下表（原表）:
create table month(
  id int,
  week varchar(10),
  day varchar(10),
  hour varchar(10),
  primary key (id)
);
得到的最终表为：
create table month(
  id int ,
  week varchar(10),
  today varchar(20),     -- 把day varchar(10)改为 today varchar(20)
  minute varchar(20),    -- 增加新的字段 minute varchar(20),删除字段hour varchar(10)
  primary key (id)
);
方案1:
alter table month change day today varchar(20);
alter table month drop column hour;
alter table month add minute varchar(20);
案例1 分析：
alter table 在mysql后台的操作是
1. 新建一临时表
2. 复制数据到临时表
3. 删除原表
4. 更名临时表为原表名
时间主要的消耗在 复制数据到临时表.
复制数据到临时表这一步，影响时间的因素有，索引维护，，数据插入，锁
所以随着修改字段的数目增加，都需要重复以上的操作.当数据量很大时,复制数据到临时表的时间就会更长.
案例2：
create table temp_month(
  id int,
  week varchar(10),
  today varchar(20),   
  minute varchar(20) default '00'  
);
insert into temp_month(id, week, today) (select id, week, day from month);
drop table month;
alter table temp_month rename to month;
alter table month add primary key(id);
案例2 分析:
自己创建一个临时表,把需要改的字段一次性修改完,用insert对所有修改的字段只用了一次“复制数据”.然后更改表名(更改表名是不需要扫描全表的)
最在后加主键(这样可以避免复制表的过程中,每插入一行数据都要对索引进行的维护操作.最后加索引是一次性对全表进行维护)
===================================================================================
以上两种方案可以看出,随着需要更改的字段的增加.方案2比方案1的速度是单调递增的.
如果使用方案1:建议设置tmp_table_size参数大小,此参数用于系统建立临时表的大小.
建议设置：sort_buffer_size 参数大小，此参数用于排序的缓存.对索引维护会使用到这个参数.
如果你使用表的存储引擎是innodb.可以更改innodb_buffer_pool_size（windows：%mysql%/my.ini. linux:/etc/mysql/my.cnf）设置来加快修改的速度.
特别是对于大量数据的表.建议设置大小为内存大小的50%-80%.