MySQL8.0原子DDL语法 详解MySQL8.0原子DDL语法

01 原子DDL介绍

    原子DDL语句将数据字典更新、存储引擎操作和与DDL操作相关联的二进制日志写入合并到单个原子操作中。该操作要么提交，对数据字典、存储引擎和二进制日志保留适用的更改，要么回滚。

    在MySQL8.0中，原子DDL操作这一特性，支持表相关操作，例如create table、drop table等，也支持非表相关操作，例如create routine、drop trigger等。

其中：

    支持的表操作包含：

drop、create、alter(操作对象是databases, tablespaces, tables, and indexes)语法、truncate语法

    支持的非表操作包含：

create、drop、alter(操作对象是trigger、event、views、)

帐户管理语句：用户和角色的create、alter、drop和rename语句，以及grant和revoke语句

    需要注意的是：跟表相关的DDL操作，需要保证存储引擎是Innodb的，非表相关的操作，则没有要求。

有些SQL语句不支持原子DDL，例如：

1、非Innodb存储引擎的表操作

2、install plugin和uninstall plugin操作(安装插件)

3、 install component和uninstallcomponent语句

4、create server、alter server和drop server语句(该语句是FEDERATED存储引擎使用的，可暂时忽略)

02 部分DDL操作的执行行为变化

    原子操作的执行行为变化，跟数据字典的组织结构变化有关，在MySQL8.0 之前，Data Dictionary除了存在与.FRM, .TRG, .OPT 文件外，还存在于系统表中（MyISAM 非事务引擎表中），在MySQL8.0 ，Data Dictionary 全部存在于Data Dictionary Storage Engine（即 InnoDB表中），这使crash recovery 维持原子性成为了可能。下面的图描述了数据字典的结构变化。

    在MySQL8.0之前，数据字典结构如下：

MySQL8.0之后，数据字典变为：

下面来看2个具体的语法变化：

(1) Drop语法的变化：

    我们给数据库里面同时创建test1的表，并没有test2的表，然后执行drop table test1，test2；观察结果。

MySQL5.7表现：

mysql> create table test1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| t1    |
| t2    |
| t3    |
| test1   |
+----------------+
4 rows in set (0.00 sec)

mysql> drop table test1,test2;
ERROR 1051 (42S02): Unknown table 'yeyz.test2'
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| t1    |
| t2    |
| t3    |
+----------------+
3 rows in set (0.00 sec)

MySQL8.0的表现：

mysql> create table test1(id int);
Query OK, 0 rows affected (0.17 sec)

mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test1   |
+----------------+
1 row in set (0.00 sec)

mysql> drop table test1,test2;
ERROR 1051 (42S02): Unknown table 'yeyz.test2'
mysql> show tables;
+----------------+
| Tables_in_yeyz |
+----------------+
| test1   |
+----------------+
1 row in set (0.00 sec)

可以看到，MySQL8.0中，当没有test2的时候，并没有删除test1这个表，它将整个语句完全回滚；而MySQL5.7中，误删除了test1这个表，没有将整个语句完全回滚。

基于这种处理机制的不同，因此，我们在使用MySQL5.7版本和MySQL8.0版本做主从复制的时候，如果使用了类似上面的语句，就会发生报错。因为二者的执行行为已经不一样了。要想解决这个问题，需要使用drop table if not exists语法，同样的，针对drop database、drop trigger等一系列操作，处理方法类似。还有一点值得注意，如果一个数据库中的所有表都是innodb的，那么drop database才是原子的，否则，drop database不是原子的。

(2) Create Table...Select 语法：

    从MySQL 8.0.21开始，在支持原子DDL的存储引擎上，当使用基于row的复制模式时，CREATE TABLE...SELECT...，该语句作为一个事务记录在二进制日志中。之前的版本中，它被记录为两个事务，一个用于create表，另一个用于insert数据。两个事务之间或插入数据时发生服务器故障可能导致复制了一张空表。通过引入原子DDL支持，CREATE TABLE ...SELECT语句现在对于基于行的复制是安全的，并且允许与基于GTID的复制一起使用。

03 DDL 操作的log如何查看？

    为了支持DDL操作的redo和rollback，InnoDB将DDL日志写入mysql.innodb_ddl_log表中，这个表存在于数据字典表空间中，如果用户想要看这个表里面的内容，需要打开参数：

mysql> show variables like '%innodb_print_ddl_logs%'; 
+-----------------------+-------+
| Variable_name   | Value |
+-----------------------+-------+
| innodb_print_ddl_logs | OFF |
+-----------------------+-------+
1 row in set (0.01 sec)

然后就可以在error log日志中看到ddl操作的日志了。相关日志如下：

[Note] [000000] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=7,
space_id=5, old_file_path=./test/t1.ibd]
[Note] [000000] InnoDB: DDL log delete : by id 18
[Note] [000000] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=7,
table_id=1058, new_file_path=test/t1]
[Note] [000000] InnoDB: DDL log delete : by id 19
[Note] [000000] InnoDB: DDL log insert : [DDL record: FREE, id=20, thread_id=7,
space_id=5, index_id=132, page_no=4]
[Note] [000000] InnoDB: DDL log delete : by id 20
[Note] [000000] InnoDB: DDL log post ddl : begin for thread id : 7
[Note] [000000] InnoDB: DDL log post ddl : end for thread id : 7

 mysql.innodb_ddl_log这个表的刷盘时机不受innodb_flush_logs_at_trx_commit参数的影响，这么做的目的是为了避免数据文件被DDL操作修改了，但是对应的redo log还没有刷新到磁盘，导致恢复或者回滚的时候报错。

   最后，我们介绍下整个原子DDL操作的几个阶段：

1、准备阶段：创建需要的对象，写入DDL log到mysql.innodb_ddl_log表，DDl log定义了如何前滚和回滚DDL操作

2、执行阶段：执行DDL的操作流程

3、提交阶段：更新数据字典，并提交数据字典事务

4、Post-DDL阶段：从mysql.innodb_ddl_log表重放并删除DDL日志。为了确保可以安全地执行回滚而不会引起不一致，在此最后阶段执行磁盘上的文件操作，例如重命名或删除数据文件。此阶段还将从mysql.innodb_dynamic_metadata数据字典表中删除动态元数据，以用于DROP TABLE，TRUNCATE TABLE和其他重建表的DDL操作。