华山资源网

解析MySQL8.0新特性——事务性数据字典与原子DDL
数据库 2024/12/23 佚名
3 1

前言

事务性数据字典与原子DDL，是MySQL 8.0推出的两个非常重要的新特性，之所以将这两个新特性放在一起，是因为两者密切相关，事务性数据字典是前提，原子DDL是一个重要应用场景。

MySQL 8.0之前的数据字典

MySQL 8.0之前的数据字典，主要由以下三部分组成：

（1）操作系统文件

db.opt：数据库元数据信息
frm：表元数据信息
par：表分区元数据信息
TRN/TRG：触发器元数据信息
ddl_log.log：DDL过程中产生的元数据信息

（2）mysql库下的非InnoDB系统表

mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='mysql' and engine<>'InnoDB';
+--------------+------------------+------------+--------+
| table_schema | table_name    | table_type | engine |
+--------------+------------------+------------+--------+
| mysql    | columns_priv   | BASE TABLE | MyISAM |
| mysql    | db        | BASE TABLE | MyISAM |
| mysql    | event      | BASE TABLE | MyISAM |
| mysql    | func       | BASE TABLE | MyISAM |
| mysql    | general_log   | BASE TABLE | CSV  |
| mysql    | ndb_binlog_index | BASE TABLE | MyISAM |
| mysql    | proc       | BASE TABLE | MyISAM |
| mysql    | procs_priv    | BASE TABLE | MyISAM |
| mysql    | proxies_priv   | BASE TABLE | MyISAM |
| mysql    | slow_log     | BASE TABLE | CSV  |
| mysql    | tables_priv   | BASE TABLE | MyISAM |
| mysql    | user       | BASE TABLE | MyISAM |
+--------------+------------------+------------+--------+
12 rows in set (0.00 sec)

（3）mysql库下的InnoDB系统表

mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='mysql' and engine='InnoDB';
+--------------+---------------------------+------------+--------+
| table_schema | table_name        | table_type | engine |
+--------------+---------------------------+------------+--------+
| mysql    | engine_cost        | BASE TABLE | InnoDB |
| mysql    | gtid_executed       | BASE TABLE | InnoDB |
| mysql    | help_category       | BASE TABLE | InnoDB |
| mysql    | help_keyword       | BASE TABLE | InnoDB |
| mysql    | help_relation       | BASE TABLE | InnoDB |
| mysql    | help_topic        | BASE TABLE | InnoDB |
| mysql    | innodb_index_stats    | BASE TABLE | InnoDB |
| mysql    | innodb_table_stats    | BASE TABLE | InnoDB |
| mysql    | plugin          | BASE TABLE | InnoDB |
| mysql    | server_cost        | BASE TABLE | InnoDB |
| mysql    | servers          | BASE TABLE | InnoDB |
| mysql    | slave_master_info     | BASE TABLE | InnoDB |
| mysql    | slave_relay_log_info   | BASE TABLE | InnoDB |
| mysql    | slave_worker_info     | BASE TABLE | InnoDB |
| mysql    | time_zone         | BASE TABLE | InnoDB |
| mysql    | time_zone_leap_second   | BASE TABLE | InnoDB |
| mysql    | time_zone_name      | BASE TABLE | InnoDB |
| mysql    | time_zone_transition   | BASE TABLE | InnoDB |
| mysql    | time_zone_transition_type | BASE TABLE | InnoDB |
+--------------+---------------------------+------------+--------+
19 rows in set (0.00 sec)

我们可以看到，数据字典被分布到多个地方，一方面不利于元数据统一管理，另一方面容易造成数据的不一致(由于操作系统文件、非InnoDB系统表均不支持事务，执行DDL操作无法保证ACID)。

MySQL 8.0的数据字典

为了解决上述问题，MySQL 8.0将数据字典统一改进为InnoDB存储引擎存储，具体分为两部分：

