Mysql非常慢的子查询优化

Question

我正在用大量数据构建sql查询，但是查询太慢了。

我有三张桌子：movies，movie_categories，skipped_movies

movies表是规范化的，我试图根据类别查询电影，同时从skipped_movies表中排除ids。

然而，我试图在我的查询中使用WHERE IN和NOT。

movies表大约有。200万行(id、name、score)

movie_categories方法。500万(id，movie_id，category_id)

skipped_movies大约有。1k行(id、movie_id、user_id)

当skipped_movies表非常小(10-20行)时，查询非常快。(大约40-50毫秒)，但是当表得到1k左右的数据时，我在查询中得到大约7到8秒的数据。

这是我正在使用的查询。

选择SQL_NO_CACHE *从movies id IN (从movie\_categories选择movie\_id category\_id = 1)和id NOT IN (从skipped\_movies选择user\_id = 1)和score <= 9按score DESC限制1的顺序选择；

我试过很多方法，但这是最快的方法。我甚至没有在任何程度上尝试过EXISTS方法。

我使用SQL_NO_CACHE只是为了测试。

我猜按规定订货的速度很慢。

Answer 1

假设(movie_id，category_id)在movies_categories表中是唯一的，我将使用join操作而不是子查询获得指定的结果。

为了排除“跳过”的电影，反连接模式就足够了.这是一个左外部联接，用于在skipped_movies中查找匹配的行，然后在WHERE子句中使用一个谓词来排除找到的任何匹配项，只留下没有匹配的行。

SELECT SQL_NO_CACHE m.*
  FROM movies m
  JOIN movie_categories c 
    ON c.movie_id = m.id 
   AND c.category_id = 1
  LEFT
  JOIN skipped_movies s
    ON s.movie_id = m.id
   AND s.user_id = 1
 WHERE s.movie_id IS NULL
   AND m.score <= 9
 ORDER
    BY m.score DESC
 LIMIT 1

适当的索引可能会提高性能..。

... ON movie_categories (category_id, movie_id)
... ON skipped_movies (user_id, movie_id)

Answer 2

大多数IN/NOT IN查询可以使用JOIN/LEFT来表示，这通常提供最佳性能。

将查询转换为使用联接：

SELECT m.*
FROM movies m
JOIN movie_categories mc ON m.id = mc.movie_id AND mc.category_id = 1
LEFT JOIN skipped_movies sm ON m.id = sm.movie_id AND sm.user_id = 1
WHERE sm.movie_id IS NULL
AND score <= 9
ORDER BY score DESC
LIMIT 1

Answer 3

你的询问似乎没问题。只是一个小小的需要。可以将*替换为表中的列/属性名称。它将使这个查询比以往任何时候都快。因为*操作真的很慢