Mysql大表添加字段失敗問題及解決

更新時(shí)間：2024年04月11日 08:41:17 作者：RobinCode

這篇文章主要介紹了Mysql大表添加字段失敗問題及解決,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

背景

最近遇到一個(gè)問題，需要在user用戶表千萬級(jí)別數(shù)據(jù)中添加兩個(gè)字段，發(fā)現(xiàn)老是加不上去，一直卡死。

表數(shù)據(jù)量不僅大，而且是一個(gè)熱點(diǎn)表，訪問頻率特別高，而且該表的訪問是在一個(gè)大事務(wù)中。加字段的時(shí)候一直在等待獲取MDL寫鎖。

這個(gè)等待也影響了后續(xù)表訪問對(duì)MDL讀鎖的獲取，導(dǎo)致后面的查詢也都被堵塞了。

更慘的是，客戶端有重試機(jī)制，查詢堵塞超過超時(shí)時(shí)間會(huì)再起一個(gè)session進(jìn)行請(qǐng)求，導(dǎo)致數(shù)據(jù)庫的線程池很快就爆滿了，直接掛掉。

MDL鎖

MDL鎖介紹

MDL鎖屬于表級(jí)別的元數(shù)據(jù)鎖。表級(jí)別鎖分為數(shù)據(jù)鎖和元數(shù)據(jù)鎖，通常我們說的加鎖一般指的是加的數(shù)據(jù)鎖。跟數(shù)據(jù)鎖一樣，元數(shù)據(jù)鎖也分讀鎖和寫鎖。

MDL不需要顯示使用，在進(jìn)行表操作時(shí)會(huì)自動(dòng)加上。當(dāng)對(duì)表數(shù)據(jù)進(jìn)行增刪改查（ insert、delete、select、update等）時(shí)，會(huì)自動(dòng)加上MDL讀鎖；當(dāng)要對(duì)表進(jìn)行加減字段的結(jié)構(gòu)修改時(shí)，會(huì)自動(dòng)加上MDL寫鎖。

讀鎖不互斥：意味著可以多個(gè)線程同時(shí)對(duì)一張表進(jìn)行增刪改查（CRUD）的操作。
寫鎖獨(dú)占：進(jìn)行結(jié)構(gòu)修改前，要先等待其他所有的MDL鎖釋放了才能獲取到MDL寫鎖。獲取到寫鎖后，在寫鎖釋放前，其他線程無法獲取到MDL讀鎖和寫鎖。也就是說，修改一個(gè)表的結(jié)構(gòu)過程中，會(huì)阻塞其他線程對(duì)表的操作

MDL鎖是MySQL自動(dòng)隱式加鎖，無需我們手動(dòng)操作。在我們執(zhí)行DML語句的時(shí)候，MySQL自動(dòng)添加MDL讀鎖。在我們執(zhí)行DDL語句的時(shí)候，MySQL自動(dòng)添加MDL寫鎖。讀鎖與讀鎖之間不互斥，讀鎖與寫鎖、寫鎖與寫鎖之間互斥。注意：MDL鎖是表鎖，會(huì)對(duì)整張表加鎖

DML（Data Manipulation Language）數(shù)據(jù)操縱語言：適用范圍：對(duì)表數(shù)據(jù)進(jìn)行操作，比如 insert、delete、select、update等。
DDL（Data Definition Language）數(shù)據(jù)定義語言：適用范圍：對(duì)表結(jié)構(gòu)進(jìn)行操作，比如create、drop、alter、rename、truncate等。

MDL鎖的必要性

MDL鎖 的存在，其實(shí)是為了保證數(shù)據(jù)的一致性。

想象一下，假如沒有 MDL鎖 ，一個(gè)查詢?cè)诒闅v表數(shù)據(jù)的過程中，另外一個(gè)線程執(zhí)行了ALTER TABLE t DELETE COLUMN 'col_1'把col_1這一列刪掉了，那查詢結(jié)果就亂了，結(jié)果中是否應(yīng)該有這一列數(shù)據(jù)？

所以為了保證并發(fā)操作下數(shù)據(jù)的一致性。

如果一個(gè)事務(wù)正在執(zhí)行中，另一個(gè)在這時(shí)修改了表結(jié)構(gòu)，不但可能導(dǎo)致當(dāng)前事務(wù)出現(xiàn)不可重復(fù)讀的問題，還有可能連事務(wù)都無法提交

事故復(fù)現(xiàn)

介紹完MDL鎖，我們?cè)賮韽?fù)現(xiàn)下事故。我們通過下面的操作序列來模擬線上情況。

Session 1	Session 2	Session 3
begin;select * from user limit 10;
	alter table user add ‘age’ int not null default ‘0’ comment ‘年齡’;(阻塞)
		select * from user limit 10;

時(shí)刻1，事務(wù)1對(duì)表user進(jìn)行查詢，注意此時(shí)事務(wù)1并未提交，所以獲取的MDL讀鎖也不會(huì)釋放。

時(shí)刻2另外一個(gè)線程想要添加字段, 由于 事務(wù)1正持著MDL讀鎖，所以事務(wù)2會(huì)陷入阻塞，等待事務(wù)1釋放讀鎖后獲取MDL寫鎖。

