問題參考自： https://www.zhihu.com/question/440231149 ，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 大表添加一列的實現的文章就介紹到這了,更多相關MySQL 大表添加一列內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！