MySQL 修改表字段優(yōu)化方案

要求:現(xiàn)在需要要改一下字段名,增加一個字段,在刪除另一個字段,改一個字段類型.

建立如下表（原表）:
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. 復(fù)制數(shù)據(jù)到臨時表
3. 刪除原表
4. 更名臨時表為原表名

時間主要的消耗在 "復(fù)制數(shù)據(jù)到臨時表".

復(fù)制數(shù)據(jù)到臨時表這一步，影響時間的因素有，索引維護(hù)，數(shù)據(jù)插入，鎖

所以隨著修改字段的數(shù)目增加，都需要重復(fù)以上的操作.當(dāng)數(shù)據(jù)量很大時,復(fù)制數(shù)據(jù)到臨時表的時間就會更長.

案例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 分析:

自己創(chuàng)建一個臨時表,把需要改的字段一次性修改完,用insert對所有修改的字段只用了一次“復(fù)制數(shù)據(jù)”.然后更改表名(更改表名是不需要掃描全表的)

最在后加主鍵(這樣可以避免復(fù)制表的過程中,每插入一行數(shù)據(jù)都要對索引進(jìn)行的維護(hù)操作.最后加索引是一次性對全表進(jìn)行維護(hù))

===================================================================================

以上兩種方案可以看出,隨著需要更改的字段的增加.方案2比方案1的速度是單調(diào)遞增的.

如果使用方案1:建議設(shè)置tmp_table_size參數(shù)大小,此參數(shù)用于系統(tǒng)建立臨時表的大小.

建議設(shè)置：sort_buffer_size 參數(shù)大小，此參數(shù)用于排序的緩存.對索引維護(hù)會使用到這個參數(shù).

如果你使用表的存儲引擎是InnoDB.可以更改innodb_buffer_pool_size（windows：%mysql%/my.ini. linux:/etc/mysql/my.cnf）設(shè)置來加快修改的速度.

特別是對于大量數(shù)據(jù)的表.建議設(shè)置大小為內(nèi)存大小的50%-80%.

服務(wù)器租用/服務(wù)器托管中國五強(qiáng)！虛擬主機(jī)域名注冊頂級提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]