Как я могу оптимизировать этот запрос и избавиться от подзапросов?

Question

Столкнулся с проблемой DDoS, из за огромного запроса. Затрачиватеся слишком много памяти и долгая обработка запроса.

Единственный запрос, который можно обработать данные правильно предоставлен ниже, но он ниразу не оптимизированный.

Имеется таблица course:

Имеется таблица block_of_course:

Имеется таблица user_to_course_completed_block:

Имеется таблица employee:

Для решения получился вот такой запрос:

SELECT
    CONCAT_WS(' ', employee.first_name, employee.middle_name, employee.last_name) AS full_name,
    GROUP_CONCAT(DISTINCT
        CONCAT_WS('/', course.name,
            (
                SELECT count(DISTINCT bfc.id)
                FROM user_to_course_completed_block as user_to_course_completed_block
                    INNER JOIN block_for_course as bfc ON bfc.id = user_to_course_completed_block.block_id
                WHERE user_to_course_completed_block.user_id = user_to_course_completed_block.user_id and bfc.course_id = course.id
            ),
                (SELECT COUNT(*) FROM block_for_course WHERE block_for_course.course_id = course.id)
            )
     ) as result,
    employee.user_id,
    course.category_id
FROM
    user_to_course_completed_block AS uc_complete
        INNER JOIN employee ON employee.user_id = uc_complete.user_id
        INNER JOIN block_for_course ON block_for_course.id = uc_complete.block_id
        INNER JOIN course ON course.id = block_for_course.course_id
group by uc_complete.user_id, category_id;

Уже пробовал использовать и временные таблицы и пытался через join подсоединить еще, но результат всегда разный и с таким результатом нельзя работать в gridview. Надо именно такой который выводит этот результат, ну или максимально приближенный:

Версия mariaDb - 10.11.6

Answer 1

То что первое приходит в голову - попытаться сократить/упростить повторных обращений в таблицы и методы группировки/сортировки. Либо как-то ограничивать получаемый набор данных - фильтровать по id, ставить limit/offset и пр. Ну и напоследок не забываем про индексы/статистику. При оптимизации нет такого понятия как одного единственного решения, ведь в конечном итоге упретесь в tradeoff по cpu/mem/disk.

Ну и по хорошему, код надо структурировать так, чтобы легче читать. К примеру, я бы разделил на три отдельные части с самой простой структурой:

-- кто, какой курс, сколько блоков выполнил
WITH user_completed AS (
  SELECT
    uc.user_id,
    c.id AS course_id,
    c.category_id,
    c.name,
    COUNT(bc.id) AS block_completed
  FROM
    user_to_course_completed_block uc
    INNER JOIN block_for_course bc ON bc.id = uc.block_id
    INNER JOIN course c ON c.id = bc.course_id
  -- если нужно вывести по конкретному user_id
  -- WHERE
  --   uc.user_id = 1
  GROUP BY
    uc.user_id,
    c.category_id,
    c.id
),

-- сколько всего блоков имеется в курсах
user_course AS (
  SELECT
    uc.user_id,
    uc.category_id,
    uc.name,
    uc.block_completed,
    COUNT(bc.id) AS block_total
  FROM
    user_completed uc
    INNER JOIN block_for_course bc ON bc.course_id = uc.course_id
  GROUP BY
    uc.user_id,
    uc.course_id,
    uc.category_id
)

SELECT
  CONCAT_WS(' ', e.first_name, e.middle_name, e.last_name) AS full_name,
  GROUP_CONCAT(
    CASE
      WHEN uc.block_total > 0
      THEN CONCAT_WS('/', uc.name, COALESCE(uc.block_completed, 0), uc.block_total)
      ELSE NULL
    END
  ) AS result,
  e.user_id,
  uc.category_id
FROM
  employee e
  -- LEFT JOIN user_course uc ON uc.user_id = e.user_id
  INNER JOIN user_course uc ON uc.user_id = e.user_id
GROUP BY
  e.user_id,
  uc.category_id

БЛОГ НА HUSL

Как я могу оптимизировать этот запрос и избавиться от подзапросов?

Ответы (1 шт):