MySQL 8.0新特性：数据字典

DBA的辛酸事儿 2021-04-07

1221

目前MySQL 8.0最新版本为8.0.23版本，针对8.0的新特性，从春节前开始做了一些相关学习和测试，后续会不阶段的分享一些8.0的新特性，供大家一起参考和学习；

本文主要针对MySQL 8.0数据字典的变化做相关介绍；

一、概述

数据字典(Data Dictionary)中存储了诸多数据库的元数据信息，包括基本Database, table, index, column, function, trigger, procedure，privilege等；以及与存储引擎相关的元数据，如InnoDB的tablespace, table_id, index_id等。MySQL8.0在数据字典上进行了诸多优化，下面会针对MySQL 8.0的数据字典做相关优化做详细的介绍。

二、MySQL8.0 数据字典

MySQL Server包含一个事务性数据字典，该字典存储有关数据库对象的信息。在以前的MySQL版本中，字典数据存储在元数据文件，非事务表和存储引擎特定的数据字典中。

下面介绍下数据字典的主要功能，优势，用法差异和局限性

MySQL数据字典的优点包括：

（1）统一存储字典数据的集中式数据字典架构的简单性。
（2）删除基于文件的元数据存储。
（3）事务性，崩溃安全的字典数据存储。
（4）字典对象的统一和集中式缓存。。
（5）一些INFORMATION_SCHEMA表的更简单和改进的实现。INFORMATION_SCHEMA和数据字典集成”
（6）原子DDL。

1、数据字典结构

8.0之前的数据字典

在介绍MySQL8.0的数据字典前，先回顾一下MySQL8.0之前的数据字典。

8.0之前，旧的数据字典信息分布在server层，mysql库下的系统表和InnoDB内部系统表三个地方，其中保存的信息分别如下所示：

server层文件

.frm files: Table metadata files. 
.par files: Partition definition files. InnoDB stopped using partition definition files in MySQL 5.7 with the introduction of native partitioning support for InnoDB tables.
.TRN files: Trigger namespace files.
.TRG files: Trigger parameter files.
.isl files: InnoDB Symbolic Link files containing the location of file-per-table tablespace files created outside of the data directory.
.db.opt files: Database configuration files. These files, one per database directory, contained database default character set attributes.

mysql库下的系统表 mysql.user mysql.db mysql.proc mysql.event等

InnoDB内部系统表

SYS_DATAFILES
SYS_FOREIGN
SYS_FOREIGN_COLS
SYS_TABLESPACES
SYS_VIRTUAL

8.0之前数据字典存在的问题

（1）数据字典分散存储，维护管理没有统一接口
（2）MyISAM系统表易损坏
（3）DDL没有原子性，server层与innodb层数据字典容易不一致
（4）文件存储数据字典扩展性不好
（5）通过information_schema查询数据字典时生成临时表不友好

8.0版本的数据字典

1、鉴于旧数据字典的种种缺点，MySQL8.0对数据字典进行了较大的改动：把所有的元数据信息都存储在InnoDB dictionary table中，并且存储在单独的表空间mysql.ibd里

