Нови възможности за разработчци в MySQL 8.0.14
С пускането на MySQL 8.0.14 Oracle запазва вече установената практика да въвежда нови възможности за разработчици дори с версии за поддръжка, които обикновено съдържат само малки подобрения и най-вече поправки на бъгове. Разгледах бележките към версията на 8.0.14, публикацията The MySQL 8.0.14 Maintenance Release is Generally Available на Geir Hoydalsvik и разбира се наръчника, експериментирах и тук отдолу е моя избор на нови възможности свързани с разработка.
Латерални производни таблици (Lateral derived tables)
Преди MySQL 8.0.14 не беше възможно за производни таблици да се обръщат към (зависят от) колони на предходните таблици в FROM клаузата. Сега това ограничение е премахнато с добавянето на ключова дума LATERAL (виж Lateral derived tables). Ключовата дума LATERAL означава, че производната таблица зависи от предходната таблица от ляво. Можете да имате повече от една LATERAL производна таблица в заявка и всяка ще зависи само от предходната таблица или производна таблица. Латералните производни таблици са така наречения "for each" цикъл на SQL и това прави възможни някои операции, които иначе не са възможни или са по-малко ефикасни.
Ето един пример. Да кажем, че искате да изчислите минималната, средната и максималната заплата за всеки отдел в организацията. Преди трябваше да го напишете така:
SELECT D.dname, DT.min_sal, DT.avg_sal, DT.max_sal
FROM dept D
LEFT JOIN
(SELECT E.deptno, MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
GROUP BY E.deptno
) AS DT
ON DT.deptno = D.deptno;
И така да използвате производна таблица DT, за да изчислите мин/сред/макс заплата за всички отдели от таблицата emp и тогава съедините с таблица dept получавайки следния резултат:
+------------+---------+-------------+---------+
| dname | min_sal | avg_sal | max_sal |
+------------+---------+-------------+---------+
| ACCOUNTING | 1300.00 | 2916.666667 | 5000.00 |
| RESEARCH | 800.00 | 2175.000000 | 3000.00 |
| SALES | 950.00 | 1566.666667 | 2850.00 |
| OPERATIONS | | | |
+------------+---------+-------------+---------+
4 rows in set (0.0014 sec)
Производната таблица е напълно независима от другата съединена таблица, тъй като може да произведе резултат сама (т.е. не зависи от стойностите на колоните на другата таблица). Плана за изпълнение на тази заявка е даден от дясно и той потвърждава, че резултата на производната таблица първо бива материализиран, за да може да бъде съединен с другата таблица.
SELECT D.dname, DT.min_sal, DT.avg_sal, DT.max_sal
FROM dept D
LEFT JOIN
(SELECT E.deptno, MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
GROUP BY E.deptno
) AS DT
ON DT.deptno = D.deptno;
И така да използвате производна таблица DT, за да изчислите мин/сред/макс заплата за всички отдели от таблицата emp и тогава съедините с таблица dept получавайки следния резултат:
+------------+---------+-------------+---------+
| dname | min_sal | avg_sal | max_sal |
+------------+---------+-------------+---------+
| ACCOUNTING | 1300.00 | 2916.666667 | 5000.00 |
| RESEARCH | 800.00 | 2175.000000 | 3000.00 |
| SALES | 950.00 | 1566.666667 | 2850.00 |
| OPERATIONS | | | |
+------------+---------+-------------+---------+
4 rows in set (0.0014 sec)
Производната таблица е напълно независима от другата съединена таблица, тъй като може да произведе резултат сама (т.е. не зависи от стойностите на колоните на другата таблица). Плана за изпълнение на тази заявка е даден от дясно и той потвърждава, че резултата на производната таблица първо бива материализиран, за да може да бъде съединен с другата таблица.
Друг подход би бил с използване на подзаявки в SELECT клаузата ето така:
SELECT D.dname,
(SELECT MIN(E.sal) FROM emp E WHERE E.deptno = D.deptno) AS min_sal,
(SELECT AVG(E.sal) FROM emp E WHERE E.deptno = D.deptno) AS avg_sal,
(SELECT MAX(E.sal) FROM emp E WHERE E.deptno = D.deptno) AS max_sal
FROM dept D;
което няма да бъде ефикасно (представете си таблица с продажби и хиляди търговци ако искате да оцените техните продажби), защото три заявки ще трябва да вършат работата на една. Не е възможно да се ползва само една подзаявка за изчисляване на всички необходими стойности в SELECT, защото такива подзаявки трябва да са скаларни. Подобна заявка ще предизвика грешка Error Code: 1241. Operand should contain 1 column(s) ако пробвате.
SELECT D.dname,
(SELECT MIN(E.sal) FROM emp E WHERE E.deptno = D.deptno) AS min_sal,
(SELECT AVG(E.sal) FROM emp E WHERE E.deptno = D.deptno) AS avg_sal,
(SELECT MAX(E.sal) FROM emp E WHERE E.deptno = D.deptno) AS max_sal
FROM dept D;
което няма да бъде ефикасно (представете си таблица с продажби и хиляди търговци ако искате да оцените техните продажби), защото три заявки ще трябва да вършат работата на една. Не е възможно да се ползва само една подзаявка за изчисляване на всички необходими стойности в SELECT, защото такива подзаявки трябва да са скаларни. Подобна заявка ще предизвика грешка Error Code: 1241. Operand should contain 1 column(s) ако пробвате.
Ако се опитате да свържете производната таблица към другата таблица с заявка като следната:
SELECT D.dname, DT.min_sal, DT.avg_sal, DT.max_sal
FROM dept D,
(SELECT MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
WHERE E.deptno = D.deptno
) AS DT;
ще получите грешка Error Code: 1054. Unknown column 'D.deptno' in 'where clause', защото таблица D не е позната на производната таблица. Заявката е незаконна в SQL-92, но в SQL-1999 става законна ако производната таблица се предшества от ключовата дума LATERAL:
SELECT D.dname, LDT.min_sal, LDT.avg_sal, LDT.max_sal
FROM dept D,
LATERAL
(SELECT MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
WHERE E.deptno = D.deptno
) AS LDT;
и създава следния резултат:
+------------+---------+-------------+---------+
| dname | min_sal | avg_sal | max_sal |
+------------+---------+-------------+---------+
| ACCOUNTING | 1300.00 | 2916.666667 | 5000.00 |
| RESEARCH | 800.00 | 2175.000000 | 3000.00 |
| SALES | 950.00 | 1566.666667 | 2850.00 |
| OPERATIONS | NULL | NULL | NULL |
+------------+---------+-------------+---------+
4 rows in set (0.1182 sec)
Както се вижда от графиката на плана за изпълнение отдясно в този случай няма групиране, но MySQL дава по-висока цена, защото достъпа до производната таблица е чрез пълно сканиране на таблица. По-интересната информация обаче е в табличния план за изпълнение (колона partitions е преднамерено скрита):
+----+-------------------+------------++------+---------------+-----------+---------+-------------------+------+----------+----------------------------+
| id | select_type | table || type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------------+------------++------+---------------+-----------+---------+-------------------+------+----------+----------------------------+
| 1 | PRIMARY | D || ALL | NULL | NULL | NULL | NULL | 4 | 100 | Rematerialize (<derived2>) |
| 1 | PRIMARY | <derived2> || ALL | NULL | NULL | NULL | NULL | 2 | 100 | NULL |
| 2 | DEPENDENT DERIVED | E || ref | fk_deptno | fk_deptno | 5 | dept_emp.D.deptno | 4 | 100 | NULL |
+----+-------------------+------------++------+---------------+-----------+---------+-------------------+------+----------+----------------------------+
3 rows in set, 2 warnings (0.0010 sec)
Note (code 1276): Field or reference 'dept_emp.D.deptno' of SELECT #2 was resolved in SELECT #1
Note (code 1003): /* select#1 */ select `dept_emp`.`d`.`dname` AS `dname`,`ldt`.`min_sal` AS `min_sal`,`ldt`.`avg_sal` AS `avg_sal`,`ldt`.`max_sal` AS `max_sal` from `dept_emp`.`dept` `d` join lateral (/* select#2 */ select min(`dept_emp`.`e`.`sal`) AS `min_sal`,avg(`dept_emp`.`e`.`sal`) AS `avg_sal`,max(`dept_emp`.`e`.`sal`) AS `max_sal` from `dept_emp`.`emp` `e` where (`dept_emp`.`e`.`deptno` = `dept_emp`.`d`.`deptno`)) `ldt`
Има две нови информация и допълнителна бележка. Плана ясно показва, че производната таблица E (derived2) е зависима (DEPENDENT) от другата таблица и че тя се материализира отново за всеки ред от D (виж EXPLAIN extra information). Това е причината поради която латералните производни таблици са познати също като "for each" цикъла на SQL. Бележката дава информация за това как външното позоваване в производната таблица е разрешено.
SELECT D.dname, DT.min_sal, DT.avg_sal, DT.max_sal
FROM dept D,
(SELECT MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
WHERE E.deptno = D.deptno
) AS DT;
ще получите грешка Error Code: 1054. Unknown column 'D.deptno' in 'where clause', защото таблица D не е позната на производната таблица. Заявката е незаконна в SQL-92, но в SQL-1999 става законна ако производната таблица се предшества от ключовата дума LATERAL:
SELECT D.dname, LDT.min_sal, LDT.avg_sal, LDT.max_sal
FROM dept D,
LATERAL
(SELECT MIN(E.sal) min_sal, AVG(E.sal) avg_sal, MAX(E.sal) max_sal
FROM emp E
WHERE E.deptno = D.deptno
) AS LDT;
и създава следния резултат:
+------------+---------+-------------+---------+
| dname | min_sal | avg_sal | max_sal |
+------------+---------+-------------+---------+
| ACCOUNTING | 1300.00 | 2916.666667 | 5000.00 |
| RESEARCH | 800.00 | 2175.000000 | 3000.00 |
| SALES | 950.00 | 1566.666667 | 2850.00 |
| OPERATIONS | NULL | NULL | NULL |
+------------+---------+-------------+---------+
4 rows in set (0.1182 sec)
Както се вижда от графиката на плана за изпълнение отдясно в този случай няма групиране, но MySQL дава по-висока цена, защото достъпа до производната таблица е чрез пълно сканиране на таблица. По-интересната информация обаче е в табличния план за изпълнение (колона partitions е преднамерено скрита):
+----+-------------------+------------++------+---------------+-----------+---------+-------------------+------+----------+----------------------------+
| id | select_type | table || type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------------+------------++------+---------------+-----------+---------+-------------------+------+----------+----------------------------+
| 1 | PRIMARY | D || ALL | NULL | NULL | NULL | NULL | 4 | 100 | Rematerialize (<derived2>) |
| 1 | PRIMARY | <derived2> || ALL | NULL | NULL | NULL | NULL | 2 | 100 | NULL |
| 2 | DEPENDENT DERIVED | E || ref | fk_deptno | fk_deptno | 5 | dept_emp.D.deptno | 4 | 100 | NULL |
+----+-------------------+------------++------+---------------+-----------+---------+-------------------+------+----------+----------------------------+
3 rows in set, 2 warnings (0.0010 sec)
Note (code 1276): Field or reference 'dept_emp.D.deptno' of SELECT #2 was resolved in SELECT #1
Note (code 1003): /* select#1 */ select `dept_emp`.`d`.`dname` AS `dname`,`ldt`.`min_sal` AS `min_sal`,`ldt`.`avg_sal` AS `avg_sal`,`ldt`.`max_sal` AS `max_sal` from `dept_emp`.`dept` `d` join lateral (/* select#2 */ select min(`dept_emp`.`e`.`sal`) AS `min_sal`,avg(`dept_emp`.`e`.`sal`) AS `avg_sal`,max(`dept_emp`.`e`.`sal`) AS `max_sal` from `dept_emp`.`emp` `e` where (`dept_emp`.`e`.`deptno` = `dept_emp`.`d`.`deptno`)) `ldt`
Има две нови информация и допълнителна бележка. Плана ясно показва, че производната таблица E (derived2) е зависима (DEPENDENT) от другата таблица и че тя се материализира отново за всеки ред от D (виж EXPLAIN extra information). Това е причината поради която латералните производни таблици са познати също като "for each" цикъла на SQL. Бележката дава информация за това как външното позоваване в производната таблица е разрешено.
Разбира се MySQL Workbench (дори надграден до 8.0.14 също) отново не е запознат с новия синтаксис (виж предишната ми публикация Нови възможности за разработчици в MySQL 8.0.13), защото не оцветява правилно новата ключова дума и показва грешка в SQL редактора точно след нея. Докладвах това като бъг 94012, но нямам много надежда, тъй като 90620, 92900 и 92908 бяха потвърдени, но са все още отворени. Номерата на версиите нямат голямо значение в днешни дни :-)
Моля, обърнете внимание, че е възможно да се направи връзка с външната таблица ако производната таблица е в подзаявка (виж пример в WL#461).
Агрегатни JSON функции вече може да се ползват като прозоречни функции
Вече е възможно да се ползват агрегатни функции JSON_ARRAYAGG и JSON_OBJECTAGG като прозоречни функции с използването на OVER клауза (виж Window Function Concepts and Syntax). Това прави всички (освен COUNT(DISTINCT) и GROUP_CONCAT) от агрегатните функции възможни за употреба като прозоречни функции след като побитовите AND/OR/XOR функции бяха направени такива с MySQL 8.0.12. Ето един пример:
SELECT E.ename, E.sal,
AVG(E.sal) OVER dw AS avg_sal,
JSON_OBJECTAGG(D.dname, E.sal) OVER dw AS dept_sal
FROM emp E,
dept D
WHERE E.deptno = D.deptno
WINDOW dw AS (PARTITION BY D.deptno);
+--------+---------+-------------+------------------------+
| ename | sal | avg_sal | dept_sal |
+--------+---------+-------------+------------------------+
| CLARK | 2450.00 | 2916.666667 | {"ACCOUNTING": 1300.0} |
| KING | 5000.00 | 2916.666667 | {"ACCOUNTING": 1300.0} |
| MILLER | 1300.00 | 2916.666667 | {"ACCOUNTING": 1300.0} |
| SMITH | 800.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| JONES | 2975.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| SCOTT | 3000.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| ADAMS | 1100.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| FORD | 3000.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| ALLEN | 1600.00 | 1566.666667 | {"SALES": 950.0} |
| WARD | 1250.00 | 1566.666667 | {"SALES": 950.0} |
| MARTIN | 1250.00 | 1566.666667 | {"SALES": 950.0} |
| BLAKE | 2850.00 | 1566.666667 | {"SALES": 950.0} |
| TURNER | 1500.00 | 1566.666667 | {"SALES": 950.0} |
| JAMES | 950.00 | 1566.666667 | {"SALES": 950.0} |
+--------+---------+-------------+------------------------+
14 rows in set (0.0021 sec)
Важно е да се отбележи, че MySQL не разрешава повторение на ключове в JSON типа данни, така че в прозорец без подредба функцията JSON_OBJECTAGG ще върне последната стойност за ключа, което може да е неопределено.
SELECT E.ename, E.sal,
AVG(E.sal) OVER dw AS avg_sal,
JSON_OBJECTAGG(D.dname, E.sal) OVER dw AS dept_sal
FROM emp E,
dept D
WHERE E.deptno = D.deptno
WINDOW dw AS (PARTITION BY D.deptno);
+--------+---------+-------------+------------------------+
| ename | sal | avg_sal | dept_sal |
+--------+---------+-------------+------------------------+
| CLARK | 2450.00 | 2916.666667 | {"ACCOUNTING": 1300.0} |
| KING | 5000.00 | 2916.666667 | {"ACCOUNTING": 1300.0} |
| MILLER | 1300.00 | 2916.666667 | {"ACCOUNTING": 1300.0} |
| SMITH | 800.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| JONES | 2975.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| SCOTT | 3000.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| ADAMS | 1100.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| FORD | 3000.00 | 2175.000000 | {"RESEARCH": 3000.0} |
| ALLEN | 1600.00 | 1566.666667 | {"SALES": 950.0} |
| WARD | 1250.00 | 1566.666667 | {"SALES": 950.0} |
| MARTIN | 1250.00 | 1566.666667 | {"SALES": 950.0} |
| BLAKE | 2850.00 | 1566.666667 | {"SALES": 950.0} |
| TURNER | 1500.00 | 1566.666667 | {"SALES": 950.0} |
| JAMES | 950.00 | 1566.666667 | {"SALES": 950.0} |
+--------+---------+-------------+------------------------+
14 rows in set (0.0021 sec)
Важно е да се отбележи, че MySQL не разрешава повторение на ключове в JSON типа данни, така че в прозорец без подредба функцията JSON_OBJECTAGG ще върне последната стойност за ключа, което може да е неопределено.
Подобрения по X протокол
Според бележките към версията данните вече винаги се обръщат в символния набор utf8mb4 (с използване на utf8mb4_general_ci колация). Другото забележително подобрение е поддръжката на функционалност за подготвяне на заявки. Бележките към версията не предоставят препратка към тази нова функционалност, но Geir Hoydalsvik е споменал WL#9270 в своята статия, така че вярвам става въпрос за подготвяне на CRUD операции (виж Preparing CRUD Statements). Един прост пример на JavaScript ще бъде следното:
MySQL Shell 8.0.14
Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
MySQL JS > \connect user@localhost
Creating a session to 'user@localhost'
Your MySQL connection id is 22 (X protocol)
Server version: 8.0.14 MySQL Community Server - GPL
MySQL localhost:33060+ ssl JS > \use test
Default schema `test` accessible through db.
MySQL localhost:33060+ ssl test JS > var usr = db.createCollection('users')
MySQL localhost:33060+ ssl test JS > usr.add({name:"User1",age:15})
Query OK, 1 item affected (0.0108 sec)
MySQL localhost:33060+ ssl test JS > usr.add({name:"User2",age:17})
Query OK, 1 item affected (0.0138 sec)
MySQL localhost:33060+ ssl test JS > usr.add({name:"User3",age:20})
Query OK, 1 item affected (0.0105 sec)
MySQL localhost:33060+ ssl test JS > usr.add({name:"User4",age:19})
Query OK, 1 item affected (0.0137 sec)
MySQL localhost:33060+ ssl test JS > usr.add({name:"User5",age:16})
Query OK, 1 item affected (0.0118 sec)
MySQL localhost:33060+ ssl test JS > usr.find()
[
{"_id": "00005c46e7e5000000000000000a","age": 15,"name": "User1"},
{"_id": "00005c46e7e5000000000000000e","age": 17,"name": "User2"},
{"_id": "00005c46e7e5000000000000000f","age": 20,"name": "User3"},
{"_id": "00005c46e7e50000000000000010","age": 19,"name": "User4"},
{"_id": "00005c46e7e50000000000000011","age": 16,"name": "User5"}
]
5 documents in set (0.0006 sec)
MySQL localhost:33060+ ssl test JS > var fcmd = usr.find('age >= :page')
MySQL localhost:33060+ ssl test JS > fcmd.bind('page', 18)
[
{"_id": "00005c46e7e5000000000000000f","age": 20,"name": "User3"},
{"_id": "00005c46e7e50000000000000010","age": 19,"name": "User4"}
]
2 documents in set (0.0006 sec)
MySQL localhost:33060+ ssl test JS > fcmd.bind('page', 16)
[
{"_id": "00005c46e7e5000000000000000e","age": 17,"name": "User2"},
{"_id": "00005c46e7e5000000000000000f","age": 20,"name": "User3"},
{"_id": "00005c46e7e50000000000000010","age": 19,"name": "User4"},
{"_id": "00005c46e7e50000000000000011","age": 16,"name": "User5"}
]
4 documents in set (0.0003 sec)
MySQL localhost:33060+ ssl test JS > fcmd.bind('page', 17)
[
{"_id": "00005c46e7e5000000000000000e","age": 17,"name": "User2"},
{"_id": "00005c46e7e5000000000000000f","age": 20,"name": "User3"},
{"_id": "00005c46e7e50000000000000010","age": 19,"name": "User4"}
]
3 documents in set (0.0004 sec)
римера създава колекция от потребители с техните имена и възраст, после печата цялата колекция. Интересната част започва с реда подчертан в жълто. Той подготвя израз използвайки именуван параметър (анонимни параметри с ? не се поддържат от X протокола), но не го изпълнява. Изпълнението се случва след като се свърже стойност към параметъра и това може да се прави много пъти произвеждайки различни резултати. Интересно е, че в общия журнал първото свързване всъщност изпълнява заявка, след това има подготовка и след това има изпълнения:
Query SELECT doc FROM `test`.`users` -> usr.find()
Query SELECT doc FROM `test`.`users` WHERE (JSON_EXTRACT(doc,'$.age') >= 18) -> fcmd.bind('page', 18)
Prepare SELECT doc FROM `test`.`users` WHERE (JSON_EXTRACT(doc,'$.age') >= ?)
Execute SELECT doc FROM `test`.`users` WHERE (JSON_EXTRACT(doc,'$.age') >= 16) -> fcmd.bind('page', 16)
Execute SELECT doc FROM `test`.`users` WHERE (JSON_EXTRACT(doc,'$.age') >= 17) -> fcmd.bind('page', 17)
Използването на подготвени изразни за многократно изпълнявани изрази може да доведе до подобрения в производителността заради спестеното време за синтактичен разбор на заявката, затова това е нещо което трябва да имате в предвид ако трябва да подобрите производителността на приложенията и скриптовете си.
Подобрения в работа с пространствени данни
Функцията ST_Distance вече приема като незадължителен трети параметър мерната единица за връщаната стойност. Възможните стойности са определени в таблица INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE заедно с коефициент за превръщане към основната единица метър (metre), което е и стойността по подразбиране. Ето един пример за изчисляване на разстоянието между София и Сидни в километри и морски мили в SRID 4326:
SELECT ST_Distance(ST_PointFromText('POINT( 42.69751 23.32415)', 4326),
ST_PointFromText('POINT(-33.86667 151.20000)', 4326)) / 1000 dist_km;
+--------------------+
| dist_km |
+--------------------+
| 15431.933058990671 |
+--------------------+
1 row in set (0.0023 sec)
SELECT ST_Distance(ST_PointFromText('POINT( 42.69751 23.32415)', 4326),
ST_PointFromText('POINT(-33.86667 151.20000)', 4326), 'nautical mile') dist_nm;
+------------------+
| dist_nm |
+------------------+
| 8332.57724567531 |
+------------------+
1 row in set (0.0008 sec)
SELECT ST_Distance(ST_PointFromText('POINT( 42.69751 23.32415)', 4326),
ST_PointFromText('POINT(-33.86667 151.20000)', 4326)) / 1000 dist_km;
+--------------------+
| dist_km |
+--------------------+
| 15431.933058990671 |
+--------------------+
1 row in set (0.0023 sec)
SELECT ST_Distance(ST_PointFromText('POINT( 42.69751 23.32415)', 4326),
ST_PointFromText('POINT(-33.86667 151.20000)', 4326), 'nautical mile') dist_nm;
+------------------+
| dist_nm |
+------------------+
| 8332.57724567531 |
+------------------+
1 row in set (0.0008 sec)
Това завършва моя преглед. Има разбира се много повече в MySQL 8.0.14 не само за разработчици, така че ви насърчавам да изследвате и откриете повече. Препратките в началото на тази статия са добри като начало.
Няма коментари:
Публикуване на коментар