MySQL 8

У зв'язку з введенням нового словника даних 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 *************************** >1 select_type: SIMPLE table : TABLES partitions : NULL type: ALL posible_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 *************************** >1 select_type: SIMPLE table : TABLES partitions : NULL type: ALL posible_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 )

Зміни у MySQL 8.0

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

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

Наступна ілюстрація показує різницю в архітектурі між MySQL 5.7 та 8.0.

NULL

Якщо ми виконаємо наведені раніше приклади MySQL 8.0, то побачимо, що оптимізатор планує використовувати індекси таблиць словника даних, в обох випадках.

mysql> EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA like 'test%' AND TABLE_NAME like 't%' ; + --+-----------+-----++------+----------------- -+----------++-----------------------+----+------- -+---------------------------------+ idselect_typetabletype possible_keys key ref rowsfilteredExtra + -- +-----------+-----++------+------------------+---- ------++-----------------------+----+--------+---- -----------------------------+ 1 SIMPLE cat index PRIMARY name NULL 1 100.00 Using index 1 SIMPLE sch ref PRIMARY,catalog_ >6 16.67 Using where ; Using index 1 SIMPLE tbl ref schema_ 26 1.11 Using index condition;Using where 1 SIMPLE col eq_refPRIMARY PRIMARY mysql.tbl.collation_ 1 100.00 Using index -------+-----++------+------------------+--------- -++-----------------------+----+--------+--------- ------------------------+

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

Джерела метаданих

У 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 не реалізовані у вигляді уявлень над таблицями словника даних, дозвольте мені спочатку розповісти, що існує два типи метаданих, які представлені в таблицях 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 для отримання більш детальної інформації.