SQLinfo.ru - Все о MySQL

В MySQL 8.0.14 добавлена поддержка производных таблиц типа LATERAL

Дата: 23.02.2019

Перевод статьи Гильема Бишота про использование коррелированных from-подзапросов (также известных как производные таблицы LATERAL или подзапросы LATERAL и являющихся частью стандарта SQL:1999)

В недавно вышедшей MySQL 8.0.14 я добавил новую функциональность - производные таблицы LATERAL.

В документации описан синтаксис и даны примеры использования для поиска наибольших значений в таблицах. В данной статье я рассмотрю решение другой задачи с помощью LATERAL: предположим, что у нас есть набор узлов, и мы хотим построить “случайный граф”, соединив каждый узел с другими узлами.

Начнем с создания таблицы вершин:

create table nodes(id int);

которую мы заполним 20-ю значениями, используя Обобщенные Табличные Выражения (добавлены мной в MySQL 8.0.1 )

insert into nodes
with recursive cte(n) as
(
  select 1
  union all
  select n+1 from cte where n<20
)
select * from cte;

Теперь создадим случайным образом ребра нашего графа. Ребра будут направленными и характеризоваться парой значений: начальным "from_id" и конечным "to_id".

create table edges (from_id int, to_id int);

От каждой вершины проведем ребра к двум (2 на текущий момент, потом усложним задачу) случайно выбранным вершинам.

Если мы сделаем это с помощью обычной производной таблицы, хранящей 2 конечные вершины:

insert into edges(from_id, to_id)
select origin_nodes.id, target_nodes.id
from nodes as origin_nodes,
(
  select id
  from nodes
  order by rand()
  limit 2
) as target_nodes;
то эта производная таблица будет вычислена (материализована) только один раз, в начале выполнения запроса INSERT. В псевдо-коде это будет:

select two random target nodes, store into target_nodes
for each row R in origin_nodes:
  join R with target_nodes
  insert the result into edges

Соответственно все вершины будут соединены с одними и теми же вершинами, выбранными в начале выполнения запроса:

select * from edges;
+---------+-------+
| from_id | to_id |
+---------+-------+
|       1 |     1 |
|       1 |     9 |
|       2 |     1 |
|       2 |     9 |
|       3 |     1 |
|       3 |     9 |
|       4 |     1 |
|       4 |     9 |
|       5 |     1 |
|       5 |     9 |
...

Это не тот результат, который мы ожидали, так что удалим его:

delete from edges;

Прежде чем выбросить предыдущий запрос SELECT, запишем его план выполнения, полученный с помощью EXPLAIN:

+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | INSERT      | edges        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL                                  |
|  1 | PRIMARY     |    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                  |
|  1 | PRIMARY     | origin_nodes | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | nodes        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using temporary; Using filesort       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+

Для решения нашей проблемы нам нужно что-то что будет пересчитываться для каждой исходной вершины, как в этом псевдо-коде:

for each row R in origin_nodes:
  select two random target nodes, store into target_nodes
  join R with target_nodes
  insert the result into edges

Чтобы достичь этого, мы должны сказать MySQL, что выбор двух случайных конечных вершин является чем-то, что зависит от текущей исходной вершины, так, чтобы это повторялось каждый раз. Таким образом, мы искусственно делаем конечные вершины зависимыми от начальной:

insert into edges(from_id, to_id)
select origin_nodes.id, target_nodes.id
from nodes as origin_nodes,
     (
       select id
       from nodes
       order by rand()+0*origin_nodes.id
       limit 2
     ) as target_nodes;

Добавление 0*origin_nodes.id не изменяет значение в части ORDER BY, но делает производную таблицу target_nodes зависящей от текущей строки в origin_nodes.

Но обычные производные таблицы не могут зависеть от предыдущих таблиц в части FROM, поэтому при выполнении этого запроса мы получим ошибку:

ERROR 1054 (42S22): Unknown column 'origin_nodes.id' in 'order clause'

Для решения этой проблемы нужно указать нашу производную таблицу, как имеющую тип LATERAL (которая по определению является производной таблицей, зависящей от предыдущих таблиц в части FROM).

insert into edges(from_id, to_id)
select origin_nodes.id, target_nodes.id
from nodes as origin_nodes,
     LATERAL (
               select id
               from nodes
               order by rand()+0*origin_nodes.id
               limit 2
             ) as target_nodes;

