mysql8.0原子ddl特性

原子DDL支持

MySQL8.0支持原子DDL。原子DDL将DDL操作相关联的数据字典更新、存储引擎操作和二进制日志写入合并到单个原子事务中。

事务要么被提交，伴随着的变更持久化到数据字典中、存储引擎和二进制日志中，要么事务中断回滚。

支持的DDL语句

原子DDL特性支持表级DDL语句同时也支持非表级DDL语句。表级DDL操作需要存储引擎的支持，而非表级DDL操作则不需要。目前，仅InnoDB存储引擎支持原子DDL。

●原子DLL特性支持的表级DDL语句包括CREATE,DROP,ALTER用于库、表空间、索引还有TRUNCATE TABLE语句。

●原子DLL特性支持的非表级DDL语句包括CREATE,DROP,ALTER作用于存储过程、触发器、视图和一些用户自定义函数。

●帐户管理语句：CREATE，ALTER，DROP，用户和角色的RENAME语句，以及GRANT和REVOKE语句。

原子DDL功能不支持以下语句：

●与表相关的DDL语句，其中涉及InnoDB以外的存储引擎。

●INSTALL PLUGIN和UNINSTALL PLUGIN语句。

●INSTALL COMPONENT和UNINSTALL COMPONENT语句。

●CREATE SERVER，ALTER SERVER和DROP SERVER语句

原子DDL特性

原子DDL特性

原子DDL语句的特性包括：

●元数据更新、二进制日志写入和存储引擎操作组合到单个事务中。

●在DDL操作期间，SQL层没有中间提交。

●数据字典、例程、事件和UDF缓存的状态与DDL操作的状态一致，这意味着将更新缓存以反映DDL操作是否成功完成或回滚。

●DDL操作中涉及的存储引擎方法不执行中间提交，存储引擎将自身注册为DDL事务的一部分。

●存储引擎支持在DDL操作的后DDL阶段执行的DDL操作的重做和回滚。

●DDL操作的可见行为是原子的，这会更改某些DDL语句的行为。

DDL语句行为的更改

本节描述由于引入原子DDL支持而导致的DDL语句行为的更改。

●如果所有命名表都使用原子DDL支持的存储引擎，则删除表操作是完全原子的。语句要么成功删除所有表，要么回滚。

●如果命名表不存在，并且无论存储引擎如何，都不进行任何更改，则DROP TABLE将失败并返回错误。以下示例演示了这种行为更改，其中DROP TABLE语句由于命名表不存在而失败：

先看看5.7版本是如何的

mysql> create table t1 (c1 int);
Query OK, 0 rows affected (0.04 sec)

mysql> drop table t1,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
Empty set (0.00 sec)

可以看出5.7版本中即使执行报错T1也被删除

以下是8.0版本

mysql> create table t1 (c1 int);
Query OK, 0 rows affected (0.07 sec)

mysql> drop table t1,t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

注意

由于这种行为的改变，当在MySQL 8.0从节点上复制时，MySQL 5.7主机上部分完成的DROP TABLE语句失败。若要避免此失败情况，请在DROP TABLE语句中使用IF EXISTS语法，以防止不存在的表发生错误。

●如果所有表都使用原子DDL支持的存储引擎，则DROP DATABASE是原子的。该语句要么成功删除所有对象，要么回滚。但是，最后一次从文件系统中删除数据库目录不是原子事务的一部分。如果由于文件系统错误或服务器停止而导致数据库目录删除失败，则不会回滚DROP DATABASE事务。

●对于不使用原子DDL支持的存储引擎的表，表删除发生在原子DROP TABLE或DROP DATABASE事务之外。此类表删除操作将分别写入二进制日志，这在DROP TABLE或DROP DATABASE操作中断的情况下，最多将存储引擎，数据字典和二进制日志之间的差异限制为一个表。对于删除多个表的操作，未使用不支持原子DDL的存储引擎的表将被删除。

●对于使用原子DDL支持的存储引擎的表，CREATE TABLE，ALTER TABLE，RENAME TABLE，TRUNCATE TABLE，CREATE TABLESPACE和DROP TABLESPACE操作将完全提交，如果服务器在其操作期间停止运行，则将回滚这些操作。在早期的MySQL版本中，这些操作的中断可能会导致存储引擎，数据字典和二进制日志之间出现差异，或者留下孤立的文件。仅当所有表都使用原子DDL支持的存储引擎时，RENAME TABLE操作才是原子的。

●如果视图不存在，并且没有进行任何更改，则DROP VIEW将失败。在此示例中演示了行为上的变化，其中DROP VIEW语句失败，因为视图不存在：

以下为8.0版本的变化

mysql>CREATE VIEW test.viewA AS SELECT * FROM t1;
Query OK, 0 rows affected (0.03 sec)
mysql>DROP VIEW test.viewA, test.viewB;
ERROR 1051 (42S02): Unknown table 'test.viewB'
mysql>SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| viewA          | VIEW       |
+----------------+------------+
1 row in set (0.00 sec)

以下为5.7版本

mysql> CREATE VIEW test.viewA AS SELECT * FROM t;
mysql> DROP VIEW test.viewA, test.viewB;
ERROR 1051 (42S02): Unknown table 'test.viewB'
mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
Empty set (0.00 sec)

注意

由于这种行为的改变，当在MySQL 8.0从机上复制时，MySQL 5.7主机上部分完成的DROP VIEW操作失败。若要避免此失败情况，请在DROP VIEW语句中使用IF EXISTS语法，以防止对不存在的视图发生错误。

