mysql - 如何优化需要查找要连接的列的两个表的 JOIN？

Question

我正在对两个表进行简单的 INNER JOIN，附加要求是每个表必须首先与单独的 1:n 表连接以获得正确的连接值。用一个简单的例子来说明，我有三个表UserInstalls(UserId，InstallDate)，UserConversion(UserId，ConversionDate)和UserAccounts(UserId，帐户ID)。我想要(AccountId、InstallDate、ConversionDate)的最终结果。

UserInstalls+--------+-------------+| UserId | InstallDate |+--------+-------------+|      1 | 2015-01-11  ||      2 | 2015-03-21  ||      3 | 2015-02-05  ||      4 | 2014-06-01  ||      5 | 2014-01-23  ||      6 | 2015-02-14  |+--------+-------------+

UserConversion+--------+----------------+| UserId | ConversionDate |+--------+----------------+|      7 | 2015-01-15     ||      8 | 2015-03-03     ||      9 | 2015-08-01     ||     10 | 2015-01-02     ||     11 | 2014-01-24     ||     12 | 2015-02-17     |+--------+----------------+

UserAccounts+--------+-----------+| UserId | AccountId |+--------+-----------+|      1 |         1 ||      7 |         1 ||      2 |         2 ||      8 |         2 ||      3 |         3 ||      9 |         3 ||      4 |         4 ||     10 |         4 ||      5 |         5 ||     11 |         5 ||      6 |         6 ||     12 |         6 |+--------+-----------+

I can simply use subqueries to lookup the AccountID, and then join the results of each subquery. In this trivial case this works fine. In practical applications involving (10,000+ rows), this is not efficient and I run into spool limitations.

SELECT x.AccountID, InstallDate, ConversionDate
FROM (SELECT AccountID, InstallDate FROM UserInstalls
    JOIN UserAccounts
    ON UserInstalls.UserId = UserAccounts.UserId) x
JOIN (SELECT AccountID, ConversionDate FROM UserConversion 
    JOIN  UserAccounts
    UserConversion.UserId = UserAccounts.UserId) y
ON x.AccountId = y.AccountId;

有什么想法可以在不使用子查询或通过脚本创建多个表的情况下执行此操作吗？交叉连接？关系划分？

谢谢!

Answer 1

您应该能够将表连接在一起而无需子查询:

SELECT ua1.AccountID, ui.InstallDate, uc.ConversionDate
FROM UserAccounts ua1
JOIN UserInstalls ui on ua1.UserId = ui.UserId
JOIN UserAccounts ua2 on ua1.AccountID = ua2.AccountID
JOIN UserConversion uc on uc.UserId = ua2.UserId