MySQL：查找不参与关系的行

Question

我有两个表：'movies'和'users'。 它们之间存在n:m的关系，描述了用户看过哪些电影。这使用一个名为'seen'的表来描述。 现在，我想要找出给定用户未曾观看的所有电影。 我的当前解决方案如下：

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

这个方法运行良好，但似乎不太适合扩展。有更好的方法吗？

- tliff

---

这个可以正常工作，但似乎不太适合扩展。有更好的方法吗？你尝试过在这个查询上使用<a href="http://dev.mysql.com/doc/refman/5.0/en/using-explain.html">EXPLAIN</a>吗？ - VolkerK

如果它没有良好的扩展性，那么您的索引就不够有效。那么你的索引是什么？ - dkretz

Answer 1

以下是一种典型的方法，可以查询而不使用你展示的子查询方法。这可能满足 @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（...）”更好。

同样，您必须测试每个解决方案并将结果进行比较，以确保选择性能最佳的方案。 选择方案之前不测量性能是浪费时间的。

- Bill Karwin

---

我总是忘记这个“OUTER JOIN”的技巧。谢谢！ - Koen.

Answer 2

您的查询不仅有效，而且是解决问题的正确方法。也许您可以找到另一种解决问题的方法？例如，在您的外部选择中使用简单的LIMIT语句，即使对于大型表格，速度也非常快。

- Darrin

Answer 3

这是您的联接表，所以是的，这看起来像是正确的解决方案。实际上，您正在从MOVIES的总数中“减去”SEEN（一个用户的电影ID集），得到该用户未观看的电影。

这被称为“负联接”，不幸的是NOT IN或NOT EXISTS是最好的选择。（我很想看到一种类似于INNER/OUTER/LEFT/RIGHT联接的负联接语法，但ON子句可以是一个减法语句）。

@Bill的无子查询解决方案应该可以工作，尽管他指出测试两种方法的性能是一个好主意。我怀疑无论是否有子查询，整个SEEN.ID索引（当然还有整个MOVIE.ID索引）都将以两种方式进行评估：这将取决于优化器如何处理它。

- Godeke

Answer 4

如果您的数据库管理系统支持位图索引，可以尝试使用它们。

- abababa22

---

他给这个问题打上了“mysql”的标签。MySQL不支持位图索引。 - Bill Karwin

哎呀，我没看标签。:( - John Smith