MySQL亿级大表安全添加字段的三种方案

1. 亿级大表 alter 的风险评估

1.1 直接执行 alter 的潜在问题

alter table `orders` add column `is_priority` tinyint null default 0;

• 锁表时间估算（经验值）：
  • mysql 5.6：约 2-6小时（完全阻塞）
  • mysql 5.7+：10-30分钟（短暂阻塞写入）
• 业务影响：
  • 所有读写请求超时
  • 连接池耗尽（too many connections）
  • 可能触发高可用切换（如 mha）

1.2 关键指标检查

-- 查看表大小（gb）
select 
    table_name, 
    round(data_length/1024/1024/1024,2) as size_gb
from information_schema.tables 
where table_schema = 'your_db' and table_name = 'orders';

-- 检查当前长事务
select * from information_schema.innodb_trx 
where time_to_sec(timediff(now(), trx_started)) > 60;

2. 三种安全方案对比

3. 方案一：mysql 原生 online ddl（5.7+）

3.1 最优执行命令

alter table `orders` 
add column `is_priority` tinyint null default 0,
algorithm=inplace, 
lock=none;

3.2 监控进度（另开会话）

-- 查看 ddl 状态
show processlist;

-- 查看 innodb 操作进度
select * from information_schema.innodb_alter_table;

3.3 预估执行时间（经验公式）

时间(min) = 表大小(gb) × 2 + 10

• 假设表大小 50gb → 约 110分钟

4. 方案二：pt-online-schema-change 实战

4.1 安装与执行

# 安装 percona toolkit
sudo yum install percona-toolkit

# 执行变更（自动创建触发器）
pt-online-schema-change \
--alter "add column is_priority tinyint null default 0" \
d=your_db,t=orders \
--chunk-size=1000 \
--max-load="threads_running=50" \
--critical-load="threads_running=100" \
--execute

4.2 关键参数说明

4.3 java 应用兼容性处理

// 在触发器生效期间，需处理重复主键异常
try {
    orderdao.insert(neworder);
} catch (duplicatekeyexception e) {
    // 自动重试或走降级逻辑
    orderdao.update(neworder);
}

5. 方案三：gh-ost 高级用法

5.1 执行命令（无需触发器）

gh-ost \
--database="your_db" \
--table="orders" \
--alter="add column is_priority tinyint null default 0" \
--assume-rbr \
--allow-on-master \
--cut-over=default \
--execute

5.2 核心优势

• 无触发器设计：避免性能损耗
• 动态限流：自动适应服务器负载
• 可交互控制：支持暂停/恢复

# 运行时控制
echo throttle | nc -u /tmp/gh-ost.sock
echo no-throttle | nc -u /tmp/gh-ost.sock

6. java 应用层适配策略

6.1 双写兼容模式（推荐）

// 在变更期间同时写入新旧字段
public void createorder(order order) {
    order.setispriority(0); // 新字段默认值
    ordermapper.insert(order);
    
    // 兼容旧代码
    if (order.getv2() == null) {
        ordermapper.updateispriority(order.getid(), 0);
    }
}

6.2 动态 sql 路由

    insert into orders 
    (id, user_id, amount
    , is_priority)
    values
    (#{id}, #{userid}, #{amount}
    , #{ispriority})

7. 监控与回滚方案

7.1 实时监控指标

# 监控复制延迟（主从架构）
pt-heartbeat --monitor --database=your_db

# 查看 gh-ost 进度
tail -f gh-ost.log

7.2 紧急回滚步骤

# pt-osc 回滚（自动清理临时表）
pt-online-schema-change --drop-new-table --alter="..." --execute

# gh-ost 回滚
gh-ost --panic-on-failure --revert

8. 总结建议

1. 首选方案：

   • mysql 8.0 → 原生algorithm=instant（秒级完成）
   • mysql 5.7 →gh-ost（无触发器影响）

2. 执行窗口：

   • 选择业务流量最低时段（如凌晨 2-4 点）
   • 提前通知业务方准备降级方案

3. 验证流程：

-- 变更后检查数据一致性
select count(*) from orders where is_priority is null;

• 后续优化：

-- 添加完成后可改为 not null
alter table orders 
modify column is_priority tinyint not null default 0;

通过合理选择工具+应用层适配，即使 1.35亿条数据 的表也能实现 零感知 的字段添加。

以上就是mysql亿级大表安全添加字段的三种方案的详细内容，更多关于mysql大表添加字段的资料请关注代码网其它相关文章！