MYSQL大表改字段慢問題如何解決

本文小編為大家詳細介紹“MYSQL大表改字段慢問題如何解決”，內容詳細，步驟清晰，細節處理妥當，希望這篇“MYSQL大表改字段慢問題如何解決”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學習新知識吧。

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

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

• 一種是先在一臺不提供服務的機器上執行ALTER TABLE操作，然后和提供服務的主庫進行切換；

• 另外一種技巧就是“影子拷貝”。影子拷貝技巧是用要求的表結構創建一張新表，然后通過重命名和刪表操作交換兩張表。

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

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

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

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

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

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

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

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

只修改.frm文件

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

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

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

• 移除（不是增加）一個列的AUTO_INCREMENT屬性。

• 增加、移除，或更改ENUM和SET常亮。如果移除的是已經有行數據用到其值的常量，查詢將會返回一個空字符串。

步驟：

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

• 執行FLUSH TABLES WITH READ LOCK。這將會關閉所有正在使用的表，并且禁止任何表被打開。

• 交換.frm文件。

• 執行UNLOCK TABLES 來釋放第二步的讀鎖。

下面以給film表的rating列增加一個常量為例來說明。當前列看起來如下：

mysql> SHOW COLUMNS FROM film LIKE 'rating';

假設我們需要為那些對電影更加謹慎的父母們增加一個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;

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

接下來用操作系統的命令交換.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命令行，現在可以解鎖表并且看到變更后的效果了：

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

最后需要做的是刪除為完成這個操作而創建的輔助表：

mysql> DROP TABLE film_new;

讀到這里，這篇“MYSQL大表改字段慢問題如何解決”文章已經介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領會，如果想了解更多相關內容的文章，歡迎關注億速云行業資訊頻道。