-rw-r----- 1 mysql mysql         56 Jan 28 15:21 auto.cnf
-rw------- 1 mysql mysql       1680 Jan 28 15:21 ca-key.pem
-rw-r--r-- 1 mysql mysql       1120 Jan 28 15:21 ca.pem
-rw-r--r-- 1 mysql mysql       1120 Jan 28 15:21 client-cert.pem
-rw------- 1 mysql mysql       1676 Jan 28 15:21 client-key.pem
-rw-r----- 1 mysql mysql      22386 Feb  1 14:03 ib_buffer_pool
-rw-r----- 1 mysql mysql 1073741824 Feb  1 16:43 ibdata1
-rw-r----- 1 mysql mysql   12582912 Feb  1 14:04 ibtmp1
drwxr-x--- 2 mysql mysql          6 Jan 28 15:21 infra
drwxr-x--- 2 mysql mysql        187 Feb  1 14:04 #innodb_temp
drwxr-x--- 2 mysql mysql        143 Jan 28 15:21 mysql
-rw-r----- 1 mysql mysql   25165824 Feb  1 16:43 mysql.ibd
drwxr-x--- 2 mysql mysql       8192 Jan 28 15:21 performance_schema
-rw------- 1 mysql mysql       1680 Jan 28 15:21 private_key.pem
-rw-r--r-- 1 mysql mysql        452 Jan 28 15:21 public_key.pem
drwxr-x--- 2 mysql mysql         31 Feb  1 15:41 query_rewrite
-rw-r--r-- 1 mysql mysql       1120 Jan 28 15:21 server-cert.pem
-rw------- 1 mysql mysql       1680 Jan 28 15:21 server-key.pem
drwxr-x--- 2 mysql mysql         28 Jan 28 15:21 sys
-rw-r----- 1 mysql mysql   11534336 Feb  1 16:43 undo_001
-rw-r----- 1 mysql mysql   11534336 Feb  1 16:43 undo_002
drwxr-x--- 2 mysql mysql       4096 Feb  1 15:45 wjqtest
-rw-r----- 1 mysql mysql    3932160 Feb  1 16:43 xb_doublewrite

2、将所有原所有原先存放于数据字典文件中的信息，全部存放到数据库系统表中，即将之前版本的.frm,.opt,.par,.TRN,.TRG,.isl文件都移除了，不再通过文件的方式存储数据字典信息

-rw-r----- 1 mysql mysql    114688 Jan 28 16:22 crl_backcmd_syndata.ibd
-rw-r----- 1 mysql mysql  83886080 Jan 28 16:24 crl_solidify_info.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:24 dic_awards_cfg.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:24 dic_crladddao_def.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:24 dic_crlcond_def.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:24 dic_datadetail_cfg.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:24 dic_dyninfo_def.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:24 dic_group_def.ibd
-rw-r----- 1 mysql mysql    147456 Jan 28 16:24 dic_groupranklist_def.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:24 dic_matchcrlcond_def.ibd
-rw-r----- 1 mysql mysql 427819008 Jan 28 16:27 dic_push_data_detail.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:27 dic_rank_trigger.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:27 dic_redissvrinfo_def.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:27 dic_relaward_def.ibd
-rw-r----- 1 mysql mysql    114688 Jan 28 16:27 match_crl_solidify_info.ibd
-rw-r----- 1 mysql mysql    131072 Feb  1 16:43 qrw_test.ibd
-rw-r----- 1 mysql mysql    114688 Feb  1 14:13 tab1.ibd
-rw-r----- 1 mysql mysql    114688 Feb  1 14:22 tab2.ibd

3、mysql库下的原有系统表由MyISAM转为了InnoDB表，没有了proc和event表，直接改存到了dictionary table中。新增component，default_roles，global_grants，password_history，role_edges表

2、数据字典对象缓存

字典对象高速缓存是一个共享的全局高速缓存，该高速缓存将先前访问的数据字典对象存储在内存中，以支持对象重用并最小化磁盘I O，字典对象缓存使用基于 LRU的逐出策略从内存中逐出最近最少使用的对象

show variables like '%definition%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| schema_definition_cache         | 256   |
| stored_program_definition_cache | 256   |
| table_definition_cache          | 1400  |
| tablespace_definition_cache     | 256   |
+---------------------------------+-------+
4 rows in set (0.03 sec)

说明：

table_definition_cache：存储表定义
schema_definition_cache：存储schema定义
stored_program_definition_cache：存储proc和func定义
tablespace_definition_cache：存储tablespace定义

4、INFORMATION_SCHEMA和数据字典集成

4.1 INFORMATION_SCHEMA表被实现为数据字典表的视图（35个）

表名	用途
CHARACTER_SETS	提供有关可用字符集的信息
CHECK_CONSTRAINTS	提供有关CHECK在表上定义的约束的信息
COLLATIONS	提供有关每个字符集的排序规则的信息
COLLATION_CHARACTER_SET_APPLICABILITY	指示什么字符集适用于什么排序规则
COLUMNS	提供表的列相关信息
COLUMN_STATISTICS	提供对列值的直方图统计信息的访问
EVENTS	提供了有关事件管理器事件的信息
FILES	提供有关存储MySQL表空间数据的文件的信息
INNODB_COLUMNS	提供有关InnoDB表列的元数据
INNODB_DATAFILES	提供了InnoDB 每个表文件和常规表空间的数据文件路径信息
INNODB_FIELDS	供有关InnoDB索引的关键列（字段）的元数据
INNODB_FOREIGN	提供有关InnoDB 外键的元数据
INNODB_FOREIGN_COLS	提供有关InnoDB 外键列的状态信息
INNODB_INDEXES	提供了有关InnoDB索引的元数据
INNODB_TABLES	提供有关InnoDB表的元数据
INNODB_TABLESPACES	提供有关InnoDB每表文件，常规表和撤消表空间的元数据
INNODB_TABLESPACES_BRIEF	提供了每个表文件，常规表，撤消表和系统表空间的空间ID，名称，路径，标志和空间类型元数据
INNODB_TABLESTATS	提供了有关InnoDB表的低级状态信息的视图
KEY_COLUMN_USAGE	描述了哪些键列具有约束。该表不提供有关功能键部分的信息，因为它们是表达式，并且该表仅提供有关列的信息
KEYWORDS
PARAMETERS	提供有关存储例程（存储过程和存储函数）的参数的信息，以及有关存储函数的返回值的信息。该PARAMETERS 表不包括内置SQL函数或用户定义函数（UDF）
PARTITIONS	提供有关表分区的信息
REFERENTIAL_CONSTRAINTS	提供有关外键的信息
RESOURCE_GROUPS	提供对有关资源组信息的访问
ROUTINES	提供有关存储例程（存储过程和存储函数）的信息。该ROUTINES表不包括内置SQL函数或用户定义函数（UDF）
SCHEMATA	提供了有关数据库的信息
STATISTICS	提供有关表索引的信息
ST_GEOMETRY_COLUMNS	提供有关存储空间数据的表列的信息
ST_SPATIAL_REFERENCE_SYSTEMS	提供有关空间数据的可用空间参考系统（SRS）的信息。该表基于SQL MM（ISO IEC 13249-3）标准
TABLES	提供有关数据库中表的信息
TABLE_CONSTRAINTS	描述了哪些表具有约束
TRIGGERS	提供有关触发器的信息
VIEWS	提供有关数据库中的视图的信息
VIEW_ROUTINE_USAGE	（从MySQL 8.0.13开始可用）提供对视图定义中使用的存储函数的信息的访问。该表未列出有关定义中使用的内置SQL函数或用户定义函数（UDF）的信息。
VIEW_TABLE_USAGE	（从MySQL 8.0.13开始可用）提供对有关视图定义中使用的表和视图的信息的访问

4.2 通过information_schema查询时不再需要生成临时表获取，而是直接从数据字典表获取

8.0之前：

show create table SCHEMATA\G
*************************** 1. row ***************************
       Table: SCHEMATA
Create Table: CREATE TEMPORARY TABLE `SCHEMATA` (
  `CATALOG_NAME` varchar(512) NOT NULL DEFAULT '',
  `SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '',
  `DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
  `DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',
  `SQL_PATH` varchar(512) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8

8.0之后：

*************************** 1. row ***************************
                View: SCHEMATA
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `SCHEMATA` AS select `cat`.`name` AS `CATALOG_NAME`,`sch`.`name` AS `SCHEMA_NAME`,`cs`.`name` AS `DEFAULT_CHARACTER_SET_NAME`,`col`.`name` AS `DEFAULT_COLLATION_NAME`,NULL AS `SQL_PATH`,`sch`.`default_encryption` AS `DEFAULT_ENCRYPTION` from (((`mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `col` on((`sch`.`default_collation_id` = `col`.`id`))) join `mysql`.`character_sets` `cs` on((`col`.`character_set_id` = `cs`.`id`))) where (0 <> can_access_database(`sch`.`name`))
character_set_client: utf8
collation_connection: utf8_general_ci

