MYSQL大表改字段慢问题如何解决

对大型表而言，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';

Field	Type	Null	Key	Default	Extra
rating	enum('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: rating
Type: enum('G','PG','PG-13','R','NC-17','PG-14')

最后需要做的是删除为完成这个操作而创建的辅助表：

mysql> DROP TABLE film_new;

以上是MYSQL大表改字段慢问题如何解决的详细内容。更多信息请关注PHP中文网其他相关文章！