MySQL表的結(jié)構(gòu)修改往往伴隨著表級鎖的問題。

• 特別是對于那些數(shù)據(jù)量較大的表，這會對業(yè)務(wù)系統(tǒng)的性能產(chǎn)生顯著影響。

通過優(yōu)化表結(jié)構(gòu)修改的操作，開發(fā)者可以避免或最小化鎖表時間，從而保證系統(tǒng)的正常運行。

表級鎖介紹

表級鎖指在執(zhí)行某些操作時，為了保證數(shù)據(jù)的一致性，對整個表加鎖。

具體來說：

• 當對表執(zhí)行ALTER TABLE操作時，MySQL默認會對表進行加鎖，阻止其他事務(wù)對該表的讀寫操作，直到ALTER TABLE操作完成。

這種鎖表行為對于小表或非高并發(fā)場景下影響不大。

• 但當數(shù)據(jù)量巨大或業(yè)務(wù)高并發(fā)時，鎖表問題可能會導致嚴重的性能瓶頸，甚至引發(fā)服務(wù)崩潰。

鎖表的具體影響

當MySQL執(zhí)行加字段操作時，鎖表會導致以下問題：

• 查詢等待：

所有查詢該表的SQL語句將被阻塞，直到表鎖釋放。

寫操作被阻止：

• 所有寫入該表的操作（如INSERT、UPDATE、DELETE）將處于等待狀態(tài)，直到鎖釋放。

系統(tǒng)響應變慢：

• 當鎖表操作長時間未完成時，業(yè)務(wù)系統(tǒng)的整體性能將會顯著下降，甚至引發(fā)宕機。

不過，在新版的MySQL 中，使用 InnoDB 存儲引擎給數(shù)據(jù)表增加一列時，并不一定會鎖表。

InnoDB 存儲引擎提供了一些機制來減少對表的鎖定，以提高并發(fā)性能。

在 MySQL 中，給數(shù)據(jù)表增加一列，是否會鎖表取決于使用的存儲引擎以及 MySQL 的版本。

MySQL5.6之前

之前版本的 MySQL 中，如果使用 ALTER TABLE 命令來增加一列，對于使用 InnoDB 存儲引擎的表，默認情況下會鎖表。

• 這意味著在操作執(zhí)行期間，表將被鎖定，其他讀取和寫入操作將被阻止，直到操作完成。

這種全表鎖定行為會導致在大型表上執(zhí)行 ALTER TABLE 操作時，產(chǎn)生長時間的鎖等待和應用的停頓。

所以：MySQL5.6版本之前，直接修改表結(jié)構(gòu)的過程中會鎖表。

具體操作步驟如下：

首先創(chuàng)建新的臨時表，表結(jié)構(gòu)通過命令ALTAR TABLE新定義的結(jié)構(gòu)。

然后把原表中數(shù)據(jù)導入到臨時表。

刪除原表。

最后把臨時表重命名為原來的表名。

MySQL5.6和MySQL8.0版本中對鎖表問題做了優(yōu)化！

MySQL5.6

從 MySQL5.6 開始，InnoDB 引入了在線 DDL (Online DDL)操作，允許一些表修改操作在不鎖定表的情況下進行。

增加一列是一個在線操作，可以使用 ALGORITHM=INPLACE 來避免全表鎖定。

ALTER TABLE your_table ADD COLUMN new_column INT ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE 表明使用就地算法來進行修改，這是在線DDL操作的一部分。

• 明確指示 MySQL 嘗試在原地修改表結(jié)構(gòu)。
• MySQL 將嘗試盡可能在不重新創(chuàng)建整個表的情況下應用修改。

LOCK=NONE 表示盡量不鎖表，最大程度減少對并發(fā)查詢的影響。

• 允許其他會話對表進行讀寫操作。
• 但可能會導致數(shù)據(jù)不一致的情況。

這樣執(zhí)行效率會高很多。而且不會鎖表。

不過也分為2種情況：

增加非空列：

會執(zhí)行一個快速的元數(shù)據(jù)操作，不會鎖定整個表：

• 在修改期間，其他會話可以繼續(xù)讀取和寫入表數(shù)據(jù)。

增加可為空列：

會執(zhí)行一個快速的元數(shù)據(jù)操作，不會鎖定整個表：

• 其他會話可以繼續(xù)讀取和寫入表數(shù)據(jù)，但在修改期間，可能會有一些短暫的行鎖定。

