对大型表而言，mysql的alter table操作的性能会成为一个显著的挑战。mysql执行大部分修改表结构操作的方法是用新的表结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。如果内存不足且表很大，同时还有很多索引，那么这种操作可能会非常耗时。alter table操作通常需要几个小时甚至几天才能完成，这是许多人都经历过的情况。
通常情况下，大多数ALTER TABLE操作会使MYSQL服务停止运行。对常见的场景，能使用的技巧只有两种：
一种是先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换；
另外一种技巧就是“影子拷贝”。影子拷贝技巧包括创建一张新表并按照所需的表结构进行操作，然后通过重命名和删除表操作交换两张表的过程。
不是所有的ALTER TABLE操作都会引起表重建。有两种不同的方法可以更改或删除列的默认值，其中一种方法速度很快，而另一种方法则速度较慢。
假如要修改电影的默认租赁期限，从三天改到五天。下面是很慢的方式：
mysql> ALTER TABLE film modify column rental_duration tinyint(3) not null default 5;
1000次读和1000次插入的操作数量可以从”SHOW STATUS”中得到。换句话说，它将整张表复制到了一张新表中，包括列的数据类型、大小和null属性都没有进行修改。
理论上，MYSQL可以跳过创建新表的吧步骤。表的默认值存储在.frm文件中，因此可以直接编辑该文件而无需修改表本身。尽管这种优化方法可行，但MYSQL目前尚未使用它，因此修改列操作都需要重建表。
另外一种方法是通过ALTER COLUMN操作来改变列的默认值；
mysql> ALTER TABLE film ALTER COLUMN rental_duration set DEFAULT 5;
这个语句会直接修改.frm文件而不涉及表数据。所以这个操作是非常快的。
只修改.frm文件
虽然修改表的.frm文件速度非常快，但 MySQL 有时会在不必要的情况下重新建表，这一点我们可以从上述示例中看出。通过承担一定的风险，可以让MYSQL进行其他类型的修改而无需重建表。
注意 下面要演示的技巧是不受官方支持的，也没有文档记录，并且也可能不能正常工作，采用这些技术需要自己承担风险。>建议在执行之前首先备份数据！
下面这些操作是有可能不需要重建表的：
移除（不是增加）一个列的AUTO_INCREMENT属性。
增加、移除，或更改ENUM和SET常亮。假如删除的是已在某行中使用过的常量值，查询将会返回一个空字符串。
步骤：
创建一张有相同结构的空表，并进行所需要的修改(例如：增加ENUM常量)。
执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。
交换.frm文件。
执行UNLOCK TABLES 来释放第二步的读锁。
下面以给film表的rating列增加一个常量为例来说明。当前列看起来如下：
mysql> SHOW COLUMNS FROM film LIKE 'rating';
ratingenum(‘G’,’PG’,’PG-13′,’R’,’NC-17′)YES
G
假设我们需要为那些对电影更加谨慎的父母们增加一个PG-14的电影分级：
mysql> CREATE TABLE film_new like film;mysql> ALTER TABLE film_new modify column rating ENUM('G','PG','PG-13','R','NC-17','PG-14') DEFAULT 'G';mysql> FLUSH TABLES WITH READ LOCK;
注意，我们是在常量列表的末尾增加一个新的值。如果把新增的值放在中间，例如：PG-13之后，则会导致已经存在的数据的含义被改变：已经存在的R值将变成PG-14，而已经存在的NC-17将成为R，等等。
接下来用操作系统的命令交换.frm文件：
/var/lib/mysql/sakila# mv film.frm film_tmp.frm/var/lib/mysql/sakila# mv film_new.frm film.frm/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm
再回到Mysql命令行，现在可以解锁表并且看到变更后的效果了：
mysql> UNLOCK TABLES;mysql> SHOW COLUMNS FROM film like 'rating'\G
****************** 1. row*********************
Field: ratingType: enum(‘G’,’PG’,’PG-13′,’R’,’NC-17′,’PG-14′)
最后需要做的是删除为完成这个操作而创建的辅助表：
mysql> DROP TABLE film_new;
以上就是MYSQL大表改字段慢问题如何解决的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/163823.html
微信扫一扫 
支付宝扫一扫 
