Нови възможности в MySQL 8.0.17

Миналия Вторник (27-ми Юли), Oracle пуснаха MySQL 8.0.7 следвайки тримесечния цикъл на нови версии въведен миналата година с 8 серията. Това е следващата версия по "поддръжката" въвеждаща някои нови възможности, както и обезценявайки някои нестандартни такива, така че ето това, което забелязах започвайки с тези, които смятам важни за разработчици.

Индекси върху множество стойности

С помощта на създавани колони (въведени с MySQL 5.7.6) и функционални индекси (въведени с MySQL 8.0.13 за което писах преди) стана възможно да се индексират данни в сложни стойности на колони като JSON. Но в JSON можете да имате скалари както и масиви, така че търсенето в масиви не беше възможно с помощта на индекс. Индексите върху множество стойности идват, за да решат това позволявайки множество записи в индекса да сочат към един и същ запис с данни. Такива индекси се създават като всеки друго функционален индекс и се използват автоматично от оптимизатора когато е възможно. Да видим един пример - регистър на преводачи с говоримите от тях езици.

CREATE TABLE translators (
  id INT AUTO_INCREMENT,
  jdata JSON,

  PRIMARY KEY(id)
);

• Случай 1 - масив от низове

Да създадем малко данни:

INSERT INTO translators (jdata)
VALUES ('{"name": "T1", "langs": ["English", "French", "Spanish"]}'),
       ('{"name": "T2", "langs": ["English", "Spanish"]}'),
       ('{"name": "T3", "langs": ["French", "Spanish"]}');

След това, да потърсим преводачи, които говорят Английски използвайки новия MEMBER OF оператор:

SELECT id, jdata->>'$.name', jdata->'$.langs'
  FROM translators
 WHERE 'English' MEMBER OF (jdata->'$.langs');

Или новата функция JSON_OVERLAPS ето така:

SELECT id, jdata->>'$.name', jdata->'$.langs'
  FROM translators
 WHERE JSON_OVERLAPS(jdata->'$.langs', '["English"]');

И двете заявки водят до един и същ резултат:

+----+------------------+----------------------------------+
| id | jdata->>'$.name' | jdata->'$.langs'                 |
+----+------------------+----------------------------------+
|  1 | T1               | ["English", "French", "Spanish"] |
|  2 | T2               | ["English", "Spanish"]           |
+----+------------------+----------------------------------+
2 rows in set (0.00 sec)

Като се имат в предвид данните очаквано заявките връщат T1 и T2, но не T3. Обаче, тези заявки правят пълно сканиране на таблицата, така че производителността им ще деградира с натрупването на данни в таблицата.

За щастие, вече е възможно да се добави индекс върху множество стойности ето така:

ALTER TABLE translators
  ADD INDEX idx_langs_arr ((CAST(jdata->'$.langs' AS CHAR(8) ARRAY)));

Това е функционален индекс, в който е необходимо да се използва функцията CAST в новата ключова дума ARRAY. С индекса плана за изпълнение на SELECT заявките отгоре става съответно:

• Случай 2 - масив от обекти

Малко по-различно е за масив от обекти, но само за JSONPath израза. Нека създадем малко данни (след като почистим Случай 1):

INSERT INTO translators (jdata)
VALUES ('{"name": "T1", "langs": [{"lang": "English"}, {"lang": "French"}, {"lang": "Spanish"}]}'),
       ('{"name": "T2", "langs": [{"lang": "English"}, {"lang": "Spanish"}]}'),
       ('{"name": "T3", "langs": [{"lang": "French"}, {"lang": "Spanish"}]}');

След това, нека да потърсим преводачи, които говорят Английски по същите два начина:

SELECT id, jdata->>'$.name', jdata->'$.langs[*].lang'
  FROM translators
 WHERE 'English' MEMBER OF (jdata->'$.langs[*].lang');

SELECT id, jdata->>'$.name', jdata->'$.langs[*].lang'
  FROM translators
 WHERE JSON_OVERLAPS(jdata->'$.langs[*].lang', '["English"]');

Точно както в първия случай заявките правят пълно сканиране на таблицата, което вече лесно може да бъде променено с добавянето на индекс върху множество стойности ето така:

ALTER TABLE translators
  ADD INDEX idx_langs_obj ((CAST(jdata->'$.langs[*].lang' AS CHAR(8) ARRAY)));

Забележете леко различния синтаксис на JSONPath израза. За да работи индекса разбира се е необходимо да се използва същия израз в WHERE клаузата както в определението на индкеса. Разработчиците, които предпочитат да пазят данни направо в JSON колони трябва да бъдат щастливи от тази нова възможност, тък като тя прави възможно индексирането не само на скаларни стойности, но също така масиви.

JSON

Освен вече споменатите нов стандартен оператор MEMBER OF за търсене на стойности в JSON масиви има три нови функции JSON_OVERLAPS, JSON_SCHEMA_VALID и JSON_SCHEMA_VALIDATION_REPORT.

• Функцията JSON_OVERLAPS сравнява два JSON документа и връща истина "ако двата документа имат някоя обща ключ-стойност двойка или елементи на масив". Както MEMBER OF и JSON_CONTAINS функцията JSON_OVERLAPS може да се облагодетелства от индекси върху множество стойности.
• Функциите JSON_SCHEMA_VALID и JSON_SCHEMA_VALIDATION_REPORT са добавени във връзка с поддръжката на JSON Schema. Първата проверява JSON документ спрямо JSON схема и връща истина ако е верен иначе лъжа, така че да може да се ползва като CHECK ограничение. Втората ще предостави подробности по грешките от проверката под формата на JSON документ.

