【MySQL】 MySQL 8.0 新特性之数据字典

MySQL Server 8.0 合并了一个全局的事务数据字典，用于存储有关数据库对象的信息，也有了原子DDL功能。在 MySQL 以前的版本中，字典数据存储在元数据文件、非事务性表和特定于存储引擎的数据字典中。

在学习 MySQL 数据字典之前，先一起了解一下什么是数据字典和元数据：

• 数据字典就类似于系统编目或花名册，它保存数据库服务器上的元数据信息（数据库的整体属性信息）。

• 元数据信息包括：数据库的属性信息、数据表的属性信息、字段的属性信息、视图的属性信息、用户信息、统计类信息等。

1. 数据字典

数据字典表是受保护的，只有在 MySQL 的 debug 版本中才能访问。但是，MySQL 支持通过 INFORMATION_SCHEMA 表和 SHOW 命令来访问存储在数据字典表中的数据。

MySQL 系统表在 MySQL 8.0 中仍然存在，可以通过 SHOW TABLES 语句在 MySQL 系统数据库来查看。MySQL 数据字典表和系统表的区别在于，数据字典表包含执行SQL查询所需的元数据，而系统表包含时区、帮助信息等辅助数据。

1.1 数据字典如何升级

MySQL 数据字典升级通常是随着整个MySQL服务器版本的升级而自动完成的一部分过程。数据字典是MySQL内部用来存储关于数据库对象（如表、列、索引等）的元数据的地方，特别是在MySQL 8.0以后引入了改进的数据字典体系结构，使得管理更加可靠和高效。

以下是MySQL数据字典升级的一般步骤：

1. 选择合适的升级路径：

   • 当您从一个低版本（如5.7）升级到MySQL 8.0及以上版本时，首先需要确定这是一个支持的升级路径，并遵循官方提供的指南。

2. 备份数据：

   • 在进行任何重大升级之前，务必先完整备份您的数据库，包括数据文件和相关的配置文件。

3. 关闭MySQL服务：

   • 卸载旧版本的MySQL服务，并停止运行的所有MySQL实例。

4. 安装新版本的MySQL：

   • 下载并安装符合您操作系统的MySQL最新稳定版二进制包或源代码并进行编译安装。

5. 升级数据字典：

   • 启动新版本的MySQL服务后，系统会自动检测到现有数据目录，并在启动过程中更新数据字典和其他内部元数据表，这通常由mysql_upgrade工具来完成。

 sudo mysql_upgrade

6. 验证升级结果：

   • 升级完成后，检查MySQL服务器日志以确认升级成功，并验证数据库和表的功能正常。

7. 解决兼容性问题：

   • 根据MySQL官方文档提示，处理任何因新版本带来的语法变更、功能弃用或其他可能影响现有应用程序的兼容性问题。
     请注意，对于大规模生产环境下的MySQL升级，还需要考虑更多因素，例如停机窗口、滚动升级策略以及在升级过程中对应用程序连接行为的影响等。

1.2 使用 MySQL 的 debug 版本查看数据字典表

数据字典表是受保护的，只有在 MySQL 的 debug 版本中才能访问。编译 debug 版本参考：Compiling MySQL for Debugging

不建议直接修改或写入数据字典表，这可能会导致您的 MySQL 实例无法运行。

• 使用调试支持编译 MySQL 后，使用此 SET 语句使数据字典表对mysql客户端会话可见：

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';

• 用于 SHOW CREATE TABLE 查看数据字典表定义。例如：

mysql> SHOW CREATE TABLE mysql.catalogs\G

2. 删除了基于文件的元数据存储

在以前的 MySQL 版本中，字典数据部分存储在元数据文件中。基于文件的元数据存储有以下问题：

• 昂贵的文件扫描成本；
• 对文件系统相关错误的敏感性太低；
• 会有一大堆处理复制和容灾故障恢复的复杂代码；
• 缺乏可扩展性导致难以为新功能和关系对象添加元数据。

下面列出已从 MySQL 中删除的元数据文件。除非另有说明，以前存储在元数据文件中的数据现在存储在数据字典表中。

