优化MySQL以更快地更改表添加列 [英] Optimize mySql for faster alter table add column

问题描述

我有一个表，该表具有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.

这是我能做的最好的吗?我有什么可以看的，以优化db调整中的add列?

Is this the best I can do? Is there something I can look at to optimize the add column in tuning of the db?

推荐答案

过去我遇到过非常相似的情况，并且我以这种方式提高了操作性能:

I faced a very similar situation in the past and i improve the performance of the operation in this way :

1. 使用包含新列的新表(使用当前表的结构).
2. 执行INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table;
3. 重命名当前表
4. 使用当前表的名称重命名新表.

1. Create a new table (using the structure of the current table) with the new column(s) included.
2. execute a INSERT INTO new_table (column1,..columnN) SELECT (column1,..columnN) FROM current_table;
3. rename the current table
4. rename the new table using the name of the current table.

这篇关于优化MySQL以更快地更改表添加列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！