代码改变世界

MySQL Online DDL介绍

2017-07-03 10:14  Kevin.hhl  阅读(287)  评论(0)    收藏  举报

DDL实现方式

      MySQL 5.5版本 添加、删除二级索引称：fast index creation，不需要copy整表的数据，但整个过程加共享锁，此表是只读的。MySQL 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. 变更完成 

 常见的ddl操作

类型

并发DML

算法

备注

添加/删除索引

 

Yes

Online(no-rebuild)

全文索引不支持

修改default值

修改列名

修改自增列值

添加/删除外键约束

Yes

Nothing

仅需要修改元数据

添加/删除列

交换列顺序

修改NULL/NOT NULL

修改ROW-FORMAT

添加/修改PK

Optimize table

Yes

 

Online(rebuild)

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

修改列类型

删除PK

转换字符集

添加全文索引

No

Copy

需要锁表，不支持online

重要：上表中的操作都会导致记录格式发生变化，需要rebuild online ddl，这个过程相当于要重建表，copy数据到新的*.ibd 文件，表数据量大会导致online ddl比较慢。

样例Online DDL:   alter table order add key `idx_uid`(user_id),lock=none,algorithm=inpllce;

 

相关MySQL参数：online_alter_log_max_size，表数据量大，适当调大此参数。

 

＊ 5.5及更早的版本如下： 

 

诟病的地方：删除索引不会释放表占用磁盘空间。