（1）数据字典表：存放最重要的元数据信息，位于mysql库下，存储在mysql共享表空间(mysql.ibd)

（2）其他系统表：存放辅助的元数据信息，位于mysql库下，存储在mysql共享表空间(mysql.ibd)

数据字典表

数据字典表是不可见，既不能通过select访问，也不会出现在show tables或information.schema.tables结果里；尝试访问会报以下错误：

mysql> select * from mysql.tables limit 10;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.tables' is rejected.

不过，在debug模式下，是可以访问这些隐藏的数据字典表的；我们重新编译安装(过程略)，并以debug模式启动进程，再次尝试访问，结果如下：

mysql> SET SESSION debug='+d,skip_dd_table_access_check';

mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
+------------------------------+-----------+--------+------------+
| name             | schema_id | hidden | type    |
+------------------------------+-----------+--------+------------+
| catalogs           |     1 | System | BASE TABLE |
| character_sets        |     1 | System | BASE TABLE |
| check_constraints      |     1 | System | BASE TABLE |
| collations          |     1 | System | BASE TABLE |
| column_statistics      |     1 | System | BASE TABLE |
| column_type_elements     |     1 | System | BASE TABLE |
| columns           |     1 | System | BASE TABLE |
| dd_properties        |     1 | System | BASE TABLE |
| events            |     1 | System | BASE TABLE |
| foreign_key_column_usage   |     1 | System | BASE TABLE |
| foreign_keys         |     1 | System | BASE TABLE |
| index_column_usage      |     1 | System | BASE TABLE |
| index_partitions       |     1 | System | BASE TABLE |
| index_stats         |     1 | System | BASE TABLE |
| indexes           |     1 | System | BASE TABLE |
| innodb_ddl_log        |     1 | System | BASE TABLE |
| innodb_dynamic_metadata   |     1 | System | BASE TABLE |
| parameter_type_elements   |     1 | System | BASE TABLE |
| parameters          |     1 | System | BASE TABLE |
| resource_groups       |     1 | System | BASE TABLE |
| routines           |     1 | System | BASE TABLE |
| schemata           |     1 | System | BASE TABLE |
| st_spatial_reference_systems |     1 | System | BASE TABLE |
| table_partition_values    |     1 | System | BASE TABLE |
| table_partitions       |     1 | System | BASE TABLE |
| table_stats         |     1 | System | BASE TABLE |
| tables            |     1 | System | BASE TABLE |
| tablespace_files       |     1 | System | BASE TABLE |
| tablespaces         |     1 | System | BASE TABLE |
| triggers           |     1 | System | BASE TABLE |
| view_routine_usage      |     1 | System | BASE TABLE |
| view_table_usage       |     1 | System | BASE TABLE |
+------------------------------+-----------+--------+------------+
32 rows in set (0.01 sec)

其他系统表

其他系统表，可以通过show tables或information_schema.tables查看，均以改进为InnoDB存储引擎(general_log、slow_log例外，这两张表并未记录元数据信息，只是用于记录日志)：

