解析MySQL8.0新特性——事务性数据字典与原子DDL

前言

事务性数据字典与原子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)

解析MySQL8.0新特性——事务性数据字典与原子DDL

扫一扫手机访问