MySQL ALTER TABLE ADD COLUMN AFTER COLUMN 的性能 - 在大表上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8190228/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performance of MySQL ALTER TABLE ADD COLUMN AFTER COLUMN - on a large table
提问by Drew
I want to achieve the following use the following command to add a column to an existing table:
我想实现以下使用以下命令向现有表添加列：
ALTER TABLE foo ADD COLUMN bar AFTER COLUMN old_column;
Can this option take substantially longer than the same command without the AFTER COLUMN option, as follows?
与没有 AFTER COLUMN 选项的相同命令相比，此选项是否会花费更长的时间，如下所示？
ALTER TABLE foo ADD COLUMN bar;
Will the first command use a greater amount of tmp table space during execution to perform the action?
第一个命令在执行期间是否会使用更多的 tmp 表空间来执行操作？
Context: I have a very large table (think over a billion rows) and I want to add an additional column using the AFTER COLUMN option, but I don't want to be penalized too much.
上下文：我有一个非常大的表（想想超过 10 亿行），我想使用 AFTER COLUMN 选项添加一个额外的列，但我不想受到太多惩罚。
采纳答案by Drew
While the other answers are useful as examples of the syntax required to add columns to a table, the answer to the actual question was provided by N.B.:
虽然其他答案作为将列添加到表所需的语法示例很有用，但实际问题的答案由NB提供：
- You'd get more CPU usage since records would have to be shifted.
- From the memory usage point of view - it'd be the samewith AFTER COLUMN option and without it.
- In most cases, a tmp table is created.There are MySQL engines that support hot schema changes (TokuDB being one) that don't create the tmp table and waste tons of resources.
However, if you're doing this with MyISAM or InnoDB - I'd say that "AFTER COLUMN" option will take slightly more time due to record shifting.
– N.B.
- 您将获得更多的 CPU 使用率，因为必须移动记录。
- 从内存使用的角度来看 - 它与 AFTER COLUMN 选项相同，没有它。
- 在大多数情况下，会创建一个 tmp 表。有一些 MySQL 引擎支持热模式更改（TokuDB 就是其中之一），它们不会创建 tmp 表并浪费大量资源。
但是，如果您使用 MyISAM 或 InnoDB 执行此操作 -我会说“AFTER COLUMN”选项会因记录移位而花费更多时间。
–注意
回答by Book Of Zeus
Here's what I would do:
这是我会做的：
CREATE TABLE newtable LIKE oldtable;
ALTER TABLE newtable ADD COLUMN columnname INT(10) UNSIGNED NOT NULL DEFAULT 0;
I don't know the type of your column. I give an example with INT. Now here you can specify WHERE you want to add this new column. By default it will add it at the end unless you specify the AFTER keyword, if you provide it, you will have to specify in the order you will insert otherwise you need to put it at the end.
我不知道你的专栏类型。我举一个 INT 的例子。现在，您可以在此处指定要添加此新列的位置。默认情况下，除非您指定 AFTER 关键字，否则它会在最后添加它，如果您提供它，则必须按插入顺序指定，否则您需要将其放在最后。
INSERT INTO newtable SELECT field1, field2, field3 /*etc...*/, newcolumn = 0 FROM oldtable;
OR, if you added it between columns:
或者，如果您在列之间添加它：
# 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;
You can add a where clause if you want to do them in batch.
如果要批量执行，可以添加 where 子句。
Once all the records are there
一旦所有记录都在那里
DROP TABLE oldtable;
RENAME TABLE newtable to oldtable;
回答by lqez
Create another table and alter the new table. ( like Book Of Zeus did )
创建另一个表并更改新表。（就像宙斯之书那样）
And using ALTER TABLE newtable DISABLE KEYSand ALTER TABLE newtable ENABLE KEYSbefore and after the inserting query can make it faster. ( like below )
并且在插入查询之前和之后使用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;
