Приложения, которые отслеживают базы данных, могут часто использовать INFORMATION_SCHEMA таблицы. Для эффективной записи запросов к этим таблицам используйте следующие общие рекомендации:

Эти INFORMATION_SCHEMA таблицы реализованы как представления на таблицах словаря данных, поэтому запросы к ним извлекают информацию из словаря данных:

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

Некоторые типы значений, даже для не-представления INFORMATION_SCHEMA таблицы, извлекаются путём поиска в словаре данных. Это включает такие значения, как имена баз данных и таблиц, типы таблиц и движки хранения.

Некоторые INFORMATION_SCHEMA таблицы содержат столбцы, предоставляющие статистику по таблицам:

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

Эти столбцы представляют динамические метаданные таблиц, то есть информацию, которая меняется при изменении содержимого таблиц.

По умолчанию MySQL извлекает кэшированные значения для этих столбцов из mysql.index_stats и mysql.innodb_table_stats таблиц словаря при запросе к столбцам, что более эффективно, чем прямое получение статистики из движка хранения. Если кэшированная статистика недоступна или устарела, MySQL извлекает последнюю статистику из движка хранения и кеширует её в mysql.index_stats и mysql.innodb_table_stats таблицах словаря. Последующие запросы извлекают кэшированную статистику, пока она не устареет. Перезапуск сервера или первое открытие mysql.index_stats и mysql.innodb_table_stats таблиц не обновляют кэшированную статистику автоматически.

Переменная сеанса information_schema_stats_expiry определяет период времени, по истечении которого кэшированная статистика становится устаревшей. По умолчанию это 86400 секунд (24 часа), но этот период может быть увеличен до одного года.

Для обновления кэшированных значений в любое время для данной таблицы используйте ANALYZE TABLE.

Запрос к столбцам статистики не сохраняет и не обновляет статистику в mysql.index_stats и mysql.innodb_table_stats таблицах словаря в следующих случаях:

information_schema_stats_expiry — переменная сеанса, и каждый сеанс клиента может определить своё значение истечения срока действия. Статистика, которая извлекается из движка хранения и кешируется одним сеансом, доступна другим сеансам.

Для INFORMATION_SCHEMA таблиц, реализованных как представления на таблицах словаря данных, индексы на базовых таблицах словаря данных позволяют оптимизатору создавать эффективные планы выполнения запросов. Чтобы увидеть выбор, сделанный оптимизатором, используйте EXPLAIN. Чтобы также увидеть запрос, используемый сервером для выполнения INFORMATION_SCHEMA запроса, используйте SHOW WARNINGS сразу после EXPLAIN.

Рассмотрим такое утверждение, которое идентифицирует сортировки для набора символов utf8mb4:

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

Как сервер обрабатывает это утверждение? Чтобы узнать, используйте 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)

Чтобы увидеть запрос, используемый для выполнения этого утверждения, используйте 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'))

Как указано в SHOW WARNINGS, сервер обрабатывает запрос к COLLATION_CHARACTER_SET_APPLICABILITY как запрос к character_sets и collations таблицам словаря в базе данных mysql.