4.3 information_schema查询以view的形式展现，更利于优化器优化查询

8.0之前：

EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'hive';
+----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key                     | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | TABLES | NULL       | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL |     NULL | Using where; Skip_open_table; Scanned 0 databases |
+----+-------------+--------+------------+------+---------------+-------------------------+---------+------+------+----------+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

8.0之后

EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'wjqtest' AND TABLE_NAME = 'qrw_test';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys      | key        | key_len | ref                     | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
|  1 | SIMPLE      | cat   | NULL       | index  | PRIMARY            | name       | 194     | NULL                    |    1 |   100.00 | Using index |
|  1 | SIMPLE      | sch   | NULL       | eq_ref | PRIMARY,catalog_id | catalog_id | 202     | mysql.cat.id,const      |    1 |   100.00 | Using index |
|  1 | SIMPLE      | tbl   | NULL       | eq_ref | schema_id          | schema_id  | 202     | mysql.sch.id,const      |    1 |   100.00 | Using where |
|  1 | SIMPLE      | col   | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.collation_id  |    1 |   100.00 | Using index |
|  1 | SIMPLE      | ts    | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.tablespace_id |    1 |   100.00 | Using index |
|  1 | SIMPLE      | stat  | NULL       | const  | PRIMARY            | PRIMARY    | 388     | const,const             |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
6 rows in set, 1 warning (0.01 sec)

5、序列化字典信息（SDI）

MySQL8.0不仅将元数据信息存储在数据字典表中，同时也冗余存储了一份在SDI中。对于非InnoDB表，SDI数据在后缀为.sdi的文件中，而对于innodb，SDI数据则直接存储与ibd中

create table t1(c1 int primary key auto_increment,name varchar(30)) engine=InnoDB;
create table t2(c1 int primary key auto_increment,name varchar(30)) engine=MyISAM;

表数据文件：

-rw-r----- 1 mysql mysql    114688 Feb  2 12:16 t1.ibd
-rw-r----- 1 mysql mysql      2769 Feb  2 12:17 t2_377.sdi
-rw-r----- 1 mysql mysql         0 Feb  2 12:17 t2.MYD
-rw-r----- 1 mysql mysql      1024 Feb  2 12:17 t2.MYI

5.1 非事务表

上述例子中MyISAM表t2的SDI为wjqdb/t2_377.sdi，其中377为table_id, t2_377.sdi可以直接打开，数据是json格式(cat wjqdb/t2_377.sdi)：