Теперь запрос работает как и ожидалось, конечные вершины меняются случайным образом:

select * from edges;
+---------+-------+
| from_id | to_id |
+---------+-------+
|       1 |     5 |
|       1 |     7 |
|       2 |     4 |
|       2 |     5 |
|       3 |     2 |
|       3 |     7 |
|       4 |     6 |
|       4 |     8 |
|       5 |     7 |
|       5 |     8 |
|       6 |     4 |
|       6 |    17 |
|       7 |    18 |
|       7 |    11 |
|       8 |     1 |
|       8 |    19 |
|       9 |     7 |
|       9 |    19 |
...

EXPLAIN показывает:

+----+-------------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type       | table        | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | INSERT            | edges        | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL                            |
|  1 | PRIMARY           | origin_nodes | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Rematerialize (derived2)        |
|  1 | PRIMARY           | derived2     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                            |
|  2 | DEPENDENT DERIVED | nodes        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using temporary; Using filesort |
+----+-------------------+--------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

Обратите внимание на следующие значения в новом плане выполнения:

  • DEPENDENT DERIVED в столбце seelct_type указывает, что производная таблица зависит от другой таблицы
  • Rematerialize (derived2) в столбце extra означает, что каждый раз, когда мы читаем строку из origin_nodes, MySQL повторно материализует производную таблицу derived2 (которая является внутренним именем для производной таблицы target_nodes).

Давайте усложним нашу задачу: до сих пор мы связывали каждую вершину с двумя случайными. Пусть теперь количество ребер, исходящих из вершины, тоже будет случайным. Скажем, соединить каждую вершину с несколькими (от нуля до четырех) случайными вершинами.

В нашем предыдущем успешном запросе:

insert into edges(from_id, to_id)
select origin_nodes.id, target_nodes.id
from nodes as origin_nodes,
     lateral (
               select id
               from nodes
               order by rand()+0*origin_nodes.id
               limit 2
             ) as target_nodes;

необходимо сделать "2" в LIMIT 2 случайным значением от 0 до 4. К сожалению, выражение в LIMIT должно быть константой, поэтому нам придется отказаться от его использования и пойти другим путем:

  • каждую вершину соединить со всеми другими
  • для каждой вершины пронумеровать исходящие ребра в случайном порядке, используя оконную функцию ROW_NUMBER
  • для каждой вершины сгенерировать случайное число от 0 до 4, и отфильтровать в части WHERE лишние ребра, сравнивая их порядковый номер с этим значением.
delete from edges;
insert into edges(from_id, to_id)
select origin_nodes.id, target_nodes.id
from nodes as origin_nodes,
lateral (
          select id,
        row_number() over (order by rand()+0*origin_nodes.id) as rn
          from nodes
          limit 4
        ) as target_nodes
where target_nodes.rn<(rand()*5);

select * from edges;
+---------+-------+
| from_id | to_id |
+---------+-------+
|       1 |     2 |
|       1 |     4 |
|       1 |     8 |
|       2 |    20 |
|       3 |    10 |
|       4 |     1 |
|       4 |    16 |
|       5 |    18 |
|       5 |     6 |
|       6 |    15 |
|       7 |     1 |
|       7 |     4 |
|       7 |    15 |
|       8 |     4 |
|       8 |     5 |
|       9 |    11 |
|       9 |    19 |
...

Как видите, из вершины №1 выходит 3 ребра к вершинам с номерами 2, 4 и 8; из второй вершины только одно ребро к вершине №20 и т.д. Как и планировалось, каждая вершина имеет случайное количество исходящих ребер.

Подводя итог, в MySQL теперь есть поддержка LATERAL, и каждый раз когда перед вами стоит задача "давайте для каждой строки найдем .." возможно вы сможете решить её, используя LATERAL производные таблицы.

Надеюсь, что эта функциональность окажется полезной для вас. Спасибо, что используете MySQL!

Дата публикации: 23.02.2019

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Статьи :
 Установка и настройка MySQL
 Коды ошибок в MySQL
>Программирование в MySQL
 Оптимизация производительности
 Кодировка символов в MySQL
 Хранение данных в MySQL
 MySQL Cluster
См. также:
 Оптимизация производительности MySQL
 Онлайн-курс по оптимизации MySQL
 Услуги по оптимизации MySQL