优化 mySql 以更快地更改表添加列
我有一个包含 170,002,225 行、大约 35 列和两个索引的表。我想添加一栏。 alter table 命令花费了大约 10 个小时。在那段时间里处理器看起来既不忙，也没有出现过多的 IO 等待。这是一个具有大量内存的 4 路高性能盒子。
这是我能做的最好的事情吗？我可以查看一些东西来优化数据库调整中的添加列吗？
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.
Is this the best I can do? Is there something I can look at to optimize the add column in tuning of the db?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我过去遇到过非常类似的情况，我通过这种方式提高了操作的性能：
INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table;I faced a very similar situation in the past and i improve the performance of the operation in this way :
INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table;MySQL中的
ALTER TABLE实际上是用新的schema创建一个新表，然后重新INSERT所有数据并删除旧表。通过创建新表、加载数据然后重命名表，您可能会节省一些时间。来自“高性能 MySQL 书”（percona 的人）：
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.From "High Performance MySQL book" (the percona guys):
好吧，我建议使用最新的 Percona MySQL builds plus，因为 MySQL 手册中有以下注释
您可以先执行
ALTER TABLE DISABLE KEYS，然后添加列，然后ALTER TABLE ENABLE KEYS。我不认为这里可以做任何事情。顺便说一句，你不能使用 MongoDB 吗？添加列时它不会重建任何内容。
Well, I would recommend using latest Percona MySQL builds plus since there is the following note in MySQL manual
You can do
ALTER TABLE DISABLE KEYSfirst, then add column and thenALTER TABLE ENABLE KEYS. I don't see anything can be done here.BTW, can't you go MongoDB? It doesn't rebuild anything when you add column.
也许您可以在更改表之前删除索引，因为构建索引需要花费大部分时间？
Maybe you can remove the index before alter the table because what is take most of the time to build is the index?
结合其他答案的一些评论，这是对我有用的解决方案（MySQL 5.6）：
create table mytablenew like mytable;alter table mytablenew add column col4a varchar(12) not col4 之后为 null;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 添加索引index1 (col1), 添加索引index2 (col2),...add index indexN (colN);rename table mytable to mytableold, mytablenew to mytable在一个 75M 行的表上，在插入之前删除索引会导致查询在 24 分钟内完成，而不是43分钟。
其他答案/评论有
insert into mytablenew (col1) select (col1) from mytable，但这会导致ERROR 1241 (21000): Operand should contains 1 columns(s)如果选择查询中有括号。其他答案/评论有
insert into mytablenew select * from mytable;，但这会导致ERROR 1136 (21S01): Column count does not match value count at row 1if您已经添加了一列。Combining some of the comments on the other answers, this was the solution that worked for me (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 mytableOn a 75M row table, dropping the indexes before the insert caused the query to complete in 24 minutes rather than 43 minutes.
Other answers/comments have
insert into mytablenew (col1) select (col1) from mytable, but this results inERROR 1241 (21000): Operand should contain 1 column(s)if you have the parenthesis in the select query.Other answers/comments have
insert into mytablenew select * from mytable;, but this results inERROR 1136 (21S01): Column count doesn't match value count at row 1if you've already added a column.