MySQL8.0新特性-原子DDL
MySQL8.0以前的DDL
DDL（Data Definition Language）定义了数据在数据库中的结构、关系以及权限等，比如CREATE、ALTER、DROP、GRANT等等。在MySQL 8.0之前的版本中，由于架构的原因，为了支持不同的存储引擎，MySQL在server层使用统一的.frm文件来存储表元数据信息；因此无法做到DDL的原子化，比如DROP TABLE t1, t2;执行过程中如果遇到server crash，有可能出现表t1被DROP掉了，但是t2没有被DROP掉的情况；即便是一条DDL，比如CREATE TABLE t1(a int);也可能在server crash的情况下导致建表不完整，有可能在建表失败的情况下遗留.frm或者.ibd文件。
MySQL Data Dictionary before MySQL8.0
MySQL为了支持插件式的架构，将metadata存储在sever层的MyISAM引擎的系统表里，同时innodb自己也需要维护一份metadata，所以导致如下弊端：
l 由于Server层的metadata存储在非事务引擎（MyISAM)里，所以在进行crash recovery的时候就不能维持原子性，容易导致数据不一致。
l 两份系统表存储的信息有所不同，访问Server层以及存储引擎需要使用不同API，这种设计导致了不能很好的统一对系统metadata的访问。
l DDL的非原子性使得Replication处理异常情况变得更加复杂。比如DROP TABLE t1, t2; 如果DROP t1成功，但是DROP t2失败，Replication就无法保证主备一致性了
MySQL8.0 INNODB原子DDL
8.0引入数据字典，且数据字典采用innodb引擎的表来管理，所以为支持原子DDL打好了基础。
MySQL Data Dictionary in MySQL8.0
如图，Server层以及Storage Engine使用同一份data dictionary用来存储metadata。同时将各自需要的metadata存储在用innodb作为存储引擎的数据字典中，所以crash recovery的时候，可以安全的进行事务回滚。
MySQL8.0 innodb的原子DDL就是将和DDL操作相关的数据字典更新、存储引擎内部操作、二进制日志写入组合到单个原子事务中，即便是DDL期间出现了Crash，也能保证事务要么提交要么回滚，不会出现不一致的现象。执行DDL时分为以下几个步骤：
l 准备：创建所需对象并将DDL日志写入mysql.innodb_ddl_log表。 DDL日志定义了如何前滚和回滚DDL操作。
l 执行：执行DDL操作。
l 提交：更新数据字典并提交数据字典事务。
l DDL后收尾工作：真正的物理删除或重命名文件; 删除innodb_ddl_log中的记录项; 对于一些ddl操作还会去更新其动态元数据信息(存储在mysql.innodb_dynamic_metadata，例如corrupt flag, auto_inc值等)
当前只有INNODB存储引擎支持原子DDL，同时只支持以下语句：
l 和表相关的DDL语句，如针对数据库、表空间、表和索引的CREATE、ALTER、DROP操作和TRUNCATE TABLE语句。
l 和表无关的DDL语句，如针对存储过程、触发器、试图、UDF的CREATE、DROP、ALTER操作；账号与权限相关的操作。
不支持以下语句：
l INSTALL PLUGIN和UNNSTALL PLUGIN语句、INSTALL COMPONENT 和UNINSTALL COMPONENT语句、CREATE SERVER, ALTER SERVER和DROP SERVER语句。
如何查看DDL日志
执行DDL时，MySQL将DDL日志写入了mysql.innodb_ddl_log表中，该表是一个隐藏的数据字典表，位于mysql.idb表空间中；为了能够观察DDL LOG，MySQL新增了innodb_print_ddl_logs选项，打开该选项同时调整log_error_verbosity的级别为3，就可以从MySQL的错误日志中观察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 |
自增长、主键 |
|
thread_id |
每个DDL日志记录都分配有一个thread_id |
|
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 日志示例：
CREATE TABLE
|
mysql> CREATE TABLE t1 (a INT PRIMARY KEY, b INT); Query OK, 0 rows affected (0.04 sec) |
|
mysql> select t1.name,t1.table_id,t1.space,t2.index_id,t2.page_no from INNODB_TABLES t1,INNODB_INDEXES t2 where t1.table_iid=t2.table_id and t1.name like '%t1%'; +---------+----------+-------+----------+---------+ | name | table_id | space | index_id | page_no | +---------+----------+-------+----------+---------+ | test/t1 | 1059 | 2 | 143 | 4 | +---------+----------+-------+----------+---------+ |
|
[InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=2, thread_id=8, space_id=2, old_file_path=./test/t1.ibd] [InnoDB] DDL log delete : 2 [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=3, thread_id=8, table_id=1059, new_file_path=test/t1] [InnoDB] DDL log delete : 3 [InnoDB] DDL log insert : [DDL record: FREE, id=4, thread_id=8, space_id=2, index_id=143, page_no=4] [InnoDB] DDL log delete : 4 [InnoDB] DDL log post ddl : begin for thread id : 8 [InnoDB] DDL log post ddl : end for thread id : 8 |
该日志描述了创建失败如何进行回滚：1、删除文件，2、释放内存中的数据字典信息，3、删除索引；表创建完成前DDL LOG写入表中，COMMIT后删除。
ADD COLUMN
|
mysql> ALTER TABLE t1 ADD COLUMN c INT; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 |
|
[InnoDB] DDL log post ddl : begin for thread id : 10 [InnoDB] DDL log post ddl : end for thread id : 10 |
没有对应的DDL LOG，因为8.0.13引入了腾讯的instant dll，加列可以只修改元数据，所以不需要记录日志。
ADD INDEX
|
mysql> ALTER TABLE t1 ADD KEY(b); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 |
|
+---------+----------+-------+----------+---------+ | name | table_id | space | index_id | page_no | +---------+----------+-------+----------+---------+ | test/t1 | 1059 | 2 | 143 | 4 | | test/t1 | 1059 | 2 | 144 | 5 | +---------+----------+-------+----------+---------+ |
|
[InnoDB] DDL log insert : [DDL record: FREE, id=5, thread_id=10, space_id=2, index_id=144, page_no=5] [InnoDB] DDL log delete : 5 [InnoDB] DDL log post ddl : begin for thread id : 10 [InnoDB] DDL log post ddl : end for thread id : 10 |
创建索引采用inplace（https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html）方式，没有临时文件，但依然需要在发生异常时清理临时索引, 因此增加了一条FREE 日志。
DROP TABLE
|
mysql> drop table t1; Query OK, 0 rows affected (0.05 sec) |
|
[InnoDB] DDL log insert : [DDL record: DROP, id=6, thread_id=10, table_id=1059] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=7, thread_id=10, space_id=2, old_file_path=./test/t1.ibd] [InnoDB] DDL log post ddl : begin for thread id : 10 [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=7, thread_id=10, space_id=2, old_file_path=./test/t1.ibd] [InnoDB] DDL log replay : [DDL record: DROP, id=6, thread_id=10, table_id=1059] [InnoDB] DDL log post ddl : end for thread id : 10 |
先记录需要操作的DDL LOG，事务提交后在最后post-ddl阶段执行真正的删除表对象和文件操作。
参考：
https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html
http://mysql.taobao.org/monthly/2018/03/02/
https://mp.weixin.qq.com/s?__biz=MzIxNTQ0MDQxNg==&mid=2247484764&idx=1&sn=1180306163bfe8a80900d6fa5a6a93f3&