MySQL之 加快ALTER TABLE的执行速度

本节内容总结自《High Performance MySQL》

加快ALTER TABLE的操作速度

MySQL执行ALTER TABLE的原理

• MySQL执行大部分修改表结构操作的方法是用一个新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表，一般而言，大部分ALTER TABLE操作都会锁表，导致MySQL服务中断。

一些ALTER TABLE的技巧

1. 先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。
2. 影子拷贝：用要求的表结构创建一张和原表无关的新表，然后通过重命名和删表操作交换两张表。

一些ALTER TABLE 可以不进行表重建

1. 例如修改列的默认值。
2. 移除一个列的AUTO_INCREMENT属性。
3. 增加，移除，或更改ENUM和SET常量。
   • 不重建表的解决方法：为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件，具体步骤如下。①：创建一张有相同结构的空表，并进行需要的修改。②：执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。③：交换.frm文件。④：执行UNLOCK TABLES来释放第2步的锁。

快速创建MyISAM索引

1. 高效的创建MyISAM索引：一个常用的技巧是先禁用索引，载入数据，然后重新启用索引。这是因为构建索引的工作被延迟到数据完全载入以后，这个时候已经可以通过排序来构建索引了。这样做会快很多，而且使得索引树的碎片更少，更紧凑。但是这个办法对唯一索引无效，因为DISABLE KEYS只对非唯一索引有效。MyISAM会在内存中构造唯一索引，并且为载入的每一行检查其唯一性。
2. 可以使用如下类似骇客的方式快速创建索引，具体步骤如下。①：用需要的表结构创建一张表，但是不包括索引。②：载入数据到表中以构建.MYD文件。③：按照需要的结构创建另一张空表，这次要包含索引，这会创建需要的.frm和.MYI文件。④：获取读锁并刷新表。⑤：重命名第二张表的.frm和.MYI文件，让MySQL认为是第一张表的文件。⑥：释放读锁。⑦：使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引。