Формат на план за изпълнение TREE в MySQL 8.0.16

Пропуснал съм една интересна нова функционалност в последната MySQL версия 8.0.16, която покрих миналия месец. Спомената е в бележките към версията (виж Functionality Added or Changed и превъртете надолу или търсете за TREE) и също в документацията (виж Obtaining Execution Plan Information). Както се оказва нов формат на план за изпълнение TREE е бил добавен.

Досега имаше две възможности за формата на плана за изпълнение създаван с EXPLAIN израза:

• TRADITIONAL - стария табличен план за изпълнение подходящ за конзола;
• JSON - план за изпълнение в JSON използван от MySQL Workbench за показване на хубави графики в Visual Explain, които използвам в презентации.

Който е запознат с традиционния табличен изход знае, че той не е подравнен и понякога е трудно да се разбере реда на операциите. Сега има възможност за използване на TREE формат, който печата "създаденото итеративно дърво" с операциите подравнени на дясно подобно на плановете за изпълнение в Oracle. Това трябва да помогне на потребителите да разберат по-добре как изпълнение на заявката всъщност се подготвя. Тази функционалност все още е експериментална, така че както синтаксиса така и изхода могат да се променят в бъдеще.

Да вземем следните две заявки, които произвеждат един и същи резултат - минимална, средна и максимална заплата за отделите в организацията въз основа на DEPT и EMP схема (виж моята адаптация за MySQL и EMP and DEPT скрипта). Втората заявка е възможна само в MySQL 8.0.14 и по-нов (виж New development features in MySQL 8.0.14).

Query 1: С използване на LEFT OUTER JOIN и производна таблица

SELECT D.dname, DT.min_sal, DT.avg_sal, DT.max_sal
  FROM dept D
       LEFT OUTER 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;

Query 2: С използване на 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;

Табличните планове за изпълнение на двете заявки (с премахнати колоните partitions, possible_keys и filtered за скъсяване на изхода) и разширения изход са както следва:

Query 1

+----+-------------+------------++-------++-------------+---------+-------------------+------++-------+
| id | select_type | table      || type  || key         | key_len | ref               | rows || Extra |
+----+-------------+------------++-------++-------------+---------+-------------------+------++-------+
|  1 | PRIMARY     | D          || ALL   || NULL        | NULL    | NULL              |    4 || NULL  |
|  1 | PRIMARY     | <derived2> || ref   || <auto_key0> | 5       | dept_emp.D.deptno |    2 || NULL  |
|  2 | DERIVED     | E          || index || fk_deptno   | 5       | NULL              |   14 || NULL  |
+----+-------------+------------++-------++-------------+---------+-------------------+------++-------+
3 rows in set, 1 warning (0.01 sec)

+-------+------+-----------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                             |
+-------+------+-----------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `dept_emp`.`D`.`dname` AS `dname`... left join (
                 /* select#2 */ select `dept_emp`.`E`.`deptno` AS `deptno`,min(`dept_emp`.`E`.`sal`) AS `min_sal`... |
+-------+------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query 2

+----+-------------------+------------++------++-----------+---------+-------------------+------++----------------------------+
| id | select_type       | table      || type || key       | key_len | ref               | rows || Extra                      |
+----+-------------------+------------++------++-----------+---------+-------------------+------++----------------------------+
|  1 | PRIMARY           | D          || ALL  || NULL      | NULL    | NULL              |    4 || Rematerialize (<derived2>) |
|  1 | PRIMARY           | <derived2> || ALL  || NULL      | NULL    | NULL              |    2 || NULL                       |
|  2 | DEPENDENT DERIVED | E          || ref  || fk_deptno | 5       | dept_emp.D.deptno |    1 || NULL                       |
+----+-------------------+------------++------++-----------+---------+-------------------+------++----------------------------+
3 rows in set, 2 warnings (0.0006 sec)
+-------+------+-----------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                             |
+-------+------+-----------------------------------------------------------------------------------------------------+
| Note  | 1276 | Field or reference 'dept_emp.D.deptno' of SELECT #2 was resolved in SELECT #1                       |
| Note  | 1003 | /* select#1 */ select `dept_emp`.`D`.`dname` AS `dname`, ... join lateral (
                 /* select#2 */ select min(`dept_emp`.`E`.`sal`) AS `min_sal`...                                     |
+-------+------+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Това не е красиво, нали? Допълнителната информация печатана с SHOW WARNINGS само отбелязва участващите заявки, за да можете по-лесно да ги намерите в плана (съответстващи на първата колона id). За втората заявка има също и бележка за това как обръщението от втората заявка е разрешено в първата заявка.

Сега, ето как изглеждат изходите от новия формат на планове за изпълнение:

Query 1

+-----------------------------------------------------------------+
| EXPLAIN                                                         |
+-----------------------------------------------------------------+
| -> Nested loop left join
    -> Table scan on D
    -> Index lookup on DT using <auto_key0> (deptno=d.deptno)
        -> Materialize
            -> Group aggregate: min(e.sal), avg(e.sal), max(e.sal)
                -> Index scan on E using fk_deptno                |
+-----------------------------------------------------------------+
1 row in set (0.0025 sec)

Query 2

+----------------------------------------------------------------------+
| EXPLAIN                                                              |
+----------------------------------------------------------------------+
| -> Nested loop inner join
    -> Invalidate materialized tables (row from D)
        -> Table scan on D
    -> Table scan on LDT
        -> Materialize (invalidate on row from D)
            -> Aggregate: min(e.sal), avg(e.sal), max(e.sal)
                -> Index lookup on E using fk_deptno (deptno=d.deptno) |
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.0007 sec)
Note (code 1276): Field or reference 'dept_emp.D.deptno' of SELECT #2 was resolved in SELECT #1

Красиво, нали? Виждате всички операции, които ще бъдат изпълнени и подравняването помага да се разбере как те са вложени или с други думи коя ще бъде изпълнена преди другите. Ще трябва да следя как новия формат ще се развие, но дори сега мисля, че ще бъде доста полезен за сложни заявки, така че е много добре, че са го добавили.

Благодаря ви разработчици на MySQL!