MySQL :: MySQL 8.4 Reference Manual :: 10.2.3 Optimizing INFORMATION

Documentation Home

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / Optimizing INFORMATION_SCHEMA Queries

10.2.3 优化INFORMATION_SCHEMA查询

监控数据库的应用程序可能会频繁使用INFORMATION_SCHEMA表。为了编写这些表的查询，遵循以下一般指南：

尽量只查询INFORMATION_SCHEMA表，这些表是数据字典表的视图。
尽量只查询静态元数据。同时选择列或使用检索条件来查询动态元数据会增加处理动态元数据的开销。

Note

在INFORMATION_SCHEMA查询中，数据库和表名的比较行为可能与您期望的不同。详细信息请见第12.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.innodb_table_stats字典表中检索缓存值，这比直接从存储引擎中检索统计信息更高效。如果缓存统计信息不可用或已过期，MySQL将从存储引擎中检索最新的统计信息，并将其缓存在mysql.index_stats和mysql.innodb_table_stats字典表中。随后的查询将检索缓存统计信息直到缓存统计信息过期。服务器重启或首次打开mysql.index_stats和mysql.innodb_table_stats表不自动更新缓存统计信息。

information_schema_stats_expiry会话变量定义了缓存统计信息过期的时间期限。默认值为86400秒（24小时），但可以延长到最多一年。

要在任何时候更新给定表的缓存值，请使用ANALYZE TABLE。

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

当缓存统计信息未过期时。
当information_schema_stats_expiry设置为0时。
在服务器处于read_only、super_read_only、transaction_read_only或innodb_read_only模式时。
当查询还需要 fetch Performance Schema 数据时。

information_schema_stats_expiry是一个会话变量，每个客户端会话都可以定义自己的过期值。由存储引擎检索的统计数据被一个会话缓存在其他会话中可用。

Note

如果innodb_read_only系统变量启用，ANALYZE TABLE可能失败，因为它不能更新数据字典中的统计表，这些表使用InnoDB。即使操作更新了表本身（例如，如果是MyISAM表），也可能出现失败。要获取更新的分布统计数据，请设置information_schema_stats_expiry=0。

对于实现为数据字典表视图的INFORMATION_SCHEMA表，基于数据字典表的索引允许优化器构建高效的查询执行计划。要查看优化器的选择结果，请使用EXPLAIN。要同时查看服务器用于执行INFORMATION_SCHEMA查询的查询语句，请使用SHOW WARNINGS，紧接着EXPLAIN。

考虑以下语句，该语句用于识别utf8mb4字符集的collations：

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系统数据库中。

PREV HOME UP NEXT