Преобразуване към FLOAT, DOUBLE и REAL

Функциите CAST и CONVERT вече могат да преобразуват към типове данни с плаваща запетая като FLOAT, DOUBLE и REAL. Нека да пробваме с неправилна стойност:

SELECT CAST('1.23.34' AS FLOAT) cast_res, CONVERT('1.23.34', FLOAT) conv_res;

+--------------------+--------------------+
| cast_res           | conv_res           |
+--------------------+--------------------+
| 1.2300000190734863 | 1.2300000190734863 |
+--------------------+--------------------+
1 row in set, 2 warnings (0.00 sec)

Има две предупреждения, така че нека ги видим с show warnings:

+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1.23.34' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '1.23.34' |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

Преобразуването към DOUBLE и REAL произвежда различен резултат и същите предупреждения:

SELECT CAST('1.23.34' AS DOUBLE) cast_res, CONVERT('1.23.34', DOUBLE) conv_res;
SELECT CAST('1.23.34' AS REAL) cast_res, CONVERT('1.23.34', REAL) conv_res;

+----------+----------+
| cast_res | conv_res |
+----------+----------+
|     1.23 |     1.23 |
+----------+----------+
1 row in set, 2 warnings (0.00 sec)

CLONE команда

В продължение на много години MySQL администратори трябваше да разтоварват основни сървъри, прехвърлят архива по мрежата и го зареждат в реплики, за да инициализират състоянието им (виж Copy a Database from one Server to Another). Аз нямам много отношение към администрация на бази данни, но това беше тромава и досадна процедура особенно в случаите на невъзобновими грешки при репликация когато трябваше да инициализирам отново състоянието на репликите, така че го смятах за тежест. Защо не мога просто да "клонирам" основния сървър след почистване на данните в репликата? Е, в новата версия  е възможно лесно да се създават нови инстанции или инициализират наново същестуващи с разработената естесвена поддръжка на провизиране в сървъра с командата CLONE. Това става възможно с новия MySQL Clone Plugin. Можете да откриете повече за това като прочетете следните статии:

• MySQL InnoDB Cluster from scratch – even more easy since 8.0.17 от Frédéric Deschamps;
• Clone: Create MySQL instance replica от Debarun Banejee; и
• Create an Asynchronous MySQL Replica in 5 minutes отново от Frédéric Deschamps.

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

Обезценяавния

Това е списък на възможносите, които биват обезценени с тази версия и ще бъдат премахнатеи в бъдещи версии:

• Функцията FOUND_ROWS и модификатора на заявки SQL_CALC_FOUND_ROWS. Документацията предлага използването на COUNT(*) за да се намери броя редове.
• Атрибути на числови типове данни:
  
  • Дължина за показване на целочислени типове данни. Вече се показва предупрежение "1681 Integer display width is deprecated and will be removed in a future release." ако се опитате да създадете таблици с INT(11) например. Аз имам доста такива определения както са били предоставени от mysqldump и MySQL Workbench, така че ще трябва да ги махна всички преди изразите да бъдат отхвърлени с грешка в бъдеще.
  • Атрибута ZEROFILL. Така или иначе никога не съм го ползвал.
  • Атрибута UNSIGNED за FLOAT, DOUBLE, и DECIMAL типове данни. Също не съм го ползвал никога.
  • AUTO_INCREMENT поддръжката за FLOAT и DOUBLE типове данни. Някой?
• Синтаксиса  FLOAT(M,D) и DOUBLE(M,D) за указването на броя цифри за типове с плаваща запетая.
• Логически оператори && (двоен амперсанд), който е синоним на AND, || (двоен пайп), който е синоним на OR и ! (удивителна), която е синоним на NOT. Не съм сигурен, че някога съм ги ползвал дори в ежедневни заявки, защото намирам ползването на AND,  OR и NOT за доста по-изразително.
• Ключовата дума BINARY за указване на _bin колации. Никога не съм я ползвал също така.

Разработчиците трябва да вземат в предвид отърваването от тези нестандартни възможности, за да предотвратят неприятни изненади в бъдеще. Аз ще прегледам и поправя проектите си възможно най-скоро.

Оправени бъгове

Във връзка с CHECK ограничения открих два бъга в предходата версия и един от тях беше оправен (виж бъг #95189 CHECK constraint comparing columns is not always enforced with UPDATE queries). За пълен пример моля, виж bug_95189_test_case.sql скрипта, така че нека просто проверим:

SET binlog_format = 'STATEMENT';
SET binlog_row_image = 'minimal';

UPDATE tst SET end_date = '2019-04-20' WHERE id = 1;
/* Error Code: 3819. Check constraint 'chk_dat' is violated. */

Значи е оправен, защото очаквано UPDATE заявката пропада дори със специфичние настройки за двоичен журнал. Другият (виж bug #95192 CHECK constraint comparing column with default value is not enforced) ще трябва да почака.

Това завършва моя преглед на новите възможности в MySQL 8.0.17 версия по поддръжка. Надявам се новите версии да донесат повече възможноти за разработчици.