MySQL: поиск строк, которые не участвуют в отношениях

У меня есть две таблицы: «фильмы» и «пользователи». Между ними существует отношение n:m, описывающее, какие фильмы смотрел пользователь. Это описано с помощью таблицы «просмотрено». Теперь я хочу узнать для данного пользователя все фильмы, которые он не видел. Мое текущее решение выглядит так:

SELECT *
FROM movies 
WHERE movies.id NOT IN (
     SELECT seen.movie_id 
     FROM seen 
     WHERE seen.user_id=123
)

Это работает нормально, но, похоже, не очень хорошо масштабируется. Есть ли лучший подход к этому?


mysql sql join entity-relationship
person tliff    schedule 12.02.2009    source источник
comment
› Это работает нормально, но не очень хорошо масштабируется. Есть ли лучший подход к этому? Пробовали ли вы ‹a href=dev. mysql.com/doc/refman/5.0/en/› по этому запросу?   -  person VolkerK    schedule 13.02.2009
comment
Если он плохо масштабируется, то ваша индексация не эффективна. Какие у вас индексы?   -  person dkretz    schedule 13.02.2009

Ответы (4)


arrow_upward
42
arrow_downward

Вот типичный способ выполнения этого запроса без использования показанного вами метода подзапроса. Это может удовлетворить просьбу @Godeke увидеть решение на основе объединения.

SELECT * 
FROM movies m
 LEFT OUTER JOIN seen s
 ON (m.id = s.movie_id AND s.user_id = 123)
WHERE s.movie_id IS NULL;

Однако для большинства марок баз данных это решение может работать хуже, чем решение для подзапросов. Лучше всего использовать EXPLAIN для анализа обоих запросов, чтобы увидеть, какой из них будет работать лучше с учетом вашей схемы и данных.

Вот еще один вариант решения подзапроса:

SELECT * 
FROM movies m
WHERE NOT EXISTS (SELECT * FROM seen s 
                  WHERE s.movie_id = m.id 
                    AND s.user_id=123);

Это коррелированный подзапрос, который необходимо оценивать для каждой строки внешнего запроса. Обычно это дорого, и ваш исходный пример запроса лучше. С другой стороны, в MySQL «NOT EXISTS» часто лучше, чем «column NOT IN (...)».

Опять же, вы должны протестировать каждое решение и сравнить результаты, чтобы быть уверенным. Выбирать какое-либо решение без измерения производительности — пустая трата времени.

person Bill Karwin    schedule 13.02.2009
comment
Я просто постоянно забываю об этом OUTER JOIN трюке. Спасибо! - person Koen.; 02.10.2015

arrow_upward
4
arrow_downward

Мало того, что ваш запрос работает, это правильный подход к проблеме, как указано. Возможно, вы найдете другой подход к проблеме? Простой LIMIT для вашего внешнего выбора должен быть очень быстрым, например, даже для больших таблиц.

person dwc    schedule 12.02.2009

arrow_upward
4
arrow_downward

Видна ваша таблица соединений, так что да, это похоже на правильное решение. Вы эффективно «вычитаете» набор идентификаторов фильмов в SEEN (для пользователя) из общего количества в MOVIES, в результате чего для этого пользователя остаются непросмотренные фильмы.

Это называется "негативным соединением", и, к сожалению, НЕ ВНУТРИ или НЕ СУЩЕСТВУЕТ - лучшие варианты. (Мне бы хотелось увидеть синтаксис отрицательного соединения, который был бы похож на соединения INNER/OUTER/LEFT/RIGHT, но где предложение ON могло бы быть оператором вычитания).

Решение @Bill без подзапроса должно работать, хотя, как он отметил, было бы неплохо проверить ваше решение на производительность в обоих направлениях. Я подозреваю, что подзапрос или нет, но весь индекс SEEN.ID (и, конечно, весь индекс MOVIE.ID) будет оцениваться в обоих направлениях: это будет зависеть от того, как оптимизатор обрабатывает его оттуда.

person Godeke    schedule 12.02.2009

arrow_upward
0
arrow_downward

Если ваша СУБД поддерживает растровые индексы, вы можете попробовать их.

person John Smith    schedule 13.02.2009
comment
Он пометил вопрос «mysql». MySQL не поддерживает растровые индексы. - person Bill Karwin; 13.02.2009
comment
Ой, я не посмотрел на бирку. :( - person John Smith; 13.02.2009