MySQL-OnlineDDL在线DDL

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失败情况

1.算法子句与特定类型的ddl或者存储引擎不兼容

2.lock子句指定了与某些特性类型的ddl操作不兼容的低锁定度(比如SHARED或NONE)

3.tmpdir或innodb_tmpdir文件系统磁盘空间不足，而MySQL在创建索引期间将临时排序文件写入磁盘

4.该操作花费很长时间，并且并行DML修改表的时间太大，以至于临时联机日志的大小超过了innodb_online_alter_log_max_size配置选项的值。这种情况导致DB_ONLINE_LOG_TOO_BIG错误

5.并发DML对表进行了更改，原始表定义允许更改，但新表定义不允许。当MySQL尝试应用并发DML语句中的所有更改时，操作仅在最后失败。例如，您可以在创建唯一索引时将重复的值插入到列中，或者在该列上创建主键索引时将NULL值插入到列中。并发DML所做的更改优先，并且ALTER TABLE操作有效地回滚。

Q&A

如何检查大表的DDL是快速的还是慢速的?

1. 克隆表结构。

2. 用少量数据填充克隆表。

3. 在克隆表上运行DDL操作。

4. 检查“ 受影响的行 ”值是否为零。非零值表示该操作将复制表数据，这可能需要进行特殊规划。例如，您可以在计划的停机时间内或在每个复制从属服务器上一次执行DDL操作。

在线DDL空间要求

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

1、临时日志文件空间

onlinDDL创建索引或者更改表(alter table)时,临时日志文件记录并发DML,online DDL操作创建索引或更改表时，临时日志文件记录并发DML。 临时日志文件根据需要由innodb_sort_buffer_size的值扩展到由innodb_online_alter_log_max_size指定的最大值.如果临时文件超出大小限制,onlin DDL将会失败,并且未提交的并发DML会回滚,nnodb_online_alter_log_max_size设置允许在联机DDL操作期间使用更多DML，但是当表被锁定以应用记录的DML时，它也会延长DDL操作结束时的时间。

如果该操作花费很长时间，并且并行DML修改了表以至于临时日志文件的大小超过了innodb_online_alter_log_max_size的值，则联机DDL操作将失败，并出现DB_ONLINE_LOG_TOO_BIG错误。

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

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

相关参数


old_alter_table

 启用此变量后，服务器将不使用优化的方法来处理ALTER TABLE操作。 它恢复为使用临时表，复制数据，然后将临时表重命名为原始表，如MySQL 5.0及更早版本所使用。

 bu ran

使用Performance Schema监控alter table进度

mysql> UPDATE performance_schema.setup_instruments
       SET ENABLED = 'YES'
       WHERE NAME LIKE 'stage/innodb/alter%';
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7  Changed: 7  Warnings: 0

　　

mysql> UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%stages%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

　　

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_current;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |            280 |           1245 |
+------------------------------------------------------+----------------+----------------+
1 row in set (0.01 sec)

　　

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
       FROM performance_schema.events_stages_history;
+------------------------------------------------------+----------------+----------------+
| EVENT_NAME                                           | WORK_COMPLETED | WORK_ESTIMATED |
+------------------------------------------------------+----------------+----------------+
| stage/innodb/alter table (read PK and internal sort) |            886 |           1213 |
| stage/innodb/alter table (flush)                     |           1213 |           1213 |
| stage/innodb/alter table (log apply table)           |           1597 |           1597 |
| stage/innodb/alter table (end)                       |           1597 |           1597 |
| stage/innodb/alter table (log apply table)           |           1981 |           1981 |
+------------------------------------------------------+----------------+----------------+
5 rows in set (0.00 sec)

　　

Online DDL限制 

1.在临时表上创建索引时,将复制该表

2.如果表上存在on...cascade或者on...set null约束,则不允许alter table lock=none;

3.在online ddl操作完成之前,他必须等待持有表上元数据锁的事务提交或者回滚,online DDL操作可能在执行阶段需要一个短暂的元数据锁,并且在更最后一个阶段更新表定义的时候需要一个元数据锁,,因此在表上持有元数据锁的事务可能导致online ddl操作阻塞. 如果长时间占有元数据锁,可能导致online DDL超时

4.在外键关系中的表上执行online DDL操作不会等待在外键关系中的另一个表上执行的事务提交或回滚.事务在正在更新的表上拥有排他元数据锁，并在与外键相关的表上拥有共享元数据锁（用于外键检查）.当需要排他的元数据锁来更新表定义时，共享的元数据锁允许进行联机DDL操作，但会在其最后阶段阻止该操作。当其他事务等待联机DDL操作完成时，此方案可能导致死锁。

5.online DDL运行期间,运行alter table的线程将会应用DML操作的online log,该DML操作是在其他线程的同一个表上同时运行的,即使是临时的输入重复值,也可能遇到1062重复输入的错误,

6.将InnoDB表的OPTIMIZE TABLE映射到ALTER TABLE操作，以重建表并更新索引统计信息并释放聚簇索引中未使用的空间。创建辅助索引的效率不高，因为键是按照它们在主键中出现的顺序插入的。 OPTIMIZE TABLE支持联机DDL支持，用于重建常规和分区的InnoDB表。

7.在MySQL 5.6之前创建的表，其中包含时间列（DATE，DATETIME或TIMESTAMP）并且未使用ALGORITHM = COPY进行重建，因此不支持ALGORITHM = INPLACE。在这种情况下，ALTER TABLE ... ALGORITHM = INPLACE操作将返回以下错误

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

　　

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

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

长时间运行的onlin DDL操作可能导致复制滞后。在从服务器上运行之前，onlinDDL操作必须在主服务器上完成运行。此外，在主服务器上同时处理的DML仅在从服务器上的DDL操作完成后才在从服务器上处理

参考:

https://blog.csdn.net/finalkof1983/article/details/88355314