SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

MySQL 8.0: улучшения в INFORMATION_SCHEMA

Дата: 30.12.2016

Данная статья является переводом статьи Гопала Шанкара (Gopal Shankar).

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

Кровавое прошлое

INFORMATION_SCHEMA была введена в MySQL 5.0 как, соответствующий стандарту SQL, способ получения метаданных с запущенного сервера MySQL. Были жалобы на производительность определенных запросов к INFORMATION_SCHEMA, особенно в случае большого количества объектов баз данных (базы, таблицы и т.д.)

В попытке решить эти проблемы, начиная с MySQL 5.1 мы сделали ряд оптимизаций производительности для увеличения скорости выполнения запросов к INFORMATION_SCHEMA. Эти оптимизации описаны в документации, и применяются, когда пользователь дает явные имя базы и имя таблицы в запросе.

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

  • метаданные из файлов, например, определения таблиц из .FRM файлов
  • детали из систем хранения, например, динамические метаданные таблиц
  • данные из глобальной структуры данных на сервере MySQL.

Для сервера MySQL, имеющего сотни баз данных, в каждой из которых сотни таблиц, запрос к INFORMATION_SCHEMA будет делать много обращений к файловой системе, чтобы прочитать все .FRM файлы. Также будет использоваться много ресурсов процессора, чтобы открыть таблицу и подготовить соответствующие структуры данных в памяти. Можно попытаться снизить нагрузку, используя кэш таблиц (системная переменная ‘table_definition_cache‘), однако, в большинстве случаев он не достаточно велик, чтобы вместить все нужные таблицы.

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

mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
    -> TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1'\G
    *************************** 1. row ***************************
           id: 1
      select_type: SIMPLE
        table: TABLES
       partitions: NULL
         type: ALL
    possible_keys: NULL
          key: TABLE_SCHEMA,TABLE_NAME
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Skip_open_table; Scanned 0 databases
    1 row in set, 1 warning (0.00 sec)
 
    mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
    -> TABLE_SCHEMA like 'test%' AND TABLE_NAME like 't%'\G
    *************************** 1. row ***************************
           id: 1
      select_type: SIMPLE
        table: TABLES
       partitions: NULL
         type: ALL
    possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using where; Skip_open_table; Scanned all databases
    1 row in set, 1 warning (0.00 sec)

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

Изменения в MySQL 8.0

Одним из основных изменений в версии 8.0 является введение общего словаря данных, реализованного в виде набора InnoDB таблиц. Это позволило нам избавиться от файлового хранения метаданных (больше нет .FRM файлов), а также приблизило MySQL к поддержке транзакционных DDL. Более подробная информация о новом словаре данных и его преимуществах дана в этой статье.

Теперь, когда метаданные всех таблиц хранятся в транзакционных таблицах словаря данных, стало возможным реализовать таблицы INFORMATION_SCHEMA в виде представлений (VIEW) над таблицами словаря данных. Это исключает затраты на создание временных таблиц при каждом обращении к INFORMATION_SCHEMA и сканирование файловой системы в поиске нужных .FRM файлов. Кроме того, стало возможным использовать всю мощь оптимизатора MySQL для подготовки лучших планов выполнения с использованием индексов в таблицах словаря данных.

Следующая иллюстрация показывает разницу в архитектуре между MySQL 5.7 и 8.0.

Если мы выполним приведенные ранее примеры в MySQL 8.0, то увидим, что оптимизатор планирует использовать индексы таблиц словаря данных, в обоих случаях.

mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
  +--+-----------+-----++------+------------------+----------++----------------------+----+--------+----------------------------------+
  |id|select_type|table||type  |possible_keys     |key       ||ref                   |rows|filtered|Extra                             |
  +--+-----------+-----++------+------------------+----------++----------------------+----+--------+----------------------------------+
  | 1|SIMPLE     |cat  ||index |PRIMARY           |name      ||NULL                  |   1|  100.00|Using index                       |
  | 1|SIMPLE     |sch  ||eq_ref|PRIMARY,catalog_id|catalog_id||mysql.cat.id,const    |   1|  100.00|Using index                       |
  | 1|SIMPLE     |tbl  ||eq_ref|schema_id         |schema_id ||mysql.sch.id,const    |   1|   10.00|Using index condition; Using where|
  | 1|SIMPLE     |col  ||eq_ref|PRIMARY           |PRIMARY   ||mysql.tbl.collation_id|   1|  100.00|Using index                       |
  +--+-----------+-----++------+------------------+----------++----------------------+----+--------+----------------------------------+
 
  mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA like 'test%' AND TABLE_NAME like 't%';
  +--+-----------+-----++------+------------------+----------++-----------------------+----+--------+---------------------------------+
  |id|select_type|table||type  |possible_keys     |key       || ref                   |rows|filtered|Extra                            |
  +--+-----------+-----++------+------------------+----------++-----------------------+----+--------+---------------------------------+
  | 1|SIMPLE     |cat  ||index |PRIMARY           |name      || NULL                  |   1|  100.00|Using index                      |
  | 1|SIMPLE     |sch  ||ref   |PRIMARY,catalog_id|catalog_id|| mysql.cat.id          |   6|   16.67|Using where; Using index         |
  | 1|SIMPLE     |tbl  ||ref   |schema_id         |schema_id || mysql.sch.id          |  26|    1.11|Using index condition;Using where|
  | 1|SIMPLE     |col  ||eq_ref|PRIMARY           |PRIMARY   || mysql.tbl.collation_id|   1|  100.00|Using index                      |
  +--+-----------+-----++------+------------------+----------++-----------------------+----+--------+---------------------------------+

Новая архитектура INFORMATION_SCHEMA дает значительный прирост производительности по сравнению с тем, что было ранее. Например, следующий запрос (для 100 баз данных с 50 таблицами в каждой) будет в 100 раз быстрее:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, ROW_FORMAT
  FROM information_schema.tables
  WHERE TABLE_SCHEMA LIKE 'db%';

Источники метаданных

В MySQL 8.0 не все таблицы INFORMATION_SCHEMA реализованы в виде представлений над таблицами словаря данных. В настоящий момент это сделано только для следующих таблиц:

  • SCHEMATA
  • TABLES
  • COLUMNS
  • VIEWS
  • CHARACTER_SETS
  • COLLATIONS
  • COLLATION_CHARACTER_SET_APPLICABILITY
  • STATISTICS
  • KEY_COLUMN_USAGE
  • TABLE_CONSTRAINTS

В будущих версиях MySQL 8 мы планируем добавить реализацию в виде представлений для следующих таблиц INFORMATION_SCHEMA:

  • EVENTS
  • TRIGGERS
  • ROUTINES
  • REFERENTIAL_CONSTRAINTS

Чтобы объяснить, почему некоторые запросы к INFORMATION_SCHEMA не реализованы в виде представлений над таблицами словаря данных, позвольте мне сначала рассказать, что существует 2 типа метаданных, которые представлены в таблицах INFORMATION_SCHEMA:

  • Статические метаданные таблицы. Например: TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE. Эти статистические данные будут считываться непосредственно из словаря данных.
  • Динамические метаданные таблицы. Например: AUTO_INCREMENT, AVG_ROW_LENGTH, DATA_FREE. Динамические метаданные часто меняются (например: auto_increment значение будет увеличиваться после каждой вставки). Во многих случаях точный расчет динамических метаданных несет дополнительные расходы, и не всегда эта точность нужна. Например, для DATA_FREE, которая показывает количество свободных байт в таблице, обычно бывает достаточно кэшированного значения.

В MySQL 8.0 динамические метаданные по умолчанию кэшируются. Для настройки используйте переменную information_schema_stats (по умолчанию установлено значение cached); для того, чтобы всегда получать динамическую информацию напрямую из системы хранения данных (за счет чуть более высокой стоимости выполнения запроса), установите information_schema_stats=latest.

В качестве альтернативы пользователь может выполнить ANALYZE TABLE, чтобы обновить кэшированные метаданные.

Заключение

Архитектура INFORMATION_SCHEMA в MySQL 8.0 это большой шаг вперед, позволяющий:

  • избавиться от многочисленных прежних ошибок
  • использовать всю мощь оптимизатора MySQL при выполнении запросов к INFORMATION_SCHEMA
  • ускорить выполнение некоторых запросов к INFORMATION_SCHEMA в 100 раз по сравнению с MySQL 5.7

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

Спасибо, что выбрали MySQL!

Дата публикации: 30.12.2016

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Статьи :
 Установка и настройка MySQL
 Коды ошибок в MySQL
 Программирование в MySQL
>Оптимизация производительности
 Кодировка символов в MySQL
 Хранение данных в MySQL
 MySQL Cluster
См. также:
 Оптимизация производительности MySQL
 Онлайн-курс по оптимизации MySQL
 Услуги по оптимизации MySQL