Нови възможности в MySQL 8.0.18
В понеделник, Oracle пусна MySQL 8.0.18 (виж още бележките към версията) и за разлика предходните версии имаше доста информация за това какво ще бъде включено в тази. лично присъствах на няколко представяния и четох някои публикации в социалните мрежи. Както вече писах основните нови възможности в тази версия са хеш съединения и EXPLAIN ANALYZE, които целят съответно да подобрят производителността на някои заявки и дадат по-добра представа за това как плана на оптимизатора се сравнява с действителното изпълнение.
Оптимизация с хеш съединение
Преди 8.0.18 единствения тип съединение извършван от MySQL беше вложения цикъл (цикъл в цикъл, вътрешен цикъл във външен цикъл). И имаше два алгоритъма изпълняващи го:
- Nested-Loop Join - най-простия където всеки ред от външния цикъл се подава към вътрешния цикъл за обработка. Очевидния недостатък на този е, че вътрешната таблица трябва да бъде четена много пъти.
- Block Nested Loop (BNL) - използва буфериране на редове от външната таблица, за да намали броя на пъти в които вътрешната таблица се чете. В този случай, вместо да се подава само един ред към вътрешния цикъл множество редове от буфера могат да бъдат подадени наведнъж. Това значително намалява броя пъти в които вътрешната таблица трябва да бъде четена. Разбира се това изисква повече памет (виж join_buffer_size). Този алгоритъм се използва за range, index или ALL типове съединения.
Има също и Batched Key Access (BKA) алгоритъм, който използва буфериране както BNL, но поставя ключовете на редовете в буфера и ги подава наведнъж към машината на базата данни за намиране в индекса. Ключовете след това се ползват за извличане на редове от вътрешната таблица. Този алгоритъм може да се ползва когато има достъп чрез индекс към вътрешната таблица.
Тук идват хеш съединенията. Идеята е да се построи хеш таблица на стойностите от външната таблица, която нормално е най-малката. След това се чете вътрешната таблица и се търсят съвпадения в хеш таблицата. Така и двете таблици в най-добрия случай могат да бъдат прочетени само по веднъж. Хеш съединенията са най-подходящи за големи резултати където не могат да се ползват индекси. Поради тяхната природа те са полезни само за съединения с равенство. От 8.0.18 MySQL ще избере хеш съединение пред BNL за всяка заявка, която използва условие с равенство за съединение (напр. ref или eq_ref типове съединения) и не ползва индекси. Да пробваме.
За примера, добих нова таблица с име job_sal с диапазон на заплати според позиция към схемата dept_emp и също създадох 1 милион повече служители. Сега да кажем, че искате да намерите всички служители с заплата извън диапазона. Аз бих написал заявка като тази:
SELECT E.ename, E.sal, JS.sal_min, JS.sal_max
FROM emp E,
job_sal JS
WHERE E.job = JS.job
AND E.sal NOT BETWEEN JS.sal_min AND JS.sal_max;
Която като се има в предвид липсата на индекси ще бъде изпълнена с BNL алгоритъм в MySQL преди 8.0.18, но в новата версия същата заявка ще се възползва от оптимизацията с хеш съединение както е показано в следващия план за изпълнение в дървовиден формат:
+----------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------+
| -> Filter: (E.sal not between JS.sal_min and JS.sal_max) (cost=499211.71 rows=442901)
-> Inner hash join (E.job = JS.job) (cost=499211.71 rows=442901)
-> Table scan on E (cost=1962.39 rows=996514)
-> Hash
-> Table scan on JS (cost=0.75 rows=5)
|
+----------------------------------------------------------------------------------------+
1 row in set (0.0023 sec)
Плана разкрива, че за по-малката таблица JS ще бъде хеширана и съединението ще бъде изпълнено с използването на хеш таблица. За съжаление, оптимизацията с хеш съединение не е видима в традиционния план за изпълнение, JSON и така в MySQL Workbench. Визуалния план за изпълнение (виж вдясно) така ще бъде подвеждащ показвайки BNL алгоритъм за съединението. Това е разбираемо, защото оптимизацията с хеш съединение е възможна само с новия изпълнител на итеративни алгоритми, който всъщност създава плана за изпълнение в дървовиден (TREE) формат. Този изпълнител не може да обясни някои заявки, така че можете да видите само съобщението "not executable by iterator executor". Обаче, наистина се надявам това да бъде подобрено в следващи версии, защото плана за изпълнение трябва да бъде последователна между форматите. Докладвах заявка за функционалност като бъг 97280.
Пробвах производителността на заявката за 1 милион служители и времето за изпълнение беше 0.89 сек. Забраняването на оптимизацията с хеш съединение с NO_HASH_JOIN подсказката увеличи времето за изпълнение до 1.26 сек. Хеш съединението със сигурност ще бъде много по-благотворно когато външната таблица има повече редове.
EXPLAIN ANALYZE
Тази нова възможност също идва върху дървовидния (TREE) план за изпълнение и и представлява нещо като инструмент за профилиране, защото освен информация за това как оптимизатора планира да изпълни заявката (виж отгоре) има също така информация от действителното ѝ изпълнение. Тази информация включва:
- времето за връщане на първия ред (в мс);
- времето за връщане на всички редове (в мс);
- броя на прочетените редове;
- броя цикли.
Тя прави възможно сравнението на оценките на оптимизатора към действителното изпълнение на заявката. Да пробваме с предишната заявка. Нарочно съм скъсил изхода.
+-------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------+
| -> Filter: (E.sal not between JS.sal_min and JS.sal_max)
(cost=502072.82 rows=442901) (actual time=0.372..747.742 rows=915768 loops=1)
-> Inner hash join (E.job = JS.job)
(cost=502072.82 rows=442901) (actual time=0.355..575.011 rows=1000014 loops=1)
-> Table scan on E (cost=2534.62 rows=996514) (actual time=0.185..353.877 rows=1000014 loops=1)
-> Hash
-> Table scan on JS (cost=0.75 rows=5) (actual time=0.133..0.144 rows=5 loops=1)
|
+-------------------------------------------------------------------------------------------------------+
1 row in set (0.7754 sec)
Ако ви изглежда познато проверете EXPLAIN ANALYZE на PostgreSQL. Моля, забележете, че времената са в милисекунди, не секунди! Мисля, че е лесно да се забележи, че броя редове оценени от оптимизатора за четенето на таблица E се различава, защото статистиките за телицата не са точни. Добавих 1 милион редове към таблица emp, така че трябва да увелича броя на пробните страници (виж innodb_stats_persistent_sample_pages) и пусна ANALYZE TABLE отново. След като го напраих оценката на оптимизатора и действителния брой редове съвпадат. Обаче, оптимизатора е също така в грешка за редовете за операциите от по-горно ниво - хеш съединението и филтъра, но това не може да се оправи със статистики за индекси. Според Norvald H. Ryeng (виж неговата статия MySQL EXPLAIN ANALYZE) както "оценения, така и действителния брой" редове "са осреднени върху всички цикли", но аз имам само един цикъл за всички операции в плана.
Във всеки случай EXPLAIN ANALYZE е чудесно допълнение към инструментариума на оптимизатора. За съжаление, нито TREE формат нито EXPLAIN ANALYZE са достъпни дори в последния MySQL Workbench, така че още една заявка за функционалност от мен като бъг 97282.
MySQL е само OpenSSL
С тази версия поддръжката на yaSSL и WolfSSL библиотеките са премахва, така че MySQL може да се компилира само с OpenSSL. Аз лично винаги съм изграждал MySQL с OpenSSL (т.е. използвайки ключа -DWITH_SSL=system), защото това е което идва с Slackware, но разбира се по-важното е, че "комбинацията MySQL/OpenSSL е много добре изпробвана и доказана в продукция" както Georgi Kodinov обяснява в публикацията си MySQL is OpenSSL-only now !.Пространствен
Функцията ST_Distance вече приема SRS аргуменити от всички геометрични типове, а не само аргументи от типове
Point и Point, или Point и MultiPoint както преди.
