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

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

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

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

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

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

Для сервера 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 реализованы в виде представлений над таблицами словаря данных. В настоящий момент это сделано только для следующих таблиц:

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

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

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

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

Заключение

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

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

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