14 数据字典

发布时间：2023-06-13 19:08:31

参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-schema.html14.1 数据字典 schema数据字典表受保护，只能

参考官方文档：

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

14.1 数据字典 schema

数据字典表受保护，只能在MySQL的调试版本中访问。但是，MySQL支持通过INFORMATION_SCHEMA表和SHOW语句访问存储在数据字典表中的数据。

相关数据字典表如下:

catalogs：目录信息。

character_sets：有关可用字符集的信息。

check_constraints：有关在表上定义的CHECK约束的信息。

collations：有关每个字符集的排序的信息。

column_statistics：列值的直方图统计信息。

column_type_elements：有关列使用的类型的信息。

column：有关表中列的信息。

dd_properties：标识数据字典属性（例如其版本）的表。服务器使用它来确定是否必须将数据字典升级到较新的版本。

events：有关事件计划程序事件的信息。如果使用--skip-grant-tables选项启动服务器，则将禁用事件调度程序，并且不会运行表中注册的事件。

foreign_keys，foreign_key_column_usage：有关外键的信息。

index_column_usage：有关索引使用的列的信息。

index_partitions：有关索引使用的分区的信息。

index_stats：用于存储执行ANALYZE TABLE时生成的动态索引统计信息。

indexes：有关表索引的信息。

innodb_ddl_log：存储DDL日志以进行崩溃安全的DDL操作。

parameter_type_elements：有关存储过程和函数参数的信息，以及有关存储函数的返回值的信息。

parameters：有关存储过程和函数的信息。

resource_groups：有关资源组的信息。

routines：有关存储过程和功能的信息。

schemata：有关schema的信息。在MySQL中，模式是数据库，因此此表提供有关数据库的信息。

st_spatial_reference_systems：关于空间数据的可用空间参考系统的信息

table_partition_values：有关表分区使用的值的信息。

table_partitions：有关表使用的分区的信息。

table_stats：有关执行ANALYZE TABLE时生成的动态表统计信息。

table：有关数据库中表的信息。

tablespace_files：有关表空间使用的文件的信息。

tablespaces：有关活动表空间的信息。

triggers：有关触发器的信息。

view_routine_usage：关于视图和视图使用的存储函数之间的依赖关系的信息。

view_table_usage：用于跟踪视图及其基础表之间的依赖关系。

数据字典表是不可见的。它们不能用SELECT读取，不会出现在SHOW TABLES的输出中，也没有在INFORMATION_SCHEMA.TABLES表中列出，依此类推。但是，在大多数情况下，可以查询相应的INFORMATION_SCHEMA表。从概念上讲，INFORMATION_SCHEMA提供了一个视图，MySQL通过该视图公开数据字典元数据。例如，您不能直接从mysql.schemata表中选择：

mysql> SELECT * FROM mysql.schemata; ERROR 3554 (HY000): Access to data dictionary table 'mysql.schemata' is rejected.

而是从相应的INFORMATION_SCHEMA表中选择该信息：

mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATAG

没有与mysql.indexes完全对应的INFORMATION_SCHEMA表，但是INFORMATION_SCHEMA.STATISTICS包含许多相同的信息。

到目前为止，还没有与mysql.foreign_keys，mysql.foreign_key_column_usage完全对应的INFORMATION_SCHEMA表。 获取外键信息的标准SQL方法是使用INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS和KEY_COLUMN_USAGE表。这些表现在被实现为foreign_keys，foreign_key_column_usage和其他数据字典表的视图。

MySQL 8.0之前的某些系统表已被数据字典表替换，并且不再存在于mysql系统schema中：

events 数据字典表取代了MySQL 8.0之前的 event表。

parameters 和例 routines 表一起取代了MySQL 8.0之前的proc表。

MySQL 系统表仍存在于MySQL 8.0中，可以通过在MySQL系统数据库上发出SHOW TABLES语句来查看。通常，MySQL数据字典表和系统表之间的区别在于，数据字典表包含执行SQL查询所需的元数据，而系统表包含辅助数据，例如时区和帮助信息。 MySQL系统表和数据字典表在升级方式上也有所不同。 MySQL服务器管理数据字典升级。

--数据字典表看不到，但是一些其他的系统表可以看到，并能够直接查询。或者使用debug版本来进行直接访问

数据字典如何升级

MySQL的新版本可能包括对数据字典表定义的更改。此类更改存在于新安装的MySQL版本中，但是在执行MySQL二进制文件的就地升级时，使用新的二进制文件重新启动MySQL服务器时将应用更改。在启动时，将服务器的数据字典版本与存储在数据字典中的版本信息进行比较，以确定是否应升级数据字典表。如果需要升级并支持升级，则服务器会创建具有更新定义的数据字典表，将持久化的元数据复制到新表中，用新表原子替换旧表，然后重新初始化数据字典。如果不需要升级，则继续启动而不更新数据字典表。

数据字典表的升级是一项原子操作，这意味着所有数据字典表都将根据需要进行升级，否则操作将失败。如果升级操作失败，则服务器启动失败并显示错误。在这种情况下，旧服务器二进制文件可与旧数据目录一起使用以启动服务器。当新的服务器二进制文件再次用于启动服务器时，将重新尝试升级数据字典。

