第 14 章 MySQL数据字典

目录

14.1 数据字典模式
14.2 移除基于文件的元数据存储
14.3 字典数据的事务性存储
14.4 字段对象缓存
14.5 INFORMATION_SCHEMA 和数据字典的集成
14.6 数据字典使用的差异性
14.7 数据字典的局限性

MySQL服务器现在使用事务性数据字典来存储数据库对象的信息。在以前的MySQL版本中,字典数据存储在元数据文件和非事务性表中。

本章描述的是数据字典主要的特性、优点、差异性以及局限性。 对于数据字典的其它介绍,参阅 MySQL 8.0 发行公告中的数据字典说明.

MySQL 8.0.中数据字典是使用InnoDB

MySQL数据字典的优点包括:

重要

相比没有没有数据字典的服务器来说,启用了数据字典的服务器,在常用的操作上存在一些差异。参阅 14.6 节, “数据字典使用的差异性”。 同样,对于升级到 MySQL 8.0的,需要通过检查特定的先决条件,来验证升级的准备,因此升级的过程也跟之前版本的MySQL有所不同。更多信息,参阅 2.10.1 节, “升级 MySQL”,尤其是 给安装的MySQL 5.7验证升级先决条件

14.1 数据字典模式

数据字典表是不可见的,只有在调试版的MySQL中可以访问,然而,MySQL支持通过 INFORMATION_SCHEMA表和 SHOW 语句来访问数据字典表中存储的数据。有关数据字典表的概述,请参阅 数据字典表

在 MySQL 8.0中仍然存在MySQL系统表,而且可以在mysql系统库下使用 SHOW TABLES语句查看。通常情况下,MySQL系统表和数据字典表之间的不同之处就是,系统表含有辅助数据,如,时区和帮助信息。然而,数据字典表含有执行SQL查询所需的数据。MySQL系统表和数据字典表在如何升级上也有不同。升级MySQL系统表需要运行 mysql_upgrade。 而数据字典升级是由MySQL服务器管理。

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

数据字典表默认都是隐藏的,但是可以通过带有debug支持的编译MySQL访问。CMake使用 -DWITH_DEBUG=1选项,以及指定 +d,skip_dd_table_access_check debug 选项和调节器。更多有关编译调试的构建信息,请参阅 28.5.1.1 节, “编译MySQL进行debug”

警告

不建议直接修改或写入数据字典表,可能会导致MySQL实例无法操作。

编译支持debug的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

14.2 移除基于文件的元数据存储

在之前的MySQL版本中,字典数据部分存储在元数据文件中。基于文件的元数据存储的问题有昂贵的文件扫描、对系统相关文件的bug敏感、处理复制和故障恢复代码的复杂,以及缺乏可扩展性,等等。使得 这使得为新特性和相关的对象添加元数据变得困难。

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

  • .frm 文件:表的元数据文件,移除了.frm文件:

    • 移除了由.frm文件结构强制限制的表的定义64KB。

    • INFORMATION_SCHEMA.TABLES中的 VERSION列,限制是硬性编码值10

  • .par 文件: 分区定义文件。 .par 文件已在在MySQL 5.7.6中移除,并引入了InnoDB 表的本地分区。

  • .TRN 文件:触发器名称空间文件。

  • .TRG 文件:触发器参数文件。

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

  • db.opt 文件: 数据配置文件。每个库目录都有一个,里面含给库设置的默认字符集。

14.3 字典数据的事务性存储

数据字典模式将字典数据保存在事务性(InnoDB)表中。数据字典表和其他非数据字典系统表一起位于 mysql库中。

数据字典表被创建在MySQL数据目录下名为mysql.ibd的独立 InnoDB表空间。 mysql.ibd 表空间文件必须在MySQL数据目录中,它的名称不能被其他表空间修改或使用。以前,这些表是在mysql库目录中单独的表空间文件中创建的。

字典数据受同样被commit、rollback和紧急恢复功能的保护,这些功能保护了存储在InnoDB 表中的用户数据。

14.4 字典对象缓存

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

