在MySQL千万级大表中如何在线添加索引而不锁表-mysql教程-PHP中文网

MySQL 5.7+ 千万级表 ADD INDEX 可近乎不锁表，但需满足InnoDB引擎、无外键、无虚拟列/全文索引等条件；执行前须用SHOW CREATE TABLE和information_schema校验，并通过SHOW PROCESSLIST确认State为altering table而非copy to tmp table。

在mysql千万级大表中如何在线添加索引而不锁表

MySQL 5.7+ 的千万级大表，只要满足条件，ALTER TABLE ... ADD INDEX 本身就能做到几乎不锁表；但“不锁表”不等于“无影响”，关键看是否触发 ALGORITHM=INPLACE, LOCK=NONE，以及你有没有踩中那些隐性限制。

如何确认你的 `ADD INDEX` 真的走 Online DDL？

不能只看语句写了 LOCK=NONE，MySQL 会根据操作类型和表结构自动降级。执行前先查清楚：

用 SHOW CREATE TABLE t 确认引擎是 InnoDB（MyISAM 不支持 Online DDL）
检查是否有外键：SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_NAME = 't' AND CONSTRAINT_SCHEMA = DATABASE() AND REFERENCED_TABLE_NAME IS NOT NULL; —— 有外键时 LOCK=NONE 会被拒绝
检查列是否含虚拟生成列、全文索引、空间索引等 —— 这些都会导致退化为 COPY 算法，锁表时间显著变长
执行 ALTER TABLE t ADD INDEX idx_x (x), ALGORITHM=INPLACE, LOCK=NONE; 后立刻查 SHOW PROCESSLIST：如果状态长期卡在 altering table 且 State 显示 copy to tmp table，说明没走 inplace

为什么明明写了 `LOCK=NONE` 却还是阻塞写入？

常见误解是“LOCK=NONE = 完全不锁”。实际上它只保证不加表级写锁，但仍有两类短暂但关键的阻塞：

元数据锁（MDL）写锁：在 DDL 开始和结束时各持有一瞬间（通常 INSERT/UPDATE 会等待 —— 如果此时有长事务未提交，MDL 会被阻塞，所有后续 DML 都排队
索引构建阶段的行级资源争用：虽然不锁表，但大量随机 IO 和 CPU 消耗会导致 innodb_row_lock_time_avg 上升、Threads_running 暴涨，业务 SQL 响应变慢甚至超时
磁盘 IOPS 打满时，INSERT 可能因刷脏页延迟而卡住，看起来像“被锁”，实则是资源瓶颈

什么情况下必须放弃原生 `ALTER`，改用 `pt-online-schema-change`？

不是数据量大就要上 pt-osc，而是当 MySQL 内部 Online DDL 被迫降级或根本不可用时才需要：

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

MySQL 版本
表有外键且无法临时禁用（--alter-foreign-keys-method=auto 风险高，易丢数据）
使用 MIXED 或 STATEMENT binlog_format（pt-osc 要求 ROW）
执行 ALTER 报错类似 LOCK=NONE is not supported for this operation 或 ALGORITHM=COPY required
你无法接受哪怕 100ms 的 MDL 写锁（比如金融类秒级强一致场景）

注意：pt-online-schema-change 会多占一份磁盘空间，且依赖触发器 —— 若原表已有大量触发器或对延迟极度敏感，它反而更重。

真正影响落地的三个细节，90% 的人会忽略

再好的方案，输在细节上就白忙：

innodb_sort_buffer_size 默认值太小（1MB），千万级建索引时建议调到 8–16MB，否则排序阶段频繁落盘，IO 暴增
不要在 sysbench 压测期间建索引 —— 即使 LOCK=NONE，也会因 buffer pool 竞争导致 QPS 断崖下跌
监控必须盯死两个指标：performance_schema.table_lock_waits_summary_by_table（看 MDL 等待）、SHOW ENGINE INNODB STATUS 中的 ROW OPERATIONS 部分（看索引构建进度），别只看 Processlist

最危险的不是锁表，而是你以为没锁表，结果业务慢得查不出原因 —— 索引构建过程本身就会吃掉大量资源，它不像事务那样有明确边界，容易被当成“后台安静任务”而放任不管。