原文
提供更好的翻译建议
目录
MySQL Server包含一个事务数据字典，用于存储有关数据库对象的信息。在以前的MySQL版本中，字典数据存储在元数据文件，非事务表和存储引擎特定的数据字典中。
本章介绍数据字典的主要功能，优点，用法差异和限制。有关数据字典功能的其他含义，请参阅MySQL 8.0发行说明中的 “ 数据字典说明 ”部分 。
MySQL数据字典的好处包括：
统一存储字典数据的集中式数据字典模式的简单性。请参见 第14.1节“数据字典模式”。
删除基于文件的元数据存储。请参见 第14.2节“删除基于文件的元数据存储”。
字典数据的事务性，崩溃安全存储。请参见 第14.3节“字典数据的事务存储”。
字典对象的统一和集中式缓存。请参见 第14.4节“字典对象缓存”。
一些INFORMATION_SCHEMA表的更简单和改进的实现
。请参见
第14.5节“INFORMATION_SCHEMA和数据字典集成”。
原子DDL。请参见第13.1.1节“原子数据定义语句支持”。
与没有数据字典的服务器相比，启用数据字典的服务器需要一些一般的操作差异; 请参见 第14.7节“数据字典使用差异”。此外，对于MySQL 8.0的升级，升级过程与以前的MySQL版本略有不同，并要求您通过检查特定的先决条件来验证安装的升级准备情况。有关更多信息，请参见第2.11节“升级MySQL”，特别是第2.11.5节“准备升级安装”。
数据字典表受到保护，只能在MySQL的调试版本中访问。但是，MySQL支持通过INFORMATION_SCHEMA表和
SHOW语句访问存储在数据字典表中的数据
。有关组成数据字典的表的概述，请参阅
数据字典表。
MySQL系统表仍然存在于MySQL 8.0中，可以通过SHOW TABLES
在mysql系统数据库上发出声明来查看。通常，MySQL数据字典表和系统表之间的区别在于数据字典表包含执行SQL查询所需的元数据，而系统表包含辅助数据，如时区和帮助信息。MySQL系统表和数据字典表在升级方式上也有所不同。MySQL服务器管理数据字典升级。SQL服务器。请参阅如何升级数据字典。升级MySQL系统表需要运行完整的MySQL升级过程。请参见
第2.11.3节“MySQL升级过程升级的内容”。
新版本的MySQL可能包括对数据字典表定义的更改。这些更改存在于新安装的MySQL版本中，但在执行MySQL二进制文件的就地升级时，在使用新二进制文件重新启动MySQL服务器时会应用更改。在启动时，将服务器的数据字典版本与存储在数据字典中的版本信息进行比较，以确定是否应该升级数据字典表。如果需要升级并且支持升级，则服务器将创建具有更新定义的数据字典表，将持久化元数据复制到新表，以原子方式将旧表替换为新表，并重新初始化数据字典。如果不需要升级，
数据字典表的升级是一种原子操作，这意味着所有数据字典表都会根据需要进行升级或操作失败。如果升级操作失败，则服务器启动失败并显示错误。在这种情况下，旧的服务器二进制文件可以与旧数据目录一起使用以启动服务器。再次使用新的服务器二进制文件启动服务器时，将重新尝试数据字典升级。
通常，在成功升级数据字典表之后，无法使用旧服务器二进制文件重新启动服务器。因此，在升级数据字典表后，不支持将MySQL服务器二进制文件降级为以前的MySQL版本。
该mysqld的
--no-dd-upgrade选项可用于防止数据字典表的自动升级启动。如果--no-dd-upgrade指定，并且服务器发现服务器的数据字典版本与存储在数据字典中的版本不同，则启动失败并显示错误，指出禁止数据字典升级。
数据字典表默认受保护，但可以通过使用调试支持（使用-DWITH_DEBUG=1
CMake选项）编译MySQL
并指定
+d,skip_dd_table_access_check
debug选项和修饰符来访问。有关编译调试版本的信息，请参见
第29.5.1.1节“编译MySQL以进行调试”。
建议不要直接修改或写入数据字典表，这可能会导致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
在以前的MySQL版本中，字典数据部分存储在元数据文件中。基于文件的元数据存储的问题包括昂贵的文件扫描，对文件系统相关错误的敏感性，处理复制和崩溃恢复失败状态的复杂代码，以及缺乏可扩展性，这使得难以为新功能和关系对象添加元数据。
下面列出的元数据文件将从MySQL中删除。除非另有说明，否则先前存储在元数据文件中的数据现在存储在数据字典表中。
.frmfiles：表元数据文件。随着.frm文件的删除：
将.frm删除文件结构强加的64KB表定义大小限制
。
该列报告硬编码值，这是MySQL 5.7中使用的最后一个
文件版本。
INFORMATION_SCHEMA.TABLES
VERSION10.frm
.parfiles：分区定义文件。
InnoDB在MySQL 5.7中停止使用分区定义文件，并引入了对InnoDB表的本机分区支持。
.TRN files：触发命名空间文件。
.TRG files：触发器参数文件。
db.opt files: Database configuration
files. These files, one per database directory, contained
database default character set attributes.
The data dictionary schema stores dictionary data in transactional
(InnoDB) tables. Data dictionary tables are
located in the mysql database together with
non-data dictionary system tables.
Data dictionary tables are created in a single
InnoDB tablespace named
mysql.ibd, which resides in the MySQL data
directory. The mysql.ibd tablespace file must
reside in the MySQL data directory and its name cannot be modified
or used by another tablespace.
Dictionary data is protected by the same commit, rollback, and
crash-recovery capabilities that protect user data that is stored
in InnoDB tables.
The dictionary object cache is a shared global cache that stores previously accessed data dictionary objects in memory to enable object reuse and minimize disk I/O. Similar to other cache mechanisms used by MySQL, the dictionary object cache uses an LRU-based eviction strategy to evict least recently used objects from memory.
The dictionary object cache comprises cache partitions that store different object types. Some cache partition size limits are configurable, whereas others are hardcoded.
tablespace definition cache
partition: Stores tablespace definition objects.
The
tablespace_definition_cache
option sets a limit for the number of tablespace definition
objects that can be stored in the dictionary object cache. The
default value is 256.
schema definition cache
partition: Stores schema definition objects. The
schema_definition_cache
option sets a limit for the number of schema definition
objects that can be stored in the dictionary object cache. The
default value is 256.
table definition cache
partition: Stores table definition objects. The
object limit is set to the value of
max_connections, which has a
default value of 151.
The table definition cache partition exists in parallel with
the table definition cache that is configured using the
table_definition_cache
configuration option. Both caches store table definitions but
serve different parts of the MySQL server. Objects in one
cache have no dependence on the existence of objects in the
other.
stored program definition cache
partition: Stores stored program definition
objects. The
stored_program_definition_cache
option sets a limit for the number of stored program
definition objects that can be stored in the dictionary object
cache. The default value is 256.
The stored program definition cache partition exists in
parallel with the stored procedure and stored function caches
that are configured using the
stored_program_cache option.
The stored_program_cache
option sets a soft upper limit for the number of cached stored
procedures or functions per connection, and the limit is
checked each time a connection executes a stored procedure or
function. The stored program definition cache partition, on
the other hand, is a shared cache that stores stored program
definition objects for other purposes. The existence of
objects in the stored program definition cache partition has
no dependence on the existence of objects in the stored
procedure cache or stored function cache, and vice versa.
character set definition cache partition: Stores character set definition objects and has a hardcoded object limit of 256.
collation definition cache partition: Stores collation definition objects and has a hardcoded object limit of 256.
For information about valid values for dictionary object cache configuration options, refer to Section 5.1.8, “Server System Variables”.
With the introduction of the data dictionary, the following
INFORMATION_SCHEMA tables are
implemented as views on data dictionary tables:
KEYWORDS
Queries on those tables are now more efficient because they obtain
information from data dictionary tables rather than by other,
slower means. In particular, for each
INFORMATION_SCHEMA table that is a view on data
dictionary tables:
The server no longer must create a temporary table for each
query of the INFORMATION_SCHEMA table.
When the underlying data dictionary tables store values
previously obtained by directory scans (for example, to
enumerate database names or table names within databases) or
file-opening operations (for example, to read information from
.frm files),
INFORMATION_SCHEMA queries for those values
now use table lookups instead. (Additionally, even for a
non-view INFORMATION_SCHEMA table, values
such as database and table names are retrieved by lookups from
the data dictionary and do not require directory or file
scans.)
Indexes on the underlying data dictionary tables permit the
optimizer to construct efficient query execution plans,
something not true for the previous implementation that
processed the INFORMATION_SCHEMA table
using a temporary table per query.
The preceding improvements also apply to
SHOW statements that display
information corresponding to the
INFORMATION_SCHEMA tables that are views on
data dictionary tables. For example, SHOW
DATABASES displays the same information as the
SCHEMATA table.
In addition to the introduction of views on data dictionary
tables, table statistics contained in the
STATISTICS and
TABLES tables is now cached to
improve INFORMATION_SCHEMA query
performance. The
information_schema_stats_expiry
system variable defines the period of time before cached table
statistics expire. The default is 86400 seconds (24 hours). If
there are no cached statistics or statistics have expired,
statistics are retrieved from storage engine when querying table
statistics columns. To update cached values at any time for a
given table, use ANALYZE TABLE
information_schema_stats_expiry
can be set to 0 to have
INFORMATION_SCHEMA queries retrieve
the latest statistics directly from the storage engine, which is
not as fast as retrieving cached statistics.
For more information, see Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
INFORMATION_SCHEMA tables in MySQL
8.0 are closely tied to the data dictionary,
resulting in several usage differences. See
Section 14.7, “Data Dictionary Usage Differences”.
In addition to storing metadata about database objects in the data
dictionary, MySQL stores it in serialized form. This data is
referred to as Serialized Dictionary Information (SDI).
InnoDB stores SDI data within its tablespace
files. Other storage engines store SDI data in
.sdi files that are created in the schema
directory. SDI data is generated in a compact
JSON format.
Serialized Dictionary Information (SDI) is present in all
InnoDB tablespace files except for temporary
tablespace and undo tablespace files. SDI records in an
InnoDB tablespace file only describe table and
tablespace objects contained within the tablespace.
SDI data in within an InnoDB tablespace file is
only updated by DDL operations on tables within the tablespace.
The presence of SDI data provides metadata redundancy. For
example, if the data dictionary becomes unavailable, object
metadata can be extracted directly from InnoDB
tablespace files using the ibd2sdi tool.
For InnoDB, an SDI record requires a single
index page, which is 16KB in size by default. However, SDI data is
compressed to reduce the storage footprint.
For partitioned InnoDB tables comprised of
multiple tablespaces, SDI data is stored in the tablespace file of
the first partition.
The MySQL server uses an internal API that is accessed during DDL operations to create and maintain SDI records.
The IMPORT TABLE statement imports
MyISAM tables based on information contained in
.sdi files. For more information, see
Section 13.2.5, “IMPORT TABLE Syntax”.
Use of a data dictionary-enabled MySQL server entails some operational differences compared to a server that does not have a data dictionary:
Previously, enabling the
innodb_read_only system
variable prevented creating and dropping tables only for the
InnoDB storage. As of MySQL
8.0, enabling
innodb_read_only prevents
these operations for all storage engines. Table creation and
drop operations for any storage engine modify data dictionary
tables in the mysql system database, but
those tables use the InnoDB storage engine
and cannot be modified when
innodb_read_only is enabled.
The same principle applies to other table operations that
require modifying data dictionary tables. Examples:
ANALYZE TABLE fails because
it updates table statistics, which are stored in the data
dictionary.
ALTER TABLE
fails because it updates the storage engine designation,
which is stored in the data dictionary.
tbl_name
ENGINE=engine_name
Enabling innodb_read_only
also has important implications for non-data dictionary
tables in the mysql system database. For
details, see the description of
innodb_read_only in
Section 15.13, “InnoDB Startup Options and System Variables”
Previously, tables in the mysql system
database were visible to DML and DDL statements. As of MySQL
8.0, data dictionary tables are invisible and
cannot be modified or queried directly. However, in most cases
there are corresponding INFORMATION_SCHEMA
tables that can be queried instead. This enables the
underlying data dictionary tables to be changed as server
development proceeds, while maintaining a stable
INFORMATION_SCHEMA interface for
application use.
INFORMATION_SCHEMA tables in MySQL
8.0 are closely tied to the data dictionary,
resulting in several usage differences:
Previously, INFORMATION_SCHEMA queries
for table statistics in the
STATISTICS and
TABLES tables retrieved
statistics directly from storage engines. As of MySQL
8.0, cached table statistics are used by
default. The
information_schema_stats_expiry
system variable defines the period of time before cached
table statistics expire. The default is 86400 seconds (24
hours). (To update the cached values at any time for a
given table, use ANALYZE
TABLE.) If there are no cached statistics or
statistics have expired, statistics are retrieved from
storage engines when querying table statistics columns. To
always retrieve the latest statistics directly from
storage engines, set
information_schema_stats_expiry
to 0. For more information, see
Section 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.
Several INFORMATION_SCHEMA tables are
views on data dictionary tables, which enables the
optimizer to use indexes on those underlying tables.
Consequently, depending on optimizer choices, the row
order of results for INFORMATION_SCHEMA
queries might differ from previous results. If a query
result must have specific row ordering characteristics,
include an ORDER BY clause.
对INFORMATION_SCHEMA表的查询可能会返回与早期MySQL系列不同的字母表中的列名。应用程序应以不区分大小写的方式测试结果集列名称。如果这不可行，则解决方法是在选择列表中使用列别名，该列别名返回所需字母表中的列名称。例如：
SELECT TABLE_SCHEMA AS table_schema，TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME ='users';
即使在命令行上显式命名， mysqldump和
mysqlpump也不再转储
INFORMATION_SCHEMA数据库。
CREATE
TABLE 要求它
dst_tbl LIKE
src_tblsrc_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系统数据库转储信息
：
以前，可以将所有表转储到
mysql系统数据库中。从MySQL 8.0开始，mysqldump和
mysqlpump只转储该数据库中的非数据字典表。
以前，使用选项时，
--routines和
--events选项不需要包括存储的例程和事件
--all-databases：转储包括mysql系统数据库，因此还包含包含存储的例程和事件定义的proc
和event表。从MySQL 8.0开始，不使用event和
proc表。相应对象的定义存储在数据字典表中，但不会转储这些表。要在使用的转储中包含存储的例程和事件
--all-databases，请使用
--routines和
--events选项明确。
以前，该
--routines选项需要表的SELECT
权限proc。从MySQL 8.0开始，不使用该表;
--routines需要全局SELECT特权。
以前，通过转储proc和event
表，可以将存储的例程和事件定义与其创建和修改时间戳一起转储
。从MySQL 8.0开始，不使用这些表，因此无法转储时间戳。
以前，创建包含非法字符的存储例程会产生警告。从MySQL 8.0开始，这是一个错误。