• .frm 文件：表定义文件，是描述数据表结构的文件。、

• .par 文件：分区定义文件。 InnoDB 引入了对 InnoDB 表的本机分区支持后，在 MySQL 5.7 中停止使用分区定义文件

• TRN 文件：触发命名空间文件。

• .TRG 文件：触发参数文件。

• .isl 文件：InnoDB 符号链接文件，包含在数据目录之外创建的 file-per-table 表空间文件的位置。

• db.opt 文件：数据库配置文件。这些文件（每个数据库目录一个）包含数据库默认字符集属性。

• ddl_log.log文件：该文件包含由数据定义语句（例如：DROP TABLE 和ALTER TABLE）生成的元数据操作的记录。

3. 字典数据的事务存储

数据字典模式将字典数据存储在事务 ( InnoDB) 表中。数据字典表与非数据字典系统表一起位于 MySQL 数据库中。

数据字典表是在一个名为 mysql.ibd 的 InnoDB 表空间中创建的，它位于 MySQL 数据目录中。mysql.ibd 表空间文件必须位于 MySQL 数据目录下，并且其名称不能被其他表空间修改或使用。

字典数据受提交、回滚和崩溃恢复功能的保护，与保护存储在InnoDB表中的用户数据相同。

4. 数据字典对象缓存

字典对象缓存是一个共享的全局缓存，它将以前访问的数据字典对象存储在内存中，以支持对象重用和最小化磁盘I/O。与 MySQL 使用的其他缓存机制类似，字典对象缓存使用基于lru的驱逐策略从内存中驱逐最近最少使用的对象。

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

字典对象缓存包括存储不同对象类型的缓存分区。一些缓存分区大小限制是可配置的，而另一些则是硬编码的。

• tablespace_definition_cache - 表空间定义缓存分区：存储表空间定义对象，默认值为 256。

• schema_definition_cache - 架构定义缓存分区：存储架构定义对象，默认值为 256。

• table_definition_cache - 表定义缓存分区，存储表定义对象。

• stored_program_definition_cache - 存储程序定义缓存分区：存储存储程序定义对象，默认值为 256。

• 字符集定义缓存分区：存储字符集定义对象，硬编码对象限制为 256。

• 归类定义缓存分区：存储归类定义对象，硬编码对象限制为 256。

5. INFORMATION_SCHEMA和数据字典集成

随着数据字典的引入，一些 INFORMATION_SCHEMA 表被实现为数据字典表的视图，对这些表的查询现在效率更高，因为它们从数据字典表获得信息，而不是通过其他较慢的方法。

5.1 查询优化

MySQL 8.0 数据字典存放在表中，对他们进行查询时，可以充分利用 SQL 语句的特性，如索引、连接等。而 MySQL 8.0 之前，需要从文件中读取数据。

MySQL 5.7

mysql> explain select * from information_schema.tables where table_schema = 'sbtest';
+----+-------------+--------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref  | rows | filtered | Extra                                            |
+----+-------------+--------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------------------------------+
|  1 | SIMPLE      | tables | NULL       | ALL  | NULL          | TABLE_SCHEMA | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned 1 database |
+----+-------------+--------+------------+------+---------------+--------------+---------+------+------+----------+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
-----------------------------------

MySQL 8.0

mysql> explain select * from information_schema.tables where table_schema = 'sbtest';
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
| 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 | NULL        |
|  1 | SIMPLE      | tbl   | NULL       | ref    | schema_id          | schema_id  | 8       | mysql.sch.id            |   92 |   100.00 | Using where |
|  1 | SIMPLE      | col   | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.collation_id  |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | ts    | NULL       | eq_ref | PRIMARY            | PRIMARY    | 8       | mysql.tbl.tablespace_id |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | stat  | NULL       | eq_ref | PRIMARY            | PRIMARY    | 388     | const,mysql.tbl.name    |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+--------------------+------------+---------+-------------------------+------+----------+-------------+
6 rows in set, 1 warning (0.73 sec)