为什么要花这么长时间重命名mysql中的列? [英] Why does it take so long to rename a column in mysql?

问题描述

我有一张12 GB的充满图片的表，我正在尝试重命名保存数据的blob列，并且它永远都在占用.有人可以说一遍为什么重命名该列花了这么长时间吗?我本来以为不管表的大小，此操作都将很快完成?

I have a 12 GB table full of pictures, I'm trying to rename the blob column that holds the data, and it is taking forever. Can someone give me a blow by blow account of why it is taking so long to rename the column? I would have thought that this operation would be pretty quick, no matter the size of the table?

我运行的查询如下

 alter table `rails_production`.`pictures` change `data` `image_file_data` mediumblob NULL

看来，大多数时间都花在等待mysql制作图片表的临时副本上，因为这非常大，所以要花一些时间.

It appears that most of the time is spent waiting for mysql to make a temporary copy of the pictures table, which since it is very large is taking a while to do.

将图片存储从数据库更改为文件系统，这是要做的事情.

It is on the list of things to do, to change the picture storage from the database to the filesystem.

Mysql Server版本:5.0.51a-24 + lenny2(Debian)

Mysql Server version: 5.0.51a-24+lenny2 (Debian)

推荐答案

我不能给您吹牛(功能请求 ALTER 的文档:

I can't give you the blow-by-blow (feature request #34354 would help, except that it probably wouldn't be back-ported to MySQL 5.0), but the extra time is due to the fact that an ALTER ... CHANGE may change the type of the column (and column attributes, if any), which necessitates converting the values stored in the column and other checks. MySQL 5.0 doesn't include optimizations for when the new type and attributes are the same as the old. From the documentation for ALTER under MySQL 5.0:

在大多数情况下，ALTER TABLE可以通过制作原始表的临时副本来工作.在副本上执行更改，然后删除原始表，并重命名新表.执行ALTER TABLE时，其他会话可以读取原始表.对该表的更新和写入将暂停，直到准备好新表，然后将其自动重定向到新表，而不会导致任何失败的更新.

In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table are stalled until the new table is ready, and then are automatically redirected to the new table without any failed updates.

[...]

如果使用RENAME以外的ALTER TABLE选项，MySQL始终会创建一个临时表，即使严格不需要复制数据(例如，当您更改列名时)也是如此.

If you use any option to ALTER TABLE other than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column).

在5.1下， ALTER 具有一些其他优化:

Under 5.1, ALTER has some additional optimizations:

在某些情况下，不需要临时表:

In some cases, no temporary table is necessary:

• 仅修改表元数据而不修改表数据的更改可以通过更改表的.frm文件而不接触表内容而立即进行.以下更改是可以通过这种方式进行的快速更改:

• Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way:

• 重命名列， InnoDB存储引擎除外.

• Renaming a column, except for the InnoDB storage engine.

[...]

这篇关于为什么要花这么长时间重命名mysql中的列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！