InnoDB在线DDL操作:MySQL不锁表修改表结构指南
MySQL 8.0+ 的 ALTER TABLE 默认就是 ALGORITHM=INPLACE 吗? 先说一个核心判断:不是。虽然 MySQL 8.0+ 对多数 DDL 操作增强了在线能力,但 ALTER TABLE 是否真正不锁表,这事儿可不能想当然。它完全取决于具体操作类型、字段位置、索引变更方
MySQL 8.0+ 的 ALTER TABLE 默认就是 ALGORITHM=INPLACE 吗?

先说一个核心判断:不是。虽然 MySQL 8.0+ 对多数 DDL 操作增强了在线能力,但 ALTER TABLE 是否真正不锁表,这事儿可不能想当然。它完全取决于具体操作类型、字段位置、索引变更方式,以及你是否显式指定了 ALGORITHM 和 LOCK 参数。默认行为下,很多操作依然会触发 COPY 算法,导致全表拷贝和锁表,尤其是在添加非空列、修改列类型或者重排列顺序的时候。
那么,实操中该怎么避坑呢?
- 动手前,先用
SHOW CREATE TABLE t1确认存储引擎是InnoDB(MyISAM 压根不支持在线 DDL,第一步就错了)。 - 强烈建议养成习惯:执行时显式声明
ALTER TABLE t1 ... ALGORITHM=INPLACE, LOCK=NONE。这相当于让 MySQL 提前做一次“可行性校验”,如果条件不满足,它会直接报错,而不是悄无声息地退化成耗时的COPY操作。 - 需要警惕的是,即使你声明了
LOCK=NONE,某些操作(比如添加二级索引)也可能强制要求LOCK=SHARED。不过别慌,这种共享锁通常只阻塞写入,不会影响读取。
哪些 ALTER 操作能真正 LOCK=NONE?
真正能做到全程不锁写(允许并发 INSERT/UPDATE/DELETE)的操作,其实相当有限。典型的“安全区”包括:
- 添加或删除二级索引:
ADD INDEX或DROP INDEX(注意,这里不包括主键索引或全文索引)。 - 重命名索引:
RENAME INDEX idx_old TO idx_new。 - 修改列默认值:
ALTER COLUMN c1 SET DEFAULT 100(前提是只改DEFAULT,不涉及修改列类型或添加NOT NULL约束)。 - 添加虚拟列并建索引:即添加 Generated Column 并为其创建索引,但需要确保表达式是确定性的。
当然,有安全区就有雷区,下面这几个坑尤其容易踩:
MODIFY COLUMN或CHANGE COLUMN几乎总是触发COPY,哪怕你只是改个COMMENT注释——因为它涉及底层行格式的重写。- 给已有数据的表添加
NOT NULL列,即使你指定了默认值,也会锁表。稳妥的做法是分三步走:先ADD COLUMN,再ALTER COLUMN ... SET DEFAULT,最后ALTER COLUMN ... SET NOT NULL。 - 像
ENGINE=InnoDB这种看似“无变更”的操作,MySQL 也可能默认走COPY。所以,务必加上ALGORITHM=INPLACE来强制校验。
如何验证某条 ALTER 是否真的在线执行?
命令执行成功,不代表它就真的“在线”完成了。关键在于,执行期间你的业务写入有没有被阻塞。最可靠的验证方式,是结合日志与实时状态监控:
- 开启动态参数
innodb_print_ddl_logs=ON,执行 DDL 后去查看错误日志。搜索DDL log关键字,如果看到add index这类记录,并且没有出现copy table,那大概率就是 inplace 操作。 - 在 DDL 执行过程中,查询
SELECT * FROM information_schema.INNODB_TRX WHERE TRX_QUERY LIKE 'alter%'。观察TRX_STATE是否长时间处于RUNNING(说明没卡住),同时检查对应的TRX_MYSQL_THREAD_ID连接是否还在正常处理业务请求。 - 使用
SHOW PROCESSLIST观察,是否有大量会话出现Waiting for table metadata lock状态。如果有,就说明其他会话已经被这个 DDL 操作阻塞了。
话说回来,从 MySQL 8.0.12 开始,新增了 performance_schema.table_lock_waits_summary_by_table 表,可以更精准地统计表级锁等待情况,这比手工排查要方便和准确得多。
为什么加索引有时快有时慢,还偶尔锁表?
同一句 CREATE INDEX,在不同场景下表现天差地别,这背后的核心原因是什么?关键在于是否启用了 ALGORITHM=INPLACE,以及索引字段本身的选择性。
- 给大表创建唯一索引时,MySQL 必须校验数据的唯一性。这个校验阶段会持有
SHARED锁(阻塞写入但不阻塞读取),而且无法并行扫描——数据量越大,校验耗时就越长。 - 如果目标字段存在大量
NULL值,或者基数很低(比如一个状态字段只有0和1两种值),优化器后续可能根本不会使用这个索引。这会导致你加了索引却看不到效果,误以为“加了没用”。 - 版本差异也很重要:在 5.7 之前的版本,
CREATE INDEX默认走的是COPY算法;8.0+ 版本默认会尝试INPLACE,但如果表上有外键、全文索引,或者使用了压缩页,操作仍有可能退化。
其实,真正影响线上稳定性的,往往不是“能不能在线”这个二元问题,而是“在线期间的资源消耗是否可控”。例如,在线创建索引会显著拉升 I/O 和 CPU 使用率,可能间接拖慢其他查询的响应速度——这种间接影响,比直接的锁表更难监控,也更容易被忽略。这才是需要持续关注的关键所在。
