MySQL大表加索引不必然锁表,但默认ALGORITHM=DEFAULT和LOCK=DEFAULT易因MDL等待或退化为COPY算法导致写阻塞甚至全表不可用;真正无锁需显式指定ALGORITHM=INPLACE、LOCK=NONE,并确保无长事务、外键、全文索引且innodb_file_per_table=ON。

MySQL大表加索引不必然锁表,但默认行为极大概率导致写阻塞甚至全表不可用——关键在是否触发元数据锁(MDL)等待,以及是否退化为COPY算法。
ALGORITHM=INPLACE, LOCK=NONE 为什么不是默认值
MySQL 自动选择 ALGORITHM=DEFAULT 和 LOCK=DEFAULT,看似省事,实则危险:
- 只要表上有任何未提交事务(哪怕只是个
SELECT ... FOR UPDATE或隐式事务中的长查询),MDL 就会卡住 DDL,状态显示为Waiting for table metadata lock -
LOCK=DEFAULT在多数二级索引场景下会降级为LOCK=SHARED(读可用、写阻塞),而非你期待的LOCK=NONE - 外键、触发器、全文索引、主键变更等操作,根本**不支持**
INPLACE,MySQL 会静默 fallback 到ALGORITHM=COPY,全程锁表 -
innodb_file_per_table=OFF时,INPLACE直接不可用,报错ALGORITHM=INPLACE is not supported
如何确认加索引是否真能并发读写
执行前必须验证计划,不能只看语句写了 LOCK=NONE:
- 先跑
EXPLAIN FORMAT=JSON ALTER TABLE users ADD INDEX idx_name (name);,检查输出中是否有"alter_algorithm": "inplace"和"lock": "none" - 若出现
"lock": "shared",说明写操作会被暂停,应避开高峰;若为"lock": "exclusive",等于直接锁表,立刻中止 - 查
SHOW VARIABLES LIKE 'innodb_file_per_table';,确保值为ON - 查
SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;,清掉运行超 1 分钟的事务
大表加索引的实际执行策略
即使满足 INPLACE+NONE,I/O 和 CPU 压力仍会让线上查询变慢——这不是锁,是资源挤占:
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 优先用
pt-online-schema-change:自动分片、限流、校验,适合千万级以上表,命令如pt-osc --alter "ADD INDEX idx_email(email)" D=test,t=users --max-load="Threads_running=25" --critical-load="Threads_running=50" - 若坚持原生命令,加索引前临时调小
innodb_buffer_pool_size(避免刷脏页风暴),加完立即恢复 - 监控
SHOW ENGINE INNODB STATUS\G中的FILE I/O部分,关注pending normal aio reads是否持续升高 - 绝对不要在从库上直接执行 DDL,GTID 模式下可能引发复制延迟突增甚至中断
被锁住时怎么快速恢复业务
一旦发现 ALTER TABLE 卡住,且其他查询全挂起,别等它自己完成:
- 用
SHOW PROCESSLIST;找出状态为Waiting for table metadata lock的线程,记下其Id - 再查
SELECT THREAD_ID, EVENT_NAME, SOURCE FROM performance_schema.events_waits_current WHERE EVENT_NAME = 'wait/lock/metadata/sql/mdl';(MySQL 8.0+),定位持有 MDL 的源头事务 - 对源头事务执行
KILL [thread_id],不是杀 DDL 线程本身——杀错线程只会让锁更久 - 如果源头是长事务且无法 kill(比如应用层没暴露连接),只能临时重启 MySQL,但这是最后手段
真正容易被忽略的点:MDL 锁和事务生命周期绑定,而不是和 SQL 执行时间绑定。一个没提交的 SELECT ... FOR UPDATE 可能在后台睡了 10 分钟,却足以让后续所有 DDL 排队等待。所以加索引前,必须确认“此刻没有活跃事务”,而不是“刚才没看到长查询”。










