mysql - 如何加速多连接的sql查询的执行？

Question

我正在处理 MySql 数据库。我需要将多个(超过 10 个)表的信息合并到一个表中。为此，我遵循典型的连接方式。

Select * from 
table_1 
Join table_2
on(table_1.id = table_2.id)
Join table_3
on(table_1.id = table_3.id)

它有效，但我在执行期间遭受了很多痛苦。还有其他优化代码的好方法吗？以下是我的代码示例:

SELECT
distinct 
u.Id, 
oc.dt,
Daily_Number_Outgoing_Calls,     
Daily_Number_Incoming_Calls,    
Daily_duration_Outgoing_Calls

FROM
creditfix.users u

JOIN

#1 Daily_No_Out_Calls
    (
        SELECT
        cl.uId,SUBSTRING(DATE,1,10) as dt,
        count(1) as Daily_Number_Outgoing_Calls

        From creditfix.call_logs as cl
            WHERE
                cl.`type`=2 #out going calls only
        GROUP by cl.uId,dt
    ) oc
    ON (u.Id=oc.Uid)

#2 Daily_No_In_Calls
    JOIN
    (
        SELECT
        cl.uId, SUBSTRING(DATE,1,10) as dt,
        count(1) as Daily_Number_Incoming_Calls
        From creditfix.call_logs as cl
        WHERE
            cl.`type`=1 #incoming calls only
        GROUP by cl.uId,dt
    ) ic
    ON (u.Id=ic.Uid)

#3 Daily_duration_Out_Calls
     JOIN
    (
        SELECT
        cl.uId,SUBSTRING(DATE,1,10) as dt, 
        (sum(duration)) as Daily_duration_Outgoing_Calls
        From creditfix.call_logs as cl
        WHERE
            cl.`type`=2 #out going calls only
        GROUP by cl.uId,dt
    ) od
    ON (u.Id=od.uid)
    # It goes on like this...

Answer 1

看起来您不需要为每一列使用单独的子查询，您应该能够在单个子查询中执行它们。

我也不认为您应该在主查询中使用 DISTINCT。

SELECT 
    u.Id, 
    cl.dt,
    cl.Daily_Number_Outgoing_Calls,     
    cl.Daily_Number_Incoming_Calls,    
    cl.Daily_duration_Outgoing_Calls,   
    cl.Daily_duration_Incoming_Calls #.... for keep on adding like this

FROM creditfix.users u
JOIN (
    SELECT uId, SUBSTRING(DATE, 1, 10) AS dt,
        SUM(`type`=2) AS Daily_Number_Outgoing_Calls,
        SUM(`type`=1) AS Daily_Number_Incoming_Calls,
        SUM(IF(`type`=2, duration, 0)) AS Daily_duration_Outgoing_Calls,
        SUM(IF(`type`=1, duration, 0)) AS Daily_duration_Incoming_Calls
    FROM creditfix.call_logs as cl
    GROUP BY uId, dt) AS cl
ON u.Id = cl.uId

参见 multiple query same table but in different columns mysql用于子查询中用于获取所有计数的逻辑。