cat t2_377.sdi | python -m json.tool
{
    "dd_object": {
        "check_constraints": [],
        "collation_id": 33,
        "columns": [
            {
                "char_length": 11,
                "collation_id": 33,
                "column_key": 2,
                "column_type_utf8": "int",
                "comment": "",
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "default_option": "",
                "default_value": "AAAAAA==",
                "default_value_null": false,
                "default_value_utf8": "",
                "default_value_utf8_null": true,
                "elements": [],
                "engine_attribute": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "has_no_default": false,
                "hidden": 1,
                "is_auto_increment": true,
                "is_explicit_collation": false,
                "is_nullable": false,
                "is_unsigned": false,
                "is_virtual": false,
                "is_zerofill": false,
                "name": "c1",
                "numeric_precision": 10,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "options": "interval_count=0;",
                "ordinal_position": 1,
                "se_private_data": "",
                "secondary_engine_attribute": "",
                "srs_id": 0,
                "srs_id_null": true,
                "type": 4,
                "update_option": ""
            },
            {
                "char_length": 90,
                "collation_id": 33,
                "column_key": 1,
                "column_type_utf8": "varchar(30)",
                "comment": "",
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "default_option": "",
                "default_value": "",
                "default_value_null": true,
                "default_value_utf8": "",
                "default_value_utf8_null": true,
                "elements": [],
                "engine_attribute": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "has_no_default": false,
                "hidden": 1,
                "is_auto_increment": false,
                "is_explicit_collation": false,
                "is_nullable": true,
                "is_unsigned": false,
                "is_virtual": false,
                "is_zerofill": false,
                "name": "name",
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "options": "interval_count=0;",
                "ordinal_position": 2,
                "se_private_data": "",
                "secondary_engine_attribute": "",
                "srs_id": 0,
                "srs_id_null": true,
                "type": 16,
                "update_option": ""
            }
        ],
        "comment": "",
        "created": 20210202041702,
        "default_partitioning": 0,
        "default_subpartitioning": 0,
        "engine": "MyISAM",
        "engine_attribute": "",
        "foreign_keys": [],
        "hidden": 1,
        "indexes": [
            {
                "algorithm": 2,
                "comment": "",
                "elements": [
                    {
                        "column_opx": 0,
                        "hidden": false,
                        "length": 4,
                        "order": 2,
                        "ordinal_position": 1
                    }
                ],
                "engine": "MyISAM",
                "engine_attribute": "",
                "hidden": false,
                "is_algorithm_explicit": false,
                "is_generated": false,
                "is_visible": true,
                "name": "PRIMARY",
                "options": "flags=0;",
                "ordinal_position": 1,
                "se_private_data": "",
                "secondary_engine_attribute": "",
                "type": 1
            }
        ],
        "last_altered": 20210202041702,
        "last_checked_for_upgrade_version_id": 0,
        "mysql_version_id": 80023,
        "name": "t2",
        "options": "avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "partition_expression": "",
        "partition_expression_utf8": "",
        "partition_type": 0,
        "partitions": [],
        "row_format": 2,
        "schema_ref": "wjqdb",
        "se_private_data": "",
        "se_private_id": 18446744073709551615,
        "secondary_engine_attribute": "",
        "subpartition_expression": "",
        "subpartition_expression_utf8": "",
        "subpartition_type": 0
    },
    "dd_object_type": "Table",
    "dd_version": 80023,
    "mysqld_version_id": 80023,
    "sdi_version": 80019
}

5.2 InnoDB事务表

上述例子中的InnoDB表t1的SDI则可以通过工具ibd2sdi可以解析出来(ibd2sdi wjqdb/t1.ibd)：