mysql> select table_schema,table_name,engine from information_schema.tables where table_schema='mysql';
+--------------+---------------------------+--------+
| TABLE_SCHEMA | TABLE_NAME        | ENGINE |
+--------------+---------------------------+--------+
| mysql    | columns_priv       | InnoDB |
| mysql    | component         | InnoDB |
| mysql    | db            | InnoDB |
| mysql    | default_roles       | InnoDB |
| mysql    | engine_cost        | InnoDB |
| mysql    | func           | InnoDB |
| mysql    | general_log        | CSV  |
| mysql    | global_grants       | InnoDB |
| mysql    | gtid_executed       | InnoDB |
| mysql    | help_category       | InnoDB |
| mysql    | help_keyword       | InnoDB |
| mysql    | help_relation       | InnoDB |
| mysql    | help_topic        | InnoDB |
| mysql    | innodb_index_stats    | InnoDB |
| mysql    | innodb_table_stats    | InnoDB |
| mysql    | password_history     | InnoDB |
| mysql    | plugin          | InnoDB |
| mysql    | procs_priv        | InnoDB |
| mysql    | proxies_priv       | InnoDB |
| mysql    | role_edges        | InnoDB |
| mysql    | server_cost        | InnoDB |
| mysql    | servers          | InnoDB |
| mysql    | slave_master_info     | InnoDB |
| mysql    | slave_relay_log_info   | InnoDB |
| mysql    | slave_worker_info     | InnoDB |
| mysql    | slow_log         | CSV  |
| mysql    | tables_priv        | InnoDB |
| mysql    | time_zone         | InnoDB |
| mysql    | time_zone_leap_second   | InnoDB |
| mysql    | time_zone_name      | InnoDB |
| mysql    | time_zone_transition   | InnoDB |
| mysql    | time_zone_transition_type | InnoDB |
| mysql    | user           | InnoDB |
+--------------+---------------------------+--------+
33 rows in set (0.00 sec)

数据字典视图

刚刚提到，数据字典表只能在debug模式下访问，那么在生产环境中，我们应该怎么去获取元数据信息呢？答案是通过information_schema库下的数据字典视图。和Oracle数据库的设计理念一样，将元数据信息存放在基表中(x$、$)，然后通过视图(v$、dba_/all_/user_)的方式提供给用户查询；MySQL数据库也是如此，将元数据信息存放在mysql库的数据字典表中隐藏起来，然后提供information_schema库视图给用户查询：

