10.2.3 优化INFORMATION_SCHEMA查询
监控数据库的应用程序可能会频繁使用INFORMATION_SCHEMA
表。为了编写这些表的查询,遵循以下一般指南:
-
尽量只查询
INFORMATION_SCHEMA
表,这些表是数据字典表的视图。 -
尽量只查询静态元数据。同时选择列或使用检索条件来查询动态元数据会增加处理动态元数据的开销。
在INFORMATION_SCHEMA
查询中,数据库和表名的比较行为可能与您期望的不同。详细信息请见第12.8.7节,“在INFORMATION_ SCHEMA搜索中使用排序规则”。
这些INFORMATION_ SCHEMA
表是数据字典表的视图,因此对它们的查询将从数据字典中检索信息:
Press CTRL+C to copyCHARACTER_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
表包含提供表统计信息的列:
Press CTRL+C to copySTATISTICS.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
字典表中:
-
当缓存统计信息未过期时。
-
在服务器处于
read_only
、super_read_only
、transaction_read_only
或innodb_read_only
模式时。 -
当查询还需要 fetch Performance Schema 数据时。
information_schema_stats_expiry
是一个会话变量,每个客户端会话都可以定义自己的过期值。由存储引擎检索的统计数据被一个会话缓存在其他会话中可用。
如果innodb_read_only
系统变量启用,ANALYZE TABLE
可能失败,因为它不能更新数据字典中的统计表,这些表使用InnoDB
。即使操作更新了表本身(例如,如果是MyISAM
表),也可能出现失败。要获取更新的分布统计数据,请设置information_schema_stats_expiry=0
。
对于实现为数据字典表视图的INFORMATION_SCHEMA
表,基于数据字典表的索引允许优化器构建高效的查询执行计划。要查看优化器的选择结果,请使用EXPLAIN
。要同时查看服务器用于执行INFORMATION_SCHEMA
查询的查询语句,请使用SHOW WARNINGS
,紧接着EXPLAIN
。
考虑以下语句,该语句用于识别utf8mb4
字符集的collations:
Press CTRL+C to copymysql> 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
:
Press CTRL+C to copymysql> 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
:
Press CTRL+C to copymysql> 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系统数据库中。