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> ОБЯСНЕТЕ SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE -> TABLE_SCHEMA = 'тест' И TABLE_NAME = 't1' \G ******************************** 1 . ред *************************** >1 select_type: SIMPLE таблица: TABLES дялове: NULL type: ALL possible_keys: NULL key: TABLE_SCHEMA,TABLE_NAME key_len: NULL ref: NULL редове : NULL филтрирано : NULL Допълнително: Използване на where ; Skip_open_table; Сканирани 0 бази данни 1 ред в набор, 1 предупреждение (0,00 сек.)

mysql> ОБЯСНЕТЕ SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE -> TABLE_SCHEMA като 'test%' И TABLE_NAME като 't%' \G **************************** 1 . ред *************************** >1 select_type: SIMPLE table: TABLES partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filte червено: NULL Допълнително: Използване на where; Skip_open_table; Сканирани всички бази данни 1 ред в набор, 1 предупреждение (0,00 сек)

Промени в MySQL 8.0

Една от основните промени във версия 8.0 е въвеждането на споделен речник на данни, реализиран като набор от InnoDB таблици. Това ни позволи да се отървем от файловото съхранение на метаданни (без повече .FRM файлове), а също така доближи MySQL до поддръжката на транзакционен DDL. Повече информация за новия речник на данни и неговите предимства е дадена в тази статия.

Сега, когато метаданните на всички таблици се съхраняват в таблици с речник на транзакционни данни, вече е възможно да се внедрят таблици INFORMATION_SCHEMA като изгледи (VIEWs) върху таблици с речник на данни. Това елиминира излишните разходи за създаване на временни таблици всеки път, когато се осъществява достъп до INFORMATION_SCHEMA и сканиране на файловата система за правилните .FRM файлове. Освен това стана възможно да се използва пълната мощ на MySQL оптимизатора за изготвяне на по-добри планове за изпълнение, като се използват индекси на таблици с речник на данни.

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

mysql

Ако изпълним предишните примери в MySQL 8.0, ще видим, че оптимизаторът планира да използва индекси на таблица с речник на данни и в двата случая.

mysql> ОБЯСНЕТЕ SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA като 'test%' AND TABLE_NAME като 't%' ; + --+-----------+-----++------+------------------+--------+----+-------+----------------------------------+ idselect_typetabletype possible_keys key ref rowsfilteredExtra + --+-----------+-----++-----+----+----------++--------------------+----+--- ---+----------------------------------+ 1 SIMPLE cat index PRIMARY name NULL 1 100.00 Използване на индекс 1 SIMPLE sch ref PRIMARY,catalog_ >6 16.67 Използване where ; Използване на индекс 1 SIMPLE tbl ref schema_ >26 1.11 Използване на условие за индекс; Използване на where 1 SIMPLE col eq_refPRIMARY PRIMARY mysql.tbl.collation_ >1 100.00 --------+--------------------------------+

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

Източници на метаданни

В MySQL 8.0 не всички таблици INFORMATION_SCHEMA са внедрени като изгледи върху таблици с речник на данни. Понастоящем това се прави само за следните таблици:

  • СХЕМИ
  • ТАБЛИЦИ
  • КОЛОНИ
  • ГЛЕДИ
  • CHARACTER_SETS
  • КОЛАЦИИ
  • COLLATION_CHARACTER_SET_APPLICABILITY
  • СТАТИСТИКА
  • KEY_COLUMN_USAGE
  • TABLE_CONSTRAINTS

В бъдещите версии на MySQL 8 планираме да добавим реализация на изглед за следните таблици INFORMATION_SCHEMA:

  • СЪБИТИЯ
  • ТРИГЕРИ
  • РУТИНИ
  • 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 (стойността по подразбиране е кеширана); за да получавате винаги динамична информация директно от системата за съхранение (с цената на малко по-висока цена на заявката), задайте 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 за повече подробности.