如何为MySQL大型表添加索引而不阻塞读写？-mysql教程-PHP中文网

mysql生产环境在线加索引优先用algorithm=inplace, lock=none；不满足前提时（如外键、mixed binlog、低版本）则用pt-osc，且加唯一索引前必须查重清理。

如何为mysql大型表添加索引而不阻塞读写？

直接用 ALTER TABLE ... ADD INDEX 默认可能锁表，尤其在千万级表上——别等执行完才发现服务卡住。真正“不阻塞读写”的方案只有两个：一是 MySQL 原生支持的 ALGORITHM=INPLACE, LOCK=NONE，二是用 pt-online-schema-change 工具模拟无锁变更。前者快但有硬性前提，后者稳但需额外部署。

ALGORITHM=INPLACE, LOCK=NONE 能跑通的前提条件

这不是开关，而是带门槛的“免锁通道”。MySQL 5.6+ 的 InnoDB 表才能走这条路，但满足版本只是第一步：

binlog_format 必须是 ROW（STATEMENT 或 MIXED 会导致 LOCK=NONE 失效）
目标表不能有外键约束（否则会自动降级为 LOCK=SHARED）
不能对 TEXT/BLOB 字段建索引而不指定前缀长度，例如 ADD INDEX idx_content (content(255))
字段不能有默认值、生成列依赖或全文索引（这些会强制回退到 ALGORITHM=COPY）
确保 innodb_file_per_table=ON（否则 INPLACE 可能失败）

怎么验证当前表是否支持无锁加索引

别猜，查 INFORMATION_SCHEMA：

SELECT 
  TABLE_NAME, 
  ENGINE, 
  TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

再确认关键参数：

SELECT @@version, @@binlog_format, @@innodb_file_per_table;

如果都达标，再试这条命令（注意替换字段和索引名）：

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at) ALGORITHM=INPLACE, LOCK=NONE;

失败时错误里通常带提示，比如 ALGORITHM=COPY is required 就说明触发了降级，得排查上面列出的任一条件是否被违反。

MySQL（Linux）

MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。

下载

pt-online-schema-change 是什么情况下必须用

当你的表有外键、用了 MIXED binlog、或者 MySQL 版本低于 5.6，ALGORITHM=INPLACE 就走不通。这时 pt-online-schema-change 是唯一靠谱选择：

它不依赖 MySQL 内部 DDL 机制，而是靠触发器捕获变更，全程不锁原表
要求 binlog_format = ROW（否则增量同步会丢数据）
外键必须显式处理：--alter-foreign-keys-method=auto，但风险高，建议先拆外键
执行前务必检查磁盘空间——它会多占一份表大小的空间
命令示例：pt-online-schema-change --alter "ADD INDEX idx_user_id (user_id)" D=your_db,t=users --execute

容易被忽略的细节：唯一索引和重复数据

加普通索引失败顶多报错退出，但加 UNIQUE 索引时，MySQL 会严格校验全表数据。哪怕只有一行重复，ALTER TABLE ... ADD UNIQUE 就直接中断，且不会告诉你哪一行冲突。

先清理再操作：

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

另外，pt-online-schema-change 对唯一索引不校验重复——它用 INSERT IGNORE 拷贝数据，冲突行直接跳过，结果是数据丢失而非报错。这点必须提前确认业务能否接受。

真正麻烦的不是语法，而是你不知道哪条隐含规则会在凌晨两点突然生效。做之前，先查 @@version 和 binlog_format，比反复重试更省时间。