加快alter table的操作速度

01

加快alter table的操作速度

MySQL中的alter table操作对于大表来讲，是一个比较严重的问题，MySQL执行大部分alter table的操作步骤是：

用新的结构创建一张表，从旧的表中查出所有的数据，然后插入到新表中，然后删除旧的表。

如果这种方法处理的表的数量很大，那么这样的操作将会花费很长时间，而且这个操作在旧版本中还会锁表，导致插入中断。一般针对这种大表的更新，常见的操作技巧无外乎两种情况，一种是在一台测试机器上执行alter table操作，然后和线上的的主库进行切换，另外一种是通过创建一张新表，然后通过重命名和删表操作来交换两张表。

当然，还有一小部分alter table的操作是不需要重建表的，例如下面的操作，我们已知需要给col_name字段添加一个默认值5，SQL如下：

alter table test modify column col_name tinyint not null default 5;

如果这个表有1000条数据，那么这个操作将会做1000次读和1000次写操作。这个操作中，其实列的类型、大小、和null值设置都没有改变，

这不是我们想看到的，MySQL中我们完全可以跳过创建新表的步骤，列的默认值实际上存储在表的.frm文件中，所以可以直接修改这个文件，而不需要改动表本身。再来看另外一种方法：

alter table test alter column col_name set default 5;

这个操作将会直接修改.frm文件而不涉及表数据，因此这个操作是相当快的。

既然我们知道直接修改frm文件可以改变表的记录结构，那么也可以通过下面的方法来进行alter一个字段属性的操作（有一定的数据损坏风险，测试之前请备份）：

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

2、执行flush tables with read lock，该操作将会关闭所有正在使用的表，当然，如果你的表没有被读取和写入，可以忽略此项。

3、交换.frm文件

4、执行unlock tables来释放读锁

5、这样就成功对表中的字段属性进行了更新。

今天家里连不上V**，这个实验改天再做吧。如果大家想尝试，需要提前备份数据，然后再进行尝试。

还有一种是用alter语法快速创建MyISAM索引的操作，我们知道MyISAM采用的是表锁，在使用alter table语法导入数据的时候有一种有效的方法，如下：

alter table test disable keys;
---load the data
alter table test enable keys;

这个操作可以把创建索引的工作放在数据完全载入之后，这样做会比较快，因为减少了索引维护和创建的时间。需要注意的是，disable keys只对非唯一索引有效，唯一索引因为要检查唯一性，将会使得性能大大降低。

如果使用的是innodb存储引擎，在导入数据的时候可以先删除唯一索引，然后进行alter操作，最后在重新创建删除掉的索引。如果你使用的是Percona MySQL，那么服务本身就会提供这样的操作。