优化 INFORMATION_SCHEMA 查询
监视数据库的应用程序可能会频繁使用INFORMATION_SCHEMA表。若要最有效地编写这些表的查询，请使用以下常规准则：
- 尝试仅查询
INFORMATION_SCHEMA作为数据字典表视图的表。 - 尝试仅查询静态元数据。选择列或对动态元数据和静态元数据使用检索条件会增加处理动态元数据的开销。
注意
INFORMATION_SCHEMA查询中数据库名称和表名称的比较行为可能与您期望的有所不同。有关详细信息，请参见“在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_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：
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 Extr : 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 Extr : 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。