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: 0mysql> 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)
