加速alter table

MySql的ALTER TABLE 操作的性能对大表来说是个大问题。MySQL执行大部分修改表结构操作的方法是新建一个需要的结构的空表，从旧表中查出所有数据插入新表，然后删除旧表。这样操作肯需要花费很长时间，如果内存不足而表又很大，而且还有很多索引的情况下尤其如此。许多人都有这样的经验。ALTER TABLE操作需要花费数个小时甚至数天才能完成。

MySQL5.1以及更新版本包含一些类型的“在线”操作的支持，这些功能不需要在整个操作过程中锁表。最近版本的InnoDB也支持通过排序来建索引，这使得建索引更快并且有一个紧凑的布局。

一般而言，大部分ALTER TABLE操作将导致MySQL服务中断。我们会展示一些在DDL操作时使用的技巧，但这是针对一些特殊场景而言的。对常见的场景，能使用的技巧只有两种：一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；另外一种技巧是“影子拷贝”。影子拷贝的技巧是用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。

不是所有的ALTER TABLE操作都会引起表重建。例如，有两种方法可以改变或者删除一个列的默认值（一种方法很快，另外一种则很慢）。假如要修改电影的默认租赁期限，从三天到五天。下面是很慢的方式：

mysql> ALTER TABLE sakila.film

         ->MODIFY COLUMN rental_duration TINYINT(3)  NOT NULL DEFAULT 5;

SHOW STATUS 显示这个语句做了1000次读和1000次插入操作。换句话说，它拷贝了整张表到一张新表，甚至列的类型，大小和可否为NULL属性都没改变。

理论上，MySQL可以跳过创建新表的步骤。列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。然而MySQL还没有采用这种优化的方法，所有的MODIFY COLUMN操作都将导致表重建。

另外一种方法是通过ALTER COLUMN操作来改变列的默认值：

mysql>ALTER TABLE sakila.film

         ->ALTER COLUMN rental_duration SET DEFAULT 5;

这个语句会直接修改.frm文件而不涉及表数据。所以，这个操作是非常快的。

只修改.frm文件

　　修改表的.frm文件是很快的，但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险，可以让MySQL做一些其他类型的修改而不用重建表。

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

• 移除(不是增加)一列的AUTO_INCREMENT属性。

• 增加、移除，或更改ENUM和SET常量。如果移除一个常量和含有该常量的行，查询将会返回一个空字串值。

基本技巧是为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，像下面这样： 
　　1. 创建一张有相同结构的空表，并进行所需要的修改（例如增加ENUM常量）。 
　　2. 执行FLUSH WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。 
　　3. 交换.frm文件（重全名）。 

　　4. 执行UNLOCK TABLES来释放第2步的读锁。

快速的创建MyISAM索引

　　高效的读取MyISAM表一般的技巧是，关闭键，读取数据，重新启用键。

　　mysql> ALTER TABLE test.load_data DISABLE KEYS; 
　　-- load the data 
　　mysql> ALTER TABLE test.load_data ENABLE KEYS;

　　这样可以工作的原因是MyISAM延迟了创建键值直到数据读取之后，重要的是，它可以有序的创建索引。结果就非常快并且会得到无碎片和紧凑的索引树。

　　不过，对于唯一索引这种方法就不行了。因为DISABLE KEYS仅仅应用于非唯一索引。MyISAM在内存中创建唯一索引并且读取每一行来校验唯一性。一旦索引大小超出了内存大小，读取会极度缓慢。

　　前一部分所说的ALTER TABLE的技巧，可以加速这个过程，前提是你需要多做一点工作和承担一部分风险。这对于备份来说很有用。比如，当你发现所有的数据是无效的并且不需要做唯一性检查。

　　你需要做的步骤如下：

　　1、创建一个有需要的结构的表。但是不要有任何的索引。

　　2、把数据加载到表中来创建.MYD文件。

　　3、创建另一个有需要的结构的空表，这次要包含索引。这会创建.frm和.MYI文件。

　　4、用个读锁来刷新表。

　　5、对第二个表的.frm和.MYI进行重命名。因此MySQL会把它们当作第一个表使用。

　　6、释放读锁。

　　7、使用REPAIR TABLE来创建表的索引。会以排序的方式创建索引，包括了唯一索引。

　　这个方法对于大表来说，速度格外的快。