Rechercher dans le manuel MySQL

8.2.3 Optimizing INFORMATION_SCHEMA Queries

Inhaltsverzeichnis

Applications that monitor databases may make frequent use of INFORMATION_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.

Note

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

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

Some INFORMATION_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 the mysql.index_stats and mysql.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 the mysql.index_stats and mysql.table_stats dictionary tables. Subsequent queries retrieve the cached statistics until the cached statistics expire.

The information_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, use ANALYZE TABLE.

Querying statistics columns does not store or update statistics in the mysql.index_stats and mysql.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.

Note

If the innodb_read_only system variable is enabled, ANALYZE TABLE may fail because it cannot update statistics tables in the data dictionary, which use InnoDB. For ANALYZE TABLE operations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is a MyISAM table). To obtain the updated distribution statistics, set information_schema_stats_expiry=0.

For INFORMATION_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, use EXPLAIN. To also see the query used by the server to execute an INFORMATION_SCHEMA query, use SHOW WARNINGS immediately following EXPLAIN.

Consider this statement, which identifies collations for the utf8mb4 character set:

  1. mysql> SELECT COLLATION_NAME
  2.        FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
  3.        WHERE CHARACTER_SET_NAME = 'utf8mb4';
  4. +----------------------------+
  5. | COLLATION_NAME             |
  6. +----------------------------+
  7. | utf8mb4_general_ci         |
  8. | utf8mb4_bin                |
  9. | utf8mb4_unicode_ci         |
  10. | utf8mb4_icelandic_ci       |
  11. | utf8mb4_latvian_ci         |
  12. | utf8mb4_romanian_ci        |
  13. | utf8mb4_slovenian_ci       |
  14. ...

How does the server process that statement? To find out, use EXPLAIN:

  1. mysql> EXPLAIN SELECT COLLATION_NAME
  2.        FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
  3.        WHERE CHARACTER_SET_NAME = 'utf8mb4'\G
  4. *************************** 1. row ***************************
  5.            id: 1
  6.   select_type: SIMPLE
  7.         table: cs
  8.    partitions: NULL
  9.          type: const
  10. possible_keys: PRIMARY,name
  11.           key: name
  12.       key_len: 194
  13.           ref: const
  14.          rows: 1
  15.      filtered: 100.00
  16.         Extra: Using index
  17. *************************** 2. row ***************************
  18.            id: 1
  19.   select_type: SIMPLE
  20.         table: col
  21.    partitions: NULL
  22.          type: ref
  23. possible_keys: character_set_id
  24.           key: character_set_id
  25.       key_len: 8
  26.           ref: const
  27.          rows: 68
  28.      filtered: 100.00
  29.         Extra: NULL
  30. 2 rows in set, 1 warning (0.01 sec)

To see the query used to statisfy that statement, use SHOW WARNINGS:

  1. mysql> SHOW WARNINGS\G
  2. *************************** 1. row ***************************
  3.   Level: Note
  4.    Code: 1003
  5. Message: /* select#1 */ select `mysql`.`col`.`name` AS `COLLATION_NAME`
  6.          from `mysql`.`character_sets` `cs`
  7.          join `mysql`.`collations` `col`
  8.          where ((`mysql`.`col`.`character_set_id` = '45')
  9.          and ('utf8mb4' = 'utf8mb4'))

As indicated by SHOW WARNINGS, the server handles the query on COLLATION_CHARACTER_SET_APPLICABILITY as a query on the character_sets and collations data dictionary tables in the mysql system database.


Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-information-schema-optimization.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut