大表ALTER TABLE操作有哪些风险及平滑方案？

大表执行ALTER TABLE操作时，由于表数据量大、变更逻辑复杂，很容易引发各类线上问题，需要提前了解潜在风险并选择合适的平滑变更方案。

大表ALTER TABLE操作的常见风险

1. 锁表导致业务不可用

在MySQL 5.6之前的版本中，很多ALTER TABLE操作会触发表级锁，执行期间整个表无法被读写，对于日均千万级访问的大表来说，几秒的锁表就可能引发大量业务请求超时。即使是在支持在线DDL的版本中，部分变更类型（如修改主键、修改列类型）仍会获取排他锁，导致业务中断。

2. 主从延迟加剧

主库执行ALTER TABLE操作后，生成的binlog会在从库回放，大表的变更binlog体积大、执行时间长，很容易造成从库复制延迟，进而影响读写分离架构下的数据一致性，严重时可能导致从库服务不可用。

3. 磁盘空间占用过高

大部分ALTER TABLE操作会创建临时表来存储变更后的数据，临时表的大小和原表相当，如果磁盘剩余空间不足，变更会直接失败，甚至可能导致数据库服务因为磁盘满而宕机。

4. 变更失败回滚成本高

大表变更如果执行到一半失败，回滚操作同样需要大量时间，期间表处于不稳定状态，业务受影响的时间会进一步延长。

大表ALTER TABLE的平滑方案

方案一：使用pt-online-schema-change工具

pt-online-schema-change是Percona Toolkit中的核心工具，通过创建影子表、增量同步数据、触发器同步增量变更的方式实现无锁变更，是大表变更的首选方案。

基本使用示例如下：

# 安装Percona Toolkit后执行，修改test库下user表的name列长度为100
pt-online-schema-change --host=127.0.0.1 --port=3306 --user=root --password=123456 
--alter="MODIFY COLUMN name VARCHAR(100)" 
D=test,t=user --execute

该工具的工作流程是：

• 创建和原表结构一致的影子表，对影子表执行ALTER变更
• 在原表上创建INSERT、UPDATE、DELETE三个触发器，将原表的增量数据同步到影子表
• 分批将原表数据拷贝到影子表，每批拷贝完成后会短暂暂停触发器
• 拷贝完成后，用影子表替换原表，最后删除原表和触发器

注意：使用时要确保原表有主键或者唯一索引，否则工具无法正常工作，同时要避免在变更期间对原表执行RENAME TABLE等DDL操作。

方案二：利用MySQL原生在线DDL特性

MySQL 5.6及以上版本支持在线DDL，部分变更操作可以在不锁表的情况下执行，不同操作的支持程度不同，可通过官方文档查询对应算法的支持情况。

示例：给大表添加普通索引，使用INPLACE算法避免表拷贝：

-- 添加索引时指定ALGORITHM和LOCK参数
ALTER TABLE user ADD INDEX idx_age (age), ALGORITHM=INPLACE, LOCK=NONE;

参数说明：

• ALGORITHM=INPLACE：表示原地执行变更，不需要拷贝全表数据
• LOCK=NONE：表示变更期间不锁表，允许读写操作

如果操作不支持INPLACE算法，会自动降级为COPY算法，此时会锁表，需要提前评估风险。

方案三：影子表手动切换方案

如果不想依赖第三方工具，也可以手动实现影子表变更，适合对变更流程有完全掌控需求的场景：

-- 1. 创建影子表，结构和原表一致
CREATE TABLE user_new LIKE user;
-- 2. 对影子表执行ALTER变更
ALTER TABLE user_new MODIFY COLUMN name VARCHAR(100);
-- 3. 锁定原表，防止新数据写入
LOCK TABLES user WRITE;
-- 4. 将原表剩余数据同步到影子表（如果有增量数据可在锁定前先同步一次）
INSERT INTO user_new SELECT * FROM user;
-- 5. 重命名表完成切换
RENAME TABLE user TO user_old, user_new TO user;
-- 6. 解锁表
UNLOCK TABLES;
-- 7. 确认数据无误后删除旧表
DROP TABLE user_old;

该方案的缺点是锁定原表的时间取决于最后同步数据的耗时，如果原表数据量极大，锁表时间会较长，适合在业务低峰期执行。

方案选择建议

如果表数据量超过千万级，优先选择pt-online-schema-change工具，几乎无业务感知；如果是添加索引、修改默认值等轻量变更，可选择MySQL原生在线DDL；如果变更逻辑复杂且无法使用工具，可在业务低峰期采用手动影子表方案，同时提前做好数据备份，避免变更失败导致数据丢失。

MySQLALTER_TABLE大表变更pt_online_schema_change修改时间：2026-06-13 20:51:23