Подзапрос в условии ON для LEFT JOIN'а в MySQL

Question

[Sorbing]

Я перекопал весь стековерфлов и утомил гугл, но я так и не понял почему MySQL запрос (ниже) работает не правильно. Прошу протянуть руку помощи или швырнуть в лицо доку).
Задача:
Нужно получить самую свежую дату из таблицы статистики для каждого юзера.
ER-диаграмма (упрощенная):

SQL запрос:

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S ON S.user_id = (
  SELECT user_id
  FROM stats AS S2
  # Без этого условия джойнит одну запись, что и нужно. Но, соответственно, ID юзера не соответствует
  WHERE S2.user_id = U.id
  ORDER BY S2.date DESC
  LIMIT 1
)
WHERE U.id = 1

Проблема:
Почему не работает LIMIT при условии WHERE в подзапросе?
Можно получить дату простым подзапросом, но мне нужно получить дополнительные поля, которые я не указывал в упрощенной схеме. И таких полей ~ 5, т.е. будет 5 подзапросов. Что будет быстрее не уверен, но пока склоняюсь что LEFT JOIN будет быстрее. Да и вопрос уже принципиален — пол дня убил.

Дамп БД на gist'e (30 строк) и тот же дамп для скачивания.

Заранее спасибо за помощь.

Answer 1

[Sorbing]

В итоге, я добавил суррогатный PK, вместо 3-х составных полей. Если не учитывать денормализацию БД, считаю это оптимальным решением. Итоговый запрос:

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S 
  ON S.id = (
    SELECT id
    FROM stats AS S2 
    WHERE S2.user_id = U.id
    ORDER BY id DESC
    LIMIT 1
  )

Я получаю необходиммые мне данные — все поля последней записи таблицы stats с любым типом (можно указать конкретный тип) для каждого юзера. При этом сортировка по ID будет быстрее, чем по дате. Запись для JOIN'а теперь однозначная, ситуация выборкой нескольких записей статсы с одинаковой датой решена.
Осталось прогнать на более-менее реальном наборе данных и оценить производительность.
Всем спасибо за помощь.

Answer 2

[Ivan Komarov]

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

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S 
  ON S.user_id = u.ID 
AND s.date = (
SELECT max(date) 
  FROM stats AS S2 
  WHERE s2.user_id = u.id
)

WHERE U.id =1 — по желанию :)

Comments

[Ivan Komarov]

p.s. и, кстати, добавьте составной индекс в таблицу stat по полям user_id и date

[Sorbing]

Это да, сенк. А что скажете про добавление суррогатного ключа вместо составного primary key из 3-х полей?

[Ivan Komarov]

Я не большой спец по mysql, если честно, я больше с T-SQL дело имел, но IMHO разницы не будет. В любом случае нужно смотреть план запроса и сравнивать результаты по скорости. Опять же все зависит от объема таблиц. И если этот запрос всегда будет выполнятся для одного юзера, то имхо разницы нет. А если для нескольких юзеров, то по-моему оба запроса так себе. Что в моём запросе SELECT max будет выполнятся для каждой выбранной записи из таблицы user, что в вашем запросе SELECT id FROM stats будет выполнятся не один раз. Если там действительно большие таблицы, и нужно получать данные сразу для многих юзеров, то может быть имеет смысл использовать временные таблицы? То есть сначала выбрать во временную таблицу запросом с группировкой max(date), user_id, а потом сделать join к этой временной таблице.

[Ivan Komarov]

я там ниже, в ветке shedal'а, написал IMHO самый правильный вариант :)

Answer 3

[Антон]

Вот похожий вопрос был — habrahabr.ru/qa/437/

Answer 4

[Вячеслав Голованов]

Простите, может я не очень понял.
Но по-моему, можно как-то проще:

SELECT user.ud, MAX(stats.date) FROM user LEFT JOIN stats ON stats.user_id = user.id 

не проверял, честно говорю.

Comments

[Ivan Komarov]

неа. во-первых тут нет отсечки по пользователю. а во-вторых если её добавить будет выводится самая старшая дата среди записей всех пользователей. Я там ниже привел правильный вариант.

[Ivan Komarov]

был не прав, с WHERE user.id = ваш запрос работает правильно.

[Sorbing]

Да, в данном запросе условие WHERE user.id = 1 нельзя опускать, иначе джойнится последняя дата любого user.id.
Для выбора одной записи будет работать. Но не для всех записей таблицы.
Хотя это моя оплошность — я привел пример с WHERE U.id = 1, но не указал что это опционально. Спасибо.

Answer 5

[egorinsk]

