MySQL `ALTER TABLE ADD COLUMN AFTER COLUMN` 在大表上的性能表现

Question

MySQL `ALTER TABLE ADD COLUMN AFTER COLUMN` 在大表上的性能表现

32

我想通过使用以下命令向现有表添加列：

ALTER TABLE foo ADD COLUMN bar AFTER COLUMN old_column;

如果使用 AFTER COLUMN 选项，这个命令会比没有使用该选项的同一命令需要花费更长的时间吗？

ALTER TABLE foo ADD COLUMN bar;

执行第一个命令时，是否会使用更多的临时表空间来执行操作？

背景信息：我有一张非常大的表格（超过十亿行），我想使用AFTER COLUMN选项添加附加列，但我不想受到太多的惩罚。

- Drew

这个表是不是经常被读取？它是一个合并表吗？ - Book Of Zeus

这是一个普通的表格。读取数据没有任何问题。 - Drew

这些表格在许多MySQL存储过程中使用吗？PHP代码？（或其他代码）？ - Book Of Zeus

5

如果使用"AFTER COLUMN"选项，由于需要对记录进行移动，CPU使用率会增加。从内存使用的角度来看，使用和不使用这个选项是一样的。在大多数情况下，都会创建一个临时表。MySQL引擎中有一些支持热模式更改的（如TokuDB），它们不会创建临时表并浪费大量资源。但如果你正在使用MyISAM或InnoDB，我会说由于记录移动，"AFTER COLUMN"选项会花费稍微更长的时间。 - N.B.

1

@Drew；在极少数情况下，您添加新列的位置有时会影响这些命令完成所需的时间。但是，通过“它是否使用不同数量（或任何）的tmp表空间来执行操作？”您是否意味着使用@BookOfZeus提供的方法？ - Nonym

显示剩余2条评论

3个回答

11

创建另一个表并修改新表（就像 Zeus 的 Book 所做的那样）。

在插入查询之前和之后使用 ALTER TABLE newtable DISABLE KEYS 和 ALTER TABLE newtable ENABLE KEYS 可以使它更快。（就像下面这样）

CREATE TABLE newtable ....;
ALTER TABLE newtable ....;

ALTER TABLE newtable DISABLE KEYS;
INSERT INTO newtable ....;
ALTER TABLE newtable ENABLE KEYS;

DROP TABLE oldtable;

- lqez

3

如果您这样做，请不要忘记在所有插入完成后优化您的表格。 - Book Of Zeus

@BookOfZeus 在这一点上，“优化表”有什么目的？ - lqez

1

我无法比这更好地解释它：http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html - Book Of Zeus

3

启用/禁用键仅适用于MyISAM。在InnoDB上，您需要删除键，然后再次添加它们。在重新添加键之前，可以运行Optimize table进行优化。来源：http://www.mysqlperformanceblog.com/2010/12/09/thinking-about-running-optimize-on-your-innodb-table-stop/ - Guillaume Perrot

10

虽然其他答案提供了向表中添加列所需的语法示例，但实际问题的答案由N.B.提供：

由于需要移动记录，因此会增加更多的CPU使用率。
从内存使用角度来看 - 使用AFTER COLUMN选项和不使用它是相同的。
在大多数情况下，会创建一个临时表。有一些支持热模式更改的MySQL引擎（如TokuDB），它们不会创建临时表并浪费大量资源。
但是，如果你正在使用MyISAM或InnoDB进行操作 - 我会说"AFTER COLUMN"选项会花费更多时间，因为需要移动记录。

– N.B.

- Drew

2

可以提供一些官方文件的链接来确认吗？ - zhuguowei

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Book Of Zeus · Accepted Answer

这是我会做的：

CREATE TABLE newtable LIKE oldtable;
ALTER TABLE newtable ADD COLUMN columnname INT(10) UNSIGNED NOT NULL DEFAULT 0;

我不知道你的列的类型是什么。这里以 INT 为例进行说明。现在，你可以指定在哪里添加此新列。默认情况下，它将在末尾添加，除非你指定 AFTER 关键字，如果你提供了它，则必须按照你要插入的顺序指定，否则你需要把它放在最后。

INSERT INTO newtable SELECT field1, field2, field3 /*etc...*/, newcolumn = 0 FROM oldtable;

或者，如果您将其添加在列之间：

# eg: ALTER TABLE newtable ADD COLUMN columnname INT(10) UNSIGNED  NULL AFTER field2; 
INSERT INTO newtable SELECT field1, field2, newcolumn = 0, field3 /*etc...*/ FROM oldtable;

如果你想要批量执行它们，可以添加一个where子句。

一旦所有记录都在那里

DROP TABLE oldtable;
RENAME TABLE newtable to oldtable;