正文

MySQL online ddl原理

IP云
IP云V管理员/0 阅读
12/01
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

背景

dba的日常工作肯定有一项是ddl变更，ddl变更会锁表，这个可以说是dba心中永远的痛，特别是执行ddl变更，导致库上大量线程处于“Waiting for meta data lock”状态的时候。因此mysql 5.6的online ddl特性是dba们最期待的新特性，这个特性解决了执行ddl锁表的问题，保证了在进行表变更时，不会堵塞线上业务读写，保障在变更时，库依然能正常对外提供访问。网上关于online ddl的文章很多，但涉及原理的很少，都是介绍语法之类的，本文将详细介绍online ddl的原理，知其然，更要知其所以然。

ddl实现方式

5.6 online ddl推出以前，执行ddl主要有两种方式copy方式和inplace方式，inplace方式又称为(fast index creation)。相对于copy方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。下面以加索引为例，简单介绍这两种方式的实现流程。

copy方式

(1).新建带索引的临时表

(2).锁原表，禁止DML，允许查询

(3).将原表数据拷贝到临时表(无排序，一行一行拷贝)

(4).进行rename，升级字典锁，禁止读写

(5).完成创建索引操作

inplace方式

(1).新建索引的数据字典

(2).锁表，禁止DML，允许查询

(3).读取聚集索引，构造新的索引项，排序并插入新索引

(4).等待打开当前表的所有只读事务提交

(5).创建索引结束

online ddl实现

online方式实质也包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式，比如修改列类型，删除主键，修改字符集等，这些操作都会导致记录格式发生变化，无法通过简单的全量+增量的方式实现online；对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(重新组织记录)，比如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等；另外一类是只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。Mysql将这两类方式分别称为rebuild方式和no-rebuild方式。更多关于哪些DDL是否可以inplace的内容可以参考官方文档：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html。online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。

Prepare阶段：

  1. 创建新的临时frm文件
  2. 持有EXCLUSIVE-MDL锁，禁止读写
  3. 根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
  4. 更新数据字典的内存对象
  5. 分配row_log对象记录增量
  6. 生成新的临时ibd文件

ddl执行阶段：

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

commit阶段

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

关键函数堆栈

拷贝数据

row_merge_build_indexes
row_merge_read_clustered_index //拷贝全量

{

遍历老表的聚集索引

row_build //创建一个row

row_merge_buf_add
//将row加入到sort_buffer
row_merge_insert_index_tuples //插入到新表(聚集索引+二级索引)
}
row_log_table_apply
//对于rebuild类型，处理增量

{

row_log_table_apply_insert   //以insert为例

row_log_table_apply_convert_mrec //将buf项转为tuple

{

插入聚集索引 //
row_ins_clust_index_entry_low

插入二级索引
// row_ins_sec_index_entry_low

}

}

对于添加索引的操作，由于不需要修改聚集索引，因此处理增量调用的是另外一套接口

row_log_apply->row_log_apply_ops->row_log_apply_op->row_log_apply_op_low->

修改表数据字典

commit_try_norebuild，commit_try_rebuild

常见的ddl操作

类型

并发DML

算法

备注

添加/删除索引

Yes

Online(no-rebuild)

全文索引不支持

修改default值

修改列名

修改自增列值

添加/删除外键约束

Yes

Nothing

仅需要修改元数据

添加/删除列

交换列顺序

修改NULL/NOT NULL

修改ROW-FORMAT

添加/修改PK

Optimize table

Yes

Online(rebuild)

由于记录格式改变，需要重建表

修改列类型

删除PK

转换字符集

添加全文索引

No

Copy

需要锁表，不支持online

若干问题

1.如何实现数据完整性

使用online ddl后，用户心中一定有一个疑问，一边做ddl，一边做dml，表中的数据不会乱吗？这里面关键部件是row_log。row_log记录了ddl变更过程中新产生的dml操作，并在ddl执行的最后将其应用到新的表中，保证数据完整性。

2.online与数据一致性如何兼得

实际上，online ddl并非整个过程都是online，在prepare阶段和commit阶段都会持有MDL-Exclusive锁，禁止读写；而在整个ddl执行阶段，允许读写。由于prepare和commit阶段相对于ddl执行阶段时间特别短，因此基本可以认为是全程online的。Prepare阶段和commit阶段的禁止读写，主要是为了保证数据一致性。Prepare阶段需要生成row_log对象和修改内存的字典；Commit阶段，禁止读写后，重做最后一部分增量，然后提交，保证数据一致。

3.如何实现server层和innodb层一致性

在prepare阶段，server层会生成一个临时的frm文件，里面包含了新表的格式；innodb层生成了临时的ibd文件(rebuild方式)；在ddl执行阶段，将数据从原表拷贝到临时ibd文件，并且将row_log增量应用到临时ibd文件；在commit阶段，innodb层修改表的数据字典，然后提交；最后innodb层和mysql层面分别重命名frm和idb文件。

4.对innodb表做ddl过程中异常了，为啥再次做ddl报#sql-xxx already exists

这个错误是什么鬼？这个表#sql-xxx实质是做ddl产生的临时表，ddl异常退出后(比如进程被kill，或者机器异常掉电等)，临时文件没有清理。再次执行时，会创建同名的#sql-xxx临时文件，从而导致报错。这里的xxx与table-id强相关，如果是这样，我们把这个讨厌的#sql-xxx临时文件删掉如何呢？再次重做ddl发现还是报同样的错误。这主要原因是，这个临时表信息在innodb的数据字典有残留，通过查询数据字典视图information_schema.innodb_sys_tables，可以发现存在一条#sql-xxx的表记录。
深层次原因：ddl整个过程不是原子的，prepare过程中会新建frm文件，ibd文件，并更新数据字典；然后再进行拷贝全量+重放增量操作；最后再rename frm文件，idb文件，并修改数据字典。由于整个过程涉及到server层和innodb层，并不是一个大事务(每次改数据字典都是单独一个事务)，所以执行过程中如果异常终止，就会导致临时表数据字典残留在系统表内。

影响：虽然临时表信息残留在数据字典内，但不影响用户后续操作。

解决方法：由于临时表与table-id强相关，如何改变table-id是我们需要做的，但表又不能被修改，table-id改变不了。这就成了一个悖论，要做ddl，需要改变table-id；要改变table-id，又需要通过ddl操作。查看源码后发现，对于online ddl，临时表名依赖于变更表的table-id(比如#sql-ib79，79就是变更表的table-id)，而对于copy类型(非online)的ddl，临时表名则不依赖于table-id(由mysqld进程号+连接会话号产生，比如sql-604d_2，604d是mysqld进程号，2是会话号)。因此，我们通过copy类型的ddl，就可以产生表名不一样的临时表了，也就可以完成ddl任务了。比如：alter table test_log add column c88 int, ALGORITHM=copy;

其它：ddl异常结束，会导致重做ddl失败。如果做ddl过程中，kill query，这个时候ddl也会退出，但退出前会做好善后工作，清理数据字典，因此再次做ddl不会存在问题。

参考文档

http://hedengcheng.com/?p=405

http://hedengcheng.com/?p=421

http://hedengcheng.com/?p=148

相关阅读

目录[+]