How to solve the problem of slow field modification in MYSQL large tables

For large tables, the performance of MYSQL's ALTER TABLE operation will become a significant challenge. The way MYSQL performs most table structure modification operations is to create an empty table with a new table structure, retrieve all the data from the old table, insert it into the new table, and then delete the old table. This operation can be very time-consuming if there is insufficient memory and the table is large and has many indexes. ALTER TABLE operations often take hours or even days to complete, which is a situation many people have experienced.

Normally, most ALTER TABLE operations will stop the MYSQL service. For common scenarios, there are only two techniques that can be used:

• One is to first perform the ALTER TABLE operation on a machine that does not provide services, and then communicate with the main library that provides services Switch;

• Another technique is "shadow copy". The shadow copy technique involves the process of creating a new table and operating on the desired table structure, and then swapping the two tables through rename and drop table operations.

Not all ALTER TABLE operations will cause table reconstruction. There are two different ways to change or remove a column's default value, one of which is fast and the other of which is slower.

Suppose you want to change the default rental period of a movie from three days to five days. The following is a very slow way:

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

The number of operations for 1000 reads and 1000 inserts can be obtained from "SHOW STATUS". In other words, it copies the entire table to a new table, including the column data type, size, and null attributes.

Theoretically, MYSQL can skip the step of creating a new table. The table's default values ​​are stored in the .frm file, so that file can be edited directly without modifying the table itself. Although this optimization method is feasible, MYSQL does not currently use it, so modifying column operations requires rebuilding the table.

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 the table data. So this operation is very fast.

Only modify the .frm file

Although it is very fast to modify the .frm file of the table, MySQL sometimes rebuilds the table unnecessarily. We can learn from the above example see. By taking some risk, MYSQL can be allowed to make other types of modifications without rebuilding the table.

Note that the techniques demonstrated below are not officially supported or documented, and may not work properly. Use these techniques at your own risk. >It is recommended to back up the data before executing!

The following operations may not require rebuilding the table:

• Remove (not add) the AUTO_INCREMENT attribute of a column.

• Add, remove, or change ENUM and SET always on. If you delete a constant value that has already been used in a row, the query will return an empty string.

Steps:

• Create an empty table with the same structure and make the required modifications (for example: add ENUM constants).

• Execute FLUSH TABLES WITH READ LOCK. This will close all tables in use and prevent any tables from being opened.

• Exchange .frm files.

• Execute UNLOCK TABLES to release the read lock in the second step.

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';

Field	Type	Null	Key	Default	Extra
rating	enum('G','PG','PG-13','R','NC- 17')	YES		G

##Hypothesis 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 at the end of the constant list. If you put the new value 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, wait.

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

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

Return to the Mysql command line, now you can unlock the table and see the effect of the change:

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 that needs to be done is to delete the auxiliary table created to complete this operation:

mysql> DROP TABLE film_new;

The above is the detailed content of How to solve the problem of slow field modification in MYSQL large tables. For more information, please follow other related articles on the PHP Chinese website!