Mysql Online DDL

　　注意：尤其是在大数据量表的DDL操作时，需要特别注意 

1.前言：

　　1.我们在数据库运维的时候往往要对一张表进行做DDL操作时候，时常会导致库上大量的线程中出现”waitting for metedata lock“状态，导致大量的并发问题，其中包括对数据库不能做DML操作(在DDL操作时)。

　　2.因此，mysql5.6中的onlie ddl特性解决了ddl锁表的问题，保证了在进行表变更的时候，不会堵塞业务上的读写。

2.Online DDL 划分：

　　1.锁与并发度划分：先说一下与DML语句的并发度方面来说明一下DDL语句的分类，其主要分为下面几类，可以在ddl语句中通过LOCK关键字来指定DDL期间加锁程度。其可选择的值如下：

值	含义
None	允许并发查询和DML
Shared	允许并发查询，阻止DML操作,适用于数据仓库等可以允许数据写入延迟的场景
Default	由数据库决定选择最大并发的模式，指定该类型与不指定LOCK关键字含义相同
Exclusive	阻塞查询和DML

 

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

   　2.是否拷贝数据划分：通过ALGORITHM关键字进行指定，值有如下几种:

copy	采用拷表方式进行表变更，该过程中不允许并发DML
inplace	该模式避免进行表的拷贝，而是在让引擎层就地重新生成表，也就是仅需要进行引擎层数据改动，不涉及Server层。在操作的准备和执行阶段，表上的排他元数据锁可能会被短暂地占用。通常，支持并发DML,且不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题.
instant	该操作仅仅修改元数据。在准备和执行期间，表上没有独占的元数据锁，并且表数据不受影响，因此操作是即时的。允许并发DML。目前仅支持在表最后增加新列；
default	系统决定，选择最优的算法执行DDL

 

　　如果没有指定ALGORITHM子句，系统决定，选择最优的算法执行DDL。 用户可以选用上述算法来执行，但本身收到DDL类型限制，如果指定的算法无法执行DDL，则ALTER操作会报错

 

3.Online DDL执行流程：

根据官网上的文档说法，Online DDL的执行流程主要分为三个阶段：

　　1.initialization阶段：

　　在初始化阶段，服务器会根据存储引擎能力、语句中指定的操作以及用户指定的 ALGORITHM 和 LOCK 选项来确定操作期间允许的并发量.在此阶段，使用共享的可升级元数据锁来保护当前表定义。(也就是说再该阶段会获取一个shared lock，该锁是具有升级功能)　　

　　2.Execution阶段：

　　在这个阶段，语句被准备和执行。 shared元数据锁是否升级为独占锁(exclusive metadata lock)取决于初始化阶段评估的因素。 如果需要独占元数据锁，则仅在语句准备期间短暂使用。如果不升级为独占元数据锁，那么shared metadata lock 就会堵塞其他的alter table 的操作，但是不会堵塞DML操作。

　　3.Commit Table Definition(提交表定义阶段)

　　在提交表定义阶段，元数据锁升级为独占以删除旧表定义并提交新表定义。 一旦被授予，独占元数据锁定的持续时间很短。

 

4.Online DDL各个阶段具体操作：

　　1.初始化阶段是用来根据ALGORITHM 和 LOCK 选项进行评估，其目的是为后续的执行阶段中的(prepare阶段和execution阶段)作准备的。

　　2.执行阶段：执行阶段其实主要包括两个小阶段(prepare阶段和execution阶段)　

• Prepare阶段:

  1. 创建新的临时frm文件(与InnoDB无关)
  2. 持有EXCLUSIVE-MDL锁，禁止读写
  3. 根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
     假如是Add Index，则选择online-norebuild即INPLACE方式
  4. 更新数据字典的内存对象
  5. 分配row_log对象记录增量(仅rebuild类型需要)
  6. 生成新的临时ibd文件(仅rebuild类型需要)

• ddl执行阶段:

  1. 降级EXCLUSIVE-MDL锁，允许读写
  2. 扫描old_table的聚集索引每一条记录rec
  3. 遍历新表的聚集索引和二级索引，逐一处理
  4. 根据rec构造对应的索引项
  5. 将构造索引项插入sort_buffer块排序
  6. 将sort_buffer块更新到新的索引上
  7. 记录ddl执行过程中产生的增量(仅rebuild类型需要)
  8. 重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)
  9. 重放row_log间产生dml操作append到row_log最后一个Block

• commit阶段:
  当前Block为row_log最后一个时，禁止读写，升级到EXCLUSIVE-MDL锁
  1. 重做row_log中最后一部分增量
  2. 更新innodb的数据字典表
  3. 提交事务(刷事务的redo日志)
  4. 修改统计信息
  5. rename临时idb文件，frm文件
  6. 变更完成

5.Online DDL的种类和状态

　　常见的online ddl 的种类有：　

　　　　索引操作

　　　　主键操作

　　　　列操作

　　　　外键操作

　　　　表操作

　　　　表空间操作

　　　　分区操作

　　 每个操作里面又包含了很多种类，比如，索引操作中包含新增索引、删除索引等操作，列操作中有新增列、修改列、删除列等等，

 

参考：https://www.cnblogs.com/zmc60/p/14872073.html