MySQL 8.4 Release Notes
监控数据库的应用程序可能会频繁使用INFORMATION_SCHEMA表。为了最有效地编写这些表的查询，请使用以下一般指南：Applications that monitor databases may make frequent use of
INFORMATION_SCHEMA tables. To write queries
for these tables most efficiently, use the following general
guidelines:
尽量只查询那些基于数据字典表的视图的
INFORMATION_SCHEMA表。Try to query onlyINFORMATION_SCHEMAtables that are views on data dictionary tables.尽量只查询静态元数据。将动态元数据与静态元数据一起选择列或使用检索条件会增加处理动态元数据的开销。Try to query only for static metadata. Selecting columns or using retrieval conditions for dynamic metadata along with static metadata adds overhead to process the dynamic metadata.
Note
在 INFORMATION_SCHEMA 查询中，数据库和表名的比较行为可能与您预期的不同。有关详细信息，请参阅 第 12.8.7 节，“使用排序规则在 INFORMATION_SCHEMA 搜索”。Comparison behavior for database and table names in
INFORMATION_SCHEMA queries might differ
from what you expect. For details, see
Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.
这些 INFORMATION_SCHEMA 表是作为数据字典表的视图实现的，因此对它们的查询会从数据字典中检索信息：These INFORMATION_SCHEMA tables are
implemented as views on data dictionary tables, so queries on
them retrieve information from the data dictionary:
CHARACTER_SETS CHECK_CONSTRAINTS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS 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 PARAMETERS PARTITIONS REFERENTIAL_CONSTRAINTS RESOURCE_GROUPS ROUTINES SCHEMATA STATISTICS TABLES TABLE_CONSTRAINTS TRIGGERS VIEWS VIEW_ROUTINE_USAGE VIEW_TABLE_USAGE
即使是非视图的 INFORMATION_SCHEMA 表，其中某些类型的值也是通过从数据字典查找获取的。这包括诸如数据库和表名、表类型以及存储引擎等值。Some types of values, even for a non-view
INFORMATION_SCHEMA table, are retrieved by
lookups from the data dictionary. This includes values such as
database and table names, table types, and storage engines.
一些 INFORMATION_SCHEMA 表包含提供表统计信息的列：Some INFORMATION_SCHEMA tables contain
columns that provide table statistics:
STATISTICS.CARDINALITY TABLES.AUTO_INCREMENT TABLES.AVG_ROW_LENGTH TABLES.CHECKSUM TABLES.CHECK_TIME TABLES.CREATE_TIME TABLES.DATA_FREE TABLES.DATA_LENGTH TABLES.INDEX_LENGTH TABLES.MAX_DATA_LENGTH TABLES.TABLE_ROWS TABLES.UPDATE_TIME
那些列表示动态表元数据；也就是说，随着表内容的变化而变化的信息。Those columns represent dynamic table metadata; that is, information that changes as table contents change.
默认情况下，当查询这些列时，MySQL 会从 mysql.index_stats 和 mysql.innodb_table_stats 字典表中检索缓存的值，这比直接从存储引擎检索统计信息更高效。如果缓存的统计信息不可用或已过期，MySQL 会从存储引擎中检索最新的统计信息，并将其缓存在 mysql.index_stats 和 mysql.innodb_table_stats 字典表中。随后的查询将检索缓存的统计信息，直到缓存的统计信息过期。服务器重启或首次打开 mysql.index_stats 和 mysql.innodb_table_stats 表不会自动更新缓存的统计信息。By default, MySQL retrieves cached values for those columns from
the mysql.index_stats and
mysql.innodb_table_stats dictionary tables
when the columns are queried, which is more efficient than
retrieving statistics directly from the storage engine. If
cached statistics are not available or have expired, MySQL
retrieves the latest statistics from the storage engine and
caches them in the mysql.index_stats and
mysql.innodb_table_stats dictionary tables.
Subsequent queries retrieve the cached statistics until the
cached statistics expire. A server restart or the first opening
of the mysql.index_stats and
mysql.innodb_table_stats tables do not update
cached statistics automatically.
information_schema_stats_expiry 会话变量定义了缓存统计数据过期的时间周期。默认值为 86400 秒（24 小时），但该时间周期可以延长至最多一年。The
information_schema_stats_expiry
session variable defines the period of time before cached
statistics expire. The default is 86400 seconds (24 hours), but
the time period can be extended to as much as one year.
要在任意时间更新给定表的缓存值，请使用 ANALYZE TABLE。To update cached values at any time for a given table, use
ANALYZE TABLE.
在以下情况下，查询统计列不会在 mysql.index_stats 和 mysql.innodb_table_stats 字典表中存储或更新统计数据：Querying statistics columns does not store or update statistics
in the mysql.index_stats and
mysql.innodb_table_stats dictionary tables
under these circumstances:
当缓存的统计信息未过期时。When cached statistics have not expired.
当
information_schema_stats_expiry设置为 0 时。Wheninformation_schema_stats_expiryis set to 0.当服务器处于
read_only、super_read_only、transaction_read_only或innodb_read_only模式时。When the server is inread_only,super_read_only,transaction_read_only, orinnodb_read_onlymode.当查询也获取性能模式数据时。When the query also fetches Performance Schema data.
information_schema_stats_expiry 是一个会话变量，每个客户端会话可以定义自己的过期值。从存储引擎检索并由一个会话缓存的统计信息对其他会话也是可用的。information_schema_stats_expiry
is a session variable, and each client session can define its
own expiration value. Statistics that are retrieved from the
storage engine and cached by one session are available to other
sessions.
Note
如果启用了 innodb_read_only 系统变量，ANALYZE
TABLE 可能会失败，因为它无法更新使用 InnoDB 的数据字典中的统计表。对于更新键分布的 ANALYZE
TABLE 操作，即使操作更新了表本身（例如，如果它是 MyISAM 表），也可能会失败。要获得更新的分布统计信息，请设置 information_schema_stats_expiry=0。If the innodb_read_only
system variable is enabled, ANALYZE
TABLE may fail because it cannot update statistics
tables in the data dictionary, which use
InnoDB. For ANALYZE
TABLE operations that update the key distribution,
failure may occur even if the operation updates the table
itself (for example, if it is a MyISAM
table). To obtain the updated distribution statistics, set
information_schema_stats_expiry=0.
对于以数据字典表视图形式实现的 INFORMATION_SCHEMA 表，底层数据字典表上的索引允许优化器构建高效的查询执行计划。要查看优化器所做的选择，请使用 EXPLAIN。要查看服务器用于执行 INFORMATION_SCHEMA 查询的实际查询语句，请在 EXPLAIN 之后立即使用 SHOW WARNINGS。For INFORMATION_SCHEMA tables implemented as
views on data dictionary tables, indexes on the underlying data
dictionary tables permit the optimizer to construct efficient
query execution plans. To see the choices made by the optimizer,
use EXPLAIN. To also see the
query used by the server to execute an
INFORMATION_SCHEMA query, use
SHOW WARNINGS immediately
following EXPLAIN.
考虑以下语句，它用于识别 utf8mb4 字符集的排序规则：Consider this statement, which identifies collations for the
utf8mb4 character set:
mysql>SELECT COLLATION_NAMEFROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITYWHERE CHARACTER_SET_NAME = 'utf8mb4';+----------------------------+ | COLLATION_NAME | +----------------------------+ | utf8mb4_general_ci | | utf8mb4_bin | | utf8mb4_unicode_ci | | utf8mb4_icelandic_ci | | utf8mb4_latvian_ci | | utf8mb4_romanian_ci | | utf8mb4_slovenian_ci | ...
服务器如何处理该语句？要了解情况，请使用 EXPLAIN：How does the server process that statement? To find out, use
EXPLAIN:
mysql>EXPLAIN SELECT COLLATION_NAMEFROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITYWHERE CHARACTER_SET_NAME = 'utf8mb4'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: cs partitions: NULL type: const possible_keys: PRIMARY,name key: name key_len: 194 ref: const rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: col partitions: NULL type: ref possible_keys: character_set_id key: character_set_id key_len: 8 ref: const rows: 68 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.01 sec)
要查看用于执行该语句的查询，请使用 SHOW WARNINGS：To see the query used to satisfy that statement, use
SHOW WARNINGS:
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `mysql`.`col`.`name` AS `COLLATION_NAME`
from `mysql`.`character_sets` `cs`
join `mysql`.`collations` `col`
where ((`mysql`.`col`.`character_set_id` = '45')
and ('utf8mb4' = 'utf8mb4'))
正如 SHOW WARNINGS 所示，服务器将对 COLLATION_CHARACTER_SET_APPLICABILITY 的查询处理为对 mysql 系统数据库中的 character_sets 和 collations 数据字典表的查询。As indicated by SHOW WARNINGS,
the server handles the query on
COLLATION_CHARACTER_SET_APPLICABILITY
as a query on the character_sets and
collations data dictionary tables in the
mysql system database.