MySQL之快速ALTER TABLE

对大表来说，修改它的列会导致拷贝整个表到一张新表，因此耗时较多。如用下列代码修改表file，将其默认值由2修改为5。通过SHOW STATUS显示做了1000次读操作和1000插入操作。即拷贝了整张表到一张新表。

ALTER TABLE user.file
MODIFY COLUMN number TINYINT(3) NOT NULL DEFAULT 5;

解决之道：仅修改表的.frm文件而不涉及表数据。
如：

ALTER TABLE user.file
ALTER COLUMN number SET DEFAULT 5;

修改.frm文件

有些操作可能不需要重建表：

1. 移除一个列的AUTO_INCREMENT属性。
2. 增加、移除或是更改ENUM和SET常量，如果移除的是已有行数据用到其值的常量，查询会返回一个空字符串。

如何修改.frm文件，关键在于创建一个新的.frm文件，用其代替原表的.frm文件。具体步骤如下：

• 创建相同结构的空表，并进行必要的修改。
• 执行FLUSH TABLES WITH READ LOCK。关闭所有正在使用的表，并禁止打开。
• 替换.frm文件
• 执行UNLOCK TABLES，释放步骤2的读锁。

快速创建MyISAM索引

将数据高速地载入MyISAM表中，技巧是先禁用索引，载入数据，然后重新启用索引，如下所示。

ALTER TABLE user.file DISABLE KEYS;
---load data
ALTER TABLE user.file ENABLE KEYS;

这个方法对非唯一索引有效，但对唯一索引无效。可行方法如下。

• 创建一个所需结构的表，但无索引。
• 载入数据到表中，构建.MYD（MyISAM数据）文件。
• 按照所需结构另外创建一个空表，包含索引，由此构建.frm文件（MyISAM数据结构）和.MYI（MyISAM索引）文件。
• 获取读锁并刷新表
• 重命名第二张表的.frm和.MYI文件，方法如修改.frm文件，让MySQL认为是第一张表的文件。
• 释放读锁
• 使用REPAIR TABLE重建表的索引，通过排序来构建所有索引，包括唯一索引。

注意：以上所有方法不受官方支持，亦无文档记录，需要自担风险，因此执行前最好首先备份数据。