《MySQL 8.0 参考手册》第 14 章 MySQL 数据字典_mysql 数据字典 工具

原文地址：MySQL 8.0 Reference Manual

MySQL 8.0 使用一个事务型的数据字典存储数据库对象的相关信息。在之前的 MySQL 版本中，字典数据存储在元数据文件、非事务型的表以及存储引擎相关的数据字典中。

本章介绍新数据字典的主要特性、优势、用法差异以及局限性。关于数据字典带来的其他影响，可以参考 MySQL 8.0 版本说明中的 “数据字典” 部分。

新的数据字典具有以下优势：

• 使用一个集中的数据字典模式，统一存储字典数据。 参见 第 14.1 节，“数据字典模式”。
• 删除了基于文件的元数据存储。参见 第 14.2 节， “删除基于文件的元数据”。
• 事务型、具有故障安全性的字典数据。参考 第
  14.3 节， “事务型数据字典”。
• 统一集中的数据字典对象缓存。参考 第
  14.4 节，“数据字典缓存”。
• 更加简单的实现并改进了某些 INFORMATION_SCHEMA 表。参考 第 14.5 节，“INFORMATION_SCHEMA 与数据字典集成”。
• 支持原子 DDL 语句。参见 第 13.1.1 节，“原子 DDL 语句”。

重要
使用数据字典的服务器与没有使用数据字典的服务器存在一些使用上的差异； 参见 第 14.7 节，“数据字典用法差异”。另外，升级到 MySQL 8.0 的过程与之前版本的升级存在一些不同之处，在升级之前需要执行某些检查，确认升级条件已经就绪。更多相关信息，可以参考, see 第 2.11.1 节，“升级 MySQL”，特别是 第 2.11.1.4 节， “升级前的准备”。

14.1 数据字典模式

数据字典表是受系统保护的，用户只有在 MySQL 调试版本中才能直接访问数据字典。不过，MySQL 支持通过 INFORMATION_SCHEMA 表和 SHOW 语句查看数据字典表中的数据。如果想要查看数据字典中包含哪些表，可以参考 数据字典表。

MySQL 8.0 中仍然存在系统表，并且可以通过在 mysql 系统数据库上执行 SHOW TABLES 语句进行查看。通常来说，MySQL 系统表与数字字典表的区别在于系统表存储的是一些辅助信息，例如时区和帮助信息；而数据字典表包含了执行 SQL 查询所需的信息。两者在升级方面也存在区别。升级 MySQL 系统表需要运行 mysql_upgrade 程序。数据字典的升级由 MySQL 服务器进行管理，参考下文。

数据字典升级过程

新版本的 MySQL 可能包含了数据字典表的修改。新安装的 MySQL 已经包含了这些变更，但是如果使用新的 MySQL 二进制文件进行就地升级，使用新版本的 MySQL 启动服务时应用这些变更。在服务器启动时，会将服务器的数据字典版本与数据字典中存储的版本信息进行比较，决定是否需要升级数据字典表。如果需要并且系统支持升级，服务器将会创建新的数据字典表，将存储的元数据复制到新的表中，使用新表替换旧表（原子性操作），并且重新初始化数据字典。如果不需要进行升级，服务器继续启动而不会升级数据字典表。

数据字典表的升级是一个原子操作，意味着所有的数据字典表都会成功升级，或者整个操作都会失败。如果升级失败，服务器启动时将会出错。此时，可以使用旧的二进制文件和旧的数据目录启动服务器。如果再次使用新的二进制文件启动服务器时，将会再次尝试数据字典的升级。

通常来说，成功升级数据字典表之后，无法再使用旧的二进制文件启动服务器。因此，数据字典表升级之后不支持 MySQL 服务器的降级。

可以使用 mysqld --no-dd-upgrade 选项阻止服务器启动时升级数据字典表。如果指定了 --no-dd-upgrade，当服务器发现它的数据字典版本与存储在数据字典表中版本不一致时，服务器将会启动失败，并且显示一个数据字典升级被禁止的错误。

使用 MySQL 调试版本查看数据字典表

默认情况下，数据字典表受系统保护，无法直接进行访问；但是如果使用支持调试的选项（使用 CMake 的 -DWITH_DEBUG=1 选项），并且指定 +d,skip_dd_table_access_check 调试选项和修饰符编译 MySQL，就可以访问数据字典表。关于调试版本的编译，可以参考 第 29.5.1.1 节， “MySQL 调试版本编译”。

警告
不推荐直接对数据字典表进行修改或者插入，可能会导致 MySQL 实例无法使用。

在使用调试选项编译 MySQL 之后，使用以下 SET 语句设置 mysql 客户端会话对于数据字典表的可见性：

mysql> SET SESSION debug='+d,skip_dd_table_access_check';
1

使用以下查询返回数据字典表的列表：

mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';
1

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

mysql> SHOW CREATE TABLE mysql.catalogs\G
1

14.2 删除基于文件的元数据

在之前的 MySQL 版本中，某些字典数据存储在元数据文件中。这种存储方法存在一些问题，包括访问时需要执行高成本的文件扫描，容易受到系统错误的影响，难以进行复制和故障时的恢复，同时缺少可扩展性，很难为新的特性和关系对象增加元数据。
MySQL 8.0 删除了以下元数据文件。除非另有说明，之前存储在元数据文件中的数据现在存储在数据字典表中。

• .frm 文件：表的元数据文件。随着 .frm 文件的删除，还带来了以下变化：
  • 删除了使用 .frm 文件时对于 64KB 表定义大小的限制。
  • INFORMATION_SCHEMA.TABLES 中的 VERSION 字段的值被硬编码成 10，表示最后在 MySQL 5.7 中使用的.frm 文件版本。
• .par 文件：分区定义文件。InnoDB 不再使用 MySQL 5.7 中的分区定义文件，而是使用 InnoDB 表的原生分区支持。
• .TRN 文件：触发器命名空间文件。
• .TRG 文件：触发器参数文件。
• .isl 文件：InnoDB 符号链接文件，它存储了在数据目录之外创建的表空间文件（file-per-table）的位置。
• db.opt 文件：数据库配置文件。这些文件（每个数据库目录一个文件）包含了数据库的默认字符集。

14.3 事务型数据字典

数据字典模式使用事务型（InnoDB）的表存储字典数据。数据字典表和非数据字典信息的系统表都位于 mysql 数据库中。

数据字典表使用单个 InnoDB 表空间进行统一存储，即 mysql.ibd，该文件位于 MySQL 数据目录中。mysql.ibd 文件必须位于 MySQL 数据目录中，并且不能修改该文件的名称，也不能被其他表空间使用。

字典数据与其他 InnoDB 表中的数据一样受到事务保护，包括提交、回滚和故障恢复。

14.4 数据字典缓存

数据字典缓存是一个全局的共享缓存区，用于缓存访问过的数据字典对象，以减少磁盘 I/O。与 MySQL 中的其他缓存机制一样，数据字典对象缓存使用一个基于 LRU 的删除策略，从内存中删除最近最少使用的对象。

数据字典缓存分为不同的缓存分区，用于缓存不同的对象类型。某些缓存分区的大小限制可以配置，而另一些缓存分区的大小是固定不变的（硬编码）。

• 表空间定义缓存分区：缓存表空间的定义。tablespace_definition_cache 选项用于设置能够缓存的表空间定义对象的数量。默认值为 256。

• 模式定义缓存分区：缓存模式的定义。schema_definition_cache 选项用于设置能够缓存的模式定义对象的数量。默认值为 256。

• 表定义缓存分区：缓存表的定义。能够缓存的表定义对象的数量等于 max_connections 的大小，默认值为 151。

  除了表定义缓存分区之外，还存在一个表定义缓存区，它使用 table_definition_cache 选项进行设置。两个缓存区都缓存了表的定义，但是用于不同的 MySQL 服务器组件。两个缓存中的对象之间没有依赖关系。

• 存储程序定义缓存分区：缓存存储程序的定义。stored_program_definition_cache 选项用于设置能够缓存的存储程序定义对象的数量。默认值为 256。

  除了存储程序定义缓存分区之外，还存在一个存储过程和存储函数的缓存区，它使用 stored_program_cache 选项进行配置。

  stored_program_cache 选项用于设置每个连接缓存的存储过程或函数的“软”上限，每次执行存储过程或函数时都会检查该上限。而另一方面，存储程序定义缓存分区是为其他用途缓存存储过程定义对象的一个共享缓存。存储程序定义缓存分区中的对象与存储过程缓存或存储函数缓存中的对象之间没有依赖关系。

• 字符集定义缓存分区：缓存字符集的定义，最多能够缓存 256 个定义对象。

• 排序规则定义缓存分区：缓存排序规则的定义，最多能够缓存 256 个定义对象。

关于数据字典对象缓存配置选项的可选值，参考 第 5.1.8 节，“服务器系统变量”。

14.5 INFORMATION_SCHEMA 与数据字典集成

随着新的数据字典的引入，以下 INFORMATION_SCHEMA 中的表被修改为基于数据字典表的视图：