字典对象缓存,包含这存储不同对象类型的缓存分区。一些缓存分区大小限制是可配置的,另一些则是固定的。

  • 表空间定义缓存分区:存储表空间定义对象。通过设置 tablespace_definition_cache 选项的值,来限制表空定义对象被字典对象缓存存储的个数。默认是256。

  • schema定义缓存分区: 存储schema 定义对象。通过设置 schema_definition_cache选项的值,来限制 schema定义对象可以被字典对象缓存存储的个数,默认是256。

  • 表定义缓存分区: 存储表定义对象。通过设置 max_connections选项的值来限制,默认值是151。

    表定义缓存分区与表定义缓存并行存在,由 table_definition_cache 配置选项配置。这两种缓存都存储表的定义,但因服务于MySQL服务器的不同位置。两者中缓存的对象不会相互依赖。

  • 存储程序定义缓存分区: 保存存储程序定义对象。通过设置 stored_program_definition_cache选项的值,来限制存储程序定义对象在字典对象缓存中存储的个数。默认值是256。

    存储程序定义缓存分区,与存储过程和存储函数缓存并行存在,这是可以由 stored_program_cache选项配置。

    stored_program_cache 选项为每个连接的存储过程或函数设置一个软的上限存储值,并且每次连接执行一个存储过程或函数时都要检查这个限制。 另一方面,存储的程序定义缓存分区是一个共享的缓存,它存储着存储程序定义对象,以用于其他目的。 存储程序定义缓存分区中的对象的存在,不依赖于存储过程缓存或存储函数缓存中的对象的存在,反之亦然,就是相互不依赖。

  • 字符集设置定义缓存分区: 存储字符集设置定义对象,固定值256。

  • collation definition cache partition: 存储 collation 定义对象,固定值256。

关于字典对象缓存配置选项的有效值的信息,参阅 5.1.5, “服务器系统表里”

14.5 INFORMATION_SCHEMA 和数据字典的集成

随着数据字典的引入,下面的 INFORMATION_SCHEMA表在数据字典表中以视图的形式实现。

现在,对这些表的查询效率更高,因为它们从数据字典表中获取信息,而不是通过其他更慢的方式获取信息。尤其是那些在数据字典表中以视图的形式实现的 INFORMATION_SCHEMA表。

  • 每次查询INFORMATION_SCHEMA表时,服务器不再创建临时表。

  • 当底层数据字典表存储那些以前通过目录扫描获得的值(例如,列举数据库中的库名或表名),或打开文件的操作(例如,从.frm 文件中读取信息)时,对这些值的信息模式查询现在可以使用表查找。 INFORMATION_SCHEMA对这些值查询现在也变为对表的查询。 (另外,即使对于非视图INFORMATION_SCHEMA表,如:库和表名之类的值,也可以通过查找数据字典来检索,不需要目录或文件扫描。)

  • 底层数据字典表中的索引,允许优化构造高效的执行计划,这在以前的实现中是不正确的,因为在以前的查询中是以临时表的方式处理 INFORMATION_SCHEMA表。

前面的改进还适用于 使用SHOW语句显示相应的在数据字典标表中以试图形式存在INFORMATION_SCHEMA表的的信息。 例如,SHOW DATABASESSCHEMATA 表显示的信息是一样的。

除了在数据字典表中引入视图外,现在还将 STATISTICSTABLES表及其元数据进行缓存,以提高INFORMATION_SCHEMA 查询性能。 缓存表的元数据是由 information_schema_stats 配置选项控制,它默认设置为CACHED 通过执行ANALYZE TABLE 语句,可以更新缓存的表的元数据。

可将information_schema_stats设置为 LATEST,以便INFORMATION_SCHEMA会话 直接从存储引擎级检索最新的元数据。当然,这样就没有检索缓存的表的元数据那么快。 的最新元数据。

更多信息,参阅 8.2.3 节, “优化INFORMATION_SCHEMA 查询

14.6 数据字典使用的差异性

