Applications that monitor databases may make frequent use ofINFORMATION_SCHEMA
tables. 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.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 inINFORMATION_SCHEMA
queries might differ from what you expect. For details, seeSection 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.
TheseINFORMATION_SCHEMA
tables are implemented as views on data dictionary tables, so queries on them retrieve information from the data dictionary:
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-viewINFORMATION_SCHEMA
table, are retrieved by lookups from the data dictionary. This includes values such as database and table names, table types, and storage engines.
SomeINFORMATION_SCHEMA
tables contain columns that provide table statistics:
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 themysql.index_stats
andmysql.table_stats
dictionary tables when the columns are queried, which is more efficient than retrieving statistics directly from the storage engine. If cached statistics are not available or have expired, MySQL retrieves the latest statistics from the storage engine and caches them in themysql.index_stats
andmysql.table_stats
dictionary tables. Subsequent queries retrieve the cached statistics until the cached statistics expire.
Theinformation_schema_stats_expiry
session variable defines the period of time before cached statistics expire. The default is 86400 seconds (24 hours), but the time period can be extended to as much as one year.
To update cached values at any time for a given table, useANALYZE TABLE
.
Querying statistics columns does not store or update statistics in themysql.index_stats
andmysql.table_stats
dictionary tables under these circumstances:
When cached statistics have not expired.
When
information_schema_stats_expiry
is set to 0.When the server is started in
read_only
,super_read_only
,transaction_read_only
, orinnodb_read_only
mode.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. Statistics that are retrieved from the storage engine and cached by one session are available to other sessions.
If theinnodb_read_only
system variable is enabled,ANALYZE TABLE
may fail because it cannot update statistics tables in the data dictionary, which useInnoDB
. ForANALYZE TABLE
operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is aMyISAM
表)。来啊btain the updated distribution statistics, setinformation_schema_stats_expiry=0
.
ForINFORMATION_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. To see the choices made by the optimizer, useEXPLAIN
. To also see the query used by the server to execute anINFORMATION_SCHEMA
query, useSHOW WARNINGS
immediately followingEXPLAIN
.
Consider this statement, which identifies collations for theutf8mb4
character set:
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 | ...
How does the server process that statement? To find out, useEXPLAIN
:
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)
To see the query used to statisfy that statement, useSHOW 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 bySHOW WARNINGS
, the server handles the query onCOLLATION_CHARACTER_SET_APPLICABILITY
as a query on thecharacter_sets
andcollations
data dictionary tables in themysql
system database.