ADSL拨号VPS包含了中国大陆（联通，移动，电信，）

中国香港，国外拨号VPS。

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: utf8collation_connection: utf8_general_ci1 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.log2020-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 : 572020-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 : 582020-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 : 592020-08-17T19:55:09.837695+08:00 73 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 732020-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新特性的资料请关注脚本之家其它相关文章！

(责任编辑：admin)