MySQL 大表添加一列的實現

Posted on by

問題參考自： 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 修改使用的算法的對比：

image

參考文檔：

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 的過程大概是：

image

可以看出，在開始階段需要 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 大表添加一列內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：