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
 
 
posted @ 2021-11-16 23:04  香吧香  阅读(17)  评论(0编辑  收藏  举报