相比没有没有数据字典的服务器来说,启用了数据字典的服务器,在常用的操作上存在一些差异:

  • 在以前,启用innodb_read_only系统变量只会阻止 InnoDB存储引擎表的创建和删除。而在MySQL 8.0 中开启,会阻止所有的存储引擎进行这些操作。因为任何存储引擎表的创建和删除操作都会修改 mysql系统库中的数据字典表。但是,这些字典表是使用的InnoDB存储引擎,而当启用innodb_read_only 时,不能修改使用InnoDB存储引擎的表。 同样的原则,也适用于对其他表的操作,且这些操作会修改数据字典表。例如:

    提示

    启用 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_SCHEMASTATISTICSTABLES表中查询表的统计信息是直接从存储一起检索, 而在MySQL 8.0中,默认是使用缓存的表统计信息,这样效果更好。因此,当服务器启动,没有缓存的统计信息,对给定的表不运行ANALYZE TABLE前。表的统计信息也不会更新。并且自从对表修改后,统计信息也会过期。想要从存储引擎获得最新的表的统计信息,可以将系统变量 information_schema_statsCACHED 修改为 LATEST。更多信息,请参阅 8.2.3 节, “优化INFORMATION_SCHEMA查询”

    • 几个以视图储存在数据字典表中的 INFORMATION_SCHEMA表,使得优化器能在底层表上使用索引。因此,基于优化器的选择,INFORMATION_SCHEMA查询的结果集中,行的排序可能不同于以前的结果。如果结果集中必须要有特殊的排序,可以使用ORDER BY 子句。

    • mysqldumpmysqlpump 不再dump INFORMATION_SCHEMA 库,即使是在命令行上明确指出。

    • CREATE TABLE dst_tbl LIKE src_tbl 需要 src_tbl 是一个基表。如果它是一个以视图方式存储在数据字典表中的INFORMATION_SCHEMA表,就会创建失败。

    • 在以前, 从 INFORMATION_SCHEMA表中选择的列的结果集标题,使用查询中指定的大小写。这个查询会生成一个带有 table_name开头的结果集:

      SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
      

      而在MySQL 8.0 中,这些标题大写;前面的查询会生成一个带有TABLE_NAME头的结果集。如果需要,可以使用列别名来实现不同的字母。例如:

      SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;
      
  • 数据字典影响着mysqldumpmysqlpump如何从mysql系统库中dump信息。

    • 在以前,可以dump mysql系统库下的所有表。而在MySQL 8.0 中, mysqldumpmysqlpump只能dumpmysql系统库下的非数据字典表。

    • 在以前,当使用--all-databases 选项时,要包含存储例程和事件,可以不需要用 --routines--events 选项,dump会包含mysql系统库,因为,也就包含了 proc和 and event表,它们储存着存储例程和事件的定义。而在MySQL 8.0 中 没有使用eventproc表,相关对象的定义都储存在数据字典表中。但是这些表不能被dump。那么在使用--all-databases选项时,想要将这些存储例程和事件包含在dump中,就要明确的给出 --routines--events选项。

    • 在以前,使用 --routines 选项需要 对proc表有SELECT权限。 而在MySQL 8.0 中,由于没有使用这个表,那么 --routines 需要有全局的SELECT 权限。

    • 在以前,通过dump procevent表,可以将存储例程和事件定义的创建及修改的时间戳一起dump。 而在MySQL 8.0 中,由于没有使用这个表,所以不能dump时间戳。

  • 在以前,创建一个含有非法字符的存储程序会产生一个告警,但是在MySQL 8.0中是产生一个错误。

14.7 数据字典的局限性

本节描述了引入MySQL数据字典的临时局限性。

  • 不支持手动的在数据目录下创建库目录(如,使用mkdir),手动创建的库目录不能被MySQL服务器识别。

  • 不支持使用复制MyISAM表中存储的数据,或移动MyISAM数据文件的方式来迁移数据,使用这种方式移动的表不能被服务器识别。

  • 不支持使用复制数据文件,对单个 MyISAM表进行备份恢复。

  • TRUNCATE TABLE,,在MySQL 8.0中被映射为 to DROP TABLECREATE TABLE。 这个暂时是非原子性的。所以在进行 TRUNCATE TABLE操作期间,服务器退出,会导致一个表被删除。 而且,如果这个表含有外键约束,还会在 InnoDB SYS_FOREIGNSYS_FOREIGN_COLS字典表之间产生一个孤立的外键条目。

  • DDL 操作需要更长的时间,因为现在是写入存储引擎、undo日志和redo日志,而不是.frm

  • DDL 操作期间,容易受到服务器退出的影响,因为现在是写入存储引擎、undo日志和redo日志,而不是.frm。 这种限制的最小风险的场景,就是主要适用于恢复操作和其他操作,如:加载大量新表的。

  • 随着在MySQL 8.0 中.isl文件的移除, 不支持对在MySQL数据目录之外创建的file-per-table表空间进行离线重新定位

  • 如果MySQL在对InnoDB表进行ALTER TABLE操作期间退出,那么可能会留下一个孤立的中间或临时表。而删除孤立的中间和临时表是不支持的。