MySQL 5.7大表ALTER仍会锁表,因ALGORITHM=INPLACE仅对部分操作真正无锁;涉及MODIFY COLUMN、收缩VARCHAR、改字符集或添加NOT NULL列等会退化为COPY算法,触发全表拷贝。

MySQL 5.7大表ALTER为什么还会锁表
因为ALGORITHM=INPLACE不等于“完全无锁”——5.7只对部分操作支持真正就地修改,比如加NULL列或普通索引;但一旦涉及MODIFY COLUMN、收缩VARCHAR长度、改字符集、或添加NOT NULL列(即使带DEFAULT),MySQL仍可能退化为COPY算法,触发全表拷贝+重建。
常见现象:Copying to tmp table持续数小时、Waiting for table metadata lock、磁盘空间暴涨一倍、主从延迟突增。
验证方式:执行SHOW PROCESSLIST,看State字段是否出现altering table或copy to tmp table;再查SHOW CREATE TABLE t1确认引擎是InnoDB,否则Online DDL直接不可用。
哪些ALTER操作在5.7里真能在线执行
不是所有ADD COLUMN都安全。关键看是否触发数据行变更:
- ✅ 安全(通常
LOCK=NONE):ADD COLUMN col INT NULL DEFAULT 0、ADD INDEX idx_name (col) - ⚠️ 危险(可能
LOCK=SHARED或更糟):ADD COLUMN col INT NOT NULL(没DEFAULT)、MODIFY COLUMN col VARCHAR(255) VARCHAR(50)、CHANGE COLUMN old_col new_col TEXT - ❌ 必须规避:
DROP COLUMN、RENAME COLUMN(5.7不支持)、外键相关变更(如删被引用列)
注意:ADD COLUMN ... DEFAULT 'xxx'看似安全,但如果表已有百万行,MySQL 5.7仍会隐式填充默认值——这步会锁表。解决办法是先加NULL列,再分批UPDATE,最后ALTER ... SET DEFAULT和MODIFY ... NOT NULL(分两步降级风险)。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
必须用原生命令时的硬性操作守则
别信“加了ALGORITHM=INPLACE, LOCK=NONE就万事大吉”——MySQL会按实际能力降级,且不报错。你得主动控制边界:
- 务必在业务低峰期执行,提前检查
innodb_lock_wait_timeout(建议调高到300秒防误杀) - 执行前确认没有长事务:
SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(NOW() - trx_started) > 60; - 显式声明并监控:
ALTER TABLE t ADD COLUMN x INT NULL, ALGORITHM=INPLACE, LOCK=NONE;,然后立刻SHOW PROCESSLIST盯状态 - 如果发现卡在
waiting for handler commit或altering table超过5分钟,立即KILL并切回pt-online-schema-change
pt-online-schema-change仍是5.7生产环境首选——它不依赖MySQL版本的DDL能力,而是靠影子表+触发器同步,只要业务能容忍几毫秒延迟,就能做到真正零锁表。
容易被忽略的隐性陷阱
最常栽跟头的地方不在SQL本身,而在周边配置和状态:
-
tmpdir磁盘空间不足会导致CREATE INDEX静默失败,报错却是The table 'xxx' is full——其实不是表满,是临时排序文件写不进去 -
READ-COMMITTED及以上隔离级别下,并发多个DDL会因MDL锁冲突引发死锁,尤其当有SELECT ... FOR UPDATE长事务存在时 - 对
TEXT/BLOB建前缀索引,若没开innodb_large_prefix=ON且ROW_FORMAT=DYNAMIC,索引会创建成功但实际无效(查询不走)
真正安全的在线变更,80%功夫在事前——查锁、估空间、看事务、验工具预演。别等ALTER跑起来才看PROCESSLIST。

