• CHARACTER_SETS
• COLLATIONS
• COLLATION_CHARACTER_SET_APPLICABILITY
• COLUMNS
• COLUMN_STATISTICS
• EVENTS
• FILES
• INNODB_COLUMNS
• INNODB_DATAFILES
• INNODB_FIELDS
• INNODB_FOREIGN
• INNODB_FOREIGN_COLS
• INNODB_INDEXES
• INNODB_TABLES
• INNODB_TABLESPACES
• INNODB_TABLESPACES_BRIEF
• INNODB_TABLESTATS
• KEY_COLUMN_USAGE
• KEYWORDS
• PARAMETERS
• PARTITIONS
• REFERENTIAL_CONSTRAINTS
• RESOURCE_GROUPS
• ROUTINES
• SCHEMATA
• STATISTICS
• ST_GEOMETRY_COLUMNS
• ST_SPATIAL_REFERENCE_SYSTEMS
• TABLES
• TABLE_CONSTRAINTS
• TRIGGERS
• VIEWS
• VIEW_ROUTINE_USAGE
• VIEW_TABLE_USAGE

现在，针对这些表的查询语句将会更加高效，因为它们通过数据字典表获取信息，而不是其他的更慢的方式。尤其是那些基于数据字典表的实现的 INFORMATION_SCHEMA 视图表：

• 服务器不再需要为每个查询的 INFORMATION_SCHEMA 表创建一个临时表。
• 对于之前需要执行目录扫描（例如，列举数据库名称或数据库中的表名）或者读取文件（例如，从 .frm 文件中读取信息）访问的底层数据字典表信息，现在查询 INFORMATION_SCHEMA 表时使用表查找的方式获取数据。（除此之外，即使是非视图的 INFORMATION_SCHEMA 表，例如数据库名称和表名这样的值也是从数据字典中返回，而不需要扫描目录或文件。）
• 优化器可以利用底层数据字典表上的索引构建高效的查询计划，而之前使用临时表的方式不会利用索引。

这些改进同样适用于 SHOW 语句，它显示了 INFORMATION_SCHEMA 视图表中的相应信息。例如，SHOW DATABASES 显示了 SCHEMATA 表中的信息。

除了引入数据字典表上的视图之外，还对 STATISTICS 和 TABLES 表中的统计信息进行了缓存，提高了 INFORMATION_SCHEMA 的查询性能。系统变量 information_schema_stats_expiry 定义了表统计信息缓存的过期时间。默认值为 86400 秒（24 小时）。如果不存在统计信息缓存或缓存过期，查询直接从存储引擎返回统计信息。可以随时使用 ANALYZE TABLE 语句更新表统计信息的缓存。

将 information_schema_stats_expiry 设置为 0 可以在查询 INFORMATION_SCHEMA 时直接从存储引擎返回最新的统计信息，但是从缓存中返回统计信息更快。

更多信息，参考 第 8.2.3 节，“INFORMATION_SCHEMA 查询优化”。

14.6 序列化数据字典信息（SDI）

除了在数据字典中存储了关于数据库对象的元数据之外，MySQL 还存储一份序列化的元数据。这些数据被称为序列化字典信息（Serialized Dictionary Information）。 InnoDB 存储引擎在它的表空间文件中存储 SDI 数据。其他存储引擎在模式目录下的相应 .sdi 文件中存储 SDI 数据。 SDI 数据使用压缩的 JSON 格式进行存储。

对于所有的 InnoDB 表空间文件，除了临时表空间和撤销表空间文件，都存储了序列化数据字典信息（SDI）。每个 InnoDB 表空间文件中的 SDI 记录只包含了该表空间中的表和表空间对象的描述。

InnoDB 表空间文件中的 SDI 信息只能通过相应表上的 DDL 操作进行更新。

SDI 数据为元数据提供了冗余的信息。例如，如果数据字典不可用，可以使用 ibd2sdi 工具直接从 InnoDB 表空间文件中提取对象的元数据信息。

对于 InnoDB，一条 SDI 记录需要一个索引页，默认大小为 16KB。不过，SDI 数据进行了压缩，以减少存储需求。

对于由多个表空间组成的 InnoDB 分区表，SDI 数据存储在第一个分区的表空间文件中。

MySQL 服务器在 DDL 操作的过程中，使用内部的 API 创建和维护 SDI 记录。

IMPORT TABLE 语句基于 .sdi 文件中的信息执行 MyISAM 表的导入。更多信息可以参见 第 13.2.5 节，“IMPORT TABLE 语法”。

14.7 数据字典用法差异

使用数据字典的 MySQL 服务器与没有使用数据字典的服务器存在一些使用上的区别：

• 在之前的版本中，启用 innodb_read_only 系统变量只会禁止 InnoDB 表的创建和删除。从 MySQL 8.0 开始，启用 innodb_read_only 将会禁止所有存储引擎表的创建和删除。任何存储引擎表的创建和删除都会修改 mysql 系统数据中的数据，而这些数据表使用 InnoDB 存储引擎，不能被修改（启用了 innodb_read_only）。任何需要修改数据字典表的其他操作也会受到同样的限制。例如：

• 无法执行 ANALYZE TABLE 语句，因为它会更新数据字典中关于表的统计信息。

