mysql加快alter操作

場景:

    mysql的alter table 操作的性能對大表來說是個大問題.mysql執行大部分修改表結構的操作方法是用新的結構創建一個空表,從舊表中查出所有數據插入新表,然后刪除舊表.這樣操作可能花費很長時間,如果內存不足而表又很大,而且還有很多索引的情況下,此種情況更甚.

    一般而言,大部分alter table操作將導致mysql服務中斷,對于常見場景,能使用的技巧有兩種,一種是先在一臺不提供服務的機器上執行alter table操作,然后和提供服務的主庫進行切換,另外一種是影子拷貝.影子拷貝的技巧是用要求的表結構創建一張和原表無關的新表,然后通過重命名和刪表的操作交換兩張表.

    不是所有的alter table操作都會引起表的重建,列如有兩種方法可以改變或刪除某個列的默認值.

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

show status 顯示了這個語句做了上千次讀和上千次插入,換句話說,它拷貝了一張表到一張新表.

理論上,mysql 可以跳過創新表的步驟,列的默認值實際上存在表的.frm文件中,所以可以直接修改這個文件而不需要改動表本身.然而mysql還沒有采用這種優化方法,所有的modify column操作都將導致表重建.

mysql>alter table test alter column  test set default 5;

這個語句會直接修改.frm文件而不涉及表數據.所以,這個操作是非常快的.

由此可以看出,修改表的.frm文件是很快的,但是mysql有時候會在沒必要的時候也重建表.如果愿意冒一些風險,可以讓mysql做一些其他類型的修改而不用重建表.但是在執行前首先應備份數據,該操作不受官方支持.

下面這些操作可能不需要重建表:

(1)移除(不受增加)一個列的auto_increment屬性

(2)增加,移除,或者更ENUM和SET常亮.如果移除的是已經有行數據用到其他值的常量,查詢將會返回一個空子串值.

基本的技術是為想要的表結構創建一個新的.frm文件,然后用它替換掉已經存在的表的.frm文件,像下面這樣:

• 創建一張有相同表結構的空表,并進行所需要的修改(列如增加ENUM常量)

• 執行flush tables with read lock.這將會關閉所有正在使用的表,并禁止任何表被打開.

• 交換.frm文件

• 執行unlock tables來釋放第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)

假設我們增加一個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('G','PG','PG-13','R','NC-17','PG-14') default 'G';
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

注意:我們是在常量列表的末尾增加一直新值

利用操作系統命令交換.frm文件

[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]#

再回到mysql就可以解鎖表和更改后的效果了

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)