[MySQL, SQL] Применение оконных функций и CTE в MySQL 8.0 для реализации накопительного итога без хаков (перевод)
Автор
Сообщение
news_bot ®
Стаж: 6 лет 9 месяцев
Сообщений: 27286
Прим. перев.: в этой статье тимлид британской компании Ticketsolve делится решением своей весьма специфичной проблемы, демонстрируя при этом общие подходы к созданию так называемых accumulating (накопительных) функций с помощью современных возможностей MySQL 8.0. Его листинги наглядны и снабжены подробными объяснениями, что помогает вникнуть в суть проблематики даже тем, кто не погружался в неё столь глубоко.
Обычная стратегия для выполнения обновлений с использованием накопительных функций в MySQL — применение пользовательских переменных и паттерна UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)).
Этот паттерн плохо работает с оптимизатором (приводя к недетерминированному поведению), поэтому от него решили отказаться. В результате возникла некая пустота, поскольку (относительно) комплексную логику теперь сложнее реализовать (по крайней мере, с той же простотой).
В статье пойдет речь о двух способах ее реализации: с использованием оконных функций (канонический подход) и с помощью рекурсивных СТЕ (общих табличных выражений).
Требования и предыстория
Хотя СТЕ достаточно интуитивно понятны, тем, кто не очень хорошо знаком с ними, я рекомендую обратиться к моей предыдущей публикации на эту тему.
То же самое справедливо и для оконных функций: я буду подробно комментировать запросы/концепции, но общее представление все же не помешает. Оконным функциям посвящено огромное количество книг и публикаций (именно поэтому я до сих пор не писал о них); при этом в большинстве примеров вычисления проводятся либо на финансовых результатах, либо на демографических показателях. Однако в данной статье я буду использовать реальный случай.
Что касается ПО, рекомендую воспользоваться MySQL 8.0.19 (но это не обязательно). Все выражения должны выполняться в одной и той же консоли, чтобы повторно использовать @venue_id.
В мире ПО существует известная архитектурная дилемма: реализовывать логику на уровне приложения или на уровне базы данных? Хотя это вполне уместный вопрос, в нашем случае я исхожу из предположения, что логика должна остаться на уровне базы; причиной для этого могут быть, например, требования к скорости (как и было в нашем случае).
Задача
В этой задаче мы распределяем места в некоем зале (театральном).
Для целей бизнеса требуется каждому месту присваивать так называемую «группировку» (grouping) — дополнительный номер, представляющий его.
Вот алгоритм определения значения группировки:
- начинаем с 0 и верхнего левого места;
- если есть пустующее место между текущим и предшествующим или это новый ряд, то прибавляем 2 к прошлому значению (если это не абсолютно первое место), в противном случае — увеличиваем значение на 1;
- присваиваем группировку месту;
- переходим к новому месту в том же ряду или к следующему ряду (если предыдущий закончился) и повторяем с пункта 2; продолжаем всё до тех пор, пока места не закончатся.
Алгоритм на псевдокоде:
current_grouping = 0
for each row:
for each number:
if (is_there_a_space_after_last_seat or is_a_new_row) and is_not_the_first_seat:
current_grouping += 2
else
current_grouping += 1
seat.grouping = current_grouping
В реальной жизни нам нужно, чтобы конфигурация слева давала значения, приведенные справа:
x→ 0 1 2 0 1 2
y ╭───┬───┬───╮ ╭───┬───┬───╮
↓ 0 │ x │ x │ │ │ 1 │ 2 │ │
├───┼───┼───┤ ├───┼───┼───┤
1 │ x │ │ x │ │ 4 │ │ 6 │
├───┼───┼───┤ ├───┼───┼───┤
2 │ x │ │ │ │ 8 │ │ │
╰───┴───┴───╯ ╰───┴───┴───╯
Подготовка
Пусть базовая таблица имеет следующее минималистское строение:
CREATE TABLE seats (
id INT AUTO_INCREMENT PRIMARY KEY,
venue_id INT,
y INT,
x INT,
`row` VARCHAR(16),
number INT,
`grouping` INT,
UNIQUE venue_id_y_x (venue_id, y, x)
);
Нам, в общем-то, не нужны столбцы row и number. С другой стороны, мы не хотим использовать и таблицу, записи которой полностью содержатся в индексе (просто чтобы быть ближе к реальным задачам).
Основываясь на диаграмме выше, координаты каждого места имеют вид (y, x):
- (0, 0), (0, 1)
- (1, 0), (1, 2)
- (2, 0)
Обратите внимание, что мы используем у как первую координату, поскольку так проще следить за рядами.
Следует загрузить достаточно большое количество записей, чтобы помешать оптимизатору «найти» неожиданные короткие пути. Конечно, мы используем рекурсивные СТЕ:
INSERT INTO seats(venue_id, y, x, `row`, number)
WITH RECURSIVE venue_ids (id) AS
(
SELECT 0
UNION ALL
SELECT id + 1 FROM venue_ids WHERE id + 1 < 100000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */
v.id,
c.y, c.x,
CHAR(ORD('A') + FLOOR(RAND() * 3) USING ASCII) `row`,
FLOOR(RAND() * 3) `number`
FROM venue_ids v
JOIN (
VALUES
ROW(0, 0),
ROW(0, 1),
ROW(1, 0),
ROW(1, 2),
ROW(2, 0)
) c (y, x)
;
ANALYZE TABLE seats;
Несколько замечаний:
- Здесь СТЕ используется интересным (надеюсь!) образом: каждый цикл представляет venue_id, но поскольку мы хотим, чтобы для каждого venue (цикла) генерировалось множество мест, мы делаем cross join (перекрестное соединение) с таблицей, содержащей данные о местах.
- Используется конструктор рядов из версии v8.0.19 (VALUES ROW()...) чтобы представлять (соединяемую) таблицу, не создавая ее фактически.
- Генерируются случайные значения для row и number в качестве заполнителей.
- Для простоты повествования мы не занимались оптимизациями (например, типы данных шире, чем нужно; индексы добавляются до вставки записей и т.д.).
Старый подход
Старый добрый подход весьма прямолинеен и незамысловат:
SET @venue_id = 5000; -- произвольный venue id; любой (хранимый) id подойдет
SET @grouping = -1;
SET @y = -1;
SET @x = -1;
WITH seat_groupings (id, y, x, `grouping`, tmp_y, tmp_x) AS
(
SELECT
id, y, x,
@grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
@y := seats.y,
@x := seats.x
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
)
UPDATE
seats s
JOIN seat_groupings sg USING (id)
SET s.grouping = sg.grouping
;
-- Query OK, 5 rows affected, 3 warnings (0,00 sec)
Что ж, это было легко (но не забывайте о предупреждениях)!
Небольшое отступление: в данном случае я пользуюсь свойствами булевой арифметики. Следующие выражения эквивалентны:
SELECT seats.x > @x + 1 OR seats.y != @y `increment`;
SELECT IF (
seats.x > @x + 1 OR seats.y != @y,
1,
0
) `increment`;
Некоторые находят это интуитивно понятным, другие — нет; тут дело вкуса. Дальше я буду использовать более компактное выражение.
Давайте посмотрим на результат:
SELECT id, y, x, `grouping` FROM seats WHERE venue_id = @venue_id ORDER BY y, x;
-- +-------+------+------+----------+
-- | id | y | x | grouping |
-- +-------+------+------+----------+
-- | 24887 | 0 | 0 | 1 |
-- | 27186 | 0 | 1 | 2 |
-- | 29485 | 1 | 0 | 4 |
-- | 31784 | 1 | 2 | 6 |
-- | 34083 | 2 | 0 | 8 |
-- +-------+------+------+----------+
Отличный подход!
Увы, у него есть «незначительный» недостаток: он прекрасно работает за исключением тех случаев, когда не работает…
Все дело в том, что оптимизатор запросов вовсе не обязательно проводит вычисления слева направо, поэтому операции присваивания (:=) могут выполняться в неверном порядке, приводя к неправильному результату. С подобной проблемой люди часто сталкиваются после обновления MySQL.
В MySQL 8.0 этот функционал действительно признан устаревшим:
-- Запустить сразу после UPDATE.
--
SHOW WARNINGS\G
-- *************************** 1. row ***************************
-- Level: Warning
-- Code: 1287
-- Message: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
-- [...]
Что ж, давайте исправим ситуацию!
Современный подход №1: оконные функции
Появление оконных функций было весьма долгожданным событием в мире MySQL.
Вообще говоря, «скользящая» природа оконных функций отлично сочетается с накопительными функциями. Однако некоторые сложные накопительные функции требуют наличия результатов последнего выражения — функциональность, которую оконные функции не поддерживают, поскольку работают на столбцах.
Это не означает, что проблему нельзя решить: просто ее необходимо переосмыслить.
В нашем случае можно разделить задачу на две части. Группировку для каждого места можно считать как сумму двух значений:
- порядкового номера каждого места,
- совокупного значения приращений всех мест, предшествующих данному.
Те, кто хорошо знаком с оконными функциями, распознают здесь типичные паттерны.
Порядковый номер каждого места — это встроенная функция:
ROW_NUMBER() OVER <window>
А вот с совокупным значением все гораздо интереснее… Чтобы его вычислить, мы выполняем два действия:
- считаем приращение для каждого места и записываем его в таблицу (или СТЕ),
- затем, для каждого места, с помощью оконной функции суммируем приращения для этого места.
Давайте посмотрим на SQL:
WITH
increments (id, increment) AS
(
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
)
SELECT
s.id, y, x,
ROW_NUMBER() OVER tzw + SUM(increment) OVER tzw `grouping`
FROM seats s
JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x)
;
-- +-------+---+---+----------+
-- | id | y | x | grouping |
-- +-------+---+---+----------+
-- | 24887 | 0 | 0 | 1 |
-- | 27186 | 0 | 1 | 2 |
-- | 29485 | 1 | 0 | 4 |
-- | 31784 | 1 | 2 | 6 |
-- | 34083 | 2 | 1 | 8 |
-- +-------+---+---+----------+
Здорово!
(Обратите внимание, что с настоящего момента я опускаю UPDATE ради простоты).
Давайте проанализируем запрос.
Высокоуровневая логика
Следующее CTE (отредактировано):
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw `increment`
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
;
-- +-------+-----------+
-- | id | increment |
-- +-------+-----------+
-- | 24887 | 0 |
-- | 27186 | 0 |
-- | 29485 | 1 |
-- | 31784 | 1 |
-- | 34083 | 1 |
-- +-------+-----------+
… вычисляет приращения для каждого места по сравнению с предыдущим (подробнее о LAG() — позже). Он работает на каждой записи и той, которая ей предшествует, и не является кумулятивным.
Теперь, чтобы подсчитать кумулятивные приращения, мы просто воспользуемся оконной функцией для вычисления суммы до каждого места и включая его:
-- (CTE here...)
SELECT
s.id, y, x,
ROW_NUMBER() OVER tzw `pos.`,
SUM(increment) OVER tzw `cum.incr.`
FROM seats s
JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x);
-- +-------+---+---+------+-----------+
-- | id | y | x | pos. | cum.incr. | (grouping)
-- +-------+---+---+------+-----------+
-- | 24887 | 0 | 0 | 1 | 0 | = 1 + 0 (curr.)
-- | 27186 | 0 | 1 | 2 | 0 | = 2 + 0 (#24887) + 0 (curr.)
-- | 29485 | 1 | 0 | 3 | 1 | = 3 + 0 (#24887) + 0 (#27186) + 1 (curr.)
-- | 31784 | 1 | 2 | 4 | 2 | = 4 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (curr.)
-- | 34083 | 2 | 1 | 5 | 3 | = 5 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (#31784)↵
-- +-------+---+---+------+-----------+ + 1 (curr.)
Оконная функция LAG()
Функция LAG в своей простейшей форме (LAG(x)) возвращает предыдущее значение заданного столбца. Классическое неудобство с такими функциями — обработка первой(-ых) записи(-ей) в окне. Поскольку предыдущей записи нет, они возвращают NULL. В случае LAG можно указать нужное значение как третий параметр:
LAG(x, 1, x - 1) -- по умолчанию равно `x -1`
LAG(y, 1, y) -- по умолчанию равно `y`
Указывая значения по умолчанию, мы гарантируем, что к самому первому месту в границах окна будет применяться та же логика, что и для места, следующего за другим (х-1) и без смены ряда (у).
Альтернативным решением является использование IFNULL, однако выражения при этом получаются весьма громоздкими:
-- Оба корректны, но ужасны!
--
IFNULL(x > LAG(x) OVER tzw + 1 OR y != LAG(y) OVER tzw, 0)
IFNULL(x > LAG(x) OVER tzw + 1, FALSE) OR IFNULL(y != LAG(y) OVER tzw, FALSE)
Второй параметр в LAG() — это число позиций, на которые надо сдвигаться назад в рамках окна; 1 — это предыдущее значение (оно также установлено по умолчанию).
Технические аспекты
Именованные окна
В нашем запросе много раз используется одно и то же окно. Следующие два запроса формально эквивалентны:
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x);
SELECT
id,
x > LAG(x, 1, x - 1) OVER (ORDER BY y, x) + 1
OR y != LAG(y, 1, y) OVER (ORDER BY y, x)
FROM seats
WHERE venue_id = @venue_id;
Однако второй может повлечь неоптимальное поведение (с чем я сталкивался — по крайней мере, в прошлом): оптимизатор может посчитать окна независимыми и отдельно высчитывать каждое. По этой причине я советую всегда использовать именованные окна (по крайней мере, когда они повторяются).
Оператор PARTITION BY
Обычно оконные функции выполняются на партиции. В нашем случае это будет выглядеть следующим образом:
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (PARTITION BY venue_id ORDER BY y, x); -- здесь!
Поскольку окно соответствует полному набору записей (который фильтруется условием WHERE), нам не нужно указывать ее (партицию).
А вот если бы этот запрос нужно было запустить на всей таблице seats, тогда пришлось бы сделать так, чтобы окно сбрасывалось для каждого venue_id.
Сортировка
В запросе ORDER BY задается на уровне окна:
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
При этом оконная сортировка идет отдельно от SELECT. Это очень важно! Поведение этого запроса:
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS ()
ORDER BY y, x
… не определено. Давайте обратимся к руководству:
Строки результата запроса определяются из выражения FROM после выполнения операторов WHERE, GROUP BY и HAVING, а выполнение в рамках окна происходит до ORDER BY, LIMIT и SELECT DISTINCT.
Некоторые соображения
Если говорить в общих чертах, для решения задач подобного типа имеет смысл рассчитывать изменение состояния для каждой записи, а затем их суммировать — вместо того, чтобы представлять каждую запись как функцию предыдущей.
Это решение более сложное, чем функционал, который оно заменяет, но в то же время надежное. Увы, этот подход не всегда возможен или легко реализуем. Именно здесь в игру вступают рекурсивные СТЕ.
Современный подход №2: рекурсивные CTE
Этот подход требует небольших хитростей из-за ограниченных возможностей СТЕ в MySQL. С другой стороны, это универсальное, прямое решение, поэтому оно не требует какого-либо переосмысления глобального подхода.
Давайте начнем с упрощенной версии конечного запроса:
-- `p_` означает `Previous` и упрощает понимание условий
--
WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
(
SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
)
UNION ALL
SELECT
s.id, s.venue_id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s
WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
ORDER BY s.venue_id, s.y, s.x
LIMIT 1
)
SELECT * FROM groupings;
Бинго! Этот запрос (относительно) прост, но, что более важно, он выражает накопительную функцию группировки самым простым возможным образом:
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
-- что эквивалентно следующему:
@grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
@y := seats.y,
@x := seats.x
Логика понятна даже для тех, кто не слишком знаком с СТЕ. Первый ряд — это первое место в зале, по порядку:
SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
В рекурсивной части мы проводим итерацию:
SELECT
s.id, s.venue_id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s
WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
ORDER BY s.venue_id, s.y, s.x
LIMIT 1
Условие WHERE вместе с операторами ORDER BY и LIMIT просто находит следующее место, то есть место с тем же venue_id, но с большими координатами (x, y) в последовательности (venue_id, x, y).
Часть s.venue_id в выражении сортировки очень важна! Она позволяет нам использовать индекс.
Оператор SELECT:
- выполняет накопление (вычисляет (p_)grouping),
- передает значения для текущего места (s.id, s.venue_id, s.y, s.x) в следующий цикл.
Мы выбираем FROM groupings, чтобы выполнить требования для рекурсивности СТЕ.
Здесь интересно то, что мы используем рекурсивное СТЕ как итератор, формируя выборку из таблицы groupings в рекурсивном подзапросе и соединяя ее с seats, чтобы найти данные для последующей обработки.
JOIN формально является перекрестным, однако из-за оператора LIMIT возвращается только одна запись.
Рабочая версия
К сожалению, приведенный выше запрос не работает, поскольку ORDER BY в настоящее время не поддерживается в рекурсивных подзапросах. Кроме того, семантика LIMIT в том виде, в котором он используется здесь, отличается от типичной, которая применяется к внешнему запросу:
LIMIT теперь поддерживается [..] Воздействие на итоговый набор данных такое же, как при использовании LIMIT с внешним SELECT
Впрочем, это не такая уж серьезная проблема. Давайте взглянем на работающую версию:
WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
(
SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
)
UNION ALL
SELECT
s.id, s.venue_id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s WHERE s.id = (
SELECT si.id
FROM seats si
WHERE si.venue_id = p_venue_id AND (si.y, si.x) > (p_y, p_x)
ORDER BY si.venue_id, si.y, si.x
LIMIT 1
)
)
SELECT * FROM groupings;
-- +-------+------+------+------------+
-- | p_id | p_y | p_x | p_grouping |
-- +-------+------+------+------------+
-- | 24887 | 0 | 0 | 1 |
-- | 27186 | 0 | 1 | 2 |
-- | 29485 | 1 | 0 | 4 |
-- | 31784 | 1 | 2 | 6 |
-- | 34083 | 2 | 0 | 8 |
-- +-------+------+------+------------+
Немного некомфортно использовать подзапрос, но данный подход работает и boilerplate здесь минимален, так как в любом случае требуется несколько выражений.
Здесь вместо того, чтобы проводить упорядочивание и лимитирование, связанное с объединением groupings и seats, мы делаем это в рамках подзапроса и передаем во внешний запрос, который затем выбирает только целевую запись.
Размышления о производительности
Давайте изучим план выполнения запроса с помощью EXPLAIN ANALYZE:
mysql> EXPLAIN ANALYZE WITH RECURSIVE groupings [...]
-> Table scan on groupings (actual time=0.000..0.001 rows=5 loops=1)
-> Materialize recursive CTE groupings (actual time=0.140..0.141 rows=5 loops=1)
-> Limit: 1 row(s) (actual time=0.019..0.019 rows=1 loops=1)
-> Index lookup on seats using venue_id_y_x (venue_id=(@venue_id)) (cost=0.75 rows=5) (actual time=0.018..0.018 rows=1 loops=1)
-> Repeat until convergence
-> Nested loop inner join (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Scan new records on groupings (cost=2.73 rows=2) (actual time=0.001..0.001 rows=2 loops=2)
-> Filter: (s.id = (select #5)) (cost=0.30 rows=1) (actual time=0.020..0.020 rows=1 loops=5)
-> Single-row index lookup on s using PRIMARY (id=(select #5)) (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
-> Select #5 (subquery in condition; dependent)
-> Limit: 1 row(s) (actual time=0.007..0.008 rows=1 loops=9)
-> Filter: ((si.y,si.x) > (groupings.p_y,groupings.p_x)) (cost=0.75 rows=5) (actual time=0.007..0.007 rows=1 loops=9)
-> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id) (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)
План вполне соответствует ожиданиям. В данном случае основа оптимального плана кроется в индексных поисках:
-> Nested loop inner join (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Single-row index lookup on s using PRIMARY (id=(select #5)) (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
-> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id) (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)
… имеющих первостепенное значение. Скорость работы значительно упадет, если производить сканирование индексов (то есть линейно сканировать записи индекса вместо того, чтобы искать сразу нужные).
Таким образом, чтобы эта стратегия работала, необходимо, чтобы связанные индексы были на месте и максимально эффективно использовались оптимизатором.
Если в будущем ограничения будут сняты, то отпадет и необходимость использовать подзапрос, что значительно упростит задачу для оптимизатора.
Альтернатива для неоптимальных планов
В случае, если оптимальный план невозможно определить, используйте временную таблицу:
CREATE TEMPORARY TABLE selected_seats (
id INT NOT NULL PRIMARY KEY,
y INT,
x INT,
UNIQUE (y, x)
)
SELECT id, y, x
FROM seats WHERE venue_id = @venue_id;
WITH RECURSIVE
groupings (p_id, p_y, p_x, p_grouping) AS
(
(
SELECT id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
)
UNION ALL
SELECT
s.id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s WHERE s.id = (
SELECT ss.id
FROM selected_seats ss
WHERE (ss.y, ss.x) > (p_y, p_x)
ORDER BY ss.y, ss.x
LIMIT 1
)
)
SELECT * FROM groupings;
Даже если в этом запросе проходят индексные сканы, они обходятся «малой кровью», поскольку таблица selected_seats очень мала.
Заключение
Я очень доволен тем, что эффективный, но в то же время имеющий определенные недостатки рабочий процесс теперь можно заменить достаточно простым функционалом, появившимися в MySQL 8.0.
Тем временем, развитие новых фич для 8.0 продолжается, что делает и без того удачный релиз ещё лучше.
Успешной рекурсии!
P.S. от переводчика
Читайте также в нашем блоге:
- «Обновление MySQL (Percona Server) с 5.7 до 8.0»;
- «Базы данных и Kubernetes (обзор и видео доклада)»;
- «Больше разработчиков должны знать это о базах данных».
===========
Источник:
habr.com
===========
===========
Автор оригинала: Saverio Miroddi
===========Похожие новости:
- [Google Chrome, Компьютерное железо, Ноутбуки, Софт] Вышла Chrome OS 84, и здесь есть на что посмотреть
- [PostgreSQL, Администрирование баз данных, Системное администрирование] Борьба с нагрузкой в PostgreSQL, помогает ли репликация в этом Андрей Сальников (Data Egret)
- [ReactJS, Лайфхаки для гиков, Облачные сервисы, Программирование] 10 инструментов для повышения продуктивности React-девелоперов в 2020 году (перевод)
- [Big Data, SQL, Высокая производительность, Системное администрирование, Хранилища данных] Benchmark ClickHouse Database and clickhousedb_fdw (PostgreSQL) (перевод)
- [Научно-популярное, Физика] В черном-черном океане живет черная-черная рыба с альбедо кожи 0.5%
- [PostgreSQL] Этюд по реализации ориентированного графа с единичными ребрами, используя PL/pgSQL
- [IT-компании, Управление e-commerce, Управление разработкой] Зачем нам 170 разработчиков
- [Kubernetes, Серверное администрирование, Системное администрирование] Практические истории из наших SRE-будней. Часть 2
- [DevOps, Серверное администрирование, Системное администрирование] Использование переменных Grafana для большей интерактивности дашбордов (перевод)
- [Гаджеты, Информационная безопасность, Компьютерное железо, Лайфхаки для гиков] Как найти скрытую камеру в съемной квартире или номере отеля
Теги для поиска: #_mysql, #_sql, #_mysql, #_mysql_8, #_nakopitelnyj_itog (накопительный итог), #_window_functions, #_cte, #_blog_kompanii_flant (
Блог компании Флант
), #_mysql, #_sql
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 22:30
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 9 месяцев |
|
 Прим. перев.: в этой статье тимлид британской компании Ticketsolve делится решением своей весьма специфичной проблемы, демонстрируя при этом общие подходы к созданию так называемых accumulating (накопительных) функций с помощью современных возможностей MySQL 8.0. Его листинги наглядны и снабжены подробными объяснениями, что помогает вникнуть в суть проблематики даже тем, кто не погружался в неё столь глубоко. Обычная стратегия для выполнения обновлений с использованием накопительных функций в MySQL — применение пользовательских переменных и паттерна UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)). Этот паттерн плохо работает с оптимизатором (приводя к недетерминированному поведению), поэтому от него решили отказаться. В результате возникла некая пустота, поскольку (относительно) комплексную логику теперь сложнее реализовать (по крайней мере, с той же простотой). В статье пойдет речь о двух способах ее реализации: с использованием оконных функций (канонический подход) и с помощью рекурсивных СТЕ (общих табличных выражений). Требования и предыстория Хотя СТЕ достаточно интуитивно понятны, тем, кто не очень хорошо знаком с ними, я рекомендую обратиться к моей предыдущей публикации на эту тему. То же самое справедливо и для оконных функций: я буду подробно комментировать запросы/концепции, но общее представление все же не помешает. Оконным функциям посвящено огромное количество книг и публикаций (именно поэтому я до сих пор не писал о них); при этом в большинстве примеров вычисления проводятся либо на финансовых результатах, либо на демографических показателях. Однако в данной статье я буду использовать реальный случай. Что касается ПО, рекомендую воспользоваться MySQL 8.0.19 (но это не обязательно). Все выражения должны выполняться в одной и той же консоли, чтобы повторно использовать @venue_id. В мире ПО существует известная архитектурная дилемма: реализовывать логику на уровне приложения или на уровне базы данных? Хотя это вполне уместный вопрос, в нашем случае я исхожу из предположения, что логика должна остаться на уровне базы; причиной для этого могут быть, например, требования к скорости (как и было в нашем случае). Задача В этой задаче мы распределяем места в некоем зале (театральном). Для целей бизнеса требуется каждому месту присваивать так называемую «группировку» (grouping) — дополнительный номер, представляющий его. Вот алгоритм определения значения группировки:
Алгоритм на псевдокоде: current_grouping = 0
for each row: for each number: if (is_there_a_space_after_last_seat or is_a_new_row) and is_not_the_first_seat: current_grouping += 2 else current_grouping += 1 seat.grouping = current_grouping В реальной жизни нам нужно, чтобы конфигурация слева давала значения, приведенные справа: x→ 0 1 2 0 1 2
y ╭───┬───┬───╮ ╭───┬───┬───╮ ↓ 0 │ x │ x │ │ │ 1 │ 2 │ │ ├───┼───┼───┤ ├───┼───┼───┤ 1 │ x │ │ x │ │ 4 │ │ 6 │ ├───┼───┼───┤ ├───┼───┼───┤ 2 │ x │ │ │ │ 8 │ │ │ ╰───┴───┴───╯ ╰───┴───┴───╯ Подготовка Пусть базовая таблица имеет следующее минималистское строение: CREATE TABLE seats (
id INT AUTO_INCREMENT PRIMARY KEY, venue_id INT, y INT, x INT, `row` VARCHAR(16), number INT, `grouping` INT, UNIQUE venue_id_y_x (venue_id, y, x) ); Нам, в общем-то, не нужны столбцы row и number. С другой стороны, мы не хотим использовать и таблицу, записи которой полностью содержатся в индексе (просто чтобы быть ближе к реальным задачам). Основываясь на диаграмме выше, координаты каждого места имеют вид (y, x):
Обратите внимание, что мы используем у как первую координату, поскольку так проще следить за рядами. Следует загрузить достаточно большое количество записей, чтобы помешать оптимизатору «найти» неожиданные короткие пути. Конечно, мы используем рекурсивные СТЕ: INSERT INTO seats(venue_id, y, x, `row`, number)
WITH RECURSIVE venue_ids (id) AS ( SELECT 0 UNION ALL SELECT id + 1 FROM venue_ids WHERE id + 1 < 100000 ) SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ v.id, c.y, c.x, CHAR(ORD('A') + FLOOR(RAND() * 3) USING ASCII) `row`, FLOOR(RAND() * 3) `number` FROM venue_ids v JOIN ( VALUES ROW(0, 0), ROW(0, 1), ROW(1, 0), ROW(1, 2), ROW(2, 0) ) c (y, x) ; ANALYZE TABLE seats; Несколько замечаний:
Старый подход Старый добрый подход весьма прямолинеен и незамысловат: SET @venue_id = 5000; -- произвольный venue id; любой (хранимый) id подойдет
SET @grouping = -1; SET @y = -1; SET @x = -1; WITH seat_groupings (id, y, x, `grouping`, tmp_y, tmp_x) AS ( SELECT id, y, x, @grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y), @y := seats.y, @x := seats.x FROM seats WHERE venue_id = @venue_id ORDER BY y, x ) UPDATE seats s JOIN seat_groupings sg USING (id) SET s.grouping = sg.grouping ; -- Query OK, 5 rows affected, 3 warnings (0,00 sec) Что ж, это было легко (но не забывайте о предупреждениях)! Небольшое отступление: в данном случае я пользуюсь свойствами булевой арифметики. Следующие выражения эквивалентны: SELECT seats.x > @x + 1 OR seats.y != @y `increment`;
SELECT IF ( seats.x > @x + 1 OR seats.y != @y, 1, 0 ) `increment`; Некоторые находят это интуитивно понятным, другие — нет; тут дело вкуса. Дальше я буду использовать более компактное выражение. Давайте посмотрим на результат: SELECT id, y, x, `grouping` FROM seats WHERE venue_id = @venue_id ORDER BY y, x;
-- +-------+------+------+----------+ -- | id | y | x | grouping | -- +-------+------+------+----------+ -- | 24887 | 0 | 0 | 1 | -- | 27186 | 0 | 1 | 2 | -- | 29485 | 1 | 0 | 4 | -- | 31784 | 1 | 2 | 6 | -- | 34083 | 2 | 0 | 8 | -- +-------+------+------+----------+ Отличный подход! Увы, у него есть «незначительный» недостаток: он прекрасно работает за исключением тех случаев, когда не работает… Все дело в том, что оптимизатор запросов вовсе не обязательно проводит вычисления слева направо, поэтому операции присваивания (:=) могут выполняться в неверном порядке, приводя к неправильному результату. С подобной проблемой люди часто сталкиваются после обновления MySQL. В MySQL 8.0 этот функционал действительно признан устаревшим: -- Запустить сразу после UPDATE.
-- SHOW WARNINGS\G -- *************************** 1. row *************************** -- Level: Warning -- Code: 1287 -- Message: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. -- [...] Что ж, давайте исправим ситуацию! Современный подход №1: оконные функции Появление оконных функций было весьма долгожданным событием в мире MySQL. Вообще говоря, «скользящая» природа оконных функций отлично сочетается с накопительными функциями. Однако некоторые сложные накопительные функции требуют наличия результатов последнего выражения — функциональность, которую оконные функции не поддерживают, поскольку работают на столбцах. Это не означает, что проблему нельзя решить: просто ее необходимо переосмыслить. В нашем случае можно разделить задачу на две части. Группировку для каждого места можно считать как сумму двух значений:
Те, кто хорошо знаком с оконными функциями, распознают здесь типичные паттерны. Порядковый номер каждого места — это встроенная функция: ROW_NUMBER() OVER <window>
А вот с совокупным значением все гораздо интереснее… Чтобы его вычислить, мы выполняем два действия:
Давайте посмотрим на SQL: WITH
increments (id, increment) AS ( SELECT id, x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw FROM seats WHERE venue_id = @venue_id WINDOW tzw AS (ORDER BY y, x) ) SELECT s.id, y, x, ROW_NUMBER() OVER tzw + SUM(increment) OVER tzw `grouping` FROM seats s JOIN increments i USING (id) WINDOW tzw AS (ORDER BY y, x) ; -- +-------+---+---+----------+ -- | id | y | x | grouping | -- +-------+---+---+----------+ -- | 24887 | 0 | 0 | 1 | -- | 27186 | 0 | 1 | 2 | -- | 29485 | 1 | 0 | 4 | -- | 31784 | 1 | 2 | 6 | -- | 34083 | 2 | 1 | 8 | -- +-------+---+---+----------+ Здорово! (Обратите внимание, что с настоящего момента я опускаю UPDATE ради простоты). Давайте проанализируем запрос. Высокоуровневая логика Следующее CTE (отредактировано): SELECT
id, x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw `increment` FROM seats WHERE venue_id = @venue_id WINDOW tzw AS (ORDER BY y, x) ; -- +-------+-----------+ -- | id | increment | -- +-------+-----------+ -- | 24887 | 0 | -- | 27186 | 0 | -- | 29485 | 1 | -- | 31784 | 1 | -- | 34083 | 1 | -- +-------+-----------+ … вычисляет приращения для каждого места по сравнению с предыдущим (подробнее о LAG() — позже). Он работает на каждой записи и той, которая ей предшествует, и не является кумулятивным. Теперь, чтобы подсчитать кумулятивные приращения, мы просто воспользуемся оконной функцией для вычисления суммы до каждого места и включая его: -- (CTE here...)
SELECT s.id, y, x, ROW_NUMBER() OVER tzw `pos.`, SUM(increment) OVER tzw `cum.incr.` FROM seats s JOIN increments i USING (id) WINDOW tzw AS (ORDER BY y, x); -- +-------+---+---+------+-----------+ -- | id | y | x | pos. | cum.incr. | (grouping) -- +-------+---+---+------+-----------+ -- | 24887 | 0 | 0 | 1 | 0 | = 1 + 0 (curr.) -- | 27186 | 0 | 1 | 2 | 0 | = 2 + 0 (#24887) + 0 (curr.) -- | 29485 | 1 | 0 | 3 | 1 | = 3 + 0 (#24887) + 0 (#27186) + 1 (curr.) -- | 31784 | 1 | 2 | 4 | 2 | = 4 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (curr.) -- | 34083 | 2 | 1 | 5 | 3 | = 5 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (#31784)↵ -- +-------+---+---+------+-----------+ + 1 (curr.) Оконная функция LAG() Функция LAG в своей простейшей форме (LAG(x)) возвращает предыдущее значение заданного столбца. Классическое неудобство с такими функциями — обработка первой(-ых) записи(-ей) в окне. Поскольку предыдущей записи нет, они возвращают NULL. В случае LAG можно указать нужное значение как третий параметр: LAG(x, 1, x - 1) -- по умолчанию равно `x -1`
LAG(y, 1, y) -- по умолчанию равно `y` Указывая значения по умолчанию, мы гарантируем, что к самому первому месту в границах окна будет применяться та же логика, что и для места, следующего за другим (х-1) и без смены ряда (у). Альтернативным решением является использование IFNULL, однако выражения при этом получаются весьма громоздкими: -- Оба корректны, но ужасны!
-- IFNULL(x > LAG(x) OVER tzw + 1 OR y != LAG(y) OVER tzw, 0) IFNULL(x > LAG(x) OVER tzw + 1, FALSE) OR IFNULL(y != LAG(y) OVER tzw, FALSE) Второй параметр в LAG() — это число позиций, на которые надо сдвигаться назад в рамках окна; 1 — это предыдущее значение (оно также установлено по умолчанию). Технические аспекты Именованные окна В нашем запросе много раз используется одно и то же окно. Следующие два запроса формально эквивалентны: SELECT
id, x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw FROM seats WHERE venue_id = @venue_id WINDOW tzw AS (ORDER BY y, x); SELECT id, x > LAG(x, 1, x - 1) OVER (ORDER BY y, x) + 1 OR y != LAG(y, 1, y) OVER (ORDER BY y, x) FROM seats WHERE venue_id = @venue_id; Однако второй может повлечь неоптимальное поведение (с чем я сталкивался — по крайней мере, в прошлом): оптимизатор может посчитать окна независимыми и отдельно высчитывать каждое. По этой причине я советую всегда использовать именованные окна (по крайней мере, когда они повторяются). Оператор PARTITION BY Обычно оконные функции выполняются на партиции. В нашем случае это будет выглядеть следующим образом: SELECT
id, x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw FROM seats WHERE venue_id = @venue_id WINDOW tzw AS (PARTITION BY venue_id ORDER BY y, x); -- здесь! Поскольку окно соответствует полному набору записей (который фильтруется условием WHERE), нам не нужно указывать ее (партицию). А вот если бы этот запрос нужно было запустить на всей таблице seats, тогда пришлось бы сделать так, чтобы окно сбрасывалось для каждого venue_id. Сортировка В запросе ORDER BY задается на уровне окна: SELECT
id, x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw FROM seats WHERE venue_id = @venue_id WINDOW tzw AS (ORDER BY y, x) При этом оконная сортировка идет отдельно от SELECT. Это очень важно! Поведение этого запроса: SELECT
id, x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw FROM seats WHERE venue_id = @venue_id WINDOW tzw AS () ORDER BY y, x … не определено. Давайте обратимся к руководству: Строки результата запроса определяются из выражения FROM после выполнения операторов WHERE, GROUP BY и HAVING, а выполнение в рамках окна происходит до ORDER BY, LIMIT и SELECT DISTINCT.
Некоторые соображения Если говорить в общих чертах, для решения задач подобного типа имеет смысл рассчитывать изменение состояния для каждой записи, а затем их суммировать — вместо того, чтобы представлять каждую запись как функцию предыдущей. Это решение более сложное, чем функционал, который оно заменяет, но в то же время надежное. Увы, этот подход не всегда возможен или легко реализуем. Именно здесь в игру вступают рекурсивные СТЕ. Современный подход №2: рекурсивные CTE Этот подход требует небольших хитростей из-за ограниченных возможностей СТЕ в MySQL. С другой стороны, это универсальное, прямое решение, поэтому оно не требует какого-либо переосмысления глобального подхода. Давайте начнем с упрощенной версии конечного запроса: -- `p_` означает `Previous` и упрощает понимание условий
-- WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS ( ( SELECT id, venue_id, y, x, 1 FROM seats WHERE venue_id = @venue_id ORDER BY y, x LIMIT 1 ) UNION ALL SELECT s.id, s.venue_id, s.y, s.x, p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y) FROM groupings, seats s WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x) ORDER BY s.venue_id, s.y, s.x LIMIT 1 ) SELECT * FROM groupings; Бинго! Этот запрос (относительно) прост, но, что более важно, он выражает накопительную функцию группировки самым простым возможным образом: p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
-- что эквивалентно следующему: @grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y), @y := seats.y, @x := seats.x Логика понятна даже для тех, кто не слишком знаком с СТЕ. Первый ряд — это первое место в зале, по порядку: SELECT id, venue_id, y, x, 1
FROM seats WHERE venue_id = @venue_id ORDER BY y, x LIMIT 1 В рекурсивной части мы проводим итерацию: SELECT
s.id, s.venue_id, s.y, s.x, p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y) FROM groupings, seats s WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x) ORDER BY s.venue_id, s.y, s.x LIMIT 1 Условие WHERE вместе с операторами ORDER BY и LIMIT просто находит следующее место, то есть место с тем же venue_id, но с большими координатами (x, y) в последовательности (venue_id, x, y). Часть s.venue_id в выражении сортировки очень важна! Она позволяет нам использовать индекс. Оператор SELECT:
Мы выбираем FROM groupings, чтобы выполнить требования для рекурсивности СТЕ. Здесь интересно то, что мы используем рекурсивное СТЕ как итератор, формируя выборку из таблицы groupings в рекурсивном подзапросе и соединяя ее с seats, чтобы найти данные для последующей обработки. JOIN формально является перекрестным, однако из-за оператора LIMIT возвращается только одна запись. Рабочая версия К сожалению, приведенный выше запрос не работает, поскольку ORDER BY в настоящее время не поддерживается в рекурсивных подзапросах. Кроме того, семантика LIMIT в том виде, в котором он используется здесь, отличается от типичной, которая применяется к внешнему запросу: LIMIT теперь поддерживается [..] Воздействие на итоговый набор данных такое же, как при использовании LIMIT с внешним SELECT
Впрочем, это не такая уж серьезная проблема. Давайте взглянем на работающую версию: WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
( ( SELECT id, venue_id, y, x, 1 FROM seats WHERE venue_id = @venue_id ORDER BY y, x LIMIT 1 ) UNION ALL SELECT s.id, s.venue_id, s.y, s.x, p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y) FROM groupings, seats s WHERE s.id = ( SELECT si.id FROM seats si WHERE si.venue_id = p_venue_id AND (si.y, si.x) > (p_y, p_x) ORDER BY si.venue_id, si.y, si.x LIMIT 1 ) ) SELECT * FROM groupings; -- +-------+------+------+------------+ -- | p_id | p_y | p_x | p_grouping | -- +-------+------+------+------------+ -- | 24887 | 0 | 0 | 1 | -- | 27186 | 0 | 1 | 2 | -- | 29485 | 1 | 0 | 4 | -- | 31784 | 1 | 2 | 6 | -- | 34083 | 2 | 0 | 8 | -- +-------+------+------+------------+ Немного некомфортно использовать подзапрос, но данный подход работает и boilerplate здесь минимален, так как в любом случае требуется несколько выражений. Здесь вместо того, чтобы проводить упорядочивание и лимитирование, связанное с объединением groupings и seats, мы делаем это в рамках подзапроса и передаем во внешний запрос, который затем выбирает только целевую запись. Размышления о производительности Давайте изучим план выполнения запроса с помощью EXPLAIN ANALYZE: mysql> EXPLAIN ANALYZE WITH RECURSIVE groupings [...]
-> Table scan on groupings (actual time=0.000..0.001 rows=5 loops=1) -> Materialize recursive CTE groupings (actual time=0.140..0.141 rows=5 loops=1) -> Limit: 1 row(s) (actual time=0.019..0.019 rows=1 loops=1) -> Index lookup on seats using venue_id_y_x (venue_id=(@venue_id)) (cost=0.75 rows=5) (actual time=0.018..0.018 rows=1 loops=1) -> Repeat until convergence -> Nested loop inner join (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2) -> Scan new records on groupings (cost=2.73 rows=2) (actual time=0.001..0.001 rows=2 loops=2) -> Filter: (s.id = (select #5)) (cost=0.30 rows=1) (actual time=0.020..0.020 rows=1 loops=5) -> Single-row index lookup on s using PRIMARY (id=(select #5)) (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5) -> Select #5 (subquery in condition; dependent) -> Limit: 1 row(s) (actual time=0.007..0.008 rows=1 loops=9) -> Filter: ((si.y,si.x) > (groupings.p_y,groupings.p_x)) (cost=0.75 rows=5) (actual time=0.007..0.007 rows=1 loops=9) -> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id) (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9) План вполне соответствует ожиданиям. В данном случае основа оптимального плана кроется в индексных поисках: -> Nested loop inner join (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Single-row index lookup on s using PRIMARY (id=(select #5)) (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5) -> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id) (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9) … имеющих первостепенное значение. Скорость работы значительно упадет, если производить сканирование индексов (то есть линейно сканировать записи индекса вместо того, чтобы искать сразу нужные). Таким образом, чтобы эта стратегия работала, необходимо, чтобы связанные индексы были на месте и максимально эффективно использовались оптимизатором. Если в будущем ограничения будут сняты, то отпадет и необходимость использовать подзапрос, что значительно упростит задачу для оптимизатора. Альтернатива для неоптимальных планов В случае, если оптимальный план невозможно определить, используйте временную таблицу: CREATE TEMPORARY TABLE selected_seats (
id INT NOT NULL PRIMARY KEY, y INT, x INT, UNIQUE (y, x) ) SELECT id, y, x FROM seats WHERE venue_id = @venue_id; WITH RECURSIVE groupings (p_id, p_y, p_x, p_grouping) AS ( ( SELECT id, y, x, 1 FROM seats WHERE venue_id = @venue_id ORDER BY y, x LIMIT 1 ) UNION ALL SELECT s.id, s.y, s.x, p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y) FROM groupings, seats s WHERE s.id = ( SELECT ss.id FROM selected_seats ss WHERE (ss.y, ss.x) > (p_y, p_x) ORDER BY ss.y, ss.x LIMIT 1 ) ) SELECT * FROM groupings; Даже если в этом запросе проходят индексные сканы, они обходятся «малой кровью», поскольку таблица selected_seats очень мала. Заключение Я очень доволен тем, что эффективный, но в то же время имеющий определенные недостатки рабочий процесс теперь можно заменить достаточно простым функционалом, появившимися в MySQL 8.0. Тем временем, развитие новых фич для 8.0 продолжается, что делает и без того удачный релиз ещё лучше. Успешной рекурсии! P.S. от переводчика Читайте также в нашем блоге:
=========== Источник: habr.com =========== =========== Автор оригинала: Saverio Miroddi ===========Похожие новости:
Блог компании Флант ), #_mysql, #_sql |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 22-Ноя 22:30
Часовой пояс: UTC + 5