Mysql如何加快大表的ALTER TABLE操作速度

MYSQL的ALTER TABLE操作的性能對大表來說是個(gè)大問題。MYSQL執(zhí)行大部分修改表結(jié)構(gòu)操作的方法是用新的表結(jié)構(gòu)創(chuàng)建一個(gè)空表，從舊表中查出所有數(shù)據(jù)插入新表，然后刪除舊表。這樣操作可能需要花費(fèi)很長時(shí)間，如果內(nèi)存不足而表又很大，而且還有很多索引的情況下尤其如此。許多人都有這樣的經(jīng)驗(yàn)，ALTER TABLE操作需要花費(fèi)數(shù)個(gè)小時(shí)甚至數(shù)天才能完成。

一般而言，大部分ALTER TABLE操作將導(dǎo)致MYSQL服務(wù)中斷。對常見的場景，能使用的技巧只有兩種：

• 一種是先在一臺(tái)不提供服務(wù)的機(jī)器上執(zhí)行ALTER TABLE操作，然后和提供服務(wù)的主庫進(jìn)行切換；
• 另外一種技巧就是“影子拷貝”。影子拷貝技巧是用要求的表結(jié)構(gòu)創(chuàng)建一張新表，然后通過重命名和刪表操作交換兩張表。

不是所有的ALTER TABLE操作都會(huì)引起表重建。例如，有兩種方法可以改變或刪除一個(gè)列的默認(rèn)值（一種方法很快，另一種則很慢）。

假如要修改電影的默認(rèn)租賃期限，從三天改到五天。下面是很慢的方式：

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

SHOW STATUS顯示這個(gè)語句做了1000次讀和1000次插入操作。換句話說，它拷貝了整張表到一張新表，甚至列的類型、大小和可否為null屬性都沒有改變。

理論上，MYSQL可以跳過創(chuàng)建新表的吧步驟。列的默認(rèn)值實(shí)際上存在表的.frm文件中，所以可以直接修改這個(gè)文件而不需要改動(dòng)表本身。然而MYSQL還沒有采用這種優(yōu)化的方法，所以MODIFY COLUMN操作都將導(dǎo)致表重建。

另外一種方法是通過ALTER COLUMN操作來改變列的默認(rèn)值；

mysql> ALTER TABLE film ALTER COLUMN rental_duration set DEFAULT 5;

這個(gè)語句會(huì)直接修改.frm文件而不涉及表數(shù)據(jù)。所以這個(gè)操作是非常快的。

只修改.frm文件

從上面的例子我們看到修改表的.frm文件是很快的，但MYSQL有時(shí)候會(huì)在沒有必要的時(shí)候也重建表。如果愿意冒一些風(fēng)險(xiǎn)，可以讓MYSQL做一些其他類型的修改而不用重建表。

注意 下面要演示的技巧是不受官方支持的，也沒有文檔記錄，并且也可能不能正常工作，采用這些技術(shù)需要自己承擔(dān)風(fēng)險(xiǎn)。>建議在執(zhí)行之前首先備份數(shù)據(jù)！

下面這些操作是有可能不需要重建表的：

• 移除（不是增加）一個(gè)列的AUTO_INCREMENT屬性。
• 增加、移除，或更改ENUM和SET常亮。如果移除的是已經(jīng)有行數(shù)據(jù)用到其值的常量，查詢將會(huì)返回一個(gè)空字符串。

步驟：

• 創(chuàng)建一張有相同結(jié)構(gòu)的空表，并進(jìn)行所需要的修改(例如：增加ENUM常量)。
• 執(zhí)行FLUSH TABLES WITH READ LOCK。這將會(huì)關(guān)閉所有正在使用的表，并且禁止任何表被打開。
• 交換.frm文件。
• 執(zhí)行UNLOCK TABLES 來釋放第二步的讀鎖。

下面以給film表的rating列增加一個(gè)常量為例來說明。當(dāng)前列看起來如下：

mysql> SHOW COLUMNS FROM film LIKE 'rating';

假設(shè)我們需要為那些對電影更加謹(jǐn)慎的父母們增加一個(gè)PG-14的電影分級：

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;

注意，我們是在常量列表的末尾增加一個(gè)新的值。如果把新增的值放在中間，例如：PG-13之后，則會(huì)導(dǎo)致已經(jīng)存在的數(shù)據(jù)的含義被改變：已經(jīng)存在的R值將變成PG-14，而已經(jīng)存在的NC-17將成為R，等等。

接下來用操作系統(tǒng)的命令交換.frm文件：

/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

再回到Mysql命令行，現(xiàn)在可以解鎖表并且看到變更后的效果了：

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

最后需要做的是刪除為完成這個(gè)操作而創(chuàng)建的輔助表：

mysql> DROP TABLE film_new;

到此這篇關(guān)于MYSQL大表改字段慢問題的解決的文章就介紹到這了,更多相關(guān)MYSQL大表改字段慢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論