mysql加快alter操作
发布时间：2022-03-29 07:18:49 所属栏目：MySql教程 来源：互联网
导读：mysql的alter table 操作的性能对大表来说是个大问题.mysql执行大部分修改表结构的操作方法是用新的结构创建一个空表,从旧表中查出所有数据插入新表,然后删除旧表.这样操作可能花费很长时间,如果内存不足而表又很大,而且还有很多索引的情况下,此种情况更甚
|
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) （编辑：上海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