【五】高性能MySql笔记——快速ALTER TABLE（Hack手法）

    MySql中大数据表的ALTER TABLE操作是非常耗时的。今天讨论下如何快速完成表结构的修改？

    ALTER TABLE的原理是用新结构创建一张新表，然后将旧表的数据拷贝进新表，最后再删除旧表。ALTER TABLE会导致数据操作服务中断。常用的方式是主备切换和影子拷贝。

    主备切换：先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换。

    影子拷贝：用要求的表结构创建一张和源表无关的新表，然后通过重命名和删除表操作交换两张表。

    Hack手法

    理论：由于MySql表中列的默认值实际上存在表的.frm文件中，所以可以直接修改这个文件而不需要改动表本身。MySql官方并没有采用这种优化方法，所有的MODIFY COLUMN操作都将导致表重建。或者我们可以使用ALTER COLUMN，这句话可以直接修改.frm文件而不涉及表数据。因此ALTER COLUMN操作比ALTER TABLE操作快很多。

    方法：直接修改.frm文件的方法虽然可以快速修改表，但却存在一定的风险，所以在使用时应该考虑清楚。基本技术是创建一个新的.frm文件，然后替换掉旧的.frm文件。

    步骤：

        ①创建一张有相同结构的空表，并进行所需要的修改；

        ②执行FLUSH TABLES WITH READ LOCK锁定所有正在使用的表；

        ③交换.frm文件；

        ④执行UNLOCK TABLES解锁所有表。

    不需要创建表的情况：

        ①移除（不是增加）一个列的AUTO_INCREMENT属性；

        ②增加、移除，或更高ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串值。

    快速创建MyISAM索引

    高效的载入数据到MyISAM表中的方法：先禁用索引、载入数据、然后重新启用索引。优点：此方法是将构建索引的工作延迟到数据完全载入之后，这时就可以使用排序来构建索引了，还可以减少索引树的碎片，使索引更紧凑。缺点：对唯一索引无效，DISABLE KEYS只对非唯一索引有效。

    注意：MyISAM会在内存中构造唯一索引，并且为载入的每一行检查唯一性。一旦索引的大小超过了有效内存大小，载入操作就会变得越来越慢。因此对于InnoDB有一个类似的技巧：依赖InnoDB的快速在线索引创建功能，先删除所有唯一索引，然后增加新的列，最后重新创建删除掉的索引。

// 禁用索引
ALTER TABLE <table> DISABLE KEYS;
// 启用索引
ALTER TABLE <table> ENABLE KEYS;

    步骤：

        ①用需要的表结构创建一张表，但是不包括索引；

        ②载入数据到表中以构建.MYD文件；

        ③按照需要的结构创建另外一张空表，这次要包含索引。这会创建需要的.frm和.MYD文件；

        ④获取读锁并刷新表；

        ⑤重命名第二张表的.frm和.MYD文件，让MySql人为是第一张表的文件；

        ⑥释放读锁；

        ⑦使用REPAIR TABLE重建表索引。