MySQL表的結構修改往往伴隨著表級鎖的問題。

• 特別是對于那些數據量較大的表，這會對業務系統的性能產生顯著影響。

通過優化表結構修改的操作，開發者可以避免或最小化鎖表時間，從而保證系統的正常運行。

表級鎖介紹

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

具體來說：

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

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

• 但當數據量巨大或業務高并發時，鎖表問題可能會導致嚴重的性能瓶頸，甚至引發服務崩潰。

鎖表的具體影響

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

• 查詢等待：

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

寫操作被阻止：

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

系統響應變慢：

• 當鎖表操作長時間未完成時，業務系統的整體性能將會顯著下降，甚至引發宕機。

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

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

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

MySQL5.6之前

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

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

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

所以：MySQL5.6版本之前，直接修改表結構的過程中會鎖表。

具體操作步驟如下：

首先創建新的臨時表，表結構通過命令ALTAR TABLE新定義的結構。

然后把原表中數據導入到臨時表。

刪除原表。

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

MySQL5.6和MySQL8.0版本中對鎖表問題做了優化！

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 嘗試在原地修改表結構。
• MySQL 將嘗試盡可能在不重新創建整個表的情況下應用修改。

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

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

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

不過也分為2種情況：

增加非空列：

會執行一個快速的元數據操作，不會鎖定整個表：

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

增加可為空列：

會執行一個快速的元數據操作，不會鎖定整個表：

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

注意：

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

• 由于內部的元數據操作、數據重組或日志寫入等引起的。

因此，在對大型表進行結構修改時，仍建議在低負載時執行，以最小化對應用程序的影響。

MySQL8.0

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

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

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

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

EXPLAIN ALTER TABLE your_table ADD COLUMN new_column INT;

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

下面是MySQL8.0的一些具體優化！

原子DDL：

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

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

立即更新元數據：

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

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

InnoDB引擎優化：

MySQL8.0 的 InnoDB 存儲引擎針對大數據表的結構修改進行了一些優化。

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

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

增量元數據更新：

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

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

Online DDL

在線DDL（Online DDL）是指在數據庫運行狀態下執行（DDL）操作。

• 例如創建、修改或刪除表結構、索引等操作，而不會造成數據庫的長時間鎖定或無法使用。

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

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

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

COPY

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

INPLACE

• MySQL5.6出現的。

INSTANT

• MySQL8.0.12出現的（騰訊DBA團隊貢獻）。

基本原理

在DDL操作，執行時，不管何種算法，都會經歷三個階段：

• 準備階段、執行階段DDL、提交階段。

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

具體實現細節可以見官方文檔:

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

總結

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

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

• 同時數據量過大的時候，會出現一些性能問題。

所以實際操作的過程中，要關注表的數據多小，最終的數據大小（要關注索引數據）。

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

• 需要特別注意在非高峰期執行 ALTER TABLE 操作，以盡量減少對業務的影響。