加快ALTER TABLE 操作速度

来源：互联网发布：淘宝外卖在哪里编辑：程序博客网时间：2024/05/21 14:09

mysql的alter table操作的性能对于大表来说是个大问题。mysql大部分修改表结构操作的方法都是用新的结构创建一个
新表，从旧表中查出数据插入新表，然后在删除旧表。这样的操作很耗费时间，而且还有可能中断mysql服务。
这里推荐一种方法来提高alter table的操作速度。（请注意数据备份）

修改数据表的.frm文件，步骤如下：
1、创建一个有相同结构的新表，并进行所需修改（例如增加ENUM常量值）。
2、执行FLUSH TABLES WITH READ LOCK.加锁禁止打开所有表。
3、交换新表和旧表的.frm文件。
4、执行UNLOCK TABLES解锁。

语句实例：修改列user_status

mysql> desc users;+-------------+---------------------+------+-----+---------+----------------+| Field       | Type                | Null | Key | Default | Extra          |+-------------+---------------------+------+-----+---------+----------------+| user_id     | int(11) unsigned    | NO   | PRI | NULL    | auto_increment || user_name   | char(125)           | NO   |     | NULL    |                || user_pass   | char(32)            | NO   |     | NULL    |                || user_status | enum('0','1')       | NO   |     | NULL    |                || user_type   | tinyint(2) unsigned | NO   | MUL | 0       |                |+-------------+---------------------+------+-----+---------+----------------+mysql>create table users_tmp like users;mysql>alter table users_tmp modify column user_status enum ('0','1','2') default '2';mysql>desc users_tmp;+-------------+---------------------+------+-----+---------+----------------+| Field       | Type                | Null | Key | Default | Extra          |+-------------+---------------------+------+-----+---------+----------------+| user_id     | int(11) unsigned    | NO   | PRI | NULL    | auto_increment || user_name   | char(125)           | NO   |     | NULL    |                || user_pass   | char(32)            | NO   |     | NULL    |                || user_status | enum('0','1','2')   | YES  |     | 2       |                || user_type   | tinyint(2) unsigned | NO   | MUL | 0       |                |+-------------+---------------------+------+-----+---------+----------------+mysql>flush tables with read lock;

#-----------------------------------这里是shell命令进入mysql的datadir目录交换.frm文件[root@localhost /var/lib/mysql/test/] cp -a users.frm users.frm.backup[root@localhost /var/lib/mysql/test/] mv users_tmp.frm users.frm #这个会直接删除删除新建的表user_tmp#-----------------------------------

mysql>unlock tables;mysql>desc users;+-------------+---------------------+------+-----+---------+----------------+| Field       | Type                | Null | Key | Default | Extra          |+-------------+---------------------+------+-----+---------+----------------+| user_id     | int(11) unsigned    | NO   | PRI | NULL    | auto_increment || user_name   | char(125)           | NO   |     | NULL    |                || user_pass   | char(32)            | NO   |     | NULL    |                || user_status | enum('0','1','2')   | YES  |     | 2       |                || user_type   | tinyint(2) unsigned | NO   | MUL | 0       |                |+-------------+---------------------+------+-----+---------+----------------+

0 0