MySQL之Online DDL再探

//

MySQL之Online DDL再探

//

昨天内容中说了MySQL5.6引入了online DDL，但是没有详细的展开说，这块儿内容比较多，只能一点一点的写了，今天再来看这里的内容。

01

Online DDL的优点

online ddl操作支持表的本地更改(无需拷贝)和并发DML操作，一般有以下几个优点：

1、一般的线上环境都是比较忙碌的，想要在一个大表中比较平滑的执行DDL变更几乎不太可能，但是线上的环境又不会接受几分钟的延迟，使用online ddl操作可以尽可能的降低这种影响。

2、online ddl中支持lock语法，lock语法可以微调对表的并发访问程度：

使用lock=none的方法可以开启表的读取和写入，

使用lock=shared方法可以允许对表进行读取，而关闭表的写入功能，

使用lock=exclusive可以禁止对表进行读写，组织并发查询和DML

换句话来说，lock语法可以平衡数据库服务并发和性能之间的竞争，但是需要注意的是：该方法有可能出现失败的情况，如果该方法不可用，该alter table 的操作会立即停止。

(失败的情况，官方文档中并没有给出样例)

3、online ddl中支持algorithm的语法，该参数有两个取值，一个是copy，另外一个是inplace，来看官方文档说明：

• COPY：对原始表的副本执行操作，并将表数据从原始表逐行复制到新表。不允许并发DML。
• INPLACE：操作避免复制表数据，但可以在适当位置重建表。在操作的准备和执行阶段可以简短地获取表上的独占元数据锁定。通常，支持并发DML。

默认情况下，MySQL5.7使用inplace的方法，而不是copy表结构的方法。因此，与传统的表复制方法相比，online ddl可以降低磁盘上的消耗和IO上的开销。

简单总结，online ddl的3个优点：

a、降低线上变更表的影响时间

b、平衡数据库服务并发性和性能之间的竞争

c、降低磁盘和IO消耗

官方文档中给的常用的在线变更表结构的例子如下：

ALTERTABLEtbl_name

ADDPRIMARYKEY(column),

ALGORITHM=INPLACE,LOCK=NONE;

02

Online DDL的种类及状态

DDL的种类有很多，比较常见的包含：

索引操作

主键操作

列操作

外键操作

表操作

表空间操作

分区操作

每个操作里面又包含了很多种类，比如，索引操作中包含新增索引、删除索引等操作，列操作中有新增列、修改列、删除列等等，这些ddl操作执行过程中的状态究竟是什么样的？我们一一来看。

首先是索引DDL操作

可以用下面的表来表示：

从上面的表中可以看出，创建或者添加二级索引的时候，使用了inplace的操作，不需要重建表，并且允许并发的DML，也就是说，在创建索引的过程中，原表是可读可写的。它数据新增元数据的操作，没有修改数据库的元数据。

下面的例子分别是其他DDL操作对应的表格，大家可以对号入座，查看每一类操作的状态。

主键DDL操作

列DDL操作

外键操作

表操作

表空间操作和分区操作用到的比较少，就不进行介绍了。

03

Online DDL对系统空间的要求

说到系统空间，我们可能第一反应是只有采用了copy办法的DDL操作才需要复制数据，这个时候，额外的系统空间可以理解，它是去保存中间临时表的，如果采用inplace方法，没有新建中间临时表，需要哪些额外的系统空间呢？

我们以创建二级索引为例，我们知道，在创建二级索引的时候，MySQL是支持我们进行DML操作的，如果我们正在对表A进行创建索引的操作，此时有一个update或者insert的SQL也来操作该表A，那么MySQL的处理办法是将该update或者insert的SQL记录在临时日志文件里面，等待创建索引的动作完毕之后，再进行该SQL操作，在一个高并发的系统中，这种DML的SQL可能非常多，如果一个DDL的操作等待的时间比较长，那它执行完毕之后，可能已经积攒了很多DML的日志，这是我们不想看到的。所以MySQL中指定了一个参数innodb_online_alter_log_max_size

来设置临时日志文件的大小，如下所示：

+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
| old_alter_table                  | OFF       |
+----------------------------------+-----------+

如果在DDL执行过程中，DML产生的临时日志文件的值超过这个值，那么DDL将会报错并回滚。另外，这个值不能设置的太大，如果设置的太大，那么DDL执行完毕后，需要应用的DML日志很多，导致DDL的时间也会变长，而且极易出错。

除此之外，还有临时排序文件所需要的系统空间，如果DDL中设计到排序问题，例如添加索引、修改索引等等，则可能需要分配额外的排序空间。

总结一下：

Online DDL对系统空间的要求：

a、如果DDL需要拷贝表数据，则需要额外的空间来保存中间临时表

b、如果DDL执行过程中支持并发DML，则DML操作产生的临时日志文件需要占用额外的系统空间

c、如果DDL执行过程中需要对数据进行排序，则需要额外的系统空间来存储额外的临时排序文件

有帮助的话还希望点下再看哈