mysql 语句 原子_MySQL8新特性(1)--原子DDL

mysql 8支持原子ddl。一个原子DDL语句包含数据字典更新、存储引擎操作、二进制日志写，事务要么被提交，应用修改被持持久化到数据字典、存储引擎和二进制日志，或者被回滚。

原子ddl是随着mysql 8中数据字典的引入被引入的。在早期版本，元数据被存储在元数据文件中，非事务型表中，存储引擎指定的目录中，这就需要协调提交操作。mysql数据字典提供了集中的、事务型元数据存储，就减少的早期版本中存在的障碍，使得可以重构ddl语句操作到原子事务中。

下面来看下在MySQL8.0之前和MySQL8.0 数据字典的区别。

1.支持的ddl语句

原子ddl特性支持表ddl语句和非表ddl语句。与表相关的ddl操作需要存储引擎的支持，非表相关的ddl操作不需要存储引擎的支持。

·支持表ddl的语句包含对数据库、表空间、表和索引的create、alter、drop操作，以及对表的truncate操作

·支持非表ddl的语句包含：create，drop语句，以及对存储函数、触发器、视图和用户定义的函数的alter语句；账号管理语句如create、alter、drop以及对角色和用户的rename语句，grant、revoke语句

2.原子ddl特性不支持以下语句：

·表相关的ddl语句涉及到非innodb存储引擎

·install plugin、uninstall plugin语句

·install component、uninstall component语句

·create server、alter server、drop server语句

3.原子ddl的特征

·元数据更新、二进制日志写、存储引擎操作被整合到一个单事务中

·ddl操作不需要在sql层协调事务提交

·数据字典、routine、event和用户定义的函数在缓存中的状态和ddl操作的状态是一致的。也即是缓存被修改来反应ddl操作是否成功还是回滚

·一个ddl操作中的存储引擎方法不执行协调提交，存储引擎会将自己注册成ddl事务的一部分

·存储引擎支持redo和回滚，发生在ddl操作的后期

·ddl操作的可见行为是原子的

4.ddl语句行为的变化

·drop table操作是完全原子的，如果所有表都是支持原子ddl的存储引擎。要么删除所有的表，要么都回滚。如果表不存在、或者没有做任何修改，drop table就会失败，无论是什么存储引擎。

·drop database操作是原子的，如果所有的表都是使用支持原子ddl的存储引擎。

·如果表使用的是不支持原子ddl的存储引擎，表删除操作就发生在原子drop table或原子drop database事务之外。也是单独写入二进制日志。对于删除多个表的操作，不使用原子ddl支持的存储引擎的表将在使用原子ddl支持的存储引擎的表之前删除。

·create table、alter table、rename table、truncate table、create tablespace、drop tablespace，对于支持原子ddl的引擎表，要么全部提交，要么全部回滚。

·如果视图不存在，drop view会失败，不做任何修改。

·不再支持部分执行账号管理操作，账号管理操作要么全部成功，要么全部回滚。

5.存储引擎的支持

目前，只有innodb存储引擎支持原子ddl。

为了支持redo和回滚ddl操作，innodb将ddl日志写入mysql.innodb_ddl_log字典表(这是隐藏的数据字典表，位于mysql.ibd数据字典表空间)

要查看ddl日志，可以开启参数innodb_print_ddl_logs

redo logs的变更会立即被刷新到mysql.innodb_ddl_log表，不管innodb_flush_log_at_trx_commit的设置。

立即刷新redo logs可以避免数据文件被DDL操作修改而redo logs没有被写入mysql.innodb_ddl_log表而引起的不一致。(这种情况会导致回滚或恢复时发生错误)

innodb分阶段执行ddl操作。比如alter table在执行提交之前可能需要多次执行prepare、perform：

(1)prepare：创建需要的对象，写ddl日志到mysql.innodb_ddl_log表。ddl日志定义了如何前滚和回滚ddl操作

(2)perform：执行ddl操作。例如为create table操作执行一个create routine

(3)commit：更新数据字典和提交数据字典事务

(4)post-ddl：replay和remove mysql.innodb_ddl_log表中ddl日志。为了保证回滚可以安全执行而不产生不一致，文件操作比如重命名或移除数据文件就发生在这一步。这一步中也会移除来自数据字典mysql.innodb_dynamic_metadata表中的动态元数据。

在post-ddl期间，会replay和remove mysql.innodb_ddl_log表中的ddl日志。只有在执行ddl操作期间，mysql server发生了halted，ddl日志才会保留在mysql.innodb_ddl_log表中，这种情况下，在恢复时会replay和remove ddl日志。

在恢复情形下，ddl事务可能被提交或者回滚。如果在DDL操作的commit阶段执行的数据字典事务出现在重做日志和二进制日志中，则该操作被认为是成功的，并被前滚。否则，在InnoDB replay数据字典重做日志时回滚不完整的数据字典事务，并回滚DDL事务。

6.查看ddl日志

要想查看存储在数据字典mysql.innodb_ddl_log表中的ddl日志。需要开启innodb_print_ddl_logs参数。开启后，ddl日志被写到stderr。取决于操作系统和mysql的配置，stderr可能是error log，终端，windows控制台。

innodb将ddl日志写入mysql.innodb_ddl_log表来支持redo和回滚ddl操作。mysql.innodb_ddl_log表是一个隐藏的数据字典表，位于mysql.ibd数据字典表空间。在非debug模式下，不能直接访问。

mysql.innodb_ddl_log的表结构：

CREATE TABLE mysql.innodb_ddl_log (

id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,#ddl日志记录的唯一标识符

thread_id BIGINT UNSIGNED NOT NULL,#线程id，用于replay和remove ddl日志

type INT UNSIGNED NOT NULL,#ddl操作类型。free：删除一个索引树；delete：删除一个文件；rename:重命名文件；drop：从mysql.innodb_dynamic_metadata数据字典表删除元数据

space_id INT UNSIGNED,#表空间id

page_no INT UNSIGNED,#页号

index_id BIGINT UNSIGNED,#索引id

table_id BIGINT UNSIGNED,#表id

old_file_path VARCHAR(512) COLLATE UTF8_BIN,#老的表空间文件路径

new_file_path VARCHAR(512) COLLATE UTF8_BIN,#新的表空间文件路径

KEY(thread_id)

);

7.ddl日志示例：

mysql> SET GLOBAL innodb_print_ddl_logs=1;

mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;

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