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

1) Попытка использовать постоянные значения поиска для имён баз данных и таблиц в предложении WHERE

Вы можете использовать этот принцип следующим образом:

Этот принцип применим к таблицам INFORMATION_SCHEMA, показанным в следующей таблице, которая показывает столбцы, для которых постоянное значение поиска позволяет серверу избежать сканирования каталога. Например, если вы выбираете из TABLES, использование постоянного значения поиска для TABLE_SCHEMA в предложении WHERE позволяет избежать сканирования каталога данных.

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

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';

Использование литерального имени базы данных test позволяет серверу проверять только каталог базы данных test, независимо от количества баз данных. В противоположность этому, следующий запрос менее эффективен, поскольку он требует сканирования каталога данных для определения имён баз данных, соответствующих шаблону 'test%':

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'test%';

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

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';

Использование литерального имени таблицы t1 позволяет серверу проверять только файлы для таблицы t1, независимо от количества таблиц в базе данных test. В противоположность этому, следующий запрос требует сканирования каталога базы данных test для определения имён таблиц, соответствующих шаблону 't%':

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';

Следующий запрос требует сканирования каталога базы данных для определения соответствующих имён баз данных для шаблона 'test%' и для каждой соответствующей базы данных – сканирования каталога базы данных для определения соответствующих имён таблиц для шаблона 't%':

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';

2) Составление запросов, минимизирующих количество открываемых файлов таблиц

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

SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test';

В этом случае, после того как сервер отсканировал каталог базы данных для определения имён таблиц в базе данных, эти имена становятся доступными без дополнительных поисков в файловой системе. Таким образом, TABLE_NAME не требует открытия файлов. Значение ENGINE (движок хранения) можно определить, открыв файл .frm таблицы, не затрагивая другие файлы таблиц, такие как .MYD или .MYI файл.

Некоторые значения, такие как INDEX_LENGTH для таблиц MyISAM, требуют открытия файла .MYD или .MYI также.

Типы оптимизации открытия файлов обозначаются следующим образом:

Следующий список показывает, как указанные выше типы оптимизации применяются к столбцам таблиц INFORMATION_SCHEMA. Для таблиц и столбцов, не имеющих имени, ни одна из оптимизаций не применяется.

3) Используйте EXPLAIN, чтобы определить, может ли сервер использовать INFORMATION_SCHEMA оптимизации для запроса

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

mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE
       TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: VIEWS
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned 0 databases

Использование постоянных значений поиска баз данных и таблиц позволяет серверу избежать сканирования каталогов. Для ссылок на VIEWS.TABLE_NAME необходимо открыть только файл .frm.

mysql> EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLES
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Open_full_table; Scanned all databases

Значения поиска не предоставлены (нет WHERE-запроса), поэтому сервер должен сканировать каталог данных и каждый каталог базы данных. Для каждой таким образом определенной таблицы выбирается имя таблицы и формат строки. TABLE_NAME не требует открытия дополнительных файлов таблиц (применяется оптимизация SKIP_OPEN_TABLE). ROW_FORMAT требует открытия всех файлов таблиц (применяется OPEN_FULL_TABLE). EXPLAIN сообщает OPEN_FULL_TABLE, потому что это дороже, чем SKIP_OPEN_TABLE.

mysql> EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_SCHEMA = 'test'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLES
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned 1 database

Значение поиска имени таблицы не предоставлено, поэтому сервер должен просканировать каталог базы данных test. Для столбцов TABLE_NAME и TABLE_TYPE соответственно применяются оптимизации SKIP_OPEN_TABLE и OPEN_FRM_ONLY. EXPLAIN сообщает об этом, так как это дороже.

mysql> EXPLAIN SELECT B.TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B
       WHERE A.TABLE_SCHEMA = 'test'
       AND A.TABLE_NAME = 't1'
       AND B.TABLE_NAME = A.TABLE_NAME\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
         type: ALL
possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Skip_open_table; Scanned 0 databases
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: B
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using where; Open_frm_only; Scanned all databases;
               Using join buffer

Для первой строки вывода EXPLAIN: Постоянные значения поиска базы данных и таблиц позволяют серверу избежать сканирования каталога для значений TABLES. Ссылки на TABLES.TABLE_NAME не требуют дополнительных файлов таблиц.

Для второй строки вывода EXPLAIN: Все значения таблицы COLUMNS являются OPEN_FRM_ONLY поисками, поэтому COLUMNS.TABLE_NAME требует открытия файла .frm.

mysql> EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: COLLATIONS
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:

В этом случае оптимизации не применяются, потому что COLLATIONS не является одной из таблиц INFORMATION_SCHEMA, для которых доступны оптимизации.