Applications that monitor databases may make frequent use of 监视数据库的应用程序可能频繁使用INFORMATION_SCHEMA tables. INFORMATION_SCHEMA表。To write queries for these tables most efficiently, use the following general guidelines:要最有效地编写这些表的查询，请使用以下一般准则：
Try to query only 尝试仅查询INFORMATION_SCHEMA tables that are views on data dictionary tables.INFORMATION_SCHEMA表，它是数据字典表的视图。
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.选择列或将动态元数据的检索条件与静态元数据一起使用会增加处理动态元数据的开销。
Comparison behavior for database and table names in INFORMATION_SCHEMA queries might differ from what you expect. INFORMATION_SCHEMA查询中数据库和表名的比较行为可能与您预期的不同。For details, see Section 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.有关详细信息，请参阅第10.8.7节，“在信息_模式搜索中使用排序规则”。
These 这些INFORMATION_SCHEMA tables are implemented as views on data dictionary tables, so queries on them retrieve information from the data dictionary:INFORMATION_SCHEMA表作为数据字典表的视图实现，因此对它们的查询从数据字典中检索信息：
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
Some types of values, even for a non-view 某些类型的值（即使对于非视图INFORMATION_SCHEMA table, are retrieved by lookups from the data dictionary. INFORMATION_SCHEMA）也是通过从数据字典中查找来检索的。This includes values such as database and table names, table types, and storage engines.这包括数据库和表名称、表类型和存储引擎等值。
Some 某些INFORMATION_SCHEMA tables contain columns that provide table statistics:INFORMATION_SCHEMA包含提供表统计信息的列：
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.这些列表示动态表元数据；也就是说，随着表内容的变化而变化的信息。
By default, MySQL retrieves cached values for those columns from the 默认情况下，当查询这些列时，MySQL会从mysql.index_stats and mysql.table_stats dictionary tables when the columns are queried, which is more efficient than retrieving statistics directly from the storage engine. mysql.index_stats和mysql.table_stats字典表中检索这些列的缓存值，这比直接从存储引擎检索统计数据更有效。If cached statistics are not available or have expired, MySQL retrieves the latest statistics from the storage engine and caches them in the 如果缓存的统计数据不可用或已过期，MySQL将从存储引擎检索最新的统计数据，并将其缓存在mysql.index_stats and mysql.table_stats dictionary tables. mysql.index_stats和mysql.table_stats字典表中。Subsequent queries retrieve the cached statistics until the cached statistics expire.后续查询检索缓存的统计信息，直到缓存的统计信息过期。
The information_schema_stats_expiry session variable defines the period of time before cached statistics expire. information_schema_stats_expir会话变量定义缓存的统计信息过期之前的时间段。The default is 86400 seconds (24 hours), but the time period can be extended to as much as one year.默认值为86400秒（24小时），但时间段可以延长到一年。
To update cached values at any time for a given table, use 要随时更新给定表的缓存值，请使用ANALYZE TABLE.ANALYZE TABLE。
Querying statistics columns does not store or update statistics in the 在以下情况下，查询统计列不会存储或更新mysql.index_stats and mysql.table_stats dictionary tables under these circumstances:mysql.index_stats和mysql.table_stats字典表中的统计信息：
When cached statistics have not expired.当缓存的统计信息尚未过期时。
When 当information_schema_stats_expiry is set to 0.information_schema_stats_expiry被设置为0时。
When the server is started in 当服务器用read_only, super_read_only, transaction_read_only, or innodb_read_only mode.read_only、super_read_only、transaction_read_only或innodb_read_only模式启动时。
When the query also fetches Performance Schema data.当查询还获取性能模式数据时。
information_schema_stats_expiry is a session variable, and each client session can define its own expiration value. information_schema_stats_expiry是一个会话变量，每个客户端会话都可以定义自己的到期值。Statistics that are retrieved from the storage engine and cached by one session are available to other sessions.从存储引擎检索并由一个会话缓存的统计信息可用于其他会话。
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. innodb_read_only系统变量，则ANALYZE TABLE可能会失败，因为它无法更新使用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). ANALYZE TABLE操作，即使该操作更新了表本身（例如，如果它是MyISAM表），也可能发生故障。To obtain the updated distribution statistics, set 要获取更新的分布统计信息，请设置information_schema_stats_expiry=0.information_schema_stats_expiry=0。
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. INFORMATION_SCHEMA表，底层数据字典表上的索引允许优化器构造有效的查询执行计划。To see the choices made by the optimizer, use 要查看优化器所做的选择，请使用EXPLAIN. EXPLAIN。To also see the query used by the server to execute an 要查看服务器用于执行信息模式查询的查询，请使用INFORMATION_SCHEMA query, use SHOW WARNINGS immediately following EXPLAIN.EXPLAIN后面紧跟着SHOW WARNINGS命令。
Consider this statement, which identifies collations for the 考虑这个语句，它标识utf8mb4 character set:utf8mb4字符集的排序：
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 | ...
How does the server process that statement? 服务器如何处理该语句？To find out, use 要找到答案，请使用EXPLAIN: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)
To see the query used to statisfy that statement, use 要查看用于统计该语句的查询，请使用SHOW WARNINGS: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'))
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.SHOW WARNINGS所示，服务器将对COLLATION_CHARACTER_SET_APPLICABILITY的查询作为对mysql系统数据库中的character_sets和collations数据字典表的查询来处理。