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&