您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 505人已围观

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

前言

事务性数据字典与原子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 | IN
                提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                
                
                                            上一篇：Mysql| 使用通配符进行模糊查询详解(like,%,_)_Mysql_
                                                                下一篇：Mysql 命令行模式访问操作mysql数据库操作_Mysql_

您现在的位置是：网站首页> 编程资料编程资料

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

相关内容

点击排行

本栏推荐

猜你喜欢