mysql添加字段或索引為什么會(huì)鎖表

前幾天，在生產(chǎn)環(huán)境對(duì)數(shù)據(jù)庫(kù)的一張大表進(jìn)行新增字段的操作，結(jié)果直接導(dǎo)致該表被鎖，所有該表的增刪改查操作全部阻塞，服務(wù)器負(fù)載飆高，進(jìn)而影響到了線上業(yè)務(wù)。

鎖表時(shí)查看服務(wù)器日志，發(fā)現(xiàn)有大量如下圖所示的報(bào)錯(cuò)信息

從上圖分析可得數(shù)據(jù)庫(kù)連接池已經(jīng)沒(méi)有空閑的連接可用了，為了快速能夠解決這個(gè)問(wèn)題，使用show processlist顯示 數(shù)據(jù)庫(kù)所有正在運(yùn)行的線程相關(guān)信息，根據(jù)執(zhí)行時(shí)間可以準(zhǔn)確判斷出新增字段對(duì)應(yīng)的線程(如下圖所示)，由于新增字段鎖表導(dǎo)致其他增刪改該表語(yǔ)句處于等待狀態(tài)，通過(guò) kill processId 殺掉修改表結(jié)構(gòu)的語(yǔ)句，馬上恢復(fù)了正常。

事后，通過(guò)網(wǎng)上查閱資料，對(duì)于造成數(shù)據(jù)庫(kù)鎖表原因分析和復(fù)盤，不同的mysql版本對(duì)于添加字段導(dǎo)致鎖表有著不同的處理方式。

mysql版本5.6之前的處理 :

• 首先創(chuàng)建新的臨時(shí)表，復(fù)制原表結(jié)構(gòu)，并新增新字段
• 然后把原表中數(shù)據(jù)導(dǎo)入到臨時(shí)表
• 刪除原表
• 最后重命名臨時(shí)表為原表名

通過(guò)mysql官網(wǎng)得知，MySQL 5.6后支持Online DDL(在線DDL)，Online DDL就是允許在表上執(zhí)行DDL的操作（例如創(chuàng)建字段）的同時(shí)不阻塞并發(fā)的DML(SELECT、UPDATE、INSERT、DELETE)操作，也就是說(shuō)在執(zhí)行DDL語(yǔ)句時(shí)，提供無(wú)鎖加字段或索引的機(jī)制，即在DDL語(yǔ)句后加 ALGORITHM=INPLACE,LOCK=NONE。

mysql版本5.6之后的處理 :

• 例子：ALTER TABLE 表名ADD COLUMN …(此處省略),ALGORITHM = INPLACE, LOCK = NONE;

ALGORITHM 描述的是DDL的實(shí)現(xiàn)方式，LOCK 描述的是執(zhí)行DDL是否加鎖

ALGORITHM 有三個(gè)參數(shù)：
Default (默認(rèn)，由mysql自行決定) ,
INPLACE (對(duì)當(dāng)前表在引擎層對(duì)數(shù)據(jù)改動(dòng))
COPY (采用拷表方式進(jìn)行表變更，缺點(diǎn)：需要多一倍存儲(chǔ)，表很大的時(shí)候，可能因?yàn)榇疟P的容量不足而操作失敗)

LOCK 有四個(gè)參數(shù)
NONE (無(wú)鎖)
SHARED (共享鎖)
DEFAULT (默認(rèn)，由mysql自行決定)
EXCLUSIVE (互斥鎖)

總結(jié)：

mysql 5.6后為了避免mysql添加字段或索引鎖表的問(wèn)題只需要在添加字段或索引的執(zhí)行語(yǔ)句后加 ALGORITHM = INPLACE, LOCK = NONE;

對(duì)于大表的修改最好滿足以下條件：

盡量選擇流量小的時(shí)候才執(zhí)行sql執(zhí)行前需要查看是否有未提交的事務(wù)預(yù)先在預(yù)發(fā)布環(huán)境先行模擬對(duì)應(yīng)的場(chǎng)景，評(píng)估風(fēng)險(xiǎn)

到此這篇關(guān)于Mysql添加字段或索引導(dǎo)致鎖表的處理方式的文章就介紹到這了,更多相關(guān)Mysql添加字段導(dǎo)致鎖表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論