• 无法执行 ALTER TABLE tbl_name ENGINE=engine_name 语句，因为它会更新数据字典中的表的存储引擎信息。

  注意
  启用 innodb_read_only 也会对 mysql 系统数据库中的非字数据字典表产生重要影响。详细信息可以参考第 15.13 节， “InnoDB 启动选项与系统变量” 中关于 innodb_read_only 的介绍。

• 在之前的版本中，mysql 系统数据库中的表可以使用 DML 和 DDL 语句直接进行操作。从 MySQL 8.0 开始，数据字典表被设置为不可见，不能直接进行查询和修改。不过，大部分的表都存在一个对应的 INFORMATION_SCHEMA 表，可以针对这些表进行查询。这种方式的好处在于，随着新版本的开发，底层的数据字典表可以不断进行修改，而INFORMATION_SCHEMA 中的接口可以保持不变，不会影响到应用程序的使用。

• MySQL 8.0 中的 INFORMATION_SCHEMA 表的实现与数据字典密切相关，导致了一些使用上的区别：

  • 在之前的版本中，查询 INFORMATION_SCHEMA 中的 STATISTICS 和 TABLES 中关于表的统计信息时，直接从相关的存储引擎返回数据。从 MySQL 8.0 开始，默认使用缓存的表统计信息。系统变量 information_schema_stats_expiry 定义了缓存表统计信息的过期时间。默认时间为 86400 秒（24小时）。（使用 ANALYZE TABLE 语句可以随时更新表的缓存统计信息。）只有当缓存统计信息不存在或者过期时，查询才会从存储引擎返回统计信息。要想总是从存储引擎返回最新的统计信息，可以将 information_schema_stats_expiry 设置为 0 。更多信息可以参考 第 8.2.3 节， “优化 INFORMATION_SCHEMA 查询”。

  • 某些 INFORMATION_SCHEMA 表是基于数据字典表的视图，优化器查询时可以利用这些底层表上的索引。因此，查询 INFORMATION_SCHEMA 信息时返回结果的顺序可能不确定，这个取决于优化器的选择。如果想要返回指定顺序的结果，可以加上 ORDER BY 子句。

  • mysqldump 和 mysqlpump 不再备份 INFORMATION_SCHEMA 数据库，即使在命令行中明确指定导出该数据库。

  • CREATE TABLE dst_tbl LIKE src_tbl 要求 src_tbl 必须是一个基表，如果它是 INFORMATION_SCHEMA 中基于数据字典表的视图时，该语句无法执行。

  • 在之前的版本中，查询 INFORMATION_SCHEMA 表时返回的列标题使用查询语句中的大小写形式。以下查询返回的标题为 table_name：

    SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
    1

    从 MySQL 8.0 开始，返回大写的列标题；上面的查询返回的标题为TABLE_NAME。如果需要，可以通过列别名的方式返回指定的大小写形式。例如：

    SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;
    1

• 数据字典对 mysqldump 和 mysqlpump 导出 mysql 系统数据库的影响如下：

  • 在之前的版本中，可以导出 mysql 系统数据库中的任何表。从 MySQL 8.0 开始，mysqldump 和 mysqlpump 只能导出其中的非数据字典表。
  • 在之前的版本中，使用 –all-databases 选项导出所有数据库表时，不需要添加 –routines 和 –events 选项也会导出存储程序和事件：导出结果中包含了 mysql 系统数据库，因此也就包含了 proc 表和 event 表中的存储程序和事件的定义。从 MySQL 8.0 开始，不再使用 event 表和 proc 表。相应的对象存储在数据字典表中，但是这些表不会被导出。想要在使用 --all-databases 导出时包含存储程序和事件，需要明确指定 --routines 和 --events 选项。
  • 在之前的版本中，使用 --routines 选项需要拥有 proc 表的 SELECT 权限。从 MySQL 8.0 开始，不再使用 proc 表；使用 --routines 选项需要全局的 SELECT 权限。
  • 在之前的版本中，通过导出 proc 表和 event 表，可以在导出存储程序和事件定义的同时，导出它们的创建时间戳和修改时间戳。从 MySQL 8.0 开始，不再使用这些表, 因此无法导出这些时间戳。

• 在之前的版本中，创建存储程序时，如果使用了非法的字符，将会产生一个警告信息。从 MySQL 8.0 开始，使用非法字符将会产生一个错误信息。

14.8 数据字典局限性

MySQL 数据字典目前还存在一些临时的局限性：

• 不支持在数据目录下手动（例如，使用 mkdir 命令）创建数据库目录。MySQL 服务器不会识别手动创建的数据库目录。
• DDL 操作需要占用更长的时间，因为它需要写入存储、回滚日志以及重做日志，而不仅仅是之前的 .frm 文件。

人生本来短暂，你又何必匆匆！点个赞再走吧！