加快ALTER TABLE 操作速度

最新推荐文章于 2024-07-10 17:42:55 发布

?Briella

最新推荐文章于 2024-07-10 17:42:55 发布

阅读量233

点赞数

文章标签：数据库 shell

原文链接：http://www.cnblogs.com/liuensong/p/10140346.html

版权

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       |                |
+-------------+---------------------+------+-----+---------+----------------+

转载于:https://www.cnblogs.com/liuensong/p/10140346.html