MySQL 表结构变更优化：如何安全添加字段而不阻塞业务？

-- 假设执行以下 DDL（MySQL 5.6）
ALTER TABLE `user` ADD COLUMN `vip_level` INT NULL DEFAULT 0;

-- 最佳实践：强制使用 INPLACE 和 NONE 锁
ALTER TABLE `user` 
ADD COLUMN `vip_level` INT NULL DEFAULT 0,
ALGORITHM=INPLACE, 
LOCK=NONE;

-- 步骤1：快速加列（不阻塞）
ALTER TABLE `user` ADD COLUMN `vip_level` INT NULL;

-- 步骤2：分批更新默认值（避免锁全表）
UPDATE `user` SET `vip_level` = 0 WHERE `id` BETWEEN 1 AND 100000;
UPDATE `user` SET `vip_level` = 0 WHERE `id` BETWEEN 100001 AND 200000;
-- ...

pt-online-schema-change \
--alter "ADD COLUMN vip_level INT NULL DEFAULT 0" \
D=mydb,t=user \
--execute

// 使用 JDBC 检查是否有长事务运行
try (Connection conn = dataSource.getConnection()) {
    ResultSet rs = conn.createStatement().executeQuery(
        "SELECT * FROM information_schema.innodb_trx " +
        "WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60"
    );
    if (rs.next()) {
        throw new IllegalStateException("存在长事务，禁止执行 DDL！");
    }
}

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface UseReplicaDataSource {}

@Aspect
@Component
public class DataSourceAspect {
    @Around("@annotation(UseReplicaDataSource)")
    public Object switchDataSource(ProceedingJoinPoint pjp) throws Throwable {
        DynamicDataSourceContextHolder.useReplica();
        try {
            return pjp.proceed();
        } finally {
            DynamicDataSourceContextHolder.clear();
        }
    }
}

// 使用示例：读操作走从库，避免主库 DDL 影响
@UseReplicaDataSource
public List<User> getAllUsers() {
    return userMapper.selectList(null);
}