MySQL 奇淫技巧：加快 ALTER TABLE 速度

####前言
MySQL 对于大表的`ALTER`操作是非常慢的，因为 MySQL 对于`ALTER`操作的的方法是创建一个新结构的表，然后将旧结构表中的数据复制过去，最后将旧表删除。如此操作对于海量数据的表来说花费的时间是非常长的。
所以对于要修改现有数据库表结构的操作还是有必要掌握些奇淫技巧的，本方法摘自《高性能 MySQL 第三版》，据书上介绍，该技巧是不受 MySQL 官方支持的，可能出现不能正常运行，做之前请确保先备份数据库。
MySQL 测试版本：5.7.21-0ubuntu0.16.04.1
####实践
现有如下表结构：
```
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(32)    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(8) | YES  |     | aaa     |                |
+-------+------------+------+-----+---------+----------------+
```
要求将`name`字段默认值改为`bbb`，常规做法是下面这样的：
> alter table test modify column name varchar(8) default 'bbb';

对于数据量非常大的表来说，以上做法会导致 MySQL 服务中断，花费时间也非常的长，所以并不可取。

下面用奇淫技巧来实现：
* 创建新结构的表
* 执行 `flush tables with read lock`，这将会关闭所有正在使用的表，并禁止被打开
* 将旧表的 `.frm` 文件替换成新表的 `.frm`
* 执行`unlock tables`释放第二步的读锁

之所以以上做法能行得通，它的原理是因为 MySQL 表字段的默认值信息是存放在`.frm`文件中的，所以可以直接修改`.frm`文件来实现我们的需求。
在做上面操作之前，可能会很好奇`.frm`文件中的内容，我也不例外，动手打开看看吧？很不幸，它是加密的，打开直接乱码，所以我们才会进行上面的操作，而不是直接去修改`.frm`文件。
创建新表：
```
mysql> create table test_new(
    -> id int(32) primary key auto_increment,
    -> name varchar(8) default 'bbb'
    -> );
```
执行锁表操作：
> flush tables with read lock

替换`.frm`文件：
> mv test_new.frm test.frm

释放读锁：
> unlock tables;

再次查看表结构：
```
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(32)    | NO   | PRI | NULL    | auto_increment |
| name  | varchar(8) | YES  |     | bbb     |                |
+-------+------------+------+-----+---------+----------------+
```
修改成功！
####结语
下班走人！！！

MySQL 奇淫技巧：加快 ALTER TABLE 速度

发表评论：