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

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

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

會(huì)造成鎖表，簡(jiǎn)易過(guò)程如下：

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

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

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

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

LOCK：

•  DEFAULT：和 ALGORITHM 的 DEFAULT 類(lèi)似
• NONE：無(wú)鎖，允許并發(fā)讀取和更新表
• SHARED：共享鎖，允許讀取不允許更新
• EXCLUSIVE：不允許讀取和更新

各個(gè)版本支持的在線 DDL 修改使用的算法的對(duì)比：

參考文檔：

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

可以通過(guò)：

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

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

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

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

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

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

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

這樣類(lèi)似的會(huì)執(zhí)行較長(zhǎng)時(shí)間的事務(wù)，也會(huì)阻塞。

所以，原則上：

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

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

最新評(píng)論