Проблема производительности с запросом mysql
Я работаю в MYSQL впервые, и у меня возникают проблемы со следующим запросом
SELECT
t.id,
t.name,
t.description,
(
SELECT
GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
FROM
occupation_skill_rate s
INNER JOIN hard_skills hs ON s.hard_skill_id = hs.id
WHERE
s.occupation_id = t.id
ORDER BY
s.rate DESC LIMIT 15
) AS skills,
(
SELECT
GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
FROM
occupation_knowledge_rate s
INNER JOIN knowledge hs ON s.knowledge_id = hs.id
WHERE
s.occupation_id = t.id
ORDER BY
s.rate DESC LIMIT 15
) AS knowledge,
(
SELECT
GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
FROM
occupation_abilities_rate s
INNER JOIN ability hs ON s.ability_id = hs.id
WHERE
s.occupation_id = t.id
ORDER BY
s.rate DESC LIMIT 15
) AS knowledge
FROM
occupations t
Таблица профессий содержит 1033 строки, а в оккупации_skill_rate содержится 34 160 строк, и выполнение этого запроса занимает более 1 минуты. Пожалуйста, дайте мне знать, если вам нужны дополнительные разъяснения, чтобы помочь мне.
Спасибо за вашу помощь Аджаи
Видите эти уродливые красные коробки с надписью «Полное сканирование таблицы»? Вот почему ваш запрос медленный. Какие INDEX объекты вы определили в этих таблицах? Пожалуйста, покажите полный вывод EXPLAIN, так как он покажет вам, какие индексы вам нужно создать.
Да, вы правы, я добавил индекс для столбца occcupation_id, и теперь полный запрос выполняется за 1 с примерно 0,600 с.
Ответы 1
Таблицы occupation_%_rate кажутся многими ко многим, верно? Им нужны эти индексы, а не идентификатор:
PRIMARY KEY(occupation_id, xxx_id)
INDEX(xxx_id, occupation_id)
Но это похоже на ORDER BY и LIMIT при использовании с GROUP_CONCAT(). Пожалуйста, опишите цель запроса; мы можем помочь переписать его.
GROUP_CONCAT допускает пункт ORDER BY, но не LIMIT. Можно ли обойтись без LIMITs?
Пример
Вместо того
( SELECT GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
FROM occupation_skill_rate s
INNER JOIN hard_skills hs ON s.hard_skill_id = hs.id
WHERE s.occupation_id = t.id
ORDER BY s.rate DESC
LIMIT 15
) AS skills;
Делать
( SELECT CONCAT( name, '|', rate ORDER BY rate DESC )
FROM (
SELECT hs.name, s.rate
FROM occupation_skill_rate s
INNER JOIN hard_skills hs
ON s.hard_skill_id = hs.id
AND s.occupation_id = t.id
ORDER BY s.rate DESC
LIMIT 15
) AS a
) AS skills
Но я подозреваю, что t не видно, что глубоко вложено.
Если это так, переставьте вещи следующим образом:
SELECT t.id, t.name, t.description, s3.skills, ...
FROM occupations AS t
JOIN (
SELECT s2.occupation_id,
CONCAT( s2.name, '|', s2.rate ORDER BY rate DESC )
AS skills
FROM (
SELECT hs.name, s1.rate, s1.occupation_id
FROM occupation_skill_rate s1
INNER JOIN hard_skills hs
ON s1.hard_skill_id = hs.id
ORDER BY s.rate DESC
LIMIT 15
) AS s2
GROUP BY s2.occupation_id
ORDER BY s2.rate DESC
) AS s3 ON s2.occupation_id = t.id
JOIN ...
JOIN ... ;
Другой
Также есть способ построить длинный GROUP_CONCAT, а затем разрезать на 15 предметов с помощью SUBSTRING_INDEX(...).
Да, я понимаю, что мы не можем использовать лимит в подзапросе. Есть ли другой обходной путь? нужно только 15 верхних строк из этого подзапроса
Я добавил еще...
Опубликуйте
EXPLAIN