ALTER TABLE 缺點

MySQL 的ALTER TABLE 操作的性能對大表來說是個大問題。

MySQL MySQL 執行大部分修改表結構操作的方法是用新結構的 創建一個，空表從舊表中查出所有數據插入，新表然后刪除舊。表這樣操作可能需要花費很長，時間 如內果存不足而表又很大而且還有很多索引的情況下尤其如此。

此許多人 都有這樣的經驗，ALTER ALTER TABLE 操作需要花費數個小時其
數天才能完成。

對于MySQL 5.5 及更早版本
一般而言大部分ALTER TABLE 操作將導致MySQL 服務中斷,常用的技巧有兩種:

• 一種是先在臺一不提供服務的機上器執行ALTER TABLE 操作，然 后和提供服
  務的主庫進行切換。
• 另外 一技巧種 是““影子拷貝""。影拷貝子 的技巧是用老的表結構創建一張和源表一樣表結構的新表，然后通過重命名和刪表操作交換兩張表

不是有所的ALTER TABLE 操作都會引起表重建的

下面這操作些 有是可能不需要重建表的，

• 移除((不 是增加) 一個列的AUTO_INCREMENT 屬性。
• 增加、移除, 或更改ENUM 和SET 常量。如 果移除的是已經有行數據用到其值的常量， 查詢將返回會 一個空字串值。

例：修改字段長度

假如要修改電影的默認租賃 期，限從三 天改到五天。下面傳統方法

mysql> ALTER TABLE sakila.film 
-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; 

SHOW STATUS 顯示這個語句做了1 000 次讀和1 000 次插入 操作。換名話說，它拷貝了 整張表到 一 張新表甚至列類的 型、大小和可否為NULL 屬性 都沒改變。

當表數據比較大時，就會特別慢。

如何優化

理論上，MySQL 可以跳過創建新表的步驟。列的默認值實際上存在表的. frm 文件中，是可以直接修改.frm 文件的。然而MySQL 還沒有采用這種優化的方法 ，所有的MODIFY COLUMN 操作都將導致表重建。

可過如下ALTER COLUMN 操作來改變列的默認值

mysql> ALTER TABLE sakila.film 
-> ALTER COLUMN rental_duration SET DEFAULT 5; 

這個語句會直接修改. frm 文件。所以，這個操作是非常快的。

例： 給表的某列增加一個常量

比如：以給sakila.flim 表的rating 列增加一個常量

SHOW COLUMNS FROM sakila.film LIKE ‘rating’; 

rating 增加一個PG-14 的電影分級:

一般做法

ALTER TABLE sakila.film MODIFY COLUMN rating ENUM('G','PG','PG-13','PG-14','R','NC-17') DEFAULT 'G';

表數據量大得情況下，會特別慢，還可能長時間影響對外服務。

優化方案

1. 創建一張有相同結構 的空表，并進行需要所的修改(例如增加ENUM 常量) 。
2. 執行FLUSH TABLES WITH READ LOCK LOCK。這將會關閉所有正在使用的表 ，并且禁止任何表被打開。
3. 交換 .frm 文件.
4. 執行 UNLOCK TABLES 來釋放第2步得讀鎖

備份數據

MUST: 一定要備份數據

執行命令創建表、鎖表命令

mysql> CREATE TABLE sakila.film_new LIKE sakila.film; 
mysql> ALTER TABLE sakila.film_new 
-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14') 
-> DEFAULT 'G'; 
mysql> FLUSH TABLES WITH READ LOCK

注意，我們是常量在 列表的末尾增加一個新的值。如果把新增的放值在，中間例如PG-13 之后則導致會已存在經 數據的的含義被改變: 已存在經 的了值將變成PG-14 ，而已存在經 的NC-17 將成為RR，等

交換.frm 文件

接下來用操作系統的命令交換.frm 文件

/var/lib/mysql/sakila# mv film.frm film_tmp.frm 
/var/lib/mysql/sakila# mv film_new.frm film. frm 
/vaxr/lib/mysql/sakila# mv film_tmp.frm film_new. frm 

解鎖、查看表字段

mysql> UNLOCK TABLES; 
mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating';

刪除輔助表

確認沒問題刪除輔助表

mysql> DROP TABLE sakila.film_new ;