Mysql之八-超大表字段添加的要点

背景

因为业务的需求, 公司的一张大表需要添加字段, 先看了下表的大小, 行数到是还行, 才几十万, 但是, 因为有大字段的原因, 这个表有几十个G, 因此, 考验的时候就来了, 找了个业务不繁忙的时候, 果断添加了字段, 可是这个过程中, ddl执行了快一个半小时, 然后磁盘的iops快要爆炸, 因为业务低锋期, 倒也还没造成什么大影响, 但难免对大表加字段产生了深深的恐惧;
于是我们来探究下大表加字段究竟做了什么动作;

版本影响

1.Mysql5.6之前, 直接修改表结构会导致整个数据库锁表, 具体内部步骤如下:(copy方式)

  • 首先创建新的临时表, 表结构通过命令alter table新定义的结构;
  • 然后把原表中数据导入到临时表;
  • 删除原表;
  • 最后把临时表重命名为原来的表名;

2.Mysql5.6之后, 引入了Online DDL, 修改表结构, 或者某些操作时, 就不会导致锁表了; 但还是要注意会锁表的情况;

Online DDL实现

DDL一般分两种形式:
1.copy方式:(需要拷贝数据, 实现非常重)

  • 新建带索引的临时表;
  • 锁原表, 禁止DML, 允许查询;
  • 将原表数据拷贝到临时表(无排序, 一行一行拷贝)
  • 进行rename, 升级字典锁, 禁止读写;
  • 完成创建索引的操作;

2.inplace方式:(这个比较轻量, 不拷贝数据, 但是只适用于创建和删除索引)

  • 新建索引的数据字典;
  • 锁表, 禁止DML, 允许查询;
  • 读取聚簇索引 构建新的索引项, 排序并插入新索引
  • 等待打开当前表的所有只读事务提交;
  • 创建索引结束

Online DDL实现:
其实实质也是包含了copy和inplace的方式, 对于不支持online形式的, 也是直接采用了copy的方式;
online DDL主要包含了3个阶段;
1.Prepare阶段:

  • 创建新的临时frm文件;
  • 持有EXCLUSIVE-MDL锁, 禁止读写;
  • 根据alter方式, 确定执行方式:(copy, online-rebuild, online-norebuild)
  • 更新数据字段内存对象;
  • 分配row_log对象记录增量;
  • 生成新的临时ibd文件;

2.ddl执行阶段:

  • 降级EXCLUSIVE-MDL锁, 允许读写;
  • 扫描old_table的聚集索引每一条记录rec;
  • 遍历新表的聚簇索引和二级索引, 逐一处理;
  • 根据rec构造对于的索引项;
  • 将构造索引项插入sort_buffer块;
  • 将sort_buffer块插入新的索引;
  • 处理ddl执行过程中产生的增量(仅rebuild类型需要)

3.commit阶段:

  • 升级到EXCLUSIVE-MDL锁, 禁止读写;
  • 重做最后row_log中最后一部分增量;
  • 更新innodb的数据字典表;
  • 提交事务(刷事务的redo日志)
  • 修改统计信息
  • rename临时idb文件, frm文件;
  • 变更完成;

常用的ddl操作表

操作 并发DML 算法 备注
添加/删除索引 YES online(no-rebuild) 全文索引不支持(聚簇)
修改default值 修改列名 修改自增列值 添加/删除外键约束 YES Noting 仅需要修改元数据
添加/删除列 交换列顺序 修改NULL/NOT NULL 修改ROW-FORMAT 添加/修改PK Optimize table YES online(rebuild) 由于记录格式改变, 需要重建表
修改列类型 删除PK 转换字符集 添加全文索引 NO Copy 需要锁表, 不支持online

online ddl总结

1.实际上的优化就是对dml锁的细化, 只在某些关键部位进行全锁, 而不是整个阶段;
2.然后运行并发读写后, 后面就需要留有一部分时间做增量的合并操作;
3.若ddl异常, 可能会导致无法再次添加ddl的动作, 那是因为整个过程并不是一个原子操作, 而是复合式的, 所有ddl异常时, 需要清除残留的数据;

最终加表字段的注意事项

1.尽量选择流量小的业务时间段;
2.如果可以的话, 进行主从切换来加字段最好;
3.执行时,先看一下有无未提交的事务, 注意查看事务information_schema.innodb_trx表;
4.加了之后, 随时关注下服务器日志情况;
出现问题的, 可能会出现这样的sql会话;

1
waiting for table metadata lock

hyhcoder wechat
扫码关注我的个人订阅号