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. Этот вариант конфигурации приведет к несколько более высокой стоимости выполнения запроса к INFORMATION_SCHEMA.
В качестве альтернативы пользователь может выполнить 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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
|
