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

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

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

mysql>altertabletestmodifycolumntesttinyint(3)notnulldefault5;

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

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

mysql>altertabletestaltercolumntestsetdefault5;

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

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

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

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

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

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

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

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

交换.frm文件

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

mysql>showcolumnsfromfilmlike'rating';+--------+------------------------------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+------------------------------------+------+-----+---------+-------+|rating|enum('G','PG','PG-13','R','NC-17')|YES||G||+--------+------------------------------------+------+-----+---------+-------+1rowinset(0.00sec)

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

mysql>createtablefilm_newlikefilm;QueryOK,0rowsaffected(0.26sec)mysql>select*fromfilm_new;Emptyset(0.00sec)mysql>altertablefilm_newmodifycolumnratingENUM('G','PG','PG-13','R','NC-17','PG-14')default'G';QueryOK,0rowsaffected(0.04sec)Records:0Duplicates:0Warnings:0mysql>flushtableswithreadlock;QueryOK,0rowsaffected(0.01sec)

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

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

[root@host1sakila]#mvfilm.frmfilm_temp.frm[root@host1sakila]#mvfilm_new.frmfilm.frm[root@host1sakila]#mvfilm_temp.frmfilm_new.frm[root@host1sakila]#

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

mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)mysql>showcolumnsfromfilmlike'rating';+--------+--------------------------------------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+--------+--------------------------------------------+------+-----+---------+-------+|rating|enum('G','PG','PG-13','R','NC-17','PG-14')|YES||G||+--------+--------------------------------------------+------+-----+---------+-------+1rowinset(0.01sec)