MySQL中的LEFT OUTER JOIN与SUBSELECT对比

Question

我有一个名为table1的表格，其中包含3列column1、column2和column3。

column1和column2是与另外两个表格相关的FOREIGN KEY。然而，column3中的数据来自n个表格。

例如，让我们考虑Facebook。为了显示活动，它可能维护一个表格，其中可能包含user1 photoliked photo1或user1 statusliked status1。因此，在这种情况下，column3不能是与特定表格相关的FOREIGN KEY。

现在有两种获取真实数据的方法：

第一种方法-

SELECT user_id,
       verb_id,
       CASE WHEN verb_id = photoliked THEN
            (SELECT photo_name FROM photos WHERE photo_id = column3) -- getting the desired data from the third column
         WHEN verb_id = statusliked THEN
            (SELECT status FROM statustable WHERE status_id = column3) 
         ELSE '' END AS performedon
FROM table1
     JOIN table2 ON user_id = user_id  -- joining the first column
     JOIN table3 ON verb_id = verb_id  -- joining the second column

第二种方法 -

SELECT user_id,
       verb_id,
       CASE WHEN verb_id = photoliked THEN
            p.photo_name
         WHEN verb_id = statusliked THEN
            s.status
         ELSE '' END AS performedon
FROM table1
     JOIN table2 ON user_id = user_id  -- joining the first column
     JOIN table3 ON verb_id = verb_id  -- joining the second column
     LEFT JOIN photos p ON p.photo_id = column3  -- joining the column3 with specific table 
     LEFT JOIN statustable s ON s.status_id = column3

问题

哪种方法更好地检索数据？哪一种查询较少消耗资源？

- JHS

---

可能是重复的 http://stackoverflow.com/a/10684539/1239506 - Moyed Ansari

不，这不是重复的。在那个问题中，在WHERE子句中有一个 IN，而SUBSELECT不依赖于主查询的任何列。 - JHS

第二个查询更好... - Sashi Kant

可以进行一次解释以分析查询。 - sel

第二个查询更好，优化，独立和更快。而第一个查询是依赖性的，并且需要更多的执行时间。 - Muhammad Raheel

Answer 1

我认为使用JOIN会更快，因为它只需要执行一次查询，而且我会尝试在JOIN中过滤verb_id

SELECT user_id,
   verb_id,
   COALESCE(p.photo_name, s.status) AS performedon
FROM table1
    JOIN table2 ON user_id = user_id  -- joining the first column
    JOIN table3 ON verb_id = verb_id  -- joining the second column
    LEFT JOIN photos p ON verb_id = 'photoliked' AND p.photo_id = column3  -- joining the column3 with specific table 
    LEFT JOIN statustable s ON verb_id = 'statusliked' AND s.status_id = column3

- vearutop

Answer 2

您可以采用这种方法：

SELECT t.user_id,
       t.verb_id,
       p.photo_name  AS performedon
FROM table1 AS t
     JOIN table2 AS t2  ON t2.user_id = t.user_id  
     JOIN table3 AS t3  ON t3.verb_id = t.verb_id 
     JOIN photos AS p  ON  p.photo_id = t.column3  
                       AND t.verb_id = 'photoliked'

UNION ALL

SELECT t.user_id,
       t.verb_id,
       s.status  
FROM table1 AS t
     JOIN table2 AS t2  ON t2.user_id = t.user_id
     JOIN table3 AS t3  ON t3.verb_id = t.verb_id
     JOIN statustable AS s  ON  s.status_id = t.column3
                            AND t.verb_id = 'statusliked' ;

- ypercubeᵀᴹ

Answer 3

第二个查询会更快，原因是第一个查询包含了所谓的相关子查询。子查询与主查询中的记录有关联。因此，对于主查询中的每个匹配记录，都需要运行子查询一次。在您的情况下，它无法运行子查询，直到确定主查询中verb_id的值。这是需要运行大量查询。

在第一个查询上执行EXPLAIN应该会显示出这个问题。当您在EXPLAIN中看到这个问题时，通常会引起警惕。

- Brent Baisley