Я бы лучше сделал SELECT user_id, MAX(date) FROM stats GROUP BY user_id, а потом бы приджойнил юзеров запросом из приложения вроде SELECT * FROM users WHERE user_id IN (...). Что-то я сомневаюсь, что четырехэтажные запросы с подзапросами будут быстро работать, да еще и в MySQL.

Answer 6

[Shedal]

Не работает ваш запрос потому, что вы из подзапроса обратно выбираете тот же самый user_id, который «подали вовнутрь». Иными словами, ваш запрос идентичен следующему:

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S ON S.user_id = U.id
WHERE U.id = 1

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

SELECT u.*, MAX(s.date) max_date
FROM user u
LEFT JOIN stats s
  ON s.user_id = u.id
WHERE u.id = 1
GROUP BY u.id

Comments

[Ivan Komarov]

Только с группировкой есть один нюанс. Для того чтобы получить значение поле type из таблицы stat нужно добавить еще один JOIN к stat. И если при этом date не уникально, вернется не одна запись.

[Shedal]

FreeTibet, это да. Но в условии про это ничего не было ;-) Но замечание верное.

[Sorbing]

Я говорил, что "… нужно получить дополнительные поля, которые я не указывал в упрощенной схеме..".
В табл. stats есть еще 5 полей, которые необходимо получить. Считаю вариант, предложенный FreeTibet оптимальным. По Вашему, в моем случае, нужно JOIN'ить таблицу stats для каждого поля этой таблицы.
Если нужно только одно поле (дата) — тогда да. Все же спасибо.

[Sorbing]

Читаю:

Не работает ваш запрос потому, что вы из подзапроса обратно выбираете тот же самый user_id, который «подали вовнутрь»

ОК, я передаю в условие склейки просто число = ID юзера:

SELECT U.*, S.*
FROM `user` AS U
LEFT JOIN stats AS S ON 1 = (
  SELECT S2.user_id
  FROM stats AS S2
  WHERE S2.user_id = 1 # это условие вообще не работает - выбираются все записи из stats
  ORDER BY S2.date DESC
  LIMIT 1
)

Почему же я ожидаю, что используя условие LIMIT 1 в подзапросе — должна быть «склеена» только одна запись из таблицы stats? А с таким запросом к каждому юзеру джойнятся все записи из stats.

[Shedal]

Почему же я ожидаю, что используя условие LIMIT 1 в подзапросе — должна быть «склеена» только одна запись из таблицы stats?

Не знаю, почему вы этого ожидаете :) LIMIT 1 у вас помогает только одному — что из подзапроса вернётся только одно значение. Но это не меняет сути джоина. Он выберет ВСЕ записи, подходящие под условие. А вообще, как я уже писал выше, делать такой подзапрос бессмысленно. Он будет эквивалентен просто джоину по условию s.user_id = u.id. Постарайтесь понять, почему. Если не получится — задайте более конкретные вопросы.

[Sorbing]

Все, разобрался с внутренними противоречивыми чувствами, или проще — я тупил, это же JOIN :). Благодарю за помощь. Буду пересматривать структуру БД.

[Ivan Komarov]

В общем я тут подумал, правильный путь это доработанный вариант shedal:

SELECT u.*, s.date, s.type
FROM user u
LEFT JOIN (
  SELECT ss.user_id, MAX(ss.date) AS max_date
  FROM stats AS ss
  GROUP BY ss.user_id
) AS ms
ON ms.user_id = u.id
LEFT JOIN stats AS s
  ON s.user_id = ms.user_id AND s.date = ms.max_date

По идее при таком запросе будет всего лишь три выборки. Ну и, естественно, связка user_id + date должна быть уникальна, иначе записи задвоятся, но это можно решить с помощью DISTINCT или того же GROUP BY.

[Sorbing]

Исходя из первоначальной задачи, я не совсем понимаю предназначение второго джойна LEFT JOIN stats AS s. В лабораторном примере user_id + date не обязаны быть уникальными, поскольку PK состоит из user_id + date + type. Но это вроде как сути задачи не меняет — нужно получить _одну_ последнюю запись статистики.

1. Например нужно получить последнюю статсу по любому типу (type):

SELECT u.id, u.*, ms.*
FROM user u
LEFT JOIN ( # фильтруем записи с последней датой
  SELECT ss.user_id, MAX(ss.date), ss.type AS max_date
  FROM stats AS ss
  GROUP BY ss.user_id
) AS ms ON ms.user_id = u.id 

… получили. На тестовых данных все сходится. Второй джойн не нужен.

2. Нужно получить последнюю статистику с учетом типа:

SELECT u.id, u.*, ms.*
FROM user u
LEFT JOIN (
  SELECT ss.user_id, MAX(ss.date), ss.type AS max_date
  FROM stats AS ss
  WHERE ss.type = 'тип №8' # дополнительное условие фильтрации по типу
  GROUP BY ss.user_id
) AS ms ON ms.user_id = u.id 

