MySQL 大表添加一列的實現

問題參考自: 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!