Mysql如何高效ALTER TABL

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 ;