Solve the problem of slow field modification of MYSQL large table

How Mysql speeds up the ALTER TABLE operation speed of large tables

The performance of MYSQL's ALTER TABLE operation is a big problem for large tables. MYSQL performs most of the operations to modify table structures by creating an empty table with the new table structure, finding out all data from the old table and inserting it into the new table, and then deleting the old table. This can take a long time, especially if the memory is insufficient and the table is large, and there are many indexes. Many people have this experience, and the ALTER TABLE operation takes hours or even days to complete.

Generally speaking, most ALTER TABLE operations will cause the MYSQL service to be interrupted. There are only two techniques that can be used for common scenarios:

• One is to first perform the ALTER TABLE operation on a machine that does not provide services, and then switch to the main library that provides services;
• Another technique is "shadow copy". The shadow copying technique is to create a new table with the required table structure, and then exchange two tables through renaming and deleting operations.

Not all ALTER TABLE operations will cause table reconstruction. For example, there are two ways to change or delete the default value of a column (one method is fast and the other is slow).

If you want to modify the default rental period of the movie, it will change from three days to five days. Here is a very slow way:

mysql> ALTER TABLE film modify column rental_duration tinyint(3) not null default 5;

SHOW STATUS shows that this statement has performed 1000 reads and 1000 inserts. In other words, it copies the entire table to a new table, and even the column type, size, and null properties have not changed.

In theory, MYSQL can skip the step of creating a new table. The default values ​​of the columns actually exist in the table's .frm file, so you can directly modify this file without changing the table itself. However, MYSQL has not adopted this optimization method, so the MODIFY COLUMN operation will lead to table reconstruction.

Another method is to change the default value of the column through the ALTER COLUMN operation;

mysql> ALTER TABLE film ALTER COLUMN rental_duration set DEFAULT 5;

This statement will directly modify the .frm file without involving table data. So this operation is very fast.

Modify only the .frm file

From the example above, we can see that modifying the .frm file of a table is very fast, but MYSQL sometimes rebuilds the table when it is not necessary. If you are willing to take some risks, you can let MYSQL make some other types of modifications without rebuilding the table.

Note The techniques to be demonstrated below are not officially supported, are not documented, and may not work properly. Using these techniques requires your own risk. > It is recommended to back up the data first before execution!

The following operations may not require rebuilding the table:

• Removes (not adds) the AUTO_INCREMENT property of a column.
• Add, remove, or change ENUM and SET to always be on. If you remove a constant whose value is used by rows of data, the query will return an empty string.

step:

• Create an empty table with the same structure and make the required modifications (for example: adding ENUM constants).
• Perform FLUSH TABLES WITH READ LOCK. This will close all tables in use and prohibit any tables from being opened.
• Exchange .frm files.
• Execute UNLOCK TABLES to release the second step of the read lock.

The following is an example of adding a constant to the rating column of the film table. The current column looks like this:

mysql> SHOW COLUMNS FROM film LIKE 'rating';

Suppose we need to add a PG-14 movie rating for parents who are more cautious about movies:

mysql> CREATE TABLE film_new like film;
mysql> ALTER TABLE film_new modify column rating ENUM('G','PG','PG-13','R','NC-17','PG-14') DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK;

Note that we are adding a new value to the end of the constant list. If the newly added value is placed in the middle, for example, after PG-13, the meaning of the existing data will be changed: the existing R value will become PG-14, and the existing NC-17 will become R, etc.

Next, use the operating system command to exchange the .frm file:

/var/lib/mysql/sakila# mv  film_tmp.frm
/var/lib/mysql/sakila# mv film_new.frm 
/var/lib/mysql/sakila# mv film_tmp.frm film_new.frm

Go back to the Mysql command line, you can now unlock the table and see the changed effect:

mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM film like 'rating'\G

****************** 1. row*********************

Field: rating
Type: enum('G','PG','PG-13','R','NC-17','PG-14')

The last thing you need to do is delete the auxiliary table created to complete this operation:

mysql> DROP TABLE film_new;

This is the article about solving the problem of slow field modification of MYSQL large tables. For more related content on slow field modification of MYSQL large tables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!