MySQL 大表添加一列的實現(xiàn)

問題參考自： https://www.zhihu.com/question/440231149 ，mysql中，一張表里有3億數(shù)據(jù)，未分表，要求是在這個大表里添加一列數(shù)據(jù)。數(shù)據(jù)庫不能停，并且還有增刪改操作。請問如何操作？答案為個人原創(chuàng)

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

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

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

• 新建一個和 Table1 完全同構(gòu)的 Table2
• 對表 Table1 加寫鎖
• 在表 Table2 上執(zhí)行 ALTER TABLE 你的表 ADD COLUMN 新列 char(128)
• 將 Table1 中的數(shù)據(jù)拷貝到 Table2
• 將 Table2 重命名為 Table1 并移除 Table1，釋放所有相關(guān)的鎖

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

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

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 }

其中的參數(shù)：

ALGORITHM：

• DEFAULT：默認方式，在 MySQL 8.0中，如果未顯示指定 ALGORITHM，那么會優(yōu)先選擇 INSTANT 算法，如果不行再使用 INPLACE 算法，如果不支持 INPLACE 算法則使用 COPY 的方式完成
• INSTANT：8.0 中新添加的算法，添加列是立即返回。但是不能是虛擬列。這個原理很簡單，對于新建一列，表所有原有數(shù)據(jù)并不是立刻發(fā)生變化，只是在表字典里面記錄下這個列和默認值，對于默認的 Dynamic 行格式（其實就是 Compressed 的變種），如果更新了這一列則原有數(shù)據(jù)標記為刪除在末尾追加更新后的記錄。這樣做就是沒有提前預留出列空間，之后更新可能經(jīng)常會發(fā)生行記錄空間變動。但是對于大多數(shù)業(yè)務，都是最近的時間的記錄才會修改，所以問題不大。
• INPLACE：在原表上直接進行修改，不會拷貝臨時表，可以逐條記錄修改，不會產(chǎn)生大量的 undolog 以及 redolog，不會占用很多 buffer。可以避免重建表帶來的IO和CPU消耗，保證期間依然良好的性能和并發(fā)。
• COPY：拷貝到臨時新表上進行修改。由于記錄拷貝，會產(chǎn)生大量的 undolog 以及 redolog，并占用很多 buffer，對業(yè)務性能有影響。

LOCK：

•  DEFAULT：和 ALGORITHM 的 DEFAULT 類似
• NONE：無鎖，允許并發(fā)讀取和更新表
• 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;

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

同時，執(zhí)行在線 DDL 的過程大概是：

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

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

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

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

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

所以，原則上：

• 避免大事務
• 在業(yè)務低峰去做表結(jié)構(gòu)變化

到此這篇關(guān)于MySQL 大表添加一列的實現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL 大表添加一列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！