优化 mySql 以更快地更改表添加列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5677932/
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
Optimize mySql for faster alter table add column
提问by Andrew
I have a table that has 170,002,225 rows with about 35 columns and two indexes. I want to add a column. The alter table command took about 10 hours. Neither the processor seemed busy during that time nor were there excessive IO waits. This is on a 4 way high performance box with tons of memory.
我有一个表，它有 170,002,225 行，大约有 35 列和两个索引。我想添加一列。alter table 命令花费了大约 10 个小时。在此期间，处理器似乎并不忙，也没有过多的 IO 等待。这是一个带有大量内存的 4 路高性能盒子。
Is this the best I can do? Is there something I can look at to optimize the add column in tuning of the db?
这是我能做的最好的吗？有什么我可以看的东西来优化数据库调整中的添加列吗？
回答by RRUZ
I faced a very similar situation in the past and i improve the performance of the operation in this way :
我过去遇到过非常相似的情况，我以这种方式提高了操作的性能：
- Create a new table (using the structure of the current table) with the new column(s) included.
- execute a
INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table; - rename the current table
- rename the new table using the name of the current table.
- 创建一个包含新列的新表（使用当前表的结构）。
- 执行一个
INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table; - 重命名当前表
- 使用当前表的名称重命名新表。
回答by FoneyOp
ALTER TABLEin MySQL is actually going to create a new table with new schema, then re-INSERTall the data and delete the old table. You might save some time by creating the new table, loading the data and then renaming the table.
ALTER TABLE在 MySQL 中实际上是要创建一个具有新模式的新表，然后重新INSERT所有数据并删除旧表。您可以通过创建新表、加载数据然后重命名表来节省一些时间。
From "High Performance MySQL book" (the percona guys):
来自“高性能 MySQL 书”（percona 人）：
The usual trick for loading MyISAM table efficiently is to disable keys, load the data and renalbe the keys:
有效加载 MyISAM 表的常用技巧是禁用键、加载数据和肾为键：
mysql> ALTER TABLE test.load_data DISABLE KEYS;
-- load data
mysql> ALTER TABLE test.load_data ENABLE KEYS;
回答by Vladislav Rastrusny
Well, I would recommend using latest Percona MySQL builds plus since there is the following note in MySQL manual
好吧，我建议使用最新的 Percona MySQL 版本，因为 MySQL 手册中有以下说明
In other cases, MySQL creates a temporary table, even if the data wouldn't strictly need to be copied. For MyISAM tables, you can speed up the index re-creation operation (which is the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.
在其他情况下，即使数据并不严格需要复制，MySQL 也会创建一个临时表。对于 MyISAM 表，您可以通过将 myisam_sort_buffer_size 系统变量设置为高值来加快索引重新创建操作（这是更改过程中最慢的部分）。
You can do ALTER TABLE DISABLE KEYSfirst, then add column and then ALTER TABLE ENABLE KEYS. I don't see anything can be done here.
您可以ALTER TABLE DISABLE KEYS先执行，然后添加列，然后添加ALTER TABLE ENABLE KEYS. 我看不到这里可以做任何事情。
BTW, can't you go MongoDB? It doesn't rebuild anything when you add column.
顺便说一句，你不能去MongoDB吗？添加列时它不会重建任何内容。
回答by Gigamegs
Maybe you can remove the index before alter the table because what is take most of the time to build is the index?
也许您可以在更改表之前删除索引，因为构建索引所需的大部分时间是什么？
回答by enharmonic
Combining some of the comments on the other answers, this was the solution that worked for me (MySQL 5.6):
结合对其他答案的一些评论，这是对我有用的解决方案（MySQL 5.6）：
create table mytablenew like mytable;alter table mytablenew add column col4a varchar(12) not null after col4;alter table mytablenew drop index index1, drop index index2,...drop index indexN;insert into mytablenew (col1,col2,...colN) select col1,col2,...colN from mytable;alter table mytablenew add index index1 (col1), add index index2 (col2),...add index indexN (colN);rename table mytable to mytableold, mytablenew to mytable
create table mytablenew like mytable;alter table mytablenew add column col4a varchar(12) not null after col4;alter table mytablenew drop index index1, drop index index2,...drop index indexN;insert into mytablenew (col1,col2,...colN) select col1,col2,...colN from mytable;alter table mytablenew add index index1 (col1), add index index2 (col2),...add index indexN (colN);rename table mytable to mytableold, mytablenew to mytable
On a 75M row table, dropping the indexes before the insert caused the query to complete in 24 minutes rather than 43 minutes.
在一个 75M 的行表上，在插入之前删除索引导致查询在 24 分钟而不是 43 分钟内完成。
Other answers/comments have insert into mytablenew (col1) select (col1) from mytable, but this results in ERROR 1241 (21000): Operand should contain 1 column(s)if you have the parenthesis in the select query.
其他答案/评论有insert into mytablenew (col1) select (col1) from mytable，但ERROR 1241 (21000): Operand should contain 1 column(s)如果您在选择查询中有括号，则会导致结果。
Other answers/comments have insert into mytablenew select * from mytable;, but this results in ERROR 1136 (21S01): Column count doesn't match value count at row 1if you've already added a column.
其他答案/评论有insert into mytablenew select * from mytable;，但ERROR 1136 (21S01): Column count doesn't match value count at row 1如果您已经添加了一列，则会导致。
