mysql加快alter操作

场景:

    mysql的alter table 操作的性能对大表来说是个大问题.mysql执行大部分修改表结构的操作方法是用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表.这样操作可能花费很长时间,如果内存不足而表又很大,而且还有很多索引的情况下,此种情况更甚.

    一般而言,大部分alter table操作将导致mysql服务中断,对于常见场景,能使用的技巧有两种,一种是先在一台不提供服务的机器上执行alter table操作,然后和提供服务的主库进行切换,另外一种是影子拷贝.影子拷贝的技巧是用要求的表结构创建一张和原表无关的新表,然后通过重命名和删表的操作交换两张表.

    不是所有的alter table操作都会引起表的重建,列如有两种方法可以改变或删除某个列的默认值.

mysql > alter table test modify column test tinyint(3) not null default 5;

show status 显示了这个语句做了上千次读和上千次插入,换句话说,它拷贝了一张表到一张新表.

理论上,mysql 可以跳过创新表的步骤,列的默认值实际上存在表的.frm文件中,所以可以直接修改这个文件而不需要改动表本身.然而mysql还没有采用这种优化方法,所有的modify column操作都将导致表重建.

mysql>alter table test alter column  test set default 5;

这个语句会直接修改.frm文件而不涉及表数据.所以,这个操作是非常快的.

由此可以看出,修改表的.frm文件是很快的,但是mysql有时候会在没必要的时候也重建表.如果愿意冒一些风险,可以让mysql做一些其他类型的修改而不用重建表.但是在执行前首先应备份数据,该操作不受官方支持.

下面这些操作可能不需要重建表:

(1)移除(不受增加)一个列的auto_increment属性

(2)增加,移除,或者更ENUM和SET常亮.如果移除的是已经有行数据用到其他值的常量,查询将会返回一个空子串值.

基本的技术是为想要的表结构创建一个新的.frm文件,然后用它替换掉已经存在的表的.frm文件,像下面这样:

• 创建一张有相同表结构的空表,并进行所需要的修改(列如增加ENUM常量)

• 执行flush tables with read lock.这将会关闭所有正在使用的表,并禁止任何表被打开.

• 交换.frm文件

• 执行unlock tables来释放第2步的读锁.

mysql> show columns from film like 'rating';
+--------+------------------------------------+------+-----+---------+-------+
| Field  | Type                               | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17') | YES  |     | G       |       |
+--------+------------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

假设我们增加一个PG-14的电影分支.

mysql> create table film_new like film;
Query OK, 0 rows affected (0.26 sec)
mysql> select * from film_new;
Empty set (0.00 sec)
mysql> alter table film_new modify column rating ENUM('G','PG','PG-13','R','NC-17','PG-14') default 'G';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

注意:我们是在常量列表的末尾增加一直新值

利用操作系统命令交换.frm文件

[root@host1 sakila]# mv film.frm film_temp.frm
[root@host1 sakila]# mv film_new.frm film.frm
[root@host1 sakila]# mv film_temp.frm film_new.frm
[root@host1 sakila]#

再回到mysql就可以解锁表和更改后的效果了

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show columns from film like 'rating';
+--------+--------------------------------------------+------+-----+---------+-------+
| Field  | Type                                       | Null | Key | Default | Extra |
+--------+--------------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17','PG-14') | YES  |     | G       |       |
+--------+--------------------------------------------+------+-----+---------+-------+
1 row in set (0.01 sec)