MySQL 8.0 Official Document Chapter 8 Optimization (29)-Optimize INFORMATION_SCHEMA query

table of Contents

The eighth chapter optimization (29)-optimize INFORMATION_SCHEMA query

8.2 Optimizing SQL statements

8.2.3 Optimize INFORMATION_SCHEMA query

Applications that monitor the database may frequently use INFORMATION_SCHEMAtables. To write queries on these tables most efficiently, use the following general guidelines:

Try to query only the view INFORMATION_SCHEMAtable of the data dictionary table.

Try to query only static metadata. Selecting columns or using search conditions to obtain dynamic metadata while querying static metadata will increase the overhead of processing dynamic metadata.

Note:
INFORMATION_SCHEMA The comparison behavior of database and table names in the query may be different from what you expect. For details, see 10.8.7 " INFORMATION_SCHEMAUsing Sorting Rules in Search".

These INFORMATION_SCHEMAtables are implemented as views of the data dictionary table, so to query them is to get 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

Even INFORMATION_SCHEMAsome types of values ​​of non-view tables are retrieved from the data dictionary. This includes database and table names, table types, and storage engines.

Some INFORMATION_SCHEMAtables contain columns that can 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

These columns represent dynamic table metadata, that is, the information changes as the table content changes.

By default, when the query these columns, MySQL will be included in the retrieval mysql.index_statsand mysql.table_statsthe columns of the dictionary table buffer value, which is more efficient than retrieving statistical information directly from the storage engine. If the buffer of statistics information is not available or has expired, so, MySQL will retrieve the latest statistics from storage engine, and put them into the buffer mysql.index_statsand mysql.table_statsthe dictionary table. Subsequent queries retrieve these buffered statistics until the information expires.

information_schema_stats_expiryThe session variable defines the time period for the cache statistics to expire. The default value is 86400 seconds (24 hours), but the time period can be extended to one year.

To update the buffer value for a given table at any time, use it ANALYZE TABLE.

In the following cases, query statistics columns will not be saved or updated mysql.index_statsand mysql.table_statsstatistical information table in the dictionary:

When the buffer statistics have not expired.

When information_schema_stats_expiryset to 0.

When the server is started in read_only (read-only), super_read_only, transaction_read_only or innodb_read_only mode.

When the query also obtains the performance summary (Performance Schema) data.

information_schema_stats_expiryIt is a session variable, and each client session can define its own expiration value. Statistics retrieved from the storage engine and cached by one session can be used in other sessions.

Note:
If you turn on the innodb_read_onlysystem variable, ANALYZE TABLEit may fail because it cannot update the statistics table in the data dictionary using InnoDB. For the ANALYZE TABLEoperation to update the key distribution , even if the operation updates the table itself (for example, if it is a MyISAM table), it may fail. To get updated distribution statistics, please set information_schema_stats_expiry=0.

For tables implemented as data dictionary table views INFORMATION_SCHEMA, indexes on the underlying data dictionary table allow the optimizer to construct efficient query execution plans. To see the choices made by the optimizer, use EXPLAIN. To view INFORMATION_SCHEMAthe query used by the server to execute the query, EXPLAINuse it immediately afterwards SHOW WARNINGS.

Consider the following statement, which identifies the utf8mb4collation of the 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 the statement? To find the answer, please use EXPLAIN:

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 view the query used to count the statement, use 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'))

As SHOW WARNINGSsuggested, the server will COLLATION_CHARACTER_SET_APPLICABILITYquery processing system for the mysql database character_setsand collationsquery the data dictionary tables.


The last episode of MySQL 8.0 official document Chapter 8 Optimization (28)-Push optimization under derived conditions