10bet网址
岬QL 8.0 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

8.2.3 Optimizing INFORMATION_SCHEMA Queries

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

  • Try to query onlyINFORMATION_SCHEMAtables 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.

Note

Comparison behavior for database and table names inINFORMATION_SCHEMAqueries might differ from what you expect. For details, seeSection 10.8.7, “Using Collation in INFORMATION_SCHEMA Searches”.

TheseINFORMATION_SCHEMAtables 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_SCHEMAtable, are retrieved by lookups from the data dictionary. This includes values such as database and table names, table types, and storage engines.

SomeINFORMATION_SCHEMAtables 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_statsandmysql.table_statsdictionary 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_statsandmysql.table_statsdictionary tables. Subsequent queries retrieve the cached statistics until the cached statistics expire.

Theinformation_schema_stats_expirysession 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_statsandmysql.table_statsdictionary tables under these circumstances:

information_schema_stats_expiryis 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.

Note

If theinnodb_read_onlysystem variable is enabled,ANALYZE TABLEmay fail because it cannot update statistics tables in the data dictionary, which useInnoDB. ForANALYZE TABLEoperations 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_SCHEMAtables 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_SCHEMAquery, useSHOW WARNINGSimmediately followingEXPLAIN.

Consider this statement, which identifies collations for theutf8mb4character 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_APPLICABILITYas a query on thecharacter_setsandcollationsdata dictionary tables in themysqlsystem database.