mysql - SQL查询性能

Question

这些 SQL 查询中哪一个会执行得更好？假设给出相同的结果:

SELECT mov_title, mov_year, mov_dt_rel, act_fname, act_lname, dir_fname, dir_lname
FROM movie 
JOIN movie_cast 
ON movie.mov_id = movie_cast.mov_id
JOIN actor
ON actor.act_id = movie_cast.act_id
JOIN movie_direction
ON movie.mov_id = movie_direction.mov_id
JOIN director
ON director.dir_id = movie_direction.dir_id
WHERE movie.mov_id IN (SELECT mov_id 
FROM rating WHERE rev_id IN (SELECT rev_id
FROM reviewer
WHERE rev_name IS NULL));

SELECT mov_title, mov_year, mov_dt_rel, dir_fname, dir_lname, 
       act_fname, act_lname
       FROM movie a, movie_direction b, director c, 
                rating d, reviewer e, actor f, movie_cast g
       WHERE a.mov_id=b.mov_id
AND  b.dir_id=c.dir_id 
 AND a.mov_id=d.mov_id 
  AND  d.rev_id=e.rev_id 
   AND  a.mov_id=g.mov_id 
    AND g.act_id=f.act_id 
     AND e.rev_name IS NULL;

一般来说，除了返回的行数之外，还有哪些查询性能考虑因素。

Answer 1

IN (SELECT ...) 可能是性能 killer 。 通常最好避免这种构造。

第一个表述(使用“JOIN..ON”)是本世纪执行 SQL 的“正确”方法。第二个(“逗号连接”)是老式的；不要使用它。 (这不是性能差异，只是风格差异。)