MySQL数据库大表DDL变更操作的原理、方案与实践教学

alter table orders add column remark varchar(255);

alter table orders add column remark varchar(255), algorithm=inplace;

alter table orders add column remark varchar(255), algorithm=instant;

-- 设置 ddl 等待超时（不要无限等待）
set lock_wait_timeout = 5;  -- 等5秒拿不到锁就放弃
alter table orders add column remark varchar(255);

-- 如果报错 lock wait timeout exceeded → 换个时间再试

create table orders_new like orders;

alter table orders_new add column remark varchar(255);

   - after insert → insert into orders_new
   - after update → update orders_new（或replace into）
   - after delete → delete from orders_new

   insert into orders_new select * from orders where id between 1 and 1000;
   insert into orders_new select * from orders where id between 1001 and 2000;
   ...

rename table orders to orders_old, orders_new to orders;

pt-online-schema-change \
  --alter "add column remark varchar(255) default null" \
  --host=db.example.com \
  --port=3306 \
  --user=admin \
  --password=secret \
  --chunk-size=1000 \
  --max-load="threads_running=50" \
  --critical-load="threads_running=100" \
  d=mydb,t=orders \
  --execute

gh-ost \
  --alter="add column remark varchar(255) default null" \
  --database=mydb \
  --table=orders \
  --host=db.example.com \
  --user=admin \
  --password=secret \
  --chunk-size=1000 \
  --max-load="threads_running=50" \
  --ok-to-drop-table \
  --execute

-- 瞬间完成，不受数据量影响
alter table orders 
add column remark varchar(255) default null comment '备注',
algorithm=instant;

-- 验证是否支持 instant
alter table orders 
add column remark varchar(255) default null,
algorithm=instant, lock=none;
-- 如果不支持会报错：algorithm=instant is not supported

# 1. 确认低峰期（凌晨2-4点）
# 2. 通知相关方

# 3. 停止服务
# 或：停止所有实例的 java 进程

# 4. 确认无连接

# 5. 执行 ddl（无并发，瞬间获取mdl锁）
alter table orders add column remark varchar(255) default null;
-- 4000万行约 5-15 分钟

# 6. 确认执行完成
desc orders;  -- 确认新字段存在

# 7. 启动服务

-- 1. 查看表数据量
select table_name, table_rows, 
       round(data_length/1024/1024, 2) as data_mb,
       round(index_length/1024/1024, 2) as index_mb
from information_schema.tables 
where table_schema = 'mydb' and table_name = 'orders';

-- 2. 查看当前表的活跃连接（是否有长事务持有mdl锁）
select * from information_schema.processlist 
where db = 'mydb' and command != 'sleep' 
order by time desc;

-- 3. 查看 mysql 版本（决定是否可用 instant）
select version();

-- 4. 查看表结构（确认当前字段）
show create table orders\g

-- 5. 估算 alter 耗时（测试环境执行）
set profiling = 1;
alter table orders_test add column remark varchar(255);
show profiles;

-- 设置超时保护（避免长时间等待mdl锁导致雪崩）
set session lock_wait_timeout = 10;  -- 最多等10秒
set session innodb_lock_wait_timeout = 10;

-- 执行 ddl
alter table orders add column remark varchar(255) default null comment '备注';

-- 如果超时报错：error 1205 (hy000): lock wait timeout exceeded
-- → 说明有长事务持有锁，需要找到并处理后重试

-- 查找持有 mdl 锁的事务
select 
    t.trx_id,
    t.trx_state,
    t.trx_started,
    timestampdiff(second, t.trx_started, now()) as running_seconds,
    t.trx_mysql_thread_id,
    p.user,
    p.host,
    p.db,
    p.command,
    p.info as current_sql
from information_schema.innodb_trx t
join information_schema.processlist p on t.trx_mysql_thread_id = p.id
order by t.trx_started asc;

-- 如果发现运行很久的事务，可以 kill（需确认不影响业务）
-- kill <trx_mysql_thread_id>;

/**
 * 方案b：新建轻量子表存储新字段.
 * 适用于：大表无法 alter 时，将新字段存到独立小表.
 */
// 1. 新表 ddl（瞬间完成，空表）
// create table order_extend_new (
//     id int auto_increment primary key,
//     order_id int not null,
//     new_field_a int default null,
//     new_field_b varchar(100) default null,
//     unique key uk_order_id (order_id)
// );
// 2. 实体类
@entity
@table(name = "order_extend_new")
public class orderextendnew extends baseentity {
    @column(name = "order_id")
    private integer orderid;
    @column(name = "new_field_a")
    private integer newfielda;
    @column(name = "new_field_b")
    private string newfieldb;
}
// 3. repository
public interface orderextendnewrepository extends jparepository<orderextendnew, integer> {
    orderextendnew findbyorderid(integer orderid);
}
// 4. 写入（在原有事务内一起写）
@transactional
public void createorder(orderdto dto) {
    order order = orderrepository.save(buildorder(dto));
    // 写新扩展表
    if (dto.getnewfielda() != null) {
        orderextendnew extend = new orderextendnew();
        extend.setorderid(order.getid());
        extend.setnewfielda(dto.getnewfielda());
        orderextendnewrepository.save(extend);
    }
}
// 5. 读取（多一次查询）
public orderdto getorder(integer orderid) {
    order order = orderrepository.findbyid(orderid).get();
    orderdto dto = converttodto(order);
    // 从新表读扩展字段
    orderextendnew extend = orderextendnewrepository.findbyorderid(orderid);
    if (extend != null) {
        dto.setnewfielda(extend.getnewfielda());
    }
    return dto;
}

-- 建表时预留扩展字段，避免日后 alter
create table order_detail (
    id int auto_increment primary key,
    order_id int not null,
    item_sku_id int not null,
    qty int not null,
    price decimal(14,2) not null,
    -- 业务字段...
    
    -- 预留扩展字段（日后需要时直接使用，无需alter）
    ext_int_1 int default null comment '预留整型字段1',
    ext_int_2 int default null comment '预留整型字段2',
    ext_int_3 int default null comment '预留整型字段3',
    ext_str_1 varchar(255) default null comment '预留字符串字段1',
    ext_str_2 varchar(255) default null comment '预留字符串字段2',
    ext_str_3 varchar(255) default null comment '预留字符串字段3',
    
    create_time datetime not null,
    update_time datetime not null,
    index idx_order_id (order_id)
) comment='订单明细表';

-- 使用时只需更新注释（不改结构，无需ddl）：
alter table order_detail 
modify column ext_int_1 int default null comment '是否定日达 0否1是';
-- modify column 改注释在 mysql 5.7 中也是 online 的

-- 1. 创建归档表
create table orders_archive_202601 like orders;

-- 2. 迁移旧数据到归档表
insert into orders_archive_202601 
select * from orders where create_time < '2026-02-01';

-- 3. 删除热表中的旧数据（分批删除，避免长事务）
delete from orders where create_time < '2026-02-01' limit 10000;
-- 重复执行直到删除完毕

-- 4. 定时任务自动归档（每月1日执行）