Запрос SQL JOIN требует более 15 секунд для запуска
У меня есть довольно большой SQL-запрос для получения данных из нескольких таблиц базы данных. Я использую условие ON, чтобы проверить, всегда ли совпадают идентификаторы guild_id, а в некоторых случаях он также проверяет идентификатор пользователя.
Это мой запрос:
SELECT
SUM( f.guild_id = 787672220503244800 AND f.winner_id LIKE '%841827102331240468%' ) AS guild_winner,
SUM( f.winner_id LIKE '%841827102331240468%' ) AS win_sum,
m.message_count,
r.bypass_role_id,
i.real_count,
i.total_count,
i.bonus_count,
i.left_count
FROM
guild_finished_giveaways AS f
JOIN guild_message_count AS m
JOIN guild_role_settings AS r
JOIN guild_invite_count AS i ON m.guild_id = f.guild_id
AND m.user_id = 841827102331240468
AND r.guild_id = f.guild_id
AND i.guild_id = f.guild_id
AND i.user_id = m.user_id
Но он работает довольно медленно, более 15 секунд. Не понимаю, зачем так долго.
Я понял, что если я удалю JOIN "guild_invite_count", это снова будет довольно быстро. У меня есть какая-то простая ошибка, которую я не вижу? Или в чем может быть дело?
Я понятия не имею об индексах. Я думал, что guild_id будет что-то вроде этого.
GROUP BY не нужно?
Ваша цепочка JOIN немного обратная. Почему у вас нет отдельного ON для каждого JOIN?
Пожалуйста, опубликуйте EXPLAIN и определения таблиц. Кроме того, я хотел бы увидеть образцы данных и ожидаемые результаты. Ваш синтаксис выглядит нетрадиционным, и я не уверен, что он делает то, что вы думаете.
Проблема почти наверняка заключается в предложении «LIKE» с ведущим подстановочным знаком.
Соединения и агрегация могут быть немного сложными. Получаете ли вы еще один результат вашей агрегации в подзапросе перед присоединением?
Является ли f.winner_id символьным/varchar столбцом, а m.user_id целочисленным/числовым столбцом? Почему?
Winner_id — это столбец типа varchar, поскольку он может содержать несколько идентификаторов в таком формате: «123 123 123».
Как просили ранее, добавьте к своему вопросу вывод EXPLAIN для вашего запроса и определения вашей таблицы. Вывод SHOW INDEX FROM tbl для каждой таблицы также будет полезен. Тот факт, что вы можете выполнить этот запрос без получения ошибки, говорит о том, что вы отключили ONLY_FULL_GROUP_BY, что является плохой идеей, поскольку позволяет выполнять недетерминированные запросы.
Хранение сериализованных данных (user_id, разделенных пробелами) в столбце f.winner_id — действительно плохая идея. Вы должны разделить их на их собственную соединительную таблицу.
Пожалуйста, укажите SHOW CREATE TABLE для каждого стола. Я предполагаю, что идентификаторы BIGINT UNSIGNED? Вам действительно нужен LIKE?
Ответы 2
Для каждого JOIN выражения нужен свой ON. Не ждите этого до конца. Как бы то ни было, сервер был вынужден создать декартово произведение всех этих таблиц, прежде чем снова сузить их, и я удивлен, что запрос вообще выполняется (я ожидал синтаксическую ошибку из-за отсутствующих предложений ON).
FROM guild_finished_giveaways AS f
JOIN guild_message_count AS m ON m.guild_id = f.guild_id
JOIN guild_role_settings AS r ON r.guild_id = f.guild_id
JOIN guild_invite_count AS i ON i.guild_id = f.guild_id
AND i.user_id = m.user_id
WHERE m.user_id = 841827102331240468
Также более чем странно использовать SUM() или любую другую агрегатную функцию в том же запросе, что и неагрегированные значения без предложения GROUP BY.
Хотя я согласен с тем, что критерии соединения должны быть в соответствующем предложении on, неверно предполагать, что сервер будет создавать декартово произведение с запросом OP. Оптимизатор разберется с неправильно расставленными критериями.
Вы используете InnoDB?
На каждом столе есть PRIMARY KEY?
Это может помочь:
m: PRIMARY KEY(user_id) -- assuming that is unique in that table
f: INDEX(guild_id, winner_id)
r: INDEX(guild_id, bypass_role_id)
i: INDEX(user_id,)
Похоже, что некоторые таблицы не должны быть отдельными — возможно, r, i, f можно объединить? (Мне нужно увидеть SHOW CREATE TABLE, чтобы сказать больше.)
НЕ иметь коммалиста в winner_id. Вместо этого создайте другую таблицу с одной строкой для каждого победителя в каждой игре (или любой другой победитель). Возможно, только для столбцов, таких как таблица сопоставления «многие ко многим».
Отметив, что выполнение, скорее всего, начнется с m, а затем перейдет к i, давайте улучшим предложение Джоэла:
FROM guild_message_count AS m
JOIN guild_invite_count AS i ON i.user_id = m.user_id
JOIN guild_finished_giveaways AS f ON f.guild_id = m.guild_id
JOIN guild_role_settings AS r ON r.guild_id = m.guild_id
WHERE m.user_id = 841827102331240468
Обратите внимание, что 3 стола соединены guild_id; но нужно только 2 =.
SUM без GROUP BY суммирует весь набор результатов (после JOINing). Но у вас есть 6 неагрегатов, поэтому вам нужно GROUP BY все 6.
Но это может привести к сильно завышенным суммам. Возможно, вам нужно сначала выполнить агрегацию чуть выше f, так как именно здесь вы суммируете. Тогда ПРИСОЕДИНЯЙТЕСЬ к остальным??
Вы пользуетесь индексом?