mysql> select table_schema,table_name,table_type,engine from information_schema.tables where table_schema='information_schema'; 
+--------------------+---------------------------------------+-------------+--------+
| TABLE_SCHEMA    | TABLE_NAME              | TABLE_TYPE | ENGINE |
+--------------------+---------------------------------------+-------------+--------+
| information_schema | ADMINISTRABLE_ROLE_AUTHORIZATIONS   | SYSTEM VIEW | NULL  |
| information_schema | APPLICABLE_ROLES           | SYSTEM VIEW | NULL  |
| information_schema | CHARACTER_SETS            | SYSTEM VIEW | NULL  |
| information_schema | CHECK_CONSTRAINTS           | SYSTEM VIEW | NULL  |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | NULL  |
| information_schema | COLLATIONS              | SYSTEM VIEW | NULL  |
| information_schema | COLUMN_PRIVILEGES           | SYSTEM VIEW | NULL  |
| information_schema | COLUMN_STATISTICS           | SYSTEM VIEW | NULL  |
| information_schema | COLUMNS                | SYSTEM VIEW | NULL  |
| information_schema | ENABLED_ROLES             | SYSTEM VIEW | NULL  |
| information_schema | ENGINES                | SYSTEM VIEW | NULL  |
| information_schema | EVENTS                | SYSTEM VIEW | NULL  |
| information_schema | FILES                 | SYSTEM VIEW | NULL  |
| information_schema | INNODB_BUFFER_PAGE          | SYSTEM VIEW | NULL  |
| information_schema | INNODB_BUFFER_PAGE_LRU        | SYSTEM VIEW | NULL  |
| information_schema | INNODB_BUFFER_POOL_STATS       | SYSTEM VIEW | NULL  |
| information_schema | INNODB_CACHED_INDEXES         | SYSTEM VIEW | NULL  |
| information_schema | INNODB_CMP              | SYSTEM VIEW | NULL  |
| information_schema | INNODB_CMP_PER_INDEX         | SYSTEM VIEW | NULL  |
| information_schema | INNODB_CMP_PER_INDEX_RESET      | SYSTEM VIEW | NULL  |
| information_schema | INNODB_CMP_RESET           | SYSTEM VIEW | NULL  |
| information_schema | INNODB_CMPMEM             | SYSTEM VIEW | NULL  |
| information_schema | INNODB_CMPMEM_RESET          | SYSTEM VIEW | NULL  |
| information_schema | INNODB_COLUMNS            | SYSTEM VIEW | NULL  |
| information_schema | INNODB_DATAFILES           | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FIELDS             | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FOREIGN            | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FOREIGN_COLS          | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FT_BEING_DELETED        | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FT_CONFIG           | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FT_DEFAULT_STOPWORD      | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FT_DELETED           | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FT_INDEX_CACHE         | SYSTEM VIEW | NULL  |
| information_schema | INNODB_FT_INDEX_TABLE         | SYSTEM VIEW | NULL  |
| information_schema | INNODB_INDEXES            | SYSTEM VIEW | NULL  |
| information_schema | INNODB_METRICS            | SYSTEM VIEW | NULL  |
| information_schema | INNODB_SESSION_TEMP_TABLESPACES    | SYSTEM VIEW | NULL  |
| information_schema | INNODB_TABLES             | SYSTEM VIEW | NULL  |
| information_schema | INNODB_TABLESPACES          | SYSTEM VIEW | NULL  |
| information_schema | INNODB_TABLESPACES_BRIEF       | SYSTEM VIEW | NULL  |
| information_schema | INNODB_TABLESTATS           | SYSTEM VIEW | NULL  |
| information_schema | INNODB_TEMP_TABLE_INFO        | SYSTEM VIEW | NULL  |
| information_schema | INNODB_TRX              | SYSTEM VIEW | NULL  |
| information_schema | INNODB_VIRTUAL            | SYSTEM VIEW | NULL  |
| information_schema | KEY_COLUMN_USAGE           | SYSTEM VIEW | NULL  |
| information_schema | KEYWORDS               | SYSTEM VIEW | NULL  |
| information_schema | OPTIMIZER_TRACE            | SYSTEM VIEW | NULL  |
| information_schema | PARAMETERS              | SYSTEM VIEW | NULL  |
| information_schema | PARTITIONS              | SYSTEM VIEW | NULL  |
| information_schema | PLUGINS                | SYSTEM VIEW | NULL  |
| information_schema | PROCESSLIST              | SYSTEM VIEW | NULL  |
| information_schema | PROFILING               | SYSTEM VIEW | NULL  |
| information_schema | REFERENTIAL_CONSTRAINTS        | SYSTEM VIEW | NULL  |
| information_schema | RESOURCE_GROUPS            | SYSTEM VIEW | NULL  |
| information_schema | ROLE_COLUMN_GRANTS          | SYSTEM VIEW | NULL  |
| information_schema | ROLE_ROUTINE_GRANTS          | SYSTEM VIEW | NULL  |
| information_schema | ROLE_TABLE_GRANTS           | SYSTEM VIEW | NULL  |
| information_schema | ROUTINES               | SYSTEM VIEW | NULL  |
| information_schema | SCHEMA_PRIVILEGES           | SYSTEM VIEW | NULL  |
| information_schema | SCHEMATA               | SYSTEM VIEW | NULL  |
| information_schema | ST_GEOMETRY_COLUMNS          | SYSTEM VIEW | NULL  |
| information_schema | ST_SPATIAL_REFERENCE_SYSTEMS     | SYSTEM VIEW | NULL  |
| information_schema | ST_UNITS_OF_MEASURE          | SYSTEM VIEW | NULL  |
| information_schema | STATISTICS              | SYSTEM VIEW | NULL  |
| information_schema | TABLE_CONSTRAINTS           | SYSTEM VIEW | NULL  |
| information_schema | TABLE_PRIVILEGES           | SYSTEM VIEW | NULL  |
| information_schema | TABLES                | SYSTEM VIEW | NULL  |
| information_schema | TABLESPACES              | SYSTEM VIEW | NULL  |
| information_schema | TRIGGERS               | SYSTEM VIEW | NULL  |
| information_schema | USER_PRIVILEGES            | SYSTEM VIEW | NULL  |
| information_schema | VIEW_ROUTINE_USAGE          | SYSTEM VIEW | NULL  |
| information_schema | VIEW_TABLE_USAGE           | SYSTEM VIEW | NULL  |
| information_schema | VIEWS                 | SYSTEM VIEW | NULL  |
+--------------------+---------------------------------------+-------------+--------+
73 rows in set (0.00 sec)

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
        View: TABLES
     Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`TABLES` AS select (`cat`.`name` collate utf8_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,`tbl`.`type` AS `TABLE_TYPE`,if((`tbl`.`type` = 'BASE TABLE'),`tbl`.`engine`,NULL) AS `ENGINE`,if((`tbl`.`type` = 'VIEW'),NULL,10) AS `VERSION`,`tbl`.`row_format` AS `ROW_FORMAT`,if((`tbl`.`type` = 'VIEW'),NULL,internal_table_rows(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`table_rows`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `TABLE_ROWS`,if((`tbl`.`type` = 'VIEW'),NULL,internal_avg_row_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`avg_row_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `AVG_ROW_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_max_data_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`max_data_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `MAX_DATA_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_index_length(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`index_length`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `INDEX_LENGTH`,if((`tbl`.`type` = 'VIEW'),NULL,internal_data_free(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`data_free`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `DATA_FREE`,if((`tbl`.`type` = 'VIEW'),NULL,internal_auto_increment(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`auto_increment`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0),`tbl`.`se_private_data`)) AS `AUTO_INCREMENT`,`tbl`.`created` AS `CREATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_update_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`update_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `UPDATE_TIME`,if((`tbl`.`type` = 'VIEW'),NULL,internal_check_time(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(cast(`stat`.`check_time` as unsigned),0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECK_TIME`,`col`.`name` AS `TABLE_COLLATION`,if((`tbl`.`type` = 'VIEW'),NULL,internal_checksum(`sch`.`name`,`tbl`.`name`,if((`tbl`.`partition_type` is null),`tbl`.`engine`,''),`tbl`.`se_private_id`,(`tbl`.`hidden` <> 'Visible'),`ts`.`se_private_data`,coalesce(`stat`.`checksum`,0),coalesce(cast(`stat`.`cached_time` as unsigned),0))) AS `CHECKSUM`,if((`tbl`.`type` = 'VIEW'),NULL,get_dd_create_options(`tbl`.`options`,if((ifnull(`tbl`.`partition_expression`,'NOT_PART_TBL') = 'NOT_PART_TBL'),0,1),if((`sch`.`default_encryption` = 'YES'),1,0))) AS `CREATE_OPTIONS`,internal_get_comment_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`,`tbl`.`comment`) AS `TABLE_COMMENT` from (((((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) left join `mysql`.`collations` `col` on((`tbl`.`collation_id` = `col`.`id`))) left join `mysql`.`tablespaces` `ts` on((`tbl`.`tablespace_id` = `ts`.`id`))) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))) where ((0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`)))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

数据字典缓存

为了减少磁盘IO，提高访问效率，MySQL 8.0引入了数据字典缓存。数据字典缓存是一块全局共享区域，通过LRU算法进行内存管理，具体包括：

tablespace definition cache partition：用于缓存表空间定义对象；大小限制由参数tablespace_definition_cache决定。
schema definition cache partition：用于缓存模式定义对象；大小限制由参数schema_definition_cache决定。
table definition cache partition：用于缓存表定义对象；大小限制由参数max_connections决定。
stored program definition cache partition：用于缓存存储过程定义对象；大小限制由参数stored_program_definition_cache决定。
character set definition cache partition：用于缓存字符集定义对象；硬编码限制256个。
collation definition cache partition：用于缓存排序规则定义对象；硬编码限制256个。

原子DDL

首先，了解一下什么是原子性？原子性是指，一个事务执行要么全部成功，要么全部失败。

在MySQL 8.0之前，由于不支持原子DDL，在服务进程异常挂掉或服务器异常宕机的情况下，有可能会导致数据字典、存储引擎结构、二进制日志之间的不一致。

在MySQL 8.0中，数据字典均被改造成InnoDB存储引擎表，原子DDL也被引入进来。原子DDL是将数据字典更新、存储引擎操作、二进制日志写入放到同一个事务里执行，要么全部成功提交，要么全部失败回滚。

接下来，我们还是先通过一个例子，来了解一下原子DDL。在这个例子中，DROP TABLE t1, t2属于同一个事务；在5.7版本中，出现了一个事务部分、成功部分失败的情况，即DROP TABLE t1成功、DROP TABLE t2失败；但在8.0版本中，因为DROP TABLE t2失败，导致整个事务全部失败回滚；这个例子就很好地体现了原子性和非原子性的区别。

5.7版本：
mysql> CREATE TABLE t1 (c1 INT);
mysql> DROP TABLE t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> SHOW TABLES;
Empty set (0.00 sec)

8.0版本：
mysql> CREATE TABLE t1 (c1 INT);
mysql> DROP TABLE t1, t2;
ERROR 1051 (42S02): Unknown table 'test.t2'
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1       |
+----------------+

在对原子DDL有初步了解后，接下来介绍一下具体过程：

（1）prepare：创建需要的对象，并将ddl日志写入到mysql.innodb_ddl_log；ddl日志记录了如何前滚和回滚ddl操作。
（2）perform：执行ddl操作。
（3）commit：更新数据字典并提交。
（4）post-ddl：重放和删除ddl日志。只有在实例异常宕机情况下，ddl日志才会继续保存在mysql.innodb_ddl_log；在在实例重启后，进行实例恢复阶段，ddl日志会重放和删除；如果第3步-数据字典更新已经成功提交，并写入redo log和binlog，那么ddl操作成功；否则，ddl操作失败，并根据ddl日志进行回滚

最后，再介绍一下，怎么查看DDL日志？

其中一个方法，是在debug级别下，访问表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)
);

另一个办法，是可以将DDL日志打印到error log进行查看(推荐)

mysql> set global innodb_print_ddl_logs=on;
Query OK, 0 rows affected (0.00 sec)

mysql> set global LOG_ERROR_VERBOSITY=3;
Query OK, 0 rows affected (0.00 sec)

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

$ tail -100f mysql-error.log
2020-08-17T19:55:09.804345+08:00 73 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=57, thread_id=73, space_id=12, old_file_path=./test/test.ibd]
2020-08-17T19:55:09.804396+08:00 73 [Note] [MY-012478] [InnoDB] DDL log delete : 57
2020-08-17T19:55:09.816850+08:00 73 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=58, thread_id=73, table_id=1069, new_file_path=test/test]
2020-08-17T19:55:09.816887+08:00 73 [Note] [MY-012478] [InnoDB] DDL log delete : 58
2020-08-17T19:55:09.820623+08:00 73 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=59, thread_id=73, space_id=12, index_id=160, page_no=4]
2020-08-17T19:55:09.820673+08:00 73 [Note] [MY-012478] [InnoDB] DDL log delete : 59
2020-08-17T19:55:09.837695+08:00 73 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 73
2020-08-17T19:55:09.837721+08:00 73 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 73

总结

MySQL 8.0对于数据字典的改进，带来了很多好处，包括元数据统一管理、数据字典缓存、information_schema性能提升、原子DDL等等。

以上就是解析MySQL8.0新特性——事务性数据字典与原子DDL的详细内容，更多关于MySQL8.0新特性的资料请关注其它相关文章！

华山资源网 Design By www.eoogi.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
华山资源网 Design By www.eoogi.com
www.eoogi.com 华山资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
华山资源网 Design By www.eoogi.com