mysql 多个联合到多个左连接

Question

我一直在尝试将包含多个联合的查询更改为包含多个左连接的查询。

联合查询是

SELECT DISTINCT user_id,typ
FROM   (SELECT user_id,
           'review' AS typ
    FROM   review
    WHERE  time > '2011-12-01'

    UNION

    SELECT user_id,
           'invite' AS typ
    FROM   invite
    WHERE  time_created > '2011-12-01'
            OR time_action > '2011-12-01'

    UNION

    SELECT user_id,
           'voter' AS typ
    FROM   vote
    WHERE  time > '2011-12-01'

    UNION

    SELECT user_id,
           'voted' AS typ
    FROM   review
    WHERE  review_id IN (SELECT object_id
                         FROM   vote
                         WHERE  vote = 1
                                AND type = 'review'
                                AND time > '2011-12-01')

    UNION

    SELECT user_id,
           'comment' AS typ
    FROM   comment
    WHERE  time > '2011-12-01'

    UNION

    SELECT user_id,
           'joined' AS typ
    FROM   USER
    WHERE  updated_time > '2011-12-01'
            OR last_login_date > '2011-12-01'

    UNION

    SELECT user_id,
           'wishlist' AS typ
    FROM   user_wishlist
    WHERE  time > '2011-12-01'

    UNION

    SELECT introducer,
           'introducer' AS typ
    FROM   introduction
    WHERE  time > '2011-12-01'

    UNION

    SELECT user_id,
           'answer' AS typ
    FROM   answer
    WHERE  time > '2011-12-01') T
WHERE  T.user_id IN (SELECT user_id
                 FROM   USER
                 WHERE  user_id < 5245762) 

我已经能够将其更改为这样的内容:

SELECT t1.user_id,
   IF(t2.user_id, 'yes', 'no')  AS review,
   IF(t3.user_id, 'yes', 'no')  AS invite,
   IF(t4.user_id, 'yes', 'no')  AS voter,
   IF(t5.user_id, 'yes', 'no')  AS voted,
   IF(t6.user_id, 'yes', 'no')  AS comment,
   IF(t7.user_id, 'yes', 'no')  AS joined,
   IF(t8.user_id, 'yes', 'no')  AS wishlist,
   IF(t9.user_id, 'yes', 'no')  AS introducer,
   IF(t10.user_id, 'yes', 'no') AS answer

FROM   (SELECT user_id
    FROM   user
    WHERE  user_id < 5245762) t1

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   review
              WHERE  time > '2011-12-01') t2

          ON t1.user_id = t2.user_id

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   invite
              WHERE  time_created > '2011-12-01'
                      OR time_action > '2011-12-01') t3

          ON t1.user_id = t3.user_id

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   vote
              WHERE  time > '2011-12-01') t4

          ON t1.user_id = t4.user_id

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   review
              WHERE  review_id IN (SELECT object_id
                                   FROM   vote
                                   WHERE  vote = 1
                                          AND type = 'review'
                                          AND time > '2011-12-01')) t5

          ON t1.user_id = t5.user_id

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   comment
              WHERE  time > '2011-12-01') t6

          ON t1.user_id = t6.user_id

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   user
              WHERE  updated_time > '2011-12-01'
                      OR last_login_date > '2011-12-01') t7

          ON t1.user_id = t7.user_id

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   user_wishlist
              WHERE  time > '2011-12-01') t8

          ON t1.user_id = t8.user_id

   LEFT JOIN (SELECT DISTINCT introducer AS user_id
              FROM   introduction
              WHERE  time > '2011-12-01') t9

          ON t1.user_id = t9.user_id

   LEFT JOIN (SELECT DISTINCT user_id
              FROM   answer
              WHERE  time > '2011-12-01') t10

          ON t1.user_id = t10.user_id  

现在我知道两个查询的结果会有点不同，第一个查询用于提供类似的结果

user_id    typ

<小时/>

1          invite
2          invite
1          voter
1          voted
2          voted

左连接查询提供的结果类似于

user_id    invite    voter     voted

<小时/>

1           yes       yes        yes
2           yes       no         yes

现在，当user_id列表大约小于500时(联合查询中的最后一个WHERE和左连接查询中的表t1)，左连接查询更快，之后联合查询更快。但在运行左连接查询后，我发现联合查询缺少某些用户的数据(没有该 user_id 的行)，而左连接查询提供了准确的结果。有没有一种方法可以使我的左连接查询比联合查询更快？或者修复我的联合查询以准确提供结果？

PS:提供 user_id 的 WHERE (T.user_id in ... in union query) (SELECT user_id from user ... in left join query) 条件只是这里的一个示例，它可以是自定义查询来自提供一列多行 user_ids 的多个表

Answer 1

您应该在 mysql 查询前面使用“EXPLAIN”。它将显示查询的 mysql 执行计划以及可能的性能瓶颈。

参见mysql引用:https://dev.mysql.com/doc/refman/5.0/en/explain.html

解释仅适用于选择查询，但不适用于更新或删除。

我们无法为您提供更多提示，因为您没有发布表的数据库架构定义，这些定义将显示您正在使用哪些字段类型以及定义了哪些键。