不在允许执行部分账户管理语句，账户管理语句对所有账户要么成功，要么回滚，如果发生错误，则无效。再早期的mysql版本中，账户管理语句可能对某些用户成功，对其他用户失败。

以下示例中，第二个CREATE USER语句返回错误，因此它不能处理其userB的账户信息。

mysql> CREATE USER userA;
mysql> CREATE USER userA, userB;
ERROR 1396 (HY000): Operation 
CREATE USER failed for 'userA'@'%'
mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
+-------+
| User  |
+-------+
| userA |
+-------+

在引入原子DDL之前，第二个CREATE USER语句为不存在的命名用户返回一个错误，但对存在的用户返回一个错误但事实上用户创建成功:

mysql> CREATE USER userA;
mysql> CREATE USER userA, userB;
ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%'
mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
+-------+
| User  |
+-------+
| userA |
| userB |
+-------+

注意

由于这种行为上的变化，当在MySQL 8.0从机上复制时，MySQL 5.7主机上部分完成的帐户管理语句失败。若要避免此失败情况，请在帐户管理语句中酌情使用IF EXISTS或IF NOT EXISTS语法，以防止与用户相关语句导致同步失败。

存储引擎支持

目前，只有InnoDB存储引擎支持原子DDL。非InnoDB存储引擎在操作中断时依然会出现部分操作完成带来的不一致的问题。

为了支持DDL操作的重做和回滚，InnoDB将DDL日志写入mysql.InnoDB_DDL_log表，该表是一个隐藏的数据字典表，位于mysql.ibd data dictionary表空间中。

要查看DDL操作期间写入mysql.innodb_DDL_log表的DDL日志，请启用innodb_print_DDL_logs配置选项。

注意

无论innodb_flush_at_trx_commit如何设置,mysql.innodb_ddl_log表所做更改的

redo logs都会立即刷新到磁盘。立即刷新redo logs可以避免数据文件被DDL操作修改的情况，但是这些操作导致的mysql.innodb_ddl_log表更改的重做日志不会持久化到磁盘。这种情况可能会在回滚或恢复期间导致错误。

InnoDB存储引擎分阶段执行DDL操作。诸如ALTER TABLE的DDL操作可以在commit阶段之前多次执行Prepare和perform阶段。

1.Prepare：创建所需的对象并将DDL日志写入mysql.innodb_DDL_log表。DDL日志定义如何前滚和回滚DDL操作。

2.Perform：执行DDL操作。例如，执行创建表操作的创建进程。

3.Commit：更新数据字典并提交数据字典事务。

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

无论事务是提交还是回滚，DDL日志都会在Post-DDL阶段从mysql.innodb_ddl_log表中重放和删除。如果在DDL操作期间服务器停止，DDL日志只应保留在mysql.innodb_ddl_log表中。在这种情况下，DDL日志将在恢复后重放和删除。

在恢复情况下，可以在重新启动服务器时提交或回滚DDL事务。如果在DDL操作的Commit阶段执行的数据字典事务存在于redo log和binary log中，则该操作被视为成功并被前滚。否则，InnoDB重放数据字典redo log时，会回滚不完整的数据字典事务，并回滚DDL事务。

查看DDL日志

要在innodb存储引擎的原子DDL操作期间查看写入mysql.innodb_ddl_log数据字典表的DDL日志，请启用innodb_print_DDL_logs使mysql将DDL日志写入stderr。根据主机操作系统和MySQL配置，stderr可能是错误日志、终端或控制台窗口。

InnoDB将DDL日志写入mysql.innodb_ddl_log表，以支持DDL操作的重做和回滚。innodb_ddl_log表是一个隐藏的数据字典表，位于mysql.ibd数据字典表空间中。与其他隐藏的数据字典表一样，mysql的非debug版本无法直接访问mysql.innodb_ddl_log。

mysql.innodb_ddl_log表结构如下：

CREATE TABLE mysql.innodb_ddl_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  thread_id BIGINT UNSIGNED NOT NULL,
  type INT UNSIGNED NOT NULL,
  space_id INT UNSIGNED,
  page_no INT UNSIGNED,
  index_id BIGINT UNSIGNED,
  table_id BIGINT UNSIGNED,
  old_file_path VARCHAR(512) COLLATE UTF8_BIN,
  new_file_path VARCHAR(512) COLLATE UTF8_BIN,
  KEY(thread_id)
);

●id：DDL日志记录的唯一标识符。

●thread_id：为每个DDL日志记录分配一个thread_id，用于重播和删除属于特定DDL事务的DDL日志。涉及多个数据文件操作的DDL事务生成多个DDL日志记录。

●type：DDL操作类型。类型包括FREE（删除索引树）、DELETE（删除文件）、RENAME（重命名文件）或drop（从mysql.innodb_dynamic_metadata 数据字典表中删除元数据）。

●space_id：表空间id。

●page_no：包含分配信息的页；例如索引树根页。

●index_id：索引id。

●table_id：表id。

●old_file_path：旧表空间文件路径。用于创建或删除表空间文件的DDL操作；也用于重命名表空间的DDL操作。

●new_file_path：新表空间文件路径。由重命名表空间文件的DDL操作使用。

此示例演示如何启用innodb_print_ddl_logs来查看为创建表操作而写入strderr的ddl日志

mysql> SET GLOBAL innodb_print_ddl_logs=1;
mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;

[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