注意：

盡管 InnoDB 存儲引擎提供了較少的鎖定，但在執(zhí)行 ALTER TABLE 語句時仍可能會有一些性能影響。

• 由于內(nèi)部的元數(shù)據(jù)操作、數(shù)據(jù)重組或日志寫入等引起的。

因此，在對大型表進行結(jié)構(gòu)修改時，仍建議在低負載時執(zhí)行，以最小化對應用程序的影響。

MySQL8.0

MySQL8.0 引入了一些新的特性，使得大多數(shù)的 ALTER TABLE 操作可以在不鎖定表的情況下完成。

• 簡單說：提高了在線 DDL 操作的能力。

在 MySQL8.0 中默認情況下，簡單的 ALTER TABLE 操作（如增加一列）通常不會鎖定表。

要確認某個特定的 ALTER TABLE 操作是否會鎖表，可以在操作執(zhí)行前使用 EXPLAIN 語句：

EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;

該命令將顯示操作的執(zhí)行計劃信息，包括是否會鎖定表。

下面是MySQL8.0的一些具體優(yōu)化！

原子DDL：

MySQL8.0 引入了原子 DDL（Atomic DDL）操作，這意味著 ALTER TABLE 語句的執(zhí)行過程中將會有更少的阻塞。

• 在增加字段的情況下，原子 DDL 機制可以減少對表的鎖定時間，并允許其他會話繼續(xù)讀取和寫入數(shù)據(jù)。

立即更新元數(shù)據(jù)：

MySQL8.0 在增加字段時立即更新表的元數(shù)據(jù)，而不需要等待整個操作完成。

• 這樣可以更快地完成 ALTER TABLE 操作，并減少對表的鎖定時間。

InnoDB引擎優(yōu)化：

MySQL8.0 的 InnoDB 存儲引擎針對大數(shù)據(jù)表的結(jié)構(gòu)修改進行了一些優(yōu)化。

• 例如，對于增加非空字段，InnoDB 不再需要復制整個表的數(shù)據(jù)。

相反，它會使用一種更輕量級的操作來添加新字段，從而減少鎖定時間和資源消耗。

增量元數(shù)據(jù)更新：

MySQL8.0 引入了增量元數(shù)據(jù)更新，這意味著在 ALTER TABLE 操作期間只需更新受影響的元數(shù)據(jù)信息，而不是整個表。

• 這樣可以減少鎖定時間和操作的開銷。

Online DDL

在線DDL（Online DDL）是指在數(shù)據(jù)庫運行狀態(tài)下執(zhí)行（DDL）操作。

• 例如創(chuàng)建、修改或刪除表結(jié)構(gòu)、索引等操作，而不會造成數(shù)據(jù)庫的長時間鎖定或無法使用。

傳統(tǒng)的DDL操作通常需要對受影響的表進行排他鎖定。

• 這可能導致其他會話無法對該表進行讀寫操作，從而影響了數(shù)據(jù)庫的正常使用。

目前支持的主流算法有三種：

COPY

• MySQL5.6之前非Online，都是執(zhí)行這種算法。

INPLACE

• MySQL5.6出現(xiàn)的。

INSTANT

• MySQL8.0.12出現(xiàn)的（騰訊DBA團隊貢獻）。

基本原理

在DDL操作，執(zhí)行時，不管何種算法，都會經(jīng)歷三個階段：

• 準備階段、執(zhí)行階段DDL、提交階段。

不同之處是，在三個階段中分別做了不同的優(yōu)化處理。

具體實現(xiàn)細節(jié)可以見官方文檔:

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

總結(jié)

MySQL5.6之后，實際單純的增加一個字段，表結(jié)構(gòu)修改和索引添加通常不會鎖定整個表。

在某些情況下，MySQL可能需要鎖定整個表。

• 同時數(shù)據(jù)量過大的時候，會出現(xiàn)一些性能問題。

所以實際操作的過程中，要關(guān)注表的數(shù)據(jù)多小，最終的數(shù)據(jù)大小（要關(guān)注索引數(shù)據(jù)）。

同時如果你的 MySQL 版本較舊或出于某些特殊原因不支持在線 DDL 操作。

• 需要特別注意在非高峰期執(zhí)行 ALTER TABLE 操作，以盡量減少對業(yè)務(wù)的影響。