21.Mysql之Online 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(提交表定义阶段)

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

 

这里官网上的实例如下：

session1	session2	session3
CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
START TRANSACTION;
SELECT * FROM t1;
	ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;
		SELECT * FROM t1;

The session 1 select  statement takes a shared metadata lock on table t1

The online DDL operation in session 2, which requires an exclusive metadata lock on table t1 to commit table definition changes, must wait for the session 1 transaction to commit or roll back.

 The select  statement issued in session 3 is blocked waiting for the exclusive metadata lock requested by the alter table operation in session 2 to be granted.

总结：

当做DML(增删改查)操作时，会申请一个MDL读锁 

当做DDL(alter)操作时，会申请一个MDL写锁

读锁之间不互斥，读写和写写之间都互斥。

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 的种类有：　

　　　　索引操作

　　　　主键操作

　　　　列操作

　　　　外键操作

　　　　表操作

　　　　表空间操作

　　　　分区操作

　　 每个操作里面又包含了很多种类，比如，索引操作中包含新增索引、删除索引等操作，列操作中有新增列、修改列、删除列等等，下图是online ddl做以上算法的是否支持和在ddl时是否需要重建表。

　　

小结

　1.添加二级索引，不需要重建表。所以会很快。

　2.添加(删除)主键索引，都需要重建表的，因此会慢很多。

   3.添加列(删除列)也需要重新建表

 

 

6.Online DDL的性能

　　DDL操作的性能在很大程度上取决于该操作是否立即就地执行，以及它是否重建了表。

      要评估DDL操作的相对性能，您可以使用ALGORITHM=INSTANT、ALGORITHM=INPLACE和ALGORITHM=COPY比较结果

　　对于修改表数据的DDL操作，您可以通过查看命令完成后显示的“行受影响”值来确定DDL操作是就地执行更改还是执行表副本。例如：

　　1.更改列的默认值（快速，不影响表数据）：　　

Query OK, 0 rows affected (0.07 sec)

　　2.Adding an index (takes time, but 0 rows affected shows that the table is not copied):

Query OK, 0 rows affected (21.42 sec)

　　3.更改列的数据类型（需要大量时间，需要重建表的所有行）：

Query OK, 1671168 rows affected (1 min 35.54 sec)

 

7.Online DDL空间要求

　　当在线 DDL 操作创建索引或更改表时，临时日志文件记录并发 DML。 临时日志文件根据需要通过 innodb_sort_buffer_size 的值扩展到 innodb_online_alter_log_max_size 指定的最大值。 如果临时日志文件超过大小限制，则在线DDL操作失败，并回滚未提交的并发DML操作。 一个大的 innodb_online_alter_log_max_size 设置允许在线 DDL 操作期间更多的 DML，但是当表被锁定以应用记录的 DML 时，它也会延长 DDL 操作结束时的时间段。

MySQL [(none)]> show variables like '%online%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| innodb_online_alter_log_max_size | 134217728 |
+----------------------------------+-----------+

默认是128M

Online DDL对系统空间的要求：

　　a、如果DDL需要拷贝表数据，则需要额外的空间来保存中间临时表

　　b、如果DDL执行过程中支持并发DML，则DML操作产生的临时日志文件需要占用额外的系统空间

　　c、如果DDL执行过程中需要对数据进行排序，则需要额外的系统空间来存储额外的临时排序文件

 

8.附加一下：

　　腾讯内部文档关于online ddl和pt-osc的介绍

MySQL 5.6后自带OnlineDDL功能（MyISAM表无法使用，且不支持全文和空间索引）
Inplace模式流程如下：
准备阶段
1. 对表加元数据锁，并升级为排他锁。（此时DML不能并行）
2. 在原表所在的路径下创建.frm和.ibd临时中转文件（no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中）
3. 申请row log（innodb_online_alter_log）空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）
执行阶段：
1. 释放DML写锁，保留DML读锁（此时DML可以并行）。
2. 扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；
3. 将所有对原表的DML操作记录在日志文件row log中
注：如果只修改元数据部分（no-rebuild）该阶段只是修改.frm文件，不需要其他操作，也不需要申请row log
提交阶段：
1. 升级DML锁，产生DML写锁（此时DML不能并行）。
2. 重做row log中的内容。（no-rebuild不需要）
3. 重命名原表文件，将临时文件改名为原表文件名，删除原表文件
4. 提交事务，变更完成。
由于MySQL自身的OnlineDDL是inplace的，当整个alter操作完成后才会将操作同步给从库，会有较高的主从延迟，不推荐采用。
8.0.12后支持INSTANT模式进行在线DDL，但是只建议追加式新增列、表改名、新增虚拟列这三种支持INSTANT的操作可以直接跑DDL，其余的都建议采用pt-osc工具



PT流程（推荐）：
1.创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
2.在新表执行alter table 语句（速度十分快）
3.在原表中创建触发器3个触发器分别对应insert,update,delete操作
4.以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
5.Rename 原表到old表中，在把临时表Rename为原表
注: 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
默认最后将旧原表删除

 总结：

　　copy:5.6之前默认适应这种方法，全程无法并行DML

　　inplace:mysql5.6出现的，在开始和提交的过程中，不能并行DDL

　　instant:mysql8.0.12出现的，唯一会阻塞只读的时机是在清理旧表结构和表定义缓存时。

 

参考：

　　https://blog.csdn.net/weixin_45238761/article/details/125343029

　　https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html