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)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30317998/viewspace-2654181/，如需轉載，請註明出處，否則將追究法律責任。