加速MySQL的alter table操作（书摘备查）

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433214

MySQL的alter table性能在表很大的时候会出现问题。MySQL执行大部分更改操作都是新建一个需要的结构的空表，然后把所有老的数据插入到新表，最后删除旧表。这会耗费很多时间，尤其是在内存紧张，而表很大并有很多索引的时候。

不是所有的alter table操作都会导致重建表。例如，可以通过两种方式创建或去掉列的默认值（一种快、一种慢）。下面是较慢的方式：

alter table film modify column rental_duration tinyint(3) not null default 5;

使用show status分析该命令发现，它执行了1001次句柄读取和1000次写入。换句话说，即使列类型、大小和可空性没有变化，它也把表拷贝到了新表中。

flush status;
alter table film modify column rental_duration tinyint(3) not null default 5;
show session status like 'handle%';

理论上，MySQL能跳过构建一个新表的方式。列的默认值实际保存在表的.frm文件中，因此可以不接触表而更改它。MySQL没有使用这种优化，而是任何modify column都会导致表重建。但是可以使用alter column改变列的默认值：

alter table film alter column rental_duration set default 5;

这个命令更改了.frm文件并且没有改动表，它非常快。（alter table可以使用alter column、modify column、change column来修改列，每个命令做的事情都不一样）。

flush status;
alter table film modify column rental_duration tinyint(3) not null default 5;
show session status like 'handle%';

1. 只修改.frm文件

下面这种技巧不被MySQL支持，也没有文档记载，而且不保证一定能工作。使用它们需要自己承担风险，建议在使用之前备份数据。

不重建表可以执行下面的操作：

• 移除（不是添加）列的auto_increment属性。
• 添加、移除或更改enum或set常量。如果移除了一个常量，查询含有该常量的行将返回空字符串。

基本的技巧是为想要的表结构创建一个.frm文件来替代现有的.frm文件，步骤如下：

1. 创建一个布局完全一样的空表，但是想改动的地方除外（例如添加enum的常量）。
2. 执行flush tables with read lock。这会关闭所有正在使用的表，并且防止任何表被打开。
3. 交换.frm文件。
4. 执行unlock tables释放读锁。

例子：

向film表的rating列添加一个常量，当前列定义如下：

show columns from film like 'rating';

现在增加一个PG-14：

create table film_new like film;
alter table film_new modify column rating enum('G','PG','PG-13','R','NC-17','PG-14') default 'G';
flush tables with read lock;

现在从系统的命令行交换.frm文件：

mv film.frm film_tmp.frm
mv film_new.frm film.frm
mv film_tmp.frm film_new.frm

回到MySQL命令行，现在可以给表解锁并查看改动是否生效；

unlock tables;
show columns from film like 'rating';

最后删除用来辅助该操作的表：

drop table film_new;

注意新值被添加到常量列表的末尾，如果放到中间，如在PG-13之后，就更改了已有数据的含义：已有R值就会变成PG-14，NC-17就会变成R，等等。

2. 快速建立MyISAM表的索引

高效加载MyISAM表的诀窍是禁用键、加载数据、启用键：

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

这不会有问题，因为它使MyISAM直到所有数据被加载后才建立键，在这个时候，它可以按照排序构建索引。它很快并且会得到无碎片、紧凑的索引树（MyISAM在使用load data infile和空表的时候也会按照排序创建索引）。

不幸的是，disable keys只适用于非唯一索引。MyISAM在内存中构建唯一索引并且在加载每一行的时候检验其唯一性，一旦索引的大小超过可用内存，加载就变得极为缓慢。

如果已经知道所有的数据都是有效的从而没有必要进行唯一性检查，可以采用下面的步骤加速这个过程（再次提醒这是不被MySQL支持，也没有文档的技巧。使用它需要承担风险，要先备份数据）：

1. 创建一个有需要的结构的表，但是没有任何索引。
2. 把数据加载到表中，以构建.MYD文件。
3. 创建另一个有需要结构的表，这次包含索引。这会创建.frm和.MYI文件。
4. 用读取锁刷新该表。
5. 重命名第2个表的.frm和.MYI文件，这样MySQL就可以把它们用在第1个表上。
6. 释放读锁。
7. 使用repair table创建表的索引。这会按照排序创建所有的索引，包括唯一索引。

这个过程对很大的表也很快。