MySQL中DDL操作引起表锁如何规避_使用ALGORITHM=INPLACE策略

MySQL DDL卡住表主因是默认COPY算法锁表，虽5.6+支持ALGORITHM=INPLACE，但字段类型变更、加唯一索引等会降级；需显式指定ALGORITHM=INPLACE, LOCK=NONE（仅部分操作支持），并检查引擎、长事务及磁盘空间。

DDL操作卡住整个表，是因为默认用了COPY算法

很多DBA都遇到过这样的场景：一个看似简单的ALTER TABLE语句执行起来却异常缓慢，甚至把整个表的读写都卡住了。这背后的“元凶”，往往是MySQL默认采用的COPY算法。

好消息是，从MySQL 5.6版本开始，引入了ALGORITHM=INPLACE选项，旨在实现“原地”变更，减少锁表影响。但这里有个常见的误区：并非所有DDL操作都会自动启用它。比如，执行ALTER TABLE t ADD COLUMN c INT添加普通列，在多数情况下确实能原地完成。然而，一旦操作涉及字段类型变更（例如将VARCHAR(255)改为VARCHAR(500)）、添加索引（尤其是唯一索引），或者修改主键，MySQL就可能悄无声息地回退到COPY模式。一旦降级，就会触发全表数据复制，并锁定整个表，阻塞所有并发读写请求。

如何判断DDL是否正在使用COPY算法呢？关键线索在执行SHOW PROCESSLIST时，如果看到状态显示为copy to tmp table，或者在慢查询日志里发现了alter table ... copy的踪迹，那就基本可以确诊了。

• 指定了也不一定行：使用ALGORITHM=INPLACE更像是一个“申请”，MySQL会自行校验该操作是否支持原地执行。如果不支持，它会直接报错ALGORITHM=INPLACE is not supported for this operation，而不会静默降级，这反而是一种安全机制。
• 锁的级别是关键：必须搭配LOCK=NONE才能真正意义上避免锁表（当然，仅限支持该组合的场景）。否则，即使算法是INPLACE，MySQL也可能施加SHARED（共享锁，允许读阻塞写）或EXCLUSIVE（排他锁）级别的锁。
• “原地”不等于“零影响”：即使成功使用INPLACE算法，DDL操作仍然需要获取元数据锁（MDL）。如果此时恰好有一个长事务正在查询这张表，ALTER语句就会被堵在等待MDL的阶段，从外部看就是“卡住不动”了。所以，有时候问题不在数据拷贝，而在等待。

哪些DDL操作真正支持 ALGORITHM=INPLACE + LOCK=NONE

那么，究竟哪些操作可以放心地使用无锁变更呢？这里需要明确一点：并非所有“看起来轻量”的操作都支持LOCK=NONE。以目前主流的InnoDB引擎为例，以下操作在MySQL 8.0中已被确认可以同时指定ALGORITHM=INPLACE, LOCK=NONE：

• 添加普通二级索引：例如 ALTER TABLE t ADD INDEX idx_name (col)
• 删除索引：例如 ALTER TABLE t DROP INDEX idx_name
• 添加虚拟列并为其建索引：即GENERATED ALWAYS AS表达式列
• 扩大VARCHAR长度：注意有前提条件，比如使用utf8mb4字符集且未超过行大小的限制
• 修改列的默认值：仅限不改动列数据类型、不改变NULL属性，单纯修改DEFAULT值

另一方面，以下操作则明确不支持LOCK=NONE，即便强行指定也会收到错误提示：在非末尾位置ADD COLUMN、使用MODIFY COLUMN变更数据类型、CHANGE COLUMN、DROP COLUMN，以及任何涉及主键的变更。面对这些操作，通常只能退而求其次，接受LOCK=SHARED（允许读但阻塞写），或者重新评估变更的必要性与时机。

执行前必须检查的三件事

语法写对了，操作也在支持列表里，是不是就能高枕无忧了？远非如此。生产环境中的许多故障，恰恰卡在了一些前置的检查环节上。执行任何线上DDL前，务必完成以下三项检查：

• 确认表引擎：首先确保当前表使用的是InnoDB引擎（通过SHOW CREATE TABLE t查看ENGINE=InnoDB）。因为像MyISAM这类引擎，完全不支持INPLACE算法。
• 排查活跃长事务：运行查询SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(NOW() - trx_started) > 60，检查是否有运行时间超过60秒的事务正在访问目标表。如果有，你的ALTER语句将不得不等待它结束，从而表现为“卡住”。
• 验证磁盘空间：即使是INPLACE操作，例如添加索引，也可能需要在临时目录进行排序，消耗额外的磁盘空间。务必确保tmpdir或innodb_tmpdir指向的路径有充足空间。空间不足的典型报错是Operating system error number 28（空间耗尽）。

线上执行时的最小风险姿势

千万别以为“在测试库跑过就万事大吉”。真实的生产负载下，元数据锁等待、瞬时I/O压力、从库复制延迟等因素，都可能将风险放大。以下是几个能有效降低风险的实战建议：

• 显式写全参数：始终完整地指定算法和锁类型，例如ALTER TABLE t ALGORITHM=INPLACE, LOCK=NONE, ADD INDEX idx_x (x)。不要省略LOCK=NONE，因为其默认值是DEFAULT，这意味着把决策权交给了MySQL，结果可能不可控。
• 操作选择有讲究：添加索引时，优先尝试ALGORITHM=INPLACE。而删除索引通常是更稳妥的操作，它几乎总是INPLACE且速度极快，风险相对更低。
• 从库先行试验：对于不确定影响的操作，一个黄金法则是先在从库上试水。可以临时STOP SLA VE，执行DDL，再START SLA VE，并密切观察同步延迟和错误日志，评估实际影响。
• 开启监控视角：利用performance_schema.table_lock_waits_summary_by_table表，在DDL执行期间监控是否有其他线程因该表被阻塞，这能帮你快速定位并发冲突。

最后，必须强调一个最容易被忽略的要点：ALGORITHM=INPLACE主要解决的是“数据拷贝层”的锁问题，但DDL语句本身仍然需要获取元数据锁（MDL）的写锁。只要有一个慢查询或未提交的长事务持有该表的MDL读锁，整个ALTER操作就会挂起等待。所以，很多时候你看到的“语句没反应”，可能并不是MySQL在后台辛苦地拷贝数据，而仅仅是——它在干等着别的会话释放锁。理解这一点，是进行高效、安全DDL操作的关键所在。