在实际项目中，虽然DBA一再强调在最开始就要做好表的设计，避免后期进行表的变更，因为表的变更代价很大，尤其当表的数据量很大时，会带来很多麻烦，但是表结构变更还是不可避免。MySQL从最初的离线变更到现在的在线变更，也进行了一系列的优化，使得表的变更代价比以前小多了。因此，学会Online DDL这招对DBA同学就显得尤为重要了。

Online DDL操作可以从两个维度来看，加锁范围和是否拷贝数据。其中，从加锁范围，可以分为以下四类:

值	含义
NONE	允许并发查询和DML
EXCLUSIVE	阻塞查询和DML
SHARED	允许并发查询，阻塞DML
DEFAULT	由数据库决定选择最大并发的模式，指定该类型与不指定LOCK关键字含义相同

默认情况下，MySQL在执行DDL操作期间应尽可能少的使用锁，以提高并发，也可以通过LOCK子句，来指定更加严格的锁，但是如果LOCK子句指定的锁级别低于特定DDL操作所允许的限制级别，语句将执行失败。

从是否拷贝数据方面划分，可分为以下四类:

值	含义
COPY	采用拷表方式进行表变更，过程中不允许并发DML
INPALCE	该模式避免进行表的拷贝，而是在引擎层重新生成表，仅需要进行引擎层数据改动，不涉及Server层，在操作的准备和执行阶段，表上的排他元数据锁可能被短暂的占用，支持并发DML。
INSTANT	该操作仅仅修改元数据，在准备和执行期间，表上没有独占的元数据锁，并且表数据不受影响，因此操作是实时的，允许并发DML，目前仅支持在表最后增加新列。
DEFAULT	系统决定，选择最优的算法执行DDL。

可参考下图:

然后，Online DDL具体涉及到哪些操作呢，每种操作具体对应的是哪种类型呢?参考网上和书上的资料，如下:

下面主要针对Copy方式和Inplace两种方式的详细流程进行学习，至于Instant后期再做安排。

copy方式较简单，流程可归结为如下五步:

1).锁表，锁表期间DML操作不可并行执行。

2).生成临时表和临时表文件，即.frm文件和.ibd文件。

3).拷贝原表数据到临时表。

4).重命名临时表和表文件。

5).删除原表及表文件，提交事务。释放锁。

Inplace方式较复杂点，总体流程可分为三大步:

1).初始化阶段:

主要检测选择获得最优的LOCK和ALGORITHM设置，如果指定的LOCK和ALGORITHM选项不满足则报错，在该阶段主要持有的是可升级的元数据锁以保护当前的表定义。

在原表的路径下创建.frm和ibd临时文件，no-rebuild操作除了创建二级索引外只创建.frm文件，当添加二级索引时，不会生成.ibd文件，但是却会修改.ibd文件，会在tmpdir目录下生成存储索引排序结果的临时文件，然后合并到.ibd文件中。

对于rebuild操作，会申请row log空间，用于存放DDL执行阶段产生的DML操作。

2).执行阶段:

该阶段主要包含语句的prepare和executed，该阶段元数据锁是否升级为exclusive取决于在初始化阶段评估，如果需要排他元数据锁，则只在语句准备期间短暂持有，在执行阶段并不持有exclusive元数据锁，如果需要拷表或修改引擎层的数据，该阶段是最耗时的阶段。扫描原表主键及二级索引的所有数据页，生成B+树，存储在临时文件中，将所有对原表的DML操作记录在日志文件row log中。

3).提交表定义阶段:

该阶段是需要持有排他锁，进行新旧表切换，即使是在INSTANT的情况下，也依然会需要在提交阶段获取元数据的排他锁，即使最大限度提高并发，也仅仅是通过缩短持有排他锁的时间来实现。对于rebuild操作，该阶段会重做row log文件中的内容，重命名原表文件，将临时文件改名为原表文件名，删除原表文件。然后提交事务，变更完成。

接下来讲解下可能导致Online DDL失败的原因和Online DDL一些需要注意的点吧。

关于Online DDL失败的情景，结合平时遇到和书本了解的，有以下几种，后期如有发现，再做更新。

1).指定的algorithm和存储引擎的算法冲突或指定的Lock类型有误。

2).innodb_sort_buffer_size参数设置过小，该值最大值由innodb_online_alter_log_max_size参数决定，如果太小。可能导致提示DB_ONLINE_LOG_TOO_BIG错误。

3).如果DDL操作涉及二级索引的创建，则需要关注索引排序空间，保证空间充足，如果涉及rebuild操作，则需要关注中间临时表空间足够。

4).当前系统中有事务占据了元数据锁，导致锁等待超时。

5).DDL添加索引时并发DML操作中发生了主键冲突，会导致alter table的操作回滚。

然后就是Online DDL需要关注的点了，差不多有以下几种:

1).使用lock=none模式时，不允许用外键约束，否则会报错。

2).Online DDL如果操作失败，执行回滚时可能会影响服务器性能。

3).长时间的Online DDL可能导致复制严重滞后，对于主从架构，在DDL期间产生的数据会和正常操作一样写入到原表，并同步到从库中执行，只是会额外记录到row log中，且写入row log的操作本身也会记录redo log,在提交阶段才会进行row log重做，此时会进行锁表，且主库和从库的数据是一致的，在主库DDL操作执行完成并提交，才会写入binlog传到从库执行，在从库执行该DDL时。对于从库本地来说依旧还是Online,在本地直接写数据不会阻塞，且会和主库一样产生row log,但是对于主库同步过来的DML操作，会被阻塞。是非Online的，因此大表的DDL操作，会造成同步延迟，建议大表Online DDL使用pt—osc或者gh-ost等工具。

以上就是对Online DDL初步学习的一些知识点了。

ITPub博客

MySQL的Online DDL相关篇(上)

最新文章