MySQL online ddl原理

online DDL是在mysql5.6版本后加入的特性，用于支持DDL执行期间DML语句的并行操作，提高数据库的吞吐量。

online DDL结构简图如下：

 

 

由上图可知online DDL大体可以分为3部分：

1、copy（ALGORITHM=COPY）这部分是offline的，在DDL执行期间其他DML不能并行，也是5.6版本前的DDL执行方法。其间生成临时表（server层的操作支持所有引擎），用于写入原表修改过的数据，同时在原表路径下会生成临时表的.frm和.ibd文件。在innodb中不支持使用inplace的操作都会自动使用copy方式执行，而MyISAM表只能使用copy方式。

2、inplace（ALGORITHM=INPLACE）所有操作在innodb引擎层完成，不需要经过临时表的中转。除上图两种特殊索引创建外，其他以inplace方式执行的操作都是online的，执行期间其他DML操作可以并行，其中又以是否重建表又分为两个部分rebuild和no-rebuild。

      rebuild部分涉及表的重建，在原表路径下创建新的.frm和.ibd文件，消耗的IO会较多。期间（原表可以修改）会申请row log空间记录DDL执行期间的DML操作，这部分操作会在DDL提交阶段应用新的表空间中。

      no-rebuild部分由于不涉及表的重建，除创建添加索引，会产生部分二级索引的写入操作外，其余操作均只修改元数据项，即只在原表路径下产生.frm文件，不会申请row log，不会消耗过多的IO，速度通常很快。 

3、inplace but offline的几种特殊DDL操作，本身是按inplace方式执行，但是执行期间DML语句却不能并行。

注：如何区分DDL语句是使用了copy方式还是inplace方式，只需要查看语句执行完成输出结果中的 X rows affected，如果X为0则是inplace（online）方式，如果不为0则是copy（offline）方式。

online DDL可选参数示意图：

 

online DDL的两个子选项包括ALGORITHM和LOCK：

对于ALGORITHM参数使用default默认值即可，不需要强制指定该值，系统会自行判断，优先使用inplace，对于不支持的表或DDL操作使用copy。

LOCK参数绝大多数情况下也不需要显式指定值，默认值default已经是尽可能允许DML的并行操作了。

例句如下，参数间使用逗号隔开：

alter table innodb_test add test int,ALGORITHM=INPLACE,LOCK=DEFAULT;

inplace(rebuild)的整体执行过程如下：

准备阶段：

1、对表加元数据共享升级锁，并升级为排他锁。（此时DML不能并行）

2、在原表所在的路径下创建.frm和.ibd临时中转文件（no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中）

3、申请row log空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）

执行阶段：

1、释放排他锁，保留元数据共享升级锁（此时DML可以并行）。

2、扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；

3、将所有对原表的DML操作记录在日志文件row log中

注：如果只修改元数据部分（no-rebuild）该阶段只是修改.frm文件，不需要其他操作，也不需要申请row log

提交阶段：

1、升级元数据共享升级锁，产生排他锁锁表（此时DML不能并行）。

2、重做row log中的内容。（no-rebuild不需要）

3、重命名原表文件，将临时文件改名为原表文件名，删除原表文件

4、提交事务，变更完成。

说明：在DDL期间产生的数据，会按照正常操作一样，写入原表，记redolog、undolog、binlog，并同步到从库去执行，只是额外会记录在row log中，并且写入row log的操作本身也会记录redolog，而在提交阶段才进行row log重做，此阶段会锁表，此时主库（新表空间+row log）和从库（表空间）数据是一致的，在主库DDL操作执行完成并提交，这个DDL才会写入binlog传到从库执行，在从库执行该DDL时，这个DDL对于从库本地来讲仍然是online的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产生row log。但是对于主库同步过来DML，此时会被阻塞，是offline的，DDL是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。所以大表的DDL操作，会造成同步延迟。

 copy的整体执行过程如下：

1、锁表，期间DML不可并行执行

2、生成临时表以及临时表文件（.frm .ibd）

3、拷贝原表数据到临时表

4、重命名临时表及文件

5、删除原表及文件

6、提交事务，释放锁

online DDL的空间要求：

由于online DDL执行期间需要创建临时表空间文件用于存储数据，以及申请row log记录DML操作，所以在执行DDL前应该先确认空间上是否满足要求，否则由于空间不够很可能导致操作失败，而进行回滚。

1、row log空间：row log空间每次申请的大小由 innodb_sort_buffer_size决定，最大值由innodb_online_alter_log_max_size，该值默认为128M，支持动态修改。对于更新频繁的表来讲，如果预计在DDL期间对表的更新操作存储可能超过128M时，需要为本次操作增大该值。当然如果不涉及rebuild操作时，不需要考虑该值。如果提示DB_ONLINE_LOG_TOO_BIG错误，则是由innodb_online_alter_log_max_size空间不足造成的。

2、索引排序空间：如果DDL操作涉及二级索引的创建，会在MySQL临时目录产生临时排序文件，将中间的排序结果写入文件，最终将内容合并到最终表或索引中，然后自动删除临时排序文件。这个路径默认为mysql全局参数tmpdir指定（默认值为/tmp，如果手动指定了innodb_tmpdir参数的路径，则tmpdir会被覆盖），且不会在原始表的目录中创建临时排序文件。tmpdir需要保证能够容纳要创建的二级索引，临时排序文件最大可能需要的空间等于表中的数据量加上索引，否则执行将报错。（官方文档的说明，实际测试200万的表加索引，并未生成临时排序文件，这有点奇怪）

3、中间表空间：如果DDL操作涉及rebuild表，则会在原表所在目录创建临时表空间文件（以#sql开头），临时表空间大小需要等于原表大小，重建完成后会自动重命名临时表空间，删除原表空间。所以执行rebuild操作时需要保证原表所在路径下有足够空间

执行DDL语句需要额外注意的是：

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

长时间运行的联机DDL操作可能导致复制滞后。在从服务器上运行之前，联机DDL操作必须在主服务器上完成运行。此外，在主服务器上同时处理的DML仅在从服务器上的DDL操作完成后才在从服务器上处理。
原文链接：https://blog.csdn.net/finalkof1983/article/details/88355314