Shulou(Shulou.com)06/01 Report--

Scene:

The performance of mysql's alter table operation is a big problem for large tables. The way MySQL performs most changes to the table structure is to create an empty table with the new structure, find out all the data from the old table and insert it into the new table, and then delete the old table. This operation can take a long time, especially if there is insufficient memory, large tables, and many indexes.

Generally speaking, most alter table operations will lead to interruption of mysql services. For common scenarios, there are two techniques that can be used: one is to perform alter table operations on a machine that does not provide services, and then switch with the main library that provides services, and the other is shadow copy. The technique of shadow copying is to create a new table independent of the original table with the required table structure, and then exchange the two tables by renaming and deleting the table.

Not all alter table operations cause table reconstruction. For example, there are two ways to change or delete the default value of a column.

Mysql > alter table test modify column test tinyint (3) not null default 5

Show status shows that this statement has been read and inserted thousands of times, in other words, it copies a table to a new table.

In theory, mysql can skip the step of innovating the table, and the default value of the column actually exists in the .frm file of the table, so you can modify the file directly without changing the table itself. However, mysql has not adopted this optimization method, and all modify column operations will result in table reconstruction.

Mysql > alter table test alter column test set default 5

This statement modifies the .frm file directly without referring to table data. Therefore, this operation is very fast.

As you can see, it is fast to modify the .frm file of a table, but mysql sometimes rebuilds the table when it is not necessary. If you are willing to take some risks, you can have mysql make some other types of changes without rebuilding the table. However, the data should be backed up before execution, which is not officially supported.

The following actions may not require rebuilding the table:

(1) remove (do not add) the auto_increment attribute of a column

(2) add, remove, or make ENUM and SET always bright. If you remove a constant that already has row data used for other values, the query will return an empty substring value.

The basic technique is to create a new .frm file for the desired table structure, and then replace the existing table .frm file with it, like this:

Create an empty table with the same table structure and make the necessary modifications (column such as adding ENUM constant)

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 for step 2.

Mysql > show columns from film like 'rating' +-+ | Field | Type | Null | Key | Default | Extra | +-+- -- + | rating | enum ('G' 'PG','PG-13','R','NC-17') | YES | | G | | +-+-- + 1 row in set (0.00 sec)

Suppose we add a movie branch of PG-14.

Mysql > create table film_new like film;Query OK, 0 rows affected (0.26 sec) mysql > select * from film_new;Empty set (0.00 sec) mysql > alter table film_new modify column rating ENUM (0 Duplicates: 0 Warnings: 0mysql > flush tables with read lock;Query OK, 0 rows affected (0 rows affected)

Note: we are adding a new value at the end of the constant list

Using operating system commands to exchange .frm files

[root@host1 sakila] # mv film.frm film_ temp.frm [root @ host1 sakila] # mv film_new.frm film.frm [root@host1 sakila] # mv film_temp.frm film_ new.frm [root @ host1 sakila] #

If you go back to mysql, you can unlock the table and the changed effect.

Mysql > unlock tables;Query OK, 0 rows affected (0.00 sec) mysql > show columns from film like 'rating' +-+-- + | Field | Type | Null | Key | Default | Extra | + -+-+ | rating | enum ('G' 'PG','PG-13','R','NC-17' 'PG-14') | YES | | G | | +-+-+ 1 row in set (0.01 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.