Movatterモバイル変換

Applications that monitor databases may make frequent use ofINFORMATION_SCHEMA tables. To write queries for these tables most efficiently, use the following general guidelines:

TheseINFORMATION_SCHEMA tables are implemented as views on data dictionary tables, so queries on them retrieve information from the data dictionary:

CHARACTER_SETSCHECK_CONSTRAINTSCOLLATIONSCOLLATION_CHARACTER_SET_APPLICABILITYCOLUMNSEVENTSFILESINNODB_COLUMNSINNODB_DATAFILESINNODB_FIELDSINNODB_FOREIGNINNODB_FOREIGN_COLSINNODB_INDEXESINNODB_TABLESINNODB_TABLESPACESINNODB_TABLESPACES_BRIEFINNODB_TABLESTATSKEY_COLUMN_USAGEPARAMETERSPARTITIONSREFERENTIAL_CONSTRAINTSRESOURCE_GROUPSROUTINESSCHEMATASTATISTICSTABLESTABLE_CONSTRAINTSTRIGGERSVIEWSVIEW_ROUTINE_USAGEVIEW_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.CARDINALITYTABLES.AUTO_INCREMENTTABLES.AVG_ROW_LENGTHTABLES.CHECKSUMTABLES.CHECK_TIMETABLES.CREATE_TIMETABLES.DATA_FREETABLES.DATA_LENGTHTABLES.INDEX_LENGTHTABLES.MAX_DATA_LENGTHTABLES.TABLE_ROWSTABLES.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.innodb_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.innodb_table_stats dictionary tables. Subsequent queries retrieve the cached statistics until the cached statistics expire. A server restart or the first opening of themysql.index_stats andmysql.innodb_table_stats tables do not update cached statistics automatically.

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.innodb_table_stats dictionary tables under these circumstances:

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.

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: constpossible_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: refpossible_keys: character_set_id          key: character_set_id      key_len: 8          ref: const         rows: 68     filtered: 100.00        Extra: NULL2 rows in set, 1 warning (0.01 sec)

To see the query used to satisfy that statement, useSHOW WARNINGS:

mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Note   Code: 1003Message: /* 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.