對線面試官：MySQL 給數據表增加一列，一定會鎖表嗎？

作者：架構師秋天 2024-08-05 10:13:59

MySQL 8.0 在處理大數據表增加字段的情況下進行了一些優化，進一步優化了減少對表的鎖定時間和降低性能影響。在MySQL8.0中，還引入了Invisible Indexes、Instant DDL和In-Place Alter升級等新功能，可以進一步提高MySQL的性能和可維護性。

一、鎖的概念

鎖的分類可以從多個方面來劃分。

圖片

從對數據操作類型分

從對數據操作粒度分

表鎖也是是InnoDB存儲引擎中的一種鎖機制，用于控制對表的并發訪問。InnoDB表鎖包括共享鎖（Shared Lock）和排他鎖（Exclusive Lock），用于實現不同程度的并發讀寫操作。嚴格意義上來說，InnoDB存儲引擎沒有MyISAM存儲引擎那種直接的完整的表級鎖。

圖片

InnoDB表鎖是自動隱式鎖定的，不需要手動指定。
InnoDB存儲引擎默認采用行級鎖（Row-Level Locking）而不是表級鎖。這意味著在InnoDB中，鎖定的粒度更細，可以同時支持并發讀寫操作，減少了鎖沖突和阻塞的可能性。
InnoDB采用多版本并發控制（MVCC）機制，讀取操作不會阻塞寫入操作，寫入操作也不會阻塞讀取操作，提高了并發性能。
InnoDB表鎖在事務中使用，并且鎖的持有時間盡可能短，以減少阻塞和沖突的可能性。
InnoDB表鎖的具體行為受到事務的隔離級別和鎖定模式的影響。通過設置合適的隔離級別和鎖定模式，可以平衡并發性能和數據一致性的要求。

在 MySQL 中，使用 InnoDB 存儲引擎給數據表增加一列時，并不一定會鎖表。InnoDB 存儲引擎提供了一些機制來減少對表的鎖定，以提高并發性能。

從 MySQL 5.6 版本開始，InnoDB 引擎引入了在線數據定義語言（DDL）操作，其中包括對表結構的修改。這些在線 DDL 操作允許在不鎖定整個表的情況下進行結構更改。具體來說，以下是可能的情況：

對于增加非空列：在 InnoDB 中，增加非空列時，會執行一個快速的元數據操作，不會鎖定整個表。這意味著在修改期間，其他會話可以繼續讀取和寫入表數據。
對于增加可為空列：在 InnoDB 中，增加可為空列時，也會執行一個快速的元數據操作，不會鎖定整個表。其他會話可以繼續讀取和寫入表數據，但在修改期間，可能會有一些短暫的行鎖定。

?
注意：盡管 InnoDB 存儲引擎提供了較少的鎖定，但在執行 ALTER TABLE 語句時仍可能會有一些性能影響。這可能是由于內部的元數據操作、數據重組或日志寫入等引起的。
?

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

針對上面的問題,MySQL8.0做了更多的優化和升級

原子 DDL： MySQL 8.0 引入了原子 DDL（Atomic DDL）操作，這意味著 ALTER TABLE 語句的執行過程中將會有更少的阻塞。在增加字段的情況下，原子 DDL 機制可以減少對表的鎖定時間，并允許其他會話繼續讀取和寫入數據。
立即更新元數據： MySQL 8.0 在增加字段時立即更新表的元數據，而不需要等待整個操作完成。這樣可以更快地完成 ALTER TABLE 操作，并減少對表的鎖定時間。
InnoDB 引擎優化： MySQL 8.0 的 InnoDB 存儲引擎針對大數據表的結構修改進行了一些優化。例如，對于增加非空字段，InnoDB 不再需要復制整個表的數據。相反，它會使用一種更輕量級的操作來添加新字段，從而減少鎖定時間和資源消耗。
增量元數據更新： MySQL 8.0 引入了增量元數據更新，這意味著在 ALTER TABLE 操作期間只需更新受影響的元數據信息，而不是整個表。這樣可以減少鎖定時間和操作的開銷。

總結上面的所有內容，實際就是單純的增加一個字段，表結構修改和索引添加通常不會鎖定整個表，在某些情況下，MySQL可能需要鎖定整個表。同時數據量過大的時候，會出現一些性能問題，所以我們實際操作的過程中，一定要關注表的數據多小，最終的數據大小（這里要關注索引數據）。

責任編輯：武曉燕來源：架構師秋天