如何在MySQL中利用Add-Index在线为千万级大表补充非空约束与索引-linux运维-PHP中文网

mysql中无法在线为已有列添加not null约束，因其需全表扫描验证null值并持snw锁；索引可在线添加（algorithm=inplace, lock=none）；稳妥方案是pt-online-schema-change；替代思路包括应用层校验和default兜底。

如何在mysql中利用add-index在线为千万级大表补充非空约束与索引

MySQL中为千万级大表在线添加非空约束（NOT NULL）和索引，不能直接用 ALTER TABLE ... MODIFY COLUMN ... NOT NULL 或 ADD INDEX 简单操作——这类语句在旧版本（如5.6及之前）会锁表、阻塞写入，导致服务中断。从 MySQL 5.6 起支持 Online DDL，但需满足特定条件才能真正“在线”；而 非空约束本身无法在线添加，这是关键前提。

非空约束（NOT NULL）无法真正在线添加

MySQL 的 NOT NULL 是列定义的一部分，添加该约束本质是修改列元数据+校验全表数据是否符合约束。即使使用 ALGORITHM=INPLACE，只要涉及 MODIFY COLUMN 或 CHANGE COLUMN 添加 NOT NULL，MySQL 仍需扫描全表验证无 NULL 值——这个过程会持有 SNW 锁（Shared-Next-Key Lock），阻塞 INSERT/UPDATE/DELETE，对大表就是事实上的“锁表”。

可行做法是分两步：

先确保数据已清洗干净：业务侧提前将目标列所有 NULL 值补为合法默认值（如 0、''、'1970-01-01' 等），并确认无新增 NULL 写入；
再用 ALGORITHM=INPLACE + LOCK=NONE 尝试变更（仅当满足条件时）：
ALTER TABLE t ADD COLUMN c INT DEFAULT 0 NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; —— 注意：这是 新增列 并设 NOT NULL，默认值填充后可在线；
但对已有列补 NOT NULL，即使有默认值，MySQL 仍要求全表扫描，LOCK=NONE 通常不被允许，会报错 LOCK=NONE is not supported。

索引可以真正在线添加（推荐用 ALGORITHM=INPLACE）

为千万级表加索引，优先使用 ALGORITHM=INPLACE（MySQL 5.6+ 默认行为），它通过排序构建索引、避免拷贝整表，在大多数情况下支持 LOCK=NONE（仅读锁，不阻塞写入）。

安全执行建议：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

确认表引擎为 InnoDB（MyISAM 不支持 Online DDL）；
显式指定算法与锁级别：
ALTER TABLE t ADD INDEX idx_col (col), ALGORITHM=INPLACE, LOCK=NONE;
若执行时报错 LOCK=NONE is not supported，说明当前操作仍需短暂排他锁（例如列上有虚拟生成列依赖、或存在外键），可降级为 LOCK=SHARED（允许读、阻塞写），比完全锁表好得多；
生产环境建议在低峰期执行，并监控 performance_schema.table_lock_waits_summary_by_table 或 SHOW PROCESSLIST 观察长事务影响。

更稳妥的方案：pt-online-schema-change（Percona Toolkit）

当原生 Online DDL 不满足要求（如必须给已有列加 NOT NULL，或担心锁风险），推荐使用 pt-online-schema-change。它通过触发器+影子表方式实现“逻辑在线”变更：

自动创建新表，添加所需索引与约束（包括 NOT NULL）；
实时同步原表增量变更（INSERT/UPDATE/DELETE）到新表；
最后原子切换表名，全程主库可读写（仅切换瞬间有毫秒级锁）；
支持限流（--max-load）、暂停（--pause-file）、失败回滚，适合核心大表。

示例命令（加索引 + 补非空）：

pt-online-schema-change \<br>
  --alter "ADD INDEX idx_col(col), MODIFY col INT NOT NULL" \<br>
  D=test,t=t1 \<br>
  --execute --no-check-alter

补充提醒：NOT NULL 的实际价值与替代思路

很多场景想加 NOT NULL，真实诉求其实是数据质量管控**或**查询性能优化**。可考虑更轻量的替代方案：

应用层写入校验（SDK/ORM 层拦截 NULL）比数据库强约束更可控；
对查询性能影响大的字段，索引本身已隐含“有效值”语义，NOT NULL 对执行计划帮助有限（除非配合 IS NOT NULL 查询且有索引覆盖）；
若只为防止误删值，可用 DEFAULT + 应用兜底，比强制 NOT NULL 更柔性。

不复杂但容易忽略：加索引前务必 ANALYZE TABLE 更新统计信息，否则优化器可能因过时的行数估算而选错执行计划。