MySQL 修改表字段优化方案

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%.