Schema optimization and indexing

The performance problem arises when ALTER TABLE is performed on a large table. MySQL Most of the changes are as follows: Create an empty table based on the new table structure, remove the data from the old table and insert it into the new table, delete the old table. This is a very long process. Many people have had to wait 1 hours or 1 days for painful experiences after alter table.

MySQL AB has started to improve performance in this area. Some of the upcoming features are support for "online" operations without locking the table. InnoDB developers are also actively trying to develop a sort to create an index. MyISAM has supported this feature, with the result that indexes are faster and the index layout is compressed.

Not all alter tables will rebuild the table. For example, you can change or delete column defaults (one fast, one slow) in two ways. If you want to change a film lease term for the original 3 days to the present 5 days. The method is as follows:

mysql> ALTER TABLE sakila.film
　　 　-> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

Using show status to monitor this statement, it did 1000 reads and 1000 inserts. In other words, copy a table into a new table. Even if the type of the column, whether the size, or not, is null has not changed.

In principle, MySQL can skip creating new tables. This default value is actually stored in the. frm file. So you can change it without the need to contact the table. MySQL does not do optimizations, however any modify column will cause the table to be rebuilt.

You can use ALTER column to modify:

mysql> ALTER TABLE sakila.film
　　 　-> ALTER COLUMN rental_duration SET DEFAULT 5;

This statement modifies the. frm file without having to manipulate the table. The result is very fast.

Just modify the. frm file

We found that modifying the marked. frm file is very fast and when it can't do that, MySQL sometimes rebuilds the table. If you are willing to take on a part of the risk, you can tell MySQL to do some type of modification without rebuilding the table.

You can do the following types of operations without rebuilding the table:

Remove the Auto_increment property of the column.

Add, remove, and change the enum and set. If you remove a constant and some rows contain this value, the query returns that value will be an empty string

The basic technique is to create a. frm file and copy it to the location of the. frm file where the table exists. The steps are as follows:

Create an empty table, of course the table layout must be accurate. Except for some changed items.

Executes flush tables with READ LOCK. This step closes all the tables used and prevents them from being opened.

Swap. frm files.

Performs unlock tables release read locks.

For example, we add a constant to the rating column of the Skila.film table. The current columns are as follows:

mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating';
+--------+------------------------------------+------+-----+---------+-------+
| Field 　| Type 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 | Null | Key | Default | Extra |
+--------+------------------------------------+------+-----+---------+-------+
| rating | enum('G','PG','PG-13','R','NC-17') | YES 　| 　 　 | G 　 　 　 | 　 　 　 |
+--------+------------------------------------+------+-----+---------+-------+

We add a PG-14 to this column.

mysql> CREATE TABLE sakila.film_new LIKE sakila.film;
mysql> ALTER TABLE sakila.film_new
　　 　-> MODIFY COLUMN rating ENUM('G','PG','PG-13','R','NC-17', 'PG-14')
　　 　-> DEFAULT 'G';
mysql> FLUSH TABLES WITH READ LOCK;

Notice that we are actually adding this PG-14, not in the middle, and doing so will modify the existing value, the R value becomes PG-14,NC-17 and so on.

Now even exchange. frm files, operating system commands are as follows

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

When we get back to the MySQL prompt, we'll unlock it and look at the result of the change.

mysql> UNLOCK TABLES;
mysql> SHOW COLUMNS FROM sakila.film LIKE 'rating'\G
*************************** 1. row ***************************
Field: rating
　Type: enum('G','PG','PG-13','R','NC-17','PG-14')

The last step is to delete the table that we have created.

mysql> DROP TABLE sakila.film_new;