Я пока не вижу надобности в дополнительном джойне. Может у нас не сходятся представления об условиях задачи? Или в моем запросе (без втого джойна) мы будем получать дополнительные поля таблицы stats в случайном порядке из-за группировки?

Answer 7

[MikhailEdoshin]

А если LIMIT убрат, он со всеми записями соединит? В SQLite, например, можно использовать внутренний идентификатор ряда без лимита, т. е. это было бы:

SELECT u.*, s.*
  FROM user u
  JOIN stat s ON s.rowid = (
       SELECT t.rowid
         FROM stat t
        WHERE t.user_id = u.id
     ORDER BY t.date DESC)
 WHERE u.id = ?

но там явно оговаривается, что (SELECT y ...) в выражении возвращает первую совпавшую запись, а не все; не знаю, как с этим в MySQL.

Comments

[Sorbing]

Если убрать LIMIT 1 из подзапроса, получим: "[Err] 1242 — Subquery returns more than 1 row"

Answer 8

[bugman]

Если таблица stats представляет собой slowly changing dimensions и перформанс запроса на получения актуальной статистики важен, я бы подумал о других подходах как например денормализовать stats и хранить актуальные значения статистики в отдельной таблице (или даже у юзера), stats же пользовать как аудит изменений.

Comments

[Sorbing]

Я уже думаю об этом). Юзеров предполагается ~10 000, по каждому юзеру в день добавляется до 2-3 тыщ записей статисти. Тут вопрос в том, что есть ТИП записи в таблице статистики. И держать последнюю дату придется по каждому типу. Типы добавляются по мере необходимости, предполагается 10-20 типов в итоге.
Получается, в статистике есть допустим, 3 поля с данными юзера + дата + тип.
Если хранить последние данные истории (статистики) в самой таблице user:
1. По мере необходимости нужно будет добавлять новые поля когда появляется новый ТИП статистических данных;
2. В итоге полей будет 3 * N типов, это еще плюс 30-60 полей к основным данным юзера. Как-то тоже не гуд.
Может у Вас есть какие-то мысли по этому поводу? Спасибо.

Answer 9

[bugman]

Если не нравится паттерн с созданием аттрибутов статистики в сущности клиента, попробуйте тогда, в самом простом случае, держать одну таблицу для актуальных значений статистики с PK [user_id, stat_type] + таблицу с историческими значениями (аудит таблицу), которую будете сопровождать на триггерах, в случае изменении первой.

В еще более хитром случае, эти таблицы можно и объединить (как у вас сейчас — изначально), но для ускорения запросов добавить флаг current, который будет либо 1 либо NULL + составной индекс [current, user_id].
Геммор триггеров из предыдущего варианта уйдет, заменит его геммор сопровождения флага current.
Всевозможные варианты организации подобных исторических справочников с "+" и "-" описаны в википедийной статье на тему «slowly changing dimensions».

Задайте себе вопрос, кроме багтрейсов и редкой аналитики, Вам действительно принципиально гонять запросы на получение и исторических и актуальных данных по одной таблице? Если нет — первый вариант самое то + партицирование и удаление самых старых партиций на шедулере (по вкусу).

Comments

[Sorbing]

Как хорошо, что на хабре уже есть перевод статьи с вики Slowly changing dimension. Инглиш читаю в парес с GTranslate'ом.

Если не нравится паттерн с созданием аттрибутов статистики в сущности клиента..

Мне не то, чтобы не нравится, я использую этот подход, когда набор данных статический, либо меняется редко (~ раз в полгода). А тут динамика типов статистики довольно динамична. И меня смущает итоговый набор в + "...30-60 полей к основным данным юзера." При этом данные статистики обновляются по каждой записи ~ раз в минуту.

Вам действительно принципиально гонять запросы на получение и исторических и актуальных данных по одной таблице?

Нет, но денормализацию я обычно провожу после проектирования полной структуры БД.
Я просто прикидываю что количество запросов на выборку = запросам на вставку, ну может SELECT'ов будет на 20% больше. В оригинале это не юзеры, а прокси сервера и после запроса и отработки прокси — логируем результат. Юзерами, пожалуй, не стоило называть лабораторную сущность, слишком упростил.
Получается для одной таблицы статистики — мы вставили новый лог и забыли. А в случае с дополнительной таблицей с актуальными данными, нужно каждый раз проверять существование записи и либо вставлять новую, либо обновлять существующую. Вот этот момент меня и смущает.
Я с ходу не могу сказать что будет быстрее. И как лучше распределить нагрузку — на выборку или на вставку.
Похоже нужно проверять на средних объемах данных.
Спасибо за подсказку и за статью, не читал.