MYSQL大表改欄位慢問題的解決

2023-09-06 22:15:57

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

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

Field	Type	Null	Key	Default	Extra
rating	enum('G','PG','PG-13','R','NC-17')	YES		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;

注意，我們是在常數列表的末尾增加一個新的值。如果把新增的值放在中間，例如：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大表改欄位慢問題的解決的文章就介紹到這了,更多相關MYSQL大表改欄位慢內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MYSQL大表改欄位慢問題的解決

只修改.frm檔案

熱門文章