" />

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MYSQL大表改字段慢問題的解決

 更新時間：2023年03月21日 16:18:46   作者：Java開發(fā)高級進階公眾號  
本文主要介紹了MYSQL大表改字段慢問題的解決，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

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

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

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

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

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

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

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

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

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

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

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

這個語句會直接修改.frm文件而不涉及表數(shù)據(jù)。所以這個操作是非?？斓?。

只修改.frm文件

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

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

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

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

步驟：

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

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

mysql> SHOW COLUMNS FROM film LIKE 'rating';
FieldTypeNullKeyDefaultExtra
ratingenum('G','PG','PG-13','R','NC-17')YESG

假設我們需要為那些對電影更加謹慎的父母們增加一個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之后，則會導致已經存在的數(shù)據(jù)的含義被改變：已經存在的R值將變成PG-14，而已經存在的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')

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

mysql> DROP TABLE film_new;

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

相關文章

最新評論