mysql修改表结构耗时长的解决方案

背景

MySQL的ALTER TABLE性能可能成为一个问题（当表非常大的时候）。 MySQL执行大多数更改，是通过创建一个空表，将旧表中的所有数据插入到新表中，并删除旧表。 这可能需要很长时间，特别是如果您的内存不足，而且表格庞大且索引很多。 许多人都有ALTER TABLE操作的经验，需要几个小时或几天才能完成。

MySQL’s ALTER TABLE performance can become a problem with very large tables. MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table. This can take a very long time, especially if you’re short on memory and the table is large and has lots of indexes. Many people have experience with ALTER TABLE operations that have taken hours or days to complete.

METHOD 1

Assuming db is the database where xxx resides

Step 1. CREATE TABLE zzz LIKE xxx;

Step 2. ALTER TABLE zzz add yyy smallint default NULL; -- Not using 0 for now

Step 3. Perform the following in the OS

service mysql stop
cd /var/lib/mysql/db
mv xxx.frm xxxold.frm
cp zzz.frm xxx.frm
service mysql start

Step 4. Try and see if xxx is accesible

If this does not work try this:

METHOD 2

Step 1. CREATE TABLE zzz LIKE xxx;

Step 2. ALTER TABLE zzz add yyy smallint default 0;

Step 3. INSERT INTO zzz (col1,col2,...colN) SELECT col1,col2,...colN FROM xxx;

Step 4. ALTER TABLE xxx RENAME jjj;

Step 5. ALTER TABLE zzz RENAME xxx;



CREATE TABLE main_table_new LIKE main_table;
ALTER TABLE main_table_new ADD COLUMN location varchar(256);
INSERT INTO main_table_new (fields_in_main_table) SELECT * FROM main_table;
RENAME TABLE main_table TO main_table_old, main_table_new TO main_table;
DROP TABLE main_table_old;

The second method has to work. The first is recommended from that book I mentioned.

method 3

ALTER TABLE test.load_data DISABLE KEYS;
...
ALTER TABLE test.load_data ENABLE KEYS;

Give them a Try !!!

P.S. before trying the first method, backup everything (LVM snapshot)