High-performance MySQL reading notes (i): Schema and data type optimization

4.5 Speed The alter TABLE operation

Principle:

The performance of the ALTER TABLE operation for MySQL is a big problem for large tables.

MySQL performs most of the modification of table structure operations by creating an empty table with the new structure, identifying all the data from the old table to insert a new table, and then deleting the old table. This can take a long time, especially if there is not enough memory and the table is large, and there are many indexes.

Characteristics:

• Most alter TABLE operations will cause the MySQL service to break
• ALTER table is essentially a table that constructs a new structure, inserting data from the old table into a new table (show STATUS shows that the statement did 1 000 reads and 1 000 insertions)
• ALTER table locks the table, the entire table is read-only locked

Workaround:

1: One is to perform an alter TABLE operation on a machine that does not serve, and then switch to the main repository that provides the service;

2: Another technique is "shadow copy." The trick of shadow copy is to create a new table that is independent of the source table with the required table structure, and then exchange two tables by renaming and deleting the table.

High-performance MySQL reading notes (i): Schema and data type optimization