MySQL 大表加字段或加索引如何优化？

简化版

大表 DDL 要先评估锁、耗时、磁盘、复制延迟和回滚风险；优先使用支持 online/inplace/instant 的变更方式，必要时借助 pt-online-schema-change 或 gh-ost，并在低峰灰度执行。

详细版

大表加字段、改字段、加索引不是普通 SQL 小事。几千万行表做 DDL 可能持有元数据锁、占用 IO、生成大量日志、导致主从延迟，甚至阻塞业务读写。

MySQL 不同版本、不同 DDL 类型支持的算法不同：有些变更可以几乎瞬间完成，有些需要重建整表。执行前要用测试环境和预检查确认 ALGORITHM、LOCK、表大小、索引大小、磁盘空间和业务流量。

工程上常见策略是：低峰执行、设置超时、先从从库或影子表验证、监控主从延迟和锁等待；超大表复杂变更使用在线变更工具，分批复制数据并短暂切换表。

完整版教学

一、大表 DDL 的风险在哪里

DDL 会改变表结构，数据库可能需要扫描或重建整张表。

ALTER TABLE orders ADD INDEX idx_user_time(user_id, created_at);

如果 orders 有 2 亿行，建索引需要读取大量数据、排序构建索引页、写磁盘，还可能影响 Buffer Pool 和 redo/binlog。

记忆钩子：大表 DDL 不是“改一下表结构”，而是在业务旁边搬一座正在使用的仓库。

二、元数据锁会阻塞业务

MySQL DDL 会涉及 metadata lock。即使某些 online DDL 支持并发读写，也需要在开始或结束阶段获取元数据锁。

常见危险场景：

长事务正在读 orders
-> ALTER TABLE 等待 metadata lock
-> 后续业务查询也被 ALTER 队列挡住
-> 连接堆积

排查时要看长事务和锁等待。执行前应控制事务时长，并设置合适的锁等待超时，避免 DDL 长时间卡住业务。

SHOW PROCESSLIST;

如果看到大量 Waiting for table metadata lock，说明已经影响业务。

三、Online DDL 的几个关键词

MySQL DDL 常见关键词包括 ALGORITHM 和 LOCK。

ALTER TABLE orders
ADD INDEX idx_user_time(user_id, created_at),
ALGORITHM=INPLACE,
LOCK=NONE;

含义要结合版本和操作类型理解：

方式	直觉含义	风险
`INSTANT`	只改元数据，最快	支持范围有限
`INPLACE`	尽量不重建表	仍可能消耗 IO
`COPY`	复制重建表	大表风险高
`LOCK=NONE`	尽量不阻塞读写	仍有短暂 MDL

不要只写参数就认为安全，数据库不支持时可能报错或退化。要在测试环境确认实际算法。

四、在线变更工具怎么做

pt-online-schema-change 和 gh-ost 的核心思想都是建立新表、增量同步、最后切换。

简化流程：

1. 创建影子表 new_orders
2. 在影子表上应用新结构
3. 分批拷贝老表数据
4. 同步变更期间的增量写入
5. 短暂锁表/切换表名
6. 清理旧表

这种方式把一次长时间重建拆成可控的分批过程，但不是零风险。它会增加复制、触发器或 binlog 压力，也需要足够磁盘空间。

如果表 500GB，影子表可能还要额外几百 GB 空间，执行前必须算账。

五、加索引前要确认收益

大表加索引成本很高，不能因为某条慢 SQL 就随便加。

要先确认：

慢 SQL 频率和耗时
现有索引是否可复用
新索引选择性如何
是否会影响写入
是否和已有索引重复

例如已有 (user_id, created_at)，再加 (user_id) 可能冗余。索引越多，插入、更新、删除需要维护的 B+ 树越多。

优化不是只让一条查询变快，还要看整张表的读写平衡。

六、上线流程要保守

大表变更建议有完整上线流程：

1. 预估表大小、行数、索引大小
2. 测试环境验证 DDL 算法
3. 检查长事务和主从延迟
4. 低峰执行，设置超时
5. 监控锁等待、QPS、IO、延迟
6. 准备回滚或中止方案

如果使用在线变更工具，还要限制拷贝速率，观察主库负载和从库延迟。不要在业务峰值对核心表直接执行未知耗时 DDL。

七、常见误区与追问

误区：加字段和加索引都是瞬间完成。 是否瞬间取决于 MySQL 版本、字段位置、默认值和 DDL 类型。
误区：Online DDL 完全不加锁。 它仍涉及元数据锁，开始或结束阶段可能短暂阻塞。
误区：大表加索引只影响查询。 建索引会消耗 IO、CPU、磁盘，并影响写入维护成本。
误区：在线变更工具没有风险。 它需要额外空间、增量同步和切换过程，也可能造成复制延迟。
追问：如何发现 metadata lock 问题？ 看 SHOW PROCESSLIST 中的 Waiting for table metadata lock 和长事务。
追问：什么时候用 gh-ost 或 pt-osc？ 超大表、原生 DDL 风险高、需要可控分批迁移时考虑。

八、加强记忆

大表 DDL 优化看五个风险：锁、IO、磁盘、复制延迟、回滚。小表可以直接改，大表要先验证算法，复杂变更用在线工具分批做。真正专业的答案不是会写 ALTER，而是知道怎么让它不拖垮业务。

简化版

详细版