SQL 大表在线加字段 / 加索引 / 修改列类型的最佳实践路径

mysql大表alter table卡住或超时，本质是锁表和复制全量数据；5.6前必锁表重建，5.7+虽默认inplace但改类型等操作仍可能退化为拷贝表，需结合引擎、版本支持矩阵及工具预演规避风险。

ALTER TABLE 大表卡住或超时，本质是锁表和复制全量数据

MySQL 5.6 之前 ALTER TABLE 加字段、改类型、建索引基本等于“停服操作”——它会锁住整张表，同时把原表拷贝一份再重建。哪怕只是加个 NOT NULL 字段，100GB 的表也可能卡住几小时。5.7+ 默认启用 ALGORITHM=INPLACE，但不是所有操作都支持就地修改；比如 MODIFY column 改类型，只要涉及字符集转换或长度收缩（如 VARCHAR(255) → VARCHAR(50)），仍会退化为拷贝表。

常见错误现象：Waiting for table metadata lock、Copying to tmp table 持续数小时、主从延迟突增、磁盘空间暴涨一倍。

• 先查当前操作是否支持 INPLACE：执行 SHOW CREATE TABLE t1 看引擎（必须是 InnoDB），再查官方文档对应版本的 ALTER TABLE 支持矩阵
• 避免在业务高峰执行；提前用 pt-online-schema-change 或 gh-ost 做预演，它们通过影子表 + 触发器/读 binlog 实现无锁变更
• 如果必须用原生命令，加 ALGORITHM=INPLACE, LOCK=NONE 显式声明（但 MySQL 会按实际能力降级，需检查 SHOW PROCESSLIST 中的 State 字段确认是否真没锁）

CREATE INDEX 在线失败的三个隐藏原因

加索引看似简单，但大表上常失败：不是语法错，而是资源或配置被卡住。MySQL 5.6+ 支持 CREATE INDEX 在线，但前提是表结构允许、内存够、且没其他长事务占着元数据锁。

典型报错：Error 1022 (23000): Can't write; duplicate key in table（其实是唯一索引校验失败）、ERROR 1205 (40001): Deadlock found（和业务更新冲突）、ERROR 1114 (HY000): The table 'xxx' is full（tmpdir 磁盘满或 innodb_sort_buffer_size 不足）。

• 建索引前先 select count(*) 确认数据量，再估算临时排序所需空间：一般按每百万行需 200–500MB tmpdir 空间估算
• 避免在 READ-COMMITTED 以上隔离级别下对同一张表并发执行多个 DDL；DDL 会持有 S 锁，和 UPDATE 的 X 锁冲突
• 对 TEXT/BLOB 列建前缀索引时，注意 innodb_large_prefix 和 ROW_FORMAT=DYNAMIC 必须同时开启，否则索引创建静默失败或截断

修改列类型（如 VARCHAR 长度）为什么比加字段更危险

ALTER TABLE ... MODIFY COLUMN 改长度，表面看只是调个数字，但 MySQL 会重新计算每行存储布局。从 VARCHAR(100) 扩到 VARCHAR(200) 是安全的（INPLACE），但从 VARCHAR(200) 缩到 VARCHAR(100) 就强制拷贝——因为要逐行校验现有数据是否超长，且可能触发行溢出页重分配。

更隐蔽的问题：字符集变更（如 utf8mb4 → utf8）看似降级，但 MySQL 不允许反向转换；而 utf8 → utf8mb4 虽支持 INPLACE，却要求 innodb_file_format=Barracuda 且 ROW_FORMAT=DYNAMIC，否则直接报错。

• 缩容列前务必 SELECT MAX(Length(col)) FROM t1，确保所有值都不超新长度
• 跨字符集修改，先用 CONVERT(col using utf8mb4) 在应用层清洗数据，再用 MODIFY COLUMN 只改定义（不转数据）
• 生产环境禁止直接 CHANGE COLUMN col col VARCHAR(50) NOT NULL 这种写法——NOT NULL 约束会触发全表扫描校验空值，极易超时

pt-online-schema-change 执行中被 kill 后的残留处理

pt-online-schema-change 是目前最稳妥的大表变更方案，但它不是原子操作。如果中途被 KILL 或进程崩溃，会留下 _t1_new、_t1_old、触发器等残留，且原表可能处于半同步状态（部分数据已写入新表但未切换）。

关键判断点：执行完命令后看输出末尾是否出现 Successfully altered `db`.`t1`.；没看到这句，一律视为失败。

• 检查是否存在 pt_osc_db_t1 这类命名的触发器：SHOW TRIGGERS LIKE 't1'，有则立刻 DROP TRIGGER
• 查残留表：SHOW TABLES LIKE '_t1_new'，确认无业务写入后 DROP TABLE；若 _t1_old 存在，说明切换失败，需人工比对新旧表数据一致性
• 别依赖 --execute 参数自动清理——它只在成功路径下生效；失败时必须手动介入，且优先停写再处理

真正难的从来不是“怎么加索引”，而是“怎么让加索引这件事不惊动线上查询”。所有工具和参数都只是缓冲带，核心逻辑永远是：先验证，再限流，最后才切流。漏掉任意一环，大表 DDL 就是定时炸弹。