MySQL :: MySQL 8.4 参考手册 :: 16.5 INFORMATION

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 16 MySQL 数据字典 / 16.5 INFORMATION_SCHEMA 和数据字典集成

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 89%

16.5 INFORMATION_SCHEMA 与数据字典集成16.5 INFORMATION_SCHEMA and Data Dictionary Integration

随着数据字典的引入，以下 INFORMATION_SCHEMA 表作为数据字典表的视图实现：With the introduction of the data dictionary, the following INFORMATION_SCHEMA tables are implemented as views on data dictionary tables:

现在对这些表的查询更加高效，因为它们从数据字典表中获取信息，而不是通过其他较慢的方式获取。特别是，对于每个作为数据字典表视图的 INFORMATION_SCHEMA 表：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:

服务器不再需要为每个 INFORMATION_SCHEMA 表的查询创建临时表。The server no longer must create a temporary table for each query of the INFORMATION_SCHEMA table.
当底层数据字典表存储先前通过目录扫描获取的值（例如，枚举数据库名称或数据库中的表名称）或文件打开操作获取的值（例如，从.frm文件中读取信息）时，对这些值的INFORMATION_SCHEMA查询现在使用表查找来实现。(此外，即使对于非视图的INFORMATION_SCHEMA表，诸如数据库和表名称之类的值也是通过数据字典查找获取的，而不需要目录或文件扫描。)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.)
底层数据字典表上的索引允许优化器构建高效的查询执行计划，而以前实现每个查询使用临时表处理INFORMATION_SCHEMA表的方式则不具备这一点。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.

前述改进同样适用于显示与基于数据字典表的INFORMATION_SCHEMA视图相对应信息的SHOW语句。例如，SHOW DATABASES显示的信息与SCHEMATA表的信息相同。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.

除了引入数据字典表的视图之外，STATISTICS 和 TABLES 表中包含的表统计信息现在也会被缓存，以提高 INFORMATION_SCHEMA 查询性能。information_schema_stats_expiry 系统变量定义了缓存表统计信息过期之前的时间周期。默认值为 86400 秒（24 小时）。如果没有缓存的统计信息或统计信息已过期，查询表统计信息列时会从存储引擎中检索统计信息。要随时更新给定表的缓存值，请使用 ANALYZE TABLEIn 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 可以设置为 0，使 INFORMATION_SCHEMA 查询直接从存储引擎获取最新的统计信息，这比获取缓存的统计信息要慢。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.

有关更多信息，请参见第 10.2.3 节，“优化 INFORMATION_SCHEMA 查询”。For more information, see Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

MySQL 8.4 中的 INFORMATION_SCHEMA 表与数据字典紧密关联，因此使用上存在若干差异。参见第 16.7 节，“数据字典使用差异”。INFORMATION_SCHEMA tables in MySQL 8.4 are closely tied to the data dictionary, resulting in several usage differences. See Section 16.7, “Data Dictionary Usage Differences”.

首页上一级下一节