mysql的alter_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: 0

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值