MySQL的SQL语句 - 数据定义语句（1）- 原子数据定义语句

林员外聊编程 2020-07-14

305

支持原子数据定义语句

MySQL 8.0支持原子数据定义语言（DDL）语句。这个特性被称为原子DDL。原子DDL语句将与DDL操作相关联的数据字典更新、存储引擎操作和二进制日志写入合并到单个原子操作中。该操作要么已提交，并将适用的更改保留到数据字典、存储引擎和二进制日志中，要么回滚，即使服务器在操作期间停止也要保障这一点。

注意

原子DDL不是事务DDL。DDL语句，无论是原子的还是其他的，都隐式地结束当前会话中活动的任何事务，就好像在执行该语句之前进行了提交一样。这意味着DDL语句不能在另一个事务中执行，也不能在事务控制语句 START TRANSACTION ... COMMIT 中执行，或与同一事务中的其他语句组合。

通过在MySQL 8.0中引入MySQL数据字典，原子DDL成为可能。在早期的MySQL版本中，元数据存储在元数据文件、非事务表和特定于存储引擎的字典中，这就需要中间提交。MySQL数据字典提供的集中化、事务性元数据存储消除了这一障碍，使得将DDL语句操作重组为原子操作成为可能。

本部分分别用以下主题描述原子DDL特性：

●支持的DDL语句

●原子DDL特点

●DDL语句行为的变更

●存储引擎支持

●查看DDL日志

支持的DDL语句

原子DDL特性支持表和非表DDL语句。与表相关的DDL操作需要存储引擎支持，而非表DDL操作则不需要。目前，只有InnoDB存储引擎支持原子DDL。

●支持的表DDL语句包括数据库、表空间、表和索引的CREATE、ALTER和DROP语句，以及TRUNCATE TABLE语句。

●支持的非表DDL语句包括：

■ CREATE和DROP语句，如果适用，用于存储程序、触发器、视图和用户定义函数（UDF）的ALTER语句。

■ 帐户管理语句：CREATE、ALTER、DROP语句，如果适用，用于用户和角色的RENAME语句，以及 GRANT 和 REVOKE 语句。

原子DDL特性不支持以下语句：

●除InnoDB之外的存储引擎与表相关的DDL语句。

●INSTALL PLUGIN 和 UNINSTALL PLUGIN语句。

●INSTALL COMPONENT 和 UNINSTALL COMPONENT语句。

●CREATE SERVER、ALTER SERVER和DROP SERVER语句。

原子DDL特性

原子DDL语句的特性包括：

●在适用的情况下，将元数据更新，二进制日志写入和存储引擎操作合并到到单个原子操作中。

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

●适用条件：

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

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

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

●DDL操作的可见行为是原子的，这改变了一些DDL语句的行为。

DDL语句行为的变化

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

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

如果命名表不存在，则DROP TABLE失败并返回错误，并且不进行任何更改（无论任何存储引擎都是如此）。下面的示例演示了这种行为更改，其中DROP TABLE语句因命名表不存在而失败：

在引入原子DDL之前，DROP TABLE删除不存在的表时报告一个错误，但对于确实存在的表则报告成功：

注意

由于这种行为的改变，在MySQL5.7主服务器上部分完成的DROP TABLE语句在MySQL8.0从属服务器上复制时会失败。若要避免这种失败情况，请在DROP TABLE语句中使用IF EXISTS语法，以防止不存在的表发生错误。

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

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

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

●从MySQL 8.0.21开始，在支持原子DDL的存储引擎上，CREATE TABLE ... SELECT语句作为单个事务被记录到二进制日志中。在MySQL8.0.21之前，使用了基于行的复制，语句作为两个事务写入二进制日志。一个事务创建表，另一个事务插入数据。在这些事务之间或在插入数据时发生服务器故障可能导致复制空表。通过引入原子DDL支持，CREATE TABLE ... SELECT语句现在对于基于行的复制是安全的，并且支持基于GTID的复制。

在同时支持原子DDL和外键约束的存储引擎上，启用基于行的复制时，不允许在CREATE TABLE ... SELECT语句中创建外键。外键约束可以在以后使用ALTER TABLE添加。

当CREATE TABLE ... SELECT作为一个原子操作应用时，在插入数据时，在表上保留一个元数据锁，这防止了在操作期间并发访问表。

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

在引入原子DDL之前，DROP VIEW为不存在的视图返回一个错误，但对于确实存在的视图返回一个成功：

注意

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

● 不再允许部分执行账户管理语句。帐户管理语句要么对所有用户都成功，要么回滚，如果发生错误不做任何更改。在早期的MySQL版本中，命名多个用户的帐户管理语句可能对某些用户成功，而对其他用户则失败。

此示例演示了行为的变化，其中第二个CREATE USER语句返回一个错误，但由于无法对所有命名用户成功执行而失败。

在引入原子DDL之前，第二个CREATE USER语句将由于不存在的命名用户返回错误，但对于确实存在的命名用户则返回成功：

注意

由于这种行为的改变，在MySQL 5.7主服务器上部分完成的帐户管理语句在MySQL 8.0从属服务器上复制时会失败。若要避免这种错误发生，请在账户管理语句中视情况使用IF EXISTS或者IF NOT EXISTS语法，避免命名用户相关的错误。

存储引擎支持

目前，只有InnoDB存储引擎支持原子DDL。不支持原子DDL的存储引擎不受DDL原子性的约束。当操作被中断或仅部分完成时，涉及不支持DDL存储引擎的DDL操作仍然能够引入不一致性。

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

要查看在DDL操作期间写入mysql.innodb_ddl_log表的DDL日志，启用innodb_print_ddl_logs配置选项。有关详细信息，请参阅查看DDL日志。

InnoDB存储引擎分阶段执行DDL操作。像ALTER TABLE这样的DDL操作可能在提交阶段之前多次执行准备阶段和执行阶段。

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

2.执行：执行DDL操作。例如，对CREATE TABLE操作执行创建例程。

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

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操作期间停止，DDL日志只应保留在 mysql.innodb_ddl_log表中。在这种情况下，DDL日志将重放，并在恢复后并删除。

在恢复的情况下，重新启动服务器时可能会提交或回滚DDL操作。如果在DDL操作的提交阶段执行的数据字典事务存在于重做日志和二进制日志中，则认为该操作成功并被前滚。否则，InnoDB回放数据字典重做日志时，将回滚不完整的数据字典事务，回滚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操作的重做和回滚。这个mysql.innodb_ddl_log表是一个隐藏的数据字典表，位于mysql.ibd数据字典表空间。与其他隐藏的数据字典表一样，在非调试版本的MySQL中无法直接访问mysql.innodb_ddl_log表。mysql.innodb_ddl_log表定义如下：