MySQL大表添加一列的實現方法

這篇文章主要介紹了MySQL大表添加一列的實現方法，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

mysql中，一張表里有3億數據，未分表，要求是在這個大表里添加一列數據。數據庫不能停，并且還有增刪改操作。請問如何操作？

以前老版本 MySQL 添加一列的方式：

ALTER TABLE 你的表 ADD COLUMN 新列 char(128);

會造成鎖表，簡易過程如下：

• 新建一個和 Table1 完全同構的 Table2

• 對表 Table1 加寫鎖

• 在表 Table2 上執行 ALTER TABLE 你的表 ADD COLUMN 新列 char(128)

• 將 Table1 中的數據拷貝到 Table2

• 將 Table2 重命名為 Table1 并移除 Table1，釋放所有相關的鎖

如果數據量特別特別大，那么鎖表時間很長，期間所有表更新都會阻塞，線上業務不能正常執行。

針對 MySQL 5.6（不包含）之前的版本，通過觸發器將一個表的更新在另一個表上重復，并進行數據同步，當數據同步完成時，業務上修改表名為新表并發布。業務不會暫停。觸發器設置類似于：

create trigger person_trigger_update AFTER UPDATE on 原有表 for each row 
begin set @x = "trigger UPDATE";
Replace into 新表 SELECT * from 原有表 where 新表.id = 原有表.id;
END IF;
end;

MySQL 5.6（包含） 以后的版本引入了在線 DDL 的功能:

Alter table 你的表 , ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}, LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }

其中的參數：

ALGORITHM：

• DEFAULT：默認方式，在 MySQL 8.0中，如果未顯示指定 ALGORITHM，那么會優先選擇 INSTANT 算法，如果不行再使用 INPLACE 算法，如果不支持 INPLACE 算法則使用 COPY 的方式完成

• INSTANT：8.0 中新添加的算法，添加列是立即返回。但是不能是虛擬列。這個原理很簡單，對于新建一列，表所有原有數據并不是立刻發生變化，只是在表字典里面記錄下這個列和默認值，對于默認的 Dynamic 行格式（其實就是 Compressed 的變種），如果更新了這一列則原有數據標記為刪除在末尾追加更新后的記錄。這樣做就是沒有提前預留出列空間，之后更新可能經常會發生行記錄空間變動。但是對于大多數業務，都是最近的時間的記錄才會修改，所以問題不大。

• INPLACE：在原表上直接進行修改，不會拷貝臨時表，可以逐條記錄修改，不會產生大量的 undolog 以及 redolog，不會占用很多 buffer。可以避免重建表帶來的IO和CPU消耗，保證期間依然良好的性能和并發。

• COPY：拷貝到臨時新表上進行修改。由于記錄拷貝，會產生大量的 undolog 以及 redolog，并占用很多 buffer，對業務性能有影響。

LOCK：

•  DEFAULT：和 ALGORITHM 的 DEFAULT 類似

• NONE：無鎖，允許并發讀取和更新表

• SHARED：共享鎖，允許讀取不允許更新

• EXCLUSIVE：不允許讀取和更新

各個版本支持的在線 DDL 修改使用的算法的對比：

參考文檔：

MySQL 5.6:https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.htmlMySQL

5.7：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.htmlMySQL

8.0：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

可以通過：

ALTER TABLE 你的表 ADD COLUMN 新列 char(128), ALGORITHM=INSTANT, LOCK=NONE;

類似的語句，實現在線增加字段。最好還是明確 ALGORITHM 以及 LOCK，這樣執行 DDL 的時候能明確知道到底會對線上業務有多大影響。

同時，執行在線 DDL 的過程大概是：

可以看出，在開始階段需要 metadata lock，metadata lock 是在 5.5 才引入到mysql，之前也有類似保護元數據的機制，只是沒有明確提出 metadata lock 概念而已。但是 5.5 之前版本(比如5.1)與5.5之后版本在保護元數據這塊有一個顯著的不同點是，5.1對于元數據的保護是語句級別的，5.5對于metadata的保護是事務級別的。所謂語句級別，即語句執行完成后，無論事務是否提交或回滾，其表結構可以被其他會話更新；而事務級別則是在事務結束后才釋放 metadata lock。

引入 metadata lock 后，主要解決了2個問題，一個是事務隔離問題，比如在可重復隔離級別下，會話A在2次查詢期間，會話B對表結構做了修改，兩次查詢結果就會不一致，無法滿足可重復讀的要求；另外一個是數據復制的問題，比如會話A執行了多條更新語句期間，另外一個會話B做了表結構變更并且先提交，就會導致 slave 在重做時，先重做 alter，再重做 update 時就會出現復制錯誤的現象。

如果當前有很多事務在執行，并且有那種包含大查詢的事務，例如：

START TRANSACTION;
select count(*) from 你的表

這樣類似的會執行較長時間的事務，也會阻塞。

所以，原則上：

• 避免大事務

• 在業務低峰去做表結構變化

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL大表添加一列的實現方法”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!