申請(qǐng) MDL 鎖的操作會(huì)形成一個(gè)隊(duì)列，隊(duì)列中寫鎖獲取優(yōu)先級(jí)高于讀鎖。

所以事務(wù)2不僅阻塞了加字段的操作，也會(huì)阻塞后續(xù)對(duì)該表的所有操作。比如后面的事務(wù)3查詢由于獲取不到MDL讀鎖都被阻塞了。

這時(shí)，如果客戶端有重試機(jī)制，查詢超時(shí)后會(huì)重新進(jìn)行請(qǐng)求，容易把數(shù)據(jù)庫的連接池給擠爆了。

Mysql服務(wù)宕機(jī)的原因

為什么會(huì)出現(xiàn)這種情況呢？

原因是在執(zhí)行查詢語句的時(shí)候，MySQL自動(dòng)加了MDL鎖（metadata lock，即元數(shù)據(jù)鎖）。

不行的話，我們可以再執(zhí)行一下show processlist命令，查看有哪些正在執(zhí)行的進(jìn)程：

可以清楚的看到Session2和Session3的語句正在等待MDL鎖，Waiting for table metadata lock。

解決方案

方案一

了解了原因，事情就比較好處理了，數(shù)據(jù)庫奔潰原因是由于加字段等待時(shí)間太長導(dǎo)致影響后續(xù)請(qǐng)求，但mysql又無法在 alter table 語句里面設(shè)定等待時(shí)間。

所以當(dāng)時(shí)做法是繼續(xù)嘗試加字段語句，語句卡住30秒就手動(dòng)cancel掉。避免對(duì)后續(xù)請(qǐng)求的影響。重試了幾次發(fā)現(xiàn)一直沒能加上。最后是通過查看接口調(diào)用監(jiān)控，在請(qǐng)求頻率較低的時(shí)間點(diǎn)給加上了。

方案二

從MySQL5.6版本開始增加了Online DDL，作用就是在執(zhí)行DDL的時(shí)候，允許并發(fā)執(zhí)行DML。簡單翻譯就是修改表結(jié)構(gòu)的時(shí)候，也能同時(shí)支持并發(fā)執(zhí)行增刪查改操作。

從MySQL8.0版本開始又優(yōu)化了Online DDL，支持快速添加列，可以實(shí)現(xiàn)給大表秒級(jí)加字段。具體用法就是在DDL語句后面增加兩個(gè)參數(shù)ALGORITHM和LOCK。

比如下面這樣：

ALTER TABLE user ADD age int NOT NULL DEFAULT '0' COMMENT '年齡', ALGORITHM=Inplace, LOCK=NONE;

ALGORITHM可以指定使用哪種算法執(zhí)行DDL，可選項(xiàng)有：

Copy：拷貝方式，MySQL5.6 之前 DDL 的執(zhí)行方式，過程就是先創(chuàng)建新表，修改新表結(jié)構(gòu)，把舊表數(shù)據(jù)復(fù)制到新表，刪除舊表，重命名新表。執(zhí)行過程非常耗時(shí)，產(chǎn)生大量的磁盤IO和占用CPU，還有使Buffer poll失效，而且需要鎖住舊表，性能較差，現(xiàn)在基本很少使用。
Inplace：原地修改，MySQL5.6開始引入的，優(yōu)點(diǎn)是不會(huì)在Server層發(fā)生表數(shù)據(jù)拷貝，過程中允許并發(fā)執(zhí)行DML操作。過程就是先添加MDL寫鎖，執(zhí)行初始化操作，然后降級(jí)為MDL讀鎖，執(zhí)行DDL操作（比較耗時(shí)，允許并發(fā)執(zhí)行DML操作），升級(jí)為MDL寫鎖，完成DDL操作。
Instant：快速修改，MySQL8.0開始引入的，可以實(shí)現(xiàn)快速給大表添加字段。

性能依次是，Instant > Inplace > Copy。

LOCK可以指定執(zhí)行過程中，是否加鎖，可選項(xiàng)有：

NONE不加鎖，允許DML操作。
SHARED加讀鎖，允許讀操作，禁止DML操作。
DEFAULT默認(rèn)鎖模式，在滿足DDL操作前提下，默認(rèn)鎖模式會(huì)允許盡可能多的讀操作和DML操作。
EXCLUSIVE加寫鎖，禁止讀操作和DML操作。

其他

這里我們重點(diǎn)關(guān)注INNODB_TRX, INNODB_LOCKS, 以及INNODB_LOCK_WAITS三張表，表如其名，這三張表記錄了正在運(yùn)行的事務(wù)（包括事務(wù)占用or釋放鎖的信息）狀態(tài)信息

select * FROM information_schema.INNODB_TRX;
select * FROM information_schema.INNODB_LOCKS;
select * FROM information_schema.INNODB_LOCK_WAITS;

desc information_schema.INNODB_TRX;
desc information_schema.INNODB_LOCKS;
desc information_schema.INNODB_LOCK_WAITS

show engine innodb status;
show processlist;