[ACDU翻译] MySQL 8.2.3优化INFORMATION_SCHEMA查询

监视数据库的应用程序可能会频繁使用 INFORMATION_SCHEMA表。若要最有效地编写这些表的查询，请使用以下常规准则：

• 尝试仅查询INFORMATION_SCHEMA 作为数据字典表视图的表。
• 尝试仅查询静态元数据。选择列或对动态元数据和静态元数据使用检索条件会增加处理动态元数据的开销。

笔记

INFORMATION_SCHEMA查询中 数据库名称和表名称的比较行为 可能与您期望的有所不同。有关详细信息，请参见 第10.8.7节“在INFORMATION_SCHEMA搜索中使用归类”。

这些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

某些类型的值，即使是非视图INFORMATION_SCHEMA表的值，也可以 通过查询从数据字典中检索出来。这包括数据库和表名，表类型和存储引擎之类的值。

一些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

这些列表示动态表元数据。即，信息随着表内容的改变而改变。

默认情况下，查询列时，MySQL从mysql.index_stats和 mysql.table_stats字典表中检索那些列的缓存值，这比直接从存储引擎中检索统计信息效率更高。如果缓存的统计信息不可用或已过期，则MySQL从存储引擎中检索最新的统计信息，并将其缓存在mysql.index_stats和 mysql.table_stats字典表中。后续查询将检索缓存的统计信息，直到缓存的统计信息到期为止。

该 information_schema_stats_expiry 会话变量定义缓存统计到期之前的一段时间。默认值为86400秒（24小时），但是时间段可以延长到一年。

要随时更新给定表的缓存值，请使用 ANALYZE TABLE。

在以下情况下， 查询统计信息列不会在mysql.index_stats和 mysql.table_stats字典表中存储或更新统计信息：

• 缓存的统计信息尚未过期时。
• 当 information_schema_stats_expiry 设置为0。
• 当服务器在启动 read_only， super_read_only， transaction_read_only，或 innodb_read_only模式。
• 当查询还获取性能架构数据时。

information_schema_stats_expiry 是一个会话变量，每个客户端会话都可以定义自己的到期值。从存储引擎检索并由一个会话缓存的统计信息可用于其他会话。

笔记

如果innodb_read_only 启用了系统变量，则ANALYZE TABLE可能会失败，因为它无法更新数据字典中使用的统计表 InnoDB。对于ANALYZE TABLE更新密钥分发的操作，即使该操作更新了表本身（例如，如果它是MyISAM 表），也可能会发生故障。要获取更新的分发统计信息，请设置 information_schema_stats_expiry=0。

对于INFORMATION_SCHEMA实现为数据字典表上的视图的表，基础数据字典表上的索引允许优化器构造有效的查询执行计划。要查看优化程序所做的选择，请使用EXPLAIN。要还查看服务器用于执行 INFORMATION_SCHEMA查询的查询，请SHOW WARNINGS紧接着使用 EXPLAIN。

考虑以下语句，该语句标识utf8mb4字符集的归类 ：

mysql> SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE 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：

mysql> EXPLAIN SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE 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：

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 作为对系统数据库中的character_sets和 collations数据字典表 的查询进行处理 mysql。