通常，成功升级数据字典表后，将无法使用旧的服务器二进制文件重新启动服务器。因此，升级数据字典表后，不支持将MySQL服务器二进制文件降级到以前的MySQL版本。

mysqld --no-dd-upgrade选项可用于防止启动时自动升级数据字典表。如果指定了--no-dd-upgrade，并且服务器发现服务器的数据字典版本与存储在数据字典中的版本不同，则启动失败，并显示一条错误指出数据字典升级被禁止。

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

在以前的MySQL版本中，字典数据部分存储在元数据文件中。基于文件的元数据存储的问题包括昂贵的文件扫描，对文件系统相关错误的敏感性，用于处理复制和崩溃恢复失败状态的复杂代码以及缺乏可扩展性，使得难以为新功能和关系对象添加元数据。

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

.frm文件：表元数据文件。删除.frm文件后：

删除了由.frm文件结构施加的64KB表定义大小限制。
INFORMATION_SCHEMA.TABLES VERSION列报告的硬编码值为10，这是MySQL 5.7中使用的最后一个.frm文件版本。

.par文件：分区定义文件。随着对InnoDB表的本机分区支持的引入，InnoDB停止在MySQL 5.7中使用分区定义文件。
.TRN文件：触发器名称空间文件。
.TRG文件：触器发参数文件。
.isl文件：InnoDB符号链接文件，包含在数据目录外部创建的每表文件表空间文件的位置。
db.opt文件：原先在mysql 目录下数据库配置文件。这些文件（每个数据库目录一个）包含数据库默认字符集属性。
ddl_log.log文件：该文件包含由数据定义语句（例如DROP TABLE和ALTER TABLE）生成的元数据操作的记录。

数据字典schema将字典数据存储在事务（InnoDB）表中。数据字典表与非数据字典系统表一起位于mysql数据库中。

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

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

--以前是myisam 引擎，现在使用innodb 以支持事务性（general_log和slow_log 除外还是CSV引擎存储）。

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。

14.5 INFORMATION_SCHEMA和数据字典集成

随着数据字典的引入，以下INFORMATION_SCHEMA表被实现为数据字典表的视图：

CHARACTER_SETS
CHECK_CONSTRAINTS
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表，数据库和表名之类的值也可以通过从数据字典中的查找来检索，并且不需要目录或文件扫描。）

基础数据字典表上的索引允许优化器构造有效的查询执行计划，对于以前使用每个查询使用临时表来处理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查询直接从存储引擎检索最新的统计信息，其速度比检索缓存的统计信息慢。

MySQL 8.0中的INFORMATION_SCHEMA表与数据字典紧密相关，因此在用法上存在一些差异。

14.6序列化字典信息（SDI）

除了将有关数据库对象的元数据存储在数据字典中之外，MySQL还以序列化形式存储它。该数据称为序列化字典信息（SDI）。 InnoDB将SDI数据存储在其表空间文件中。 NDBCLUSTER将SDI数据存储在NDB词典中。其他存储引擎将SDI数据存储在为表的数据库目录中的给定表创建的.sdi文件中。 SDI数据以紧凑的JSON格式生成。

除临时表空间和undo表空间文件外，所有InnoDB表空间文件中都存在序列化字典信息（SDI）。 InnoDB表空间文件中的SDI记录仅描述表空间中包含的表和表空间对象。

SDI数据通过表或CHECK TABLE FOR UPGRADE上的DDL操作来更新。将MySQL服务器升级到新版本时，不会更新SDI数据。

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

对于InnoDB，SDI记录需要一个索引页，默认情况下大小为16KB。但是，SDI数据被压缩以减少存储空间。

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

MySQL服务器使用在DDL操作期间访问的内部API来创建和维护SDI记录。

IMPORT TABLE语句根据.sdi文件中包含的信息导入MyISAM表。

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系统数据库中的非数据字典表也具有重要意义。

以前，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。

一些INFORMATION_SCHEMA表是数据字典表的视图，这使优化程序可以在这些基础表上使用索引。因此，根据优化程序的选择，INFORMATION_SCHEMA查询的结果行顺序可能与以前的结果不同。如果查询结果必须具有特定的行顺序特征，请包括ORDER BY子句。

与早期的MySQL系列不同，对INFORMATION_SCHEMA表的查询可能以不同的字母大小写返回列名称。应用程序应以不区分大小写的方式测试结果集的列名。如果这不可行，则解决方法是在选择列表中使用列别名，该别名以必需的字母大小写形式返回列名称。例如：

SELECT TABLE_SCHEMA AS table_schema, TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';

即使在命令行上明确命名，mysqldump和mysqlpump也不再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;

数据目录会影响mysqldump和mysqlpump从mysql系统数据库中转储信息的方式：

以前，可以dump mysql系统数据库中的所有表。从MySQL 8.0开始，mysqldump和mysqlpump仅转储该数据库中的非数据字典表。

以前，使用--all-databases选项时，不需要--routines和--events选项包括存储的例程和事件：转储包括mysql系统数据库，因为 proc和event表也包含存储过程和事件的定义。从MySQL 8.0开始，不使用event和proc表。对应对象的定义存储在数据字典表中，但不转储这些表。要在使用--all-databases创建的转储中包含存储的例程和事件，请显式使用--routines和--events选项。