孤独求学人

MySQL 给数据表增加一列，一定会锁表吗？

2024-11-21 杜世伟 Mysql

在 MySQL 中，给数据表增加一列（ALTER TABLE 操作）是否会锁表，取决于以下几个因素：

1. 存储引擎
InnoDB：这是 MySQL 默认的事务性存储引擎，从 MySQL 5.6 开始，InnoDB 支持在线 DDL（Online DDL），在某些情况下可以避免完全锁表。

MyISAM：这种存储引擎不支持在线 DDL，因此会对表进行完全锁定。

2. ALTER TABLE 的具体操作
不同的操作对锁表的影响不同：
新增列：简单的增加一个不带默认值或允许 NULL 的列，InnoDB 通常支持在线 DDL，可以避免锁表。
新增列并带有默认值：如果需要对现有数据进行填充（例如新增列设置了非空约束且有默认值），可能会触发对表中每一行的更新，此时通常会锁表。

新增列并影响索引：如果新增的列涉及索引或主键，也可能导致表锁。

3. MySQL 版本
MySQL 5.6 及以上：
支持大部分在线 DDL 操作。
常见的 ADD COLUMN 操作可以避免长时间的表锁，但仍会有短暂的元数据锁。
MySQL 8.0：
对 DDL 操作优化更多，支持几乎无锁的 ADD COLUMN。

新增列时不会重写整个表（除非涉及复杂操作）。

4. 表的数据量

如果表的数据量很大，可能需要更多的时间来完成变更，某些情况下即便支持在线 DDL，仍然会导致较长时间的元数据锁。

5. 是否使用 ALGORITHM 指定 DDL 模式
MySQL 提供了三种 DDL 算法，可以通过 ALGORITHM 明确指定：

INPLACE：尽可能避免锁表（但可能会有短暂的 MDL 锁）。
COPY：会锁表并创建表的一个副本。
INSTANT：从 MySQL 8.0.12 开始支持，新增列时仅修改元数据，完全避免表锁（但对新增列的限制较多）。
示例操作对锁表的影响

-- 新增允许 NULL 的列，可能使用 INPLACE 模式，避免锁表
ALTER TABLE my_table ADD COLUMN new_column INT;

-- 新增非空列并带默认值，可能导致表重写（可能锁表）
ALTER TABLE my_table ADD COLUMN new_column INT NOT NULL DEFAULT 0;

-- 强制指定算法为 INPLACE
ALTER TABLE my_table ADD COLUMN new_column INT NOT NULL DEFAULT 0 ALGORITHM=INPLACE;

-- 强制指定算法为 INSTANT（仅 MySQL 8.0.12 及以上支持）
ALTER TABLE my_table ADD COLUMN new_column INT ALGORITHM=INSTANT;

结论
不会总是锁表，特别是在使用 InnoDB 并且操作是支持在线 DDL 的。
如果担心锁表，可以：
尽量使用 MySQL 8.0 及以上版本。
确认表的存储引擎为 InnoDB。
使用 ALGORITHM=INPLACE 或 ALGORITHM=INSTANT 明确指定无锁模式。
对于 MySQL 5.6 以下版本或 MyISAM 表，新增列通常会锁表。

标签: mysql InnoDB lock ddl