如果您不了解ALTER TABLE的语法,可以先参考:
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
使用ALTER TABLE 可以修改一张表的结构。比如,添加或者删除一个字段,创建或者删除一个索引,修改一个字段的类型,或者重命名一个字段,或者表的一些元信息。我们还可以通过ALTER TABLE来修改表引擎。
在大多数情况下,ALTER TABLE通过建立一个原表的拷贝,并在拷贝上修改,然后删除原表,最后修改拷贝的名称为原表名来实现。在执行ALTER TABLE的时候是不能更新(update or insert)数据的, 但是其他的session是可以read的。
下面来具体看看如何最快的执行ALTER TABLE(这儿不考虑 分区/MySQL Cluster/表空间)
在某些特定的情况下,ALTER TABLE 是不需要建立临时拷贝的,所以是很快的。
1. 重命名一个字段或者索引
2. 修改一个字段的默认值
3. 添加新的枚举值到enum的末尾 或者 添加新的set值到set的末尾
4. 仅仅重命名表,而不修改其余任何参数
其他的情况,MySQL会建立一个临时表来执行ALTER TABLE。
对于MyISAM表,增大如下的变量可以加快ALTER TABLE的执行过程。
1. myisam_sort_buffer_size MySQL会在REPAIR TABLE索引排序或者使用ALTER TABLE/CREATE INDEX创建索引的时候申请缓冲的大小。
2. myisam_max_sort_file_size MySQL在使用REPAIR TABLE/ALTER TABLE/LOAD DATA重建MyISAM索引的时候允许申请的最大临时文件的大小,如果超过这个大小,MySQL将会使用很慢的key cache。
对于MyISAM表,还可以通过批量的建立索引来加快整个ALTER TABLE执行过程。通过在ALTER TABLE之前 运行 ALTER TABLE ... DISABLE KEYS/在ALTER TABLE之后运行ALTER TABLE ... ENABLE KEY来激活这个特性。但是个该特性对于主键和唯一索引不起作用。
在数据量比较大的时候,采用dump data/drop table/recreate table/load data的方式来修改表结构,会比ALTER TABLE快很多,当然这要看具体的应用,因为ALTER TABLE的过程中,可以提供继续read, 而采用dump data的方式则不行。
根据
http://everythingmysql.ning.com/profiles/blogs/whats-faster-than-alter
上的测试结果,80W条记录,约5G的数据量,采用dump data可以比ALTER TABLE直接修改快47%.
采用dump data的方式基本步骤如下:
1. 使用 select * from tablename into outfile "/tmp/data"; 保存数据到临时文件 /tmp/data
2. drop table tablename;
3. create table tablename (...);
4. 如果是 MyISAM, alter table tablename disable keys;
5. load data infile "/tmp/data" into tablename;
6. 如果是 MyISAM, alter tabe tablename enable keys;
最后,需要特别说明一下,有些时候,不一定需要最快,但应该选择最适合、最可靠的方式。
参考:
1. http://venublog.com/2010/01/04/performance-comparison-of-repair-by-sorting-or-by-keycache/
2. http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
3. http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
4. http://everythingmysql.ning.com/xn/detail/3993569%3ABlogPost%3A171