mysql添加字段或索引為什么會鎖表

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

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

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

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

mysql版本5.6之前的處理 :

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

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

mysql版本5.6之后的處理 :

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

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

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

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

總結(jié)：

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

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

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

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

最新評論