ibd2sdi t1.ibd     
["ibd2sdi"
,
{
        "type": 1,
        "id": 376,
        "object":
                {
    "mysqld_version_id": 80023,
    "dd_version": 80023,
    "sdi_version": 80019,
    "dd_object_type": "Table",
    "dd_object": {
        "name": "t1",
        "mysql_version_id": 80023,
        "created": 20210202041648,
        "last_altered": 20210202041648,
        "hidden": 1,
        "options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
        "columns": [
            {
                "name": "c1",
                "type": 4,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": true,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 1,
                "char_length": 11,
                "numeric_precision": 10,
                "numeric_scale": 0,
                "numeric_scale_null": false,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": false,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "AAAAAA==",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "table_id=1078;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 2,
                "column_type_utf8": "int",
                "elements": [],
                "collation_id": 33,
                "is_explicit_collation": false
            },
            {
                "name": "name",
                "type": 16,
                "is_nullable": true,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 1,
                "ordinal_position": 2,
                "char_length": 90,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "interval_count=0;",
                "se_private_data": "table_id=1078;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "varchar(30)",
                "elements": [],
                "collation_id": 33,
                "is_explicit_collation": false
            },
            {
                "name": "DB_TRX_ID",
                "type": 10,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 3,
                "char_length": 6,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "table_id=1078;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            },
            {
                "name": "DB_ROLL_PTR",
                "type": 9,
                "is_nullable": false,
                "is_zerofill": false,
                "is_unsigned": false,
                "is_auto_increment": false,
                "is_virtual": false,
                "hidden": 2,
                "ordinal_position": 4,
                "char_length": 7,
                "numeric_precision": 0,
                "numeric_scale": 0,
                "numeric_scale_null": true,
                "datetime_precision": 0,
                "datetime_precision_null": 1,
                "has_no_default": false,
                "default_value_null": true,
                "srs_id_null": true,
                "srs_id": 0,
                "default_value": "",
                "default_value_utf8_null": true,
                "default_value_utf8": "",
                "default_option": "",
                "update_option": "",
                "comment": "",
                "generation_expression": "",
                "generation_expression_utf8": "",
                "options": "",
                "se_private_data": "table_id=1078;",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "column_key": 1,
                "column_type_utf8": "",
                "elements": [],
                "collation_id": 63,
                "is_explicit_collation": false
            }
        ],
        "schema_ref": "wjqdb",
        "se_private_id": 1078,
        "engine": "InnoDB",
        "last_checked_for_upgrade_version_id": 0,
        "comment": "",
        "se_private_data": "autoinc=0;version=0;",
        "engine_attribute": "",
        "secondary_engine_attribute": "",
        "row_format": 2,
        "partition_type": 0,
        "partition_expression": "",
        "partition_expression_utf8": "",
        "default_partitioning": 0,
        "subpartition_type": 0,
        "subpartition_expression": "",
        "subpartition_expression_utf8": "",
        "default_subpartitioning": 0,
        "indexes": [
            {
                "name": "PRIMARY",
                "hidden": false,
                "is_generated": false,
                "ordinal_position": 1,
                "comment": "",
                "options": "flags=0;",
                "se_private_data": "id=168;root=4;space_id=19;table_id=1078;trx_id=6034;",
                "type": 1,
                "algorithm": 2,
                "is_algorithm_explicit": false,
                "is_visible": true,
                "engine": "InnoDB",
                "engine_attribute": "",
                "secondary_engine_attribute": "",
                "elements": [
                    {
                        "ordinal_position": 1,
                        "length": 4,
                        "order": 2,
                        "hidden": false,
                        "column_opx": 0
                    },
                    {
                        "ordinal_position": 2,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 2
                    },
                    {
                        "ordinal_position": 3,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 3
                    },
                    {
                        "ordinal_position": 4,
                        "length": 4294967295,
                        "order": 2,
                        "hidden": true,
                        "column_opx": 1
                    }
                ],
                "tablespace_ref": "wjqdb/t1"
            }
        ],
        "foreign_keys": [],
        "check_constraints": [],
        "partitions": [],
        "collation_id": 33
    }
}
}
,
{
        "type": 2,
        "id": 24,
        "object":
                {
    "mysqld_version_id": 80023,
    "dd_version": 80023,
    "sdi_version": 80019,
    "dd_object_type": "Tablespace",
    "dd_object": {
        "name": "wjqdb/t1",
        "comment": "",
        "options": "autoextend_size=0;encryption=N;",
        "se_private_data": "flags=16417;id=19;server_version=80023;space_version=1;state=normal;",
        "engine": "InnoDB",
        "engine_attribute": "",
        "files": [
            {
                "ordinal_position": 1,
                "filename": "./wjqdb/t1.ibd",
                "se_private_data": "id=19;"
            }
        ]
    }
}
}
]

5.3 其他表空间的SDI

ibd2sdi mysql.ibd，可以查看所以mysql下的表，包括new dictionary和mysql下的普通表。需要注意的是ibdata1中不存放SDI信息，使用ibd2sdi解析它会出现以下提示：

[INFO] ibd2sdi: SDI is empty.

6、 8.0新数据字典带来的影响

6.1 INFORMATION_SCHEMA性能提升

数据库在查询INFORMATION_SCHEMA的表时，不再一定需要创建一张临时表，可以直接查询数据字典表
在之前版本中，数据字典信息不一定是存放于表中，所以在获取数据字典信息时候，不仅仅是查表操作。例如读取数据库表结构信息，底层其实是读取.frm文件来获得，是一个文件打开读取的操作。而在新版本中，数据字典信息都可以通过直接查表的方式获取，替代那些获取信息慢的方式
对存储引擎的改进之后，在查询INFORMATIONS_SCHEMA表时，如果表上有索引，优化器会合理的利用索引
对于INFORMATION_SCHEMA下的STATISTICS表和TABLES表中的信息，8.0中通字典对象过缓存的方式，以提高查询的性能。可以通过设置information_schema_stats_expiry参数设置缓存数据的过期时间，默认是86400秒。查询这两张表的数据的时候，首先是到缓存中进行查询，缓存中没有缓存数据，或者缓存数据过期了，查询会从存储引擎中获取最新的数据。如果需要获取最新的数据，可以通过设置information_schema_stats_expiry参数为0或者ANALYZE TABLE操作

6.2 原子DDL

MySQL8.0开始支持原子DDL操作，一个原子DDL操作，具体的操作内容包括：数据字典更新，存储引擎层的操作，在binlog中记录DDL操作。并且这些操作都是原子性的，表示中间过程出现错误的时候，是可以完整回退的。这在之前版本的DDL操作中是不支持的。之前数据库版本中一直没有支持原子DDL的特性，是有原因的，因为在早期的数据库版本中，数据库元信息存放于元信息文件中、非事务性表中以及特定存储引擎的数据字典中。这些都无法保证DDL操作内容在一个事务当中，无法保证原子性。

6.3 innodb_read_only对所有存储引擎生效

在8.0之前版本中，innodb_read_only参数可以阻止对InnoDB存储引擎表的create和drop等更新操作。但是在MySQL8.0中，开启innodb_read_only参数阻止了所有存储引擎的这些操作。create或者drop表的操作都需要更新数据字典表，8.0中这个数据字典表都改为了InnoDB存储引擎，所以对于数据字典表的更新会失败，从而导致各存储引擎create和drop表失败。同样的像ANALYZE TABLE和ALTER TABLE tbl_name ENGINE=engine_name这种操作也会失败，因为这些操作都要去更新数据字典表。

6.4 mysqldump mysqlpump导出的内容影响

之前版本的mysqldump和mysqlpump可以导出mysql系统库中的所有表的内容，8.0之后，只能导出mysql系统库中的非data dictionary table。
之前版本当使用–all-databases参数导出数据的时候，不加–routines和–events选项也可以导出触发器、存储过程等信息，因为这些信息都存放于proc和event表中，导出所有表即可导出这些信息。但是在8.0中，proc表和event表都不再使用，并且定义触发器、存储过程的数据字典表不会被导出，所以在8.0中使用mysqldump、mysqlpump导出数据的时候，如果需要导出触发器、存储过程等内容，一定需要加上–routines和–events选项。
之前版本中–routines选项导出的时候，备份账户需要有proc表的SELECT权限，在8.0中需要对所有表的SELECT权限
之前版本中，导出触发器、存储过程可以同时导出触发器、存储过程的创建和修改的时间戳，8.0中不再支持。

7、新数据字典的局限性

通过手动mkdir的方式在数据目录下创建库目录，这种方式是不会被数据库所识别到。
DDL操作会花费更长的时间，因为之前的DDL操作是直接对.frm文件进行更改操作，只要写一个文件，现在是需要更新数据字典表，代表着需要将数据写到存储引擎、read log、undo log中。

参考链接

https://dev.mysql.com/doc/refman/8.0/en/data-dictionary.html

https://cloud.tencent.com/developer/article/1427699

欢迎关注公众号

mysql

文章转载自DBA的辛酸事